|
|
|
A common problem when moving to c-treeACE SQL is managing access permissions for a number of users. Frequently, there are groups of users with a defined role requiring the same granted privileges to data. And, of course, users may need to be added or removed over time. Up until now, table privileges must be individually granted. Having a role definition defined at the group level makes it much easier to grant privileges in bulk.
Group management of users is now available for c-treeACE SQL. Assigning users to established groups greatly simplifies SQL rights management.
Group definitions are administered and maintained within c-treeACE with already existing utilities such as the ctadmn Administrator utility, sa_admin Security Administration utility or the Security Administrator tool. All user and group definitions are managed from this central point and are available from both the core c-treeACE database engine as well as from c-treeACE SQL. These definitions are securely stored in the FAIRCOM.FCS user group security information file.
Once a group is created, GRANT privileges to the group from SQL as you would a user. When users are assigned to the group with the mentioned utilities, these privileges are immediately applied to that user.
Important: There remains a limitation that SQL group names cannot be the same as a SQL user name.
Example
To create 4 users belonging to two group definitions you could script something such as the following:
Create Groups
sa_admin -aADMIN -pADMIN -oga ODBCUSER -d ODBC_users
sa_admin -aADMIN -pADMIN -oga ORDERDESK -d Shipping_desk
GRANT SQL Group Privileges
isql -s grant_file.sql -u ADMIN -a ADMIN 6597@localhost:ctreeSQL
where grant_file.sql contains the following:
GRANT SELECT ON custmaster TO ODBCUSER
GRANT SELECT ON custorder TO ODBCUSER
GRANT SELECT ON orderitem TO ODBCUSER
GRANT SELECT ON itemmaster TO ODBCUSER
GRANT ALL ON custmaster TO ORDERDESK
GRANT ALL ON custorder TO ORDERDESK
GRANT ALL ON orderitem TO ORDERDESK
GRANT ALL ON itemmaster TO ORDERDESK
Now, any user assigned to ODBCUSER receive SELECT privilege, and likewise, users assigned to ORDERDESK. receive ALL privileges
Create Users
sa_admin -aADMIN -pADMIN -oua JOEBOB -w secret_password -g ODBCUSER
sa_admin -aADMIN -pADMIN -oua SALLYSUE -w secret_password -g ODBCUSER
sa_admin -aADMIN -pADMIN -oua ALICE -w secret_password -g ORDERDESK
sa_admin -aADMIN -pADMIN -oua WALTER -w secret_password -g ORDERDESK
Further privileges can be directly assigned to users on top of any assigned group privileges:
GRANT EXECUTE ON update_monthly_balance_proc TO ALICE
