Announcement

As of April 17, 2018, this site is now in read-only mode. To start new Blackbaud Developer’s discussions, you will find Developer Discussions in the Blackbaud CRM Community.

To post or respond to Blackbaud Community discussions, you will have to login using a www.blackbaud.com login. Learn how to login here. If you have questions or need assistance, please email community@blackbaud.com.

1.7k questions

3.2k answers

401 users

Categories

1.7k questions

3.2k answers

401 users

When I create a table using a table spec, permissions are not assigned to the new table. I am expecting the BBAPPFXSERVICEROLE to be granted rights to select from the table.

Currently, I use a post-deploy SQL script to assign rights. Is this the expected method to setup SQL database permissions?

I'm working with BBEC 2.9.

<TableSpec 
    xmlns="bb_appfx_table"
    xmlns:common="bb_appfx_commontypes" 
    ID="9d501351-6372-44ce-9c75-75969cfb035b"
    Name="USR_TESTTABLE"
    Description="Test table for permission demonstration."
    Author="Blackbaud Professional Services"
    Tablename="USR_TESTTABLE"   
  >

  <!-- define fields on the table -->
    <Fields>
      <TextField Name="COLUMN1" Length="20" Required="true"/>
      <NumberField Name="COLUMN2" Type="int" DefaultValue="10" />
    </Fields>
</TableSpec>
asked by bobstefkovich (580 points)

3 Answers

 
Best answer

I believe what you are doing is correct. I have had to write lot's of grant select, insert, update, delete, etc. for the BBAPPFXSERVICEROLE on both oob and custom tables before while doing BBIS work. I know 2 or 3 other people are following the same practice.

answered by chriswhisenhunt (3.9k points)
selected by bobstefkovich

If you are accessing these database objects from CLR data forms, business processes, etc. then you can grant them to BBAPPFXSERVICEROLE by using the <GrantExecuteList> element in any CLR catalog implementation element.

For example, correspondence business process grants rights to these objects.

<ProcessorComponent
    AssemblyName="Blackbaud.AppFx.Constituent.Catalog"
    ClassName="Blackbaud.AppFx.Constituent.Catalog.CorrespondenceProcess"
    >
    <common:GrantExecuteList>
        <common:GrantExecute>USP_CORRESPONDENCEPROCESS_GETPARAMETERS</GrantExecute>
        <common:GrantExecute>UFN_BUSINESSPROCESS_IDSETCANBECREATED</GrantExecute>
        <common:GrantExecute>UFN_BUSINESSPROCESS_IDSETEXISTS</GrantExecute>
    </GrantExecuteList>
</ProcessorComponent>

If all of your select, insert, and update logic is wrapped by a stored procedure, there should be no need to grant rights to tables and columns.

However, in some cases, it's not practical to implement everything in a stored procedure or function. I those cases you can also include one of the other grant lists.

  • GrantExecuteList
  • GrantSelectList
  • GrantUpdateList
  • GrantInsertList
  • GrantDeleteList

This applies to several features, not just business processes. You can define those grant list elements in any of these spec CLR implementations.

  • CLRDataForm
  • CLRDataList
  • CLRGlobalChange
  • CLRIDMapper
  • CLRPrompt
  • CLRSearchList
  • CLRSimpleList
  • CLRTaskStatusInfo
  • CLRTranslationFunction
answered by tommyvernieri (393 points)
Thanks Tommy.  I originally thought none of these problems we saw were associated with CLR code, but our last one was exactly this.

BBIS runs under whatever user the app pool is assigned to. Usually thats NT AUTHORITY\NETWORK SERVICE. You can either explicitly grant permissions to that user or (more reliably) grant them to BBAPPFXSERVICEROLE.

A SQL Script is the best way to do that. The only way around it would be to have BBIS running under a user that has the same access level as CRM, which would not be advisable.

answered by josephstyons (1.8k points)
...