Advantage Developer Zone

 
 
 

Assigning Data Dictionary Permissions

Wednesday, October 22, 2008

Advantage 9 included several new Database Roles which make managing database permissions easier. For example every user created in the database is a member of the DB:Public group. By assigning appropriate permissions to the DB:Public group any new user will immediately have the proper permissions required by your application.

However, rights are not automatically assigned to the DB:Public group and it can take some time to configure the rights manually. This is especially true if you have added many objects to your database during the development process. You can automate the process of assigning permissions using SQL Scripts

A Brief Overview of User Permissions

The administrative account (ADSSYS) and members of the DB:Admin group have full rights to all objects (i.e. Tables, Views, Stored Procedures, etc…) within the database. Other users and groups must be explicitly granted rights to access the database objects. There are two basic categories of permissions; Normal User Permissions and Administrative Permissions.

Normal user permissions include standard access to the objects. The READ, UPDATE, INSERT and DELETE permissions allow users to view and manipulate data in tables and views. The EXECUTE permission is required for a user to run a stored procedure. To access data via a data dictionary link users must have the LINK_ACCESS permission. The INHERIT permission allows a user to be granted the rights assigned to any group they are a member of.

Administrative permissions allow users to add and modify objects within the database. The ALTER, CREATE and DROP permissions allow the users to modify Tables, Views, Triggers, Stored Procedures and other database objects. To allow users to assign permissions for other users their rights must be assigned using WITH GRANT. This allows them to assign any permission they have the grant option for to any other user or group.

For a complete description of all the data dictionary permissions refer to the Advantage Data Dictionary User Permissions topic in the Advantage Help File.

Using the SQL GRANT Statement

To assign user permissions using SQL use the GRANT statement. The GRANT statement assigns the specified permission to the specified object. The following statement grants Read/Write access to the Customer table for the DB:Public group. Note that the group name must be delimited since it includes a colon ( : ).

     GRANT SELECT, UPDATE, INSERT, DELETE ON Customer TO [DB:Public]

To assign the ability to assign rights on an object the WITH GRANT clause must be specified. The following example grants Read/Write access to the Supervisors group with the ability to assign Read/Write permissions to others.

     GRANT SELECT, UPDATE, INSERT, DELETE ON Customer TO Supervisors WITH GRANT

You can remove permissions from a user or group using the REVOKE statement. The syntax is similar to the GRANT statement except rights are revoked FROM a user or group, whereas rights are assigned TO a user or group. The following statement removes Read/Write access to the customers table from the DB:Public group.

     REVOKE SELECT, UPDATE, INSERT, DELETE ON Customer FROM [DB:Public]

Assigning Rights to Multiple Objects

Rights can be assigned using ARC through a graphical interface. To assign permissions simply right-click on the user or group that you wish to assign permissions to and click the permissions button on the dialog box. You can assign permissions to multiple objects by right clicking on an item from the list and selecting “Grant <permission> to All”.

Rights for all objects can also be assigned using SQL Scripts. The database objects can be listed using various system tables. For instance you can get a list of all the tables in the database by querying system.tables. This information can then be used to assign the appropriate rights. The following SQL script assigns Read\Write access for all of the tables in the database to the DB:Public group.

     DECLARE cTables CURSOR AS SELECT * FROM system.tables;
     DECLARE @Stmt STRING;

     OPEN cTables;

     WHILE FETCH cTables DO
          @Stmt = 'GRANT SELECT, UPDATE, INSERT, DELETE ON ' + cTables.Name +
                          ' TO [DB:Public]';
          EXECUTE IMMEDIATE @Stmt;
     END WHILE;

     CLOSE cTables;

Permissions can be granted to other objects in the database by using other system tables such as system.functions, system.links, system.storedprocedures and system.views. 

Summary

Database permissions are a powerful mechanism for securing your data by restricting access. To use these permissions you need to enable “Logins Required” and “Check User Rights” on your data dictionary. Once these have been enabled all users must log into the dictionary and they will only have permissions which they are assigned.

The DB:Public group is an easy way to manage permissions for all database users. By assigning appropriate rights to this group any user added to the dictionary will automatically receive the proper access permissions. After creating or modifying your data dictionary, rights for multiple objects can be easily assigned using ARC or an SQL script.