Security with the Data Dictionary

Advantage Data Dictionaries (i.e. databases) have many security options which can restrict access to data. This gives developers the ability to define permissions for any object within the database (i.e. Tables, Views, Stored Procedures, etc…). For example a specific set of views which summarize sales figures could be restricted so only the managers group can open them.

Advantage 9.0 added some additional options for configuring database security referred to as database roles. These include four new groups which are added to all data dictionaries. You can also restrict connections to Advantage allowing only data dictionary connections.

Database User Permissions

I want to quickly highlight some of the user permissions that are available within a Data Dictionary. A complete description of each of the permissions is available in the Advantage Help File.

Normal user permissions include: READ, UPDATE, INSERT, DELETE, EXECUTE, LINK_ACCESS and INHERIT. Most users will have the INHERIT permission meaning that they get their rights from their group membership. In general it is best to assign permissions, also referred to as rights, to groups and then assign users to the appropriate group. Most of these permissions are self-explanatory with the exception of LINK_ACCESS. This is allows users to open tables through a dictionary link.

Administrative permissions include: ALTER, CREATE, DROP and WITH GRANT. The ALTER permission provides some additional permissions for certain objects. The ALTER permission on a table allows for creating, deleting and modifying any Triggers, Indexes or Relations defined for the table. There is no explicit CREATE or DROP permission for these table specific objects.

Finally the WITH GRANT permission allows users to assign specific rights to other users or groups. Therefore if a user has ALTER permissions on a table and WITH GRANT permissions the user could grant the ALTER permission on the table to other users.

Database Groups

Four groups are automatically added to every version 9 and above data dictionary; DB:Admins, DB:Backup, DB:Debug and DB:Public. The first three groups have specific rights pre-defined and cannot be altered. Every user in the dictionary is automatically added to the DB:Public group and inherit all the rights assigned to the group. This makes management of new users easier since they will automatically have the same rights as all other dictionary users. You must configure the rights for the DB:Public group.

Users added to the DB:Admins have the same rights as the ADSSYS account. However, members of the DB:Admins group cannot change the ADSSYS password. You must login to the ADSSYS account to change its password, other passwords can be changed by members of the DB:Admins group.

Users added to the DB:Backup group have the permission to run backup operations. This gives members EXECUTE rights for the sp_BackupDatabase and sp_BackupFreeTables system procedures. If you are using the AdsBackup utility which ships with Advantage you can specify a username using the –y switch. This option was added in the latest service release of Advantage 9 (version 9.0.0.7 at the time of this posting) and does not exist in prior versions. 

Users added to the DB:Debug group have the permission to debug SQL scripts executed on any connection to the database. Members of this group also have permission to modify any trigger, stored procedure or user defined function, however, they cannot create new objects.

Disabling Free Table Connections

For an additional level of security you can completely disable all connections to free tables. All users must connect to a dictionary when accessing data stored on the Advantage server. Any attempt to open a free table on the server will result in a 7083 "An Advantage Data Dictionary connection is required" error.

This can be set using the Advantage Configuration Utility (pictured left). The setting is found on the Configuration Utility tab under Misc. Settings. This is a simple on or off feature. You can manually set this by setting the DISABLE_FREE_CONNECTIONS to a non-zero value.

For Windows servers this is stored under the following registry key HKEY_LOCAL_MACHINE\ SYSTEM\ CurrentControlSet\ Services\ Advantage\ Configuration. For Linux and NetWare servers this is specified in the server configuration file.