Advantage Developer Zone

 
 
 

Advantage Version 9.0 Data Dictionary Roles

Tuesday, September 09, 2008

With the release of Advantage Version 9.0 several new database roles have been added to the data dictionary. These roles will assist in the administration and increase security of your database. Each of these pre-defined roles will automatically be created whenever a dictionary is created with version 9.0 and above. 

There are three new administrative roles; DB:Admin, DB:Debug and DB:Backup, which provide specific functionality to the members of the role. Additionally all dictionary users will automatically be added to a group called DB:Public.

A Quick Note about Advantage Permissions

Advantage Data Dictionary (database) permissions are split into two main categories, normal user permissions and administrative permissions. Normal user permissions include: Select, Insert, Update, Delete, Execute, Link_Access and Inherit. Administrative permissions include: Alter, Create, Drop and With Grant. 

Not all permissions apply to all dictionary objects, for example Execute permissions only apply to Stored Procedures and SQL User Defined Functions. Permissions are cumulative meaning that the user will have all of the permissions assigned to every group they are a member of.

Administrative Groups

To allow complete administrative control of the data dictionary you can assign users to the DB:Admin group. Users in this group have the same permissions as the ADSSYS user allowing them to modify all dictionary objects. The only restriction on members of this group is the ability to change the ADSSYS password. 

To allow users to develop triggers, stored procedures and SQL user defined functions for the data dictionary add them to the DB:Debug group. Users in this group can create and modify these objects as well as use the debugger to walk through SQL Scripts. These users can not modify any other dictionary objects. Therefore if a user needs to create other objects consider adding them to the DB:Admin group instead. 

Users in the DB:Backup group are allowed to perform backup operations on data dictionaries. In previous versions the ADSSYS user was the only user who could backup or restore a data dictionary (database). Free tables do not require any password to backup or restore unless they are encrypted.

Note: The adsbackup utility did not support specifying a username in the initial 9.0 release. The –y switch was added in the latest service release (9.0.0.7) so you can specify a user with the appropriate rights for the utility.
The permissions for the pre-defined administrative groups are fixed and cannot be altered. Attempts to change the name or permissions on one of these groups will result in a 5054 (permission denied) or a 5136 (invalid object type) error.

DB:Public Group

All of the users defined in your data dictionary are added to the DB:Public group. This allows for easy administration of rights for your general users. Since every new user is automatically added to the DB:Public group they will inherit all rights associated with this group. 

Assign the database rights that all “normal” users of your application will require to perform their tasks. This eliminates the need to create a group specifically for your users and you do not have to add the new user to any other groups. You may still want to create groups which have more database rights depending on your application’s requirements.

Summary

Database permissions provide a powerful and convenient way to control access to data dictionary objects. Advantage provides a wide array of permissions which can be assigned to users and groups providing normal data access as well as administrative permissions. 

Database roles have been added in Advantage version 9 which provide specific functionality for members assigned to these roles. The three administrative roles; DB:Admin, DB:Debug and DB:Backup, allow their members administrative functionality without having to provide the ADSSYS password. 

Managing normal database users is also easier with the addition of the DB:Public group. All users are automatically assigned to this group eliminating the need to create a specific group for your normal database users.