Security Group
 
Note:
1. These query examples are based on The Raiser's Edge SAMPLE data
2. For the checkboxes, RIGHTS = 15 (YES) and RIGHTS =14 or 0 (NO)
3. The SUPERSECTOR_ID identifies which permission area in the data. This number is not standard and would depend on the database and the user group security set up.
5. Items listed with N/A in the list only appears in the SECURITYSECTOR table when one of the rights is unchecked (Add / Edit / View / Delete).
6. To print a report with details on the Security Group, go to Admin > Security > right click on the desired Security Group > select Preview

Example query for the list of “Record Types” (#1 above):
/* SQL to display a list of Record Types available to a specific security group and user */
SELECT SECURITYGROUP.NAME as 'SECURITY GROUP', USERS.NAME as 'USER NAME',
SECURITYSECTOR.DESCRIPTION as 'SUB DESCRIPTION', SECURITYSECTOR.RIGHTS as 'SUB RIGHTS',
(SELECT Checkbox =
CASE SECURITYSECTOR.RIGHTS
WHEN 15 THEN 'YES'
ELSE 'NO'
END) as CHECKMARK

FROM SECURITYGROUP
INNER JOIN SECURITYSECTOR ON SECURITYGROUP.SECURITYGROUP_ID =SECURITYSECTOR.SECURITYGROUP_ID
INNER JOIN SECURITYUSER ON SECURITYGROUP.SECURITYGROUP_ID = SECURITYUSER.SECURITYGROUP_ID
INNER JOIN USERS ON SECURITYUSER.USERS_ID = USERS.USER_ID

WHERE SECURITYGROUP.NAME='Data Entry'
and USERS.NAME='David'
and SECURITYSECTOR.SUPERSECTOR_ID = 1 -- Record Types

ORDER BY SECURITYSECTOR.SEQUENCE


For #2 and #3 in the screen shot the following SQL displayed that information:
/* Display Miscellaneous Constituents Options
RIGHTS = 14 = No = Unchecked
RIGHTS = 15 = Yes = Checked
*/

SELECT SECURITYGROUP.NAME as 'SECURITY GROUP', USERS.NAME as 'USER NAME',
SECURITYSECTOR.DESCRIPTION as 'SUB DESCRIPTION', SECURITYSECTOR.RIGHTS as 'SUB RIGHTS', SECURITYSECTOR.TYPE, *

FROM SECURITYGROUP
INNER JOIN SECURITYSECTOR ON SECURITYGROUP.SECURITYGROUP_ID =SECURITYSECTOR.SECURITYGROUP_ID
INNER JOIN SECURITYUSER ON SECURITYGROUP.SECURITYGROUP_ID = SECURITYUSER.SECURITYGROUP_ID
INNER JOIN USERS ON SECURITYUSER.USERS_ID = USERS.USER_ID

WHERE SECURITYGROUP.NAME='Data Entry'
and SECURITYSECTOR.SUPERSECTOR_ID= 2 -- Misc Constituent Options
/*
--- USING SAMPLE DATA ---
NULL = Group Priviledges - shows all 23 when all deselected (all unmarked checkboxes)
Shows 14 when all is marked -

1 = Record Types
2 = Misc Constit Options
3 = Consituent Priviledges
15 = Action (Constituent) Priviledges
17 = Jobs Priviledges
19 = Memberships Priviledges
etc..
*/

ORDER BY SECURITYSECTOR.SEQUENCE


RIGHTS values are expressed as binary:

1 - (0001) - View
2 - (0010) - Add
4 - (0100) - Edit
8 - (1000) - Delete


For example, a RIGHTS value of 13 would be Binary 1101 which would mean

8 (1000) - Delete = On (check-mark in Security Module)
4 ( 100) - Edit   = On
2 (  00) - Add    = Off
1 (   1) - View   = On


The following chart shows all possible values for RIGHTS and what switches would be turned on.

0 = off (unchecked)
1 = on (checked)

 

Rights Table Definition

Decimal Value Delete Edit Add View
10001
20010
30011
40100
50101
60110
70111
81000
91001
101010
111011
121100
131101
141110
151111
Disclaimer: Blackbaud provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Blackbaud Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.