Tip #30 – Modifying Permissions with SQL

User and group permissions can become very complicated, there are many different combinations of permissions for various database objects. It is always best to assign permissions to groups and assign users to the appropriate group. Permissions with Advantage are additive so the user will always have the highest permission assigned directly or inherited from the group(s) they are a member of. Valid permissions are listed below:

• SELECT
• INSERT
• UPDATE
• DELETE
• EXECUTE
• ACCESS
• INHERIT
• ALTER
• CREATE
• DROP
• WITH GRANT

Permissions are assigned using the GRANT SQL Statement. Permissions are removed using the REVOKE SQL statement. You can allow groups or users to assign permissions by specifying WITH GRANT when granting permissions. This allows the members of the group or individual users to assign these permissions to other users and groups.

// Assign Read permission on Customer table to Managers group

GRANT SELECT ON Customer TO Managers;

// Assign multiple permissions on Customer table

GRANT INSERT, UPDATE ON Customer TO Managers;

// Assign all permissions 

GRANT ALL ON Invoice TO Managers;

// Assign Insert permission on Customer table 

// to Managers group with grant permission

GRANT INSERT ON Customer TO Managers WITH GRANT;

// Remove Read permission from user chrisf on Customer table

REVOKE READ ON Customer FROM chrisf 

// Remove all Permissions from user chrisf on Invoice table 

REVOKE ALL ON Invoice FROM chrisf

You can also modify field permissions by specifying the field (column) name when specifying a permission( i.e SELECT(column name) ). Create permissions are assigned for each individual object type.

// Add Create Table permissions to Managers

GRANT CREATE TABLE TO Managers

// Add Create View permissions to Managers with grant

GRANT CREATE VIEW TO Managers WITH GRANT