Advantage Data Dictionary User Permissions

Advantage Concepts

The Advantage Data Dictionary implements its own level of user permissions. Each user added to the Advantage Data Dictionary can be granted or revoked permissions to access, update, remove, or create objects such as tables, stored procedures, referential integrity, etc. User groups (roles) can be defined in the database to give users effective permissions based on their user group membership (see Effective Permissions vs Explicit Permissions).

Note To enforce user permissions, the ADS_DD_VERIFY_ACCESS_RIGHTS database property must be set to TRUE. This property can be set using the AdsDDSetDatabaseProperty API or using the Advantage Database Manager in Advantage Data Architect (see the ‘Check User Rights’ check box on the database properties screen). See AdsDDSetDatabaseProperty for more information.

ADSSYS

All Advantage data dictionaries contain an administrative user called ADSSYS. This user has permissions to perform any operation or update on the dictionary. Be aware that if the ADSSYS password is lost, it cannot be recovered or reset.

Normal User Permissions

Most users only need permissions to perform basic operations on a database object (table, stored procedure, dictionary link, etc). These permissions include READ, UPDATE, INSERT, DELETE, EXECUTE, LINK_ACCESS and INHERIT. See the table below to determine which permission applies to each database object.

Administrative Permissions

Normal users can be granted ALTER, CREATE, DROP, and WITH GRANT permissions to be able to perform administrative operations on an Advantage Data Dictionary. Only a user with the WITH GRANT permission can grant the permission to another user. For example:

User A can only grant READ permissions to user B if they already have both READ and WITH GRANT permissions on the specific database object themselves.

WITH GRANT permissions can only be granted to a user or group in addition to another permission. For example, if a user is given READ WITH GRANT permissions, by default the user will have READ permissions whether they did before or not.

Database Permissions

Listed here are all available permissions for users and user groups. See AdsDDGrantPermission in the Advantage Client Engine Help documentation (ACE.hlp or ace.htm) for more information about each permission. (Note that each of the Advantage products and their corresponding Help files are installed separately.)

Users and User Groups

Permisssion	Description
ADS_PERMISSION_READ	Allows users to read records from the specified table.
ADS_PERMISSION_UPDATE	Allows users to update records in the specified table.
ADS_PERMISSION_EXECUTE	Allows users to execute the specified stored procedure.
ADS_PERMISSION_INHERIT	Allows permissions granted to groups to be inherited by the group member. User inherits the permission from the user groups by default but the inheritance to a specific database object may be granted or revoked by the ADSSYS user or members of the DB:Admin group.
ADS_PERMISSION_INSERT	Allows users to insert records into the specified table.
ADS_PERMISSION_DELETE	Allows users to delete records from the specified table.
ADS_PERMISSION_LINK_ACCESS	Allows users to access the specified data dictionary link.
ADS_PERMISSION_CREATE	Allows users to create objects of the specified type.
ADS_PERMISSION_ALTER	Allows users to alter the specified database object.
ADS_PERMISSION_DROP	Allows users to drop or remove the specified database object.
ADS_PERMISSION_WITH_GRANT	Allows users to grant the specified permission to other users.
ADS_PERMISSION_ALL_WITH_GRANT	Specifies all applicable permissions along with WITH GRANT.
ADS_PERMISSION_ALL	Specifies all applicable permissions for the database object.

Administrative Operation

The table below shows what permissions are required to perform administrative operations on the database or database objects.

Object Type	Create	Delete	Modify
Table	CREATE TABLE	DROP on the table	ALTER on the table
Relation	ALTER on related tables	ALTER on related tables	ALTER on related tables
Index File	ALTER on parent table	ALTER on parent table	ALTER on parent table
Field	ALTER on parent table	ALTER on parent table	ALTER on parent table
Index	ALTER on parent table	ALTER on parent table	ALTER on parent table
View	CREATE VIEW	DROP on the view	ALTER on the view
User	CREATE USER	DROP on the user	ALTER on the user
User Group	CREATE USER GROUP	DROP on the group	ALTER on the group
Procedure	CREATE PROCEDURE	DROP on the procedure	ALTER on the procedure
Database			ALTER DATABASE
Link	CREATE LINK	DROP on the link	ALTER on the link
Trigger	ALTER on parent table	ATLER on parent table	ALTER on parent table
Publication	CREATE PUBLICATION	DROP on the publication	ALTER on the publication
Article	ALTER on the parent publication	ALTER on the parent publication	ALTER on the parent publication
Subscription	CREATE SUBSCRIPTION	DROP on the subscription	ALTER on the subscription

Object Properties

Below are tables that show what permissions are required to read or update database object properties.

Database Properties

Property	Read	Update
ADS_DD_COMMENT		ALTER
ADS_DD_VERSION_MAJOR		ALTER
ADS_DD_VERSION_MINOR		ALTER
ADS_DD_USER_DEFINED_PROP		ALTER
ADS_DD_DEFAULT_TABLE_PATH		ALTER
ADS_DD_TEMP_TABLE_PATH		ALTER
ADS_DD_FTS_DELIMITERS		ALTER
ADS_DD_FTS_NOISE		ALTER
ADS_DD_FTS_DROP_CHARS		ALTER
ADS_DD_FTS_CONDITIONAL_CHARS		ALTER
ADS_DD_ENCRYPTED	ALTER	ALTER
ADS_DD_LOG_IN_REQUIRED	ALTER	ALTER
ADS_DD_VERIFY_ACCESS_RIGHTS	ALTER	ALTER
ADS_DD_ENCRYPT_TABLE_PASSWORD	ALTER	ALTER
ADS_DD_ENCRYPT_NEW_TABLE	ALTER	ALTER
ADS_DD_ADMIN_PASSWORD	ADSSYS only	ADSSYS only
ADS_DD_ALLOW_ADSSYS_NET_ACCESS	ADSSYS only	ADSSYS only
ADS_DD_ENABLE_INTERNET	ALTER	ALTER
ADS_DD_INTERNET_SECURITY_LEVEL	ALTER	ALTER
ADS_DD_MAX_FAILED_ATTEMPTS	ALTER	ALTER
ADS_DD_LOGINS_DISABLED	ALTER	ALTER
ADS_DD_LOGINS_DISABLED_ERRSTR	ATLER	ALTER

Table Properties

Property	Read	Update
ADS_DD_COMMENT	READ, UPDATE, INSERT, or DELETE (any or all columns)	ALTER
ADS_DD_USER_DEFINED_PROP	READ, UPDATE, INSERT, or DELETE (any or all columns)	ALTER
ADS_DD_TABLE_PRIMARY_KEY	READ, UPDATE, INSERT, or DELETE (any or all columns)	ALTER
ADS_DD_TABLE_ENCRYPTION	READ, UPDATE, INSERT, or DELETE (any or all columns)	ALTER
ADS_DD_TABLE_DEFAULT_INDEX	READ, UPDATE, INSERT, or DELETE (any or all columns)	ALTER
ADS_DD_TABLE_TYPE	READ, UPDATE, INSERT, or DELETE (any or all columns)	NA
ADS_DD_TABLE_FIELD_COUNT	READ, UPDATE, INSERT, or DELETE (any or all columns)	NA
ADS_DD_TABLE_VALIDATION_EXPR	ALTER, UPDATE, or INSERT	ALTER
ADS_DD_TABLE_VALIDATION_MSG	ALTER, UPDATE, or INSERT	ALTER
ADS_DD_TABLE_IS_RI_PARENT	ALTER	ALTER
ADS_DD_TABLE_AUTO_CREATE	ALTER	ALTER
ADS_DD_TABLE_PERMISSION_LEVEL	ALTER	ALTER
ADS_DD_TABLE_MEMO_BLOCK_SIZE	ALTER	ALTER
ADS_DD_TABLE_IS_RI_PARENT	ALTER	NA

Field Properties

Note Field permissions are derived from table permissions. To update field properties, the user must have ALTER permissions on the table that contains the field.

Property	Read	Update
ADS_DD_COMMENT	READ, UPDATE, INSERT, or DELETE on the field	ALTER on the table
ADS_DD_USER_DEFINED_PROP	READ, UPDATE, INSERT, or DELETE on the field	ALTER on the table
ADS_DD_FIELD_LENGTH	READ, UPDATE, INSERT, or DELETE on the field	NA
ADS_DD_FIELD_TYPE	READ, UPDATE, INSERT, or DELETE on the field	NA
ADS_DD_FIELD_DECIMAL	READ, UPDATE, INSERT, or DELETE on the field	NA
ADS_DD_FIELD_OPTIONS	READ, UPDATE, INSERT, or DELETE on the field	NA
ADS_DD_FIELD_DEFAULT_VALUE	ALTER on the table, or UPDATE or INSERT on the field	ALTER on the table
ADS_DD_FIELD_CAN_NULL	ALTER on the table, or UPDATE or INSERT on the field	ALTER on the table
ADS_DD_FIELD_MIN_VALUE	ALTER on the table, or UPDATE or INSERT on the field	ALTER on the table
ADS_DD_FIELD_MAX_VALUE	ALTER on the table, or UPDATE or INSERT on the field	ALTER on the table
ADS_DD_FIELD_VALIDATION_MSG	ALTER on the table, or UPDATE or INSERT on the field	ALTER on the table

Index File Properties

Note Index file permissions are derived from table permissions. To update index file properties, the user must have ALTER permissions on the table that the index file is associated.

Property	Read	Update
ADS_DD_INDEX_FILE_PATH	READ, UPDATE, INSERT, or DELETE (any or all columns)	NA
ADS_DD_INDEX_FILE_PAGESIZE	READ, UPDATE, INSERT, or DELETE (any or all columns)	NA

Index Properties

Note Index permissions are derived from table permissions. To update index properties, the user must have ALTER permissions on the table that the index is associated.

Property	Read	Update
ADS_DD_INDEX_FILE_NAME	READ, UPDATE, INSERT, or DELETE (any or all columns)	NA
ADS_DD_INDEX_KEY_LENGTH	READ, UPDATE, INSERT, or DELETE (any or all columns)	NA
ADS_DD_INDEX_KEY_TYPE	READ, UPDATE, INSERT, or DELETE (any or all columns)	NA
ADS_DD_INDEX_EXPRESSION	ALTER on the table	NA
ADS_DD_INDEX_CONDITION	ALTER on the table	NA
ADS_DD_INDEX_OPTIONS	ALTER on the table	NA

Link Properties

Property	Read	Update
ADS_DD_LINK_PATH	ALTER	ALTER
ADS_DD_LINK_OPTIONS	ALTER	ALTER
ADS_DD_LINK_USERNAME	ALTER	ALTER

Procedure Properties

Property	Read	Update
ADS_DD_COMMENT	EXECUTE	ALTER
ADS_DD_PROC_INPUT	EXECUTE	ALTER
ADS_DD_PROC_OUTPUT	EXECUTE	ALTER
ADS_DD_PROC_DLL_NAME	ALTER	ALTER
ADS_DD_PROC_DLL_FUNCTION_NAME	ALTER	ALTER
ADS_DD_PROC_INVOKE_OPTION	ALTER	ALTER

Referential Integrity Properties

Note Referential integrity permissions are derived from table permissions. To update referential integrity properties, the user must have ALTER permissions on the table that the referential integrity is associated.

Property	Read	Update
ADS_DD_RI_PRIMARY_TABLE	ALTER on both tables	NA
ADS_DD_RI_PRIMARY_INDEX	ALTER on both tables	NA
ADS_DD_RI_FOREIGN_TABLE	ALTER on both tables	NA
ADS_DD_RI_FOREIGN_INDEX	ALTER on both tables	NA
ADS_DD_RI_NO_PKEY_ERROR	ALTER on both tables	NA
ADS_DD_RI_CASCADE_ERROR	ALTER on both tables	NA
ADS_DD_RI_UPDATERULE	ALTER on both tables	NA
ADS_DD_RI_DELETERULE	ALTER on both tables	NA

User Properties

Property	Read	Update
ADS_DD_COMMENT	Current user or ALTER on user	ALTER
ADS_DD_USER_DEFINED_PROP	Current user or ALTER on user	ALTER
ADS_DD_USER_GROUP_MEMBERSHIP	Current user or ALTER on user	ALTER
ADS_DD_LOGINS_DISABLED	Current user or ALTER on user	Current user or ALTER on user
ADS_DD_USER_PASSWORD	Current user or ALTER on user	Current user or ALTER on user
ADS_DD_ENABLE_INTERNET	Current user or ALTER on user	Current user or ALTER on user

User Group Properties

Property	Read	Update
ADS_DD_COMMENT	Group member or ALTER on group	ALTER
ADS_DD_USER_DEFINED_PROP	Group member or ALTER on group	ALTER

View Properties

Property	Read	Update
ADS_DD_COMMENT	READ	ALTER
ADS_DD_VIEW_STMT	ALTER	ALTER
ADS_DD_VIEW_STMT_LEN	ALTER	NA

Trigger Properties

Note Trigger permissions are derived from table permissions. To update a trigger property, the user must have ALTER permissions on the table that the trigger is associated.

Property	Read	Update
ADS_DD_TRIG_TABLEID	ALTER on the table	NA
ADS_DD_TRIG_EVENT_TYPE	ALTER on the table	NA
ADS_DD_TRIG_TRIGGER_TYPE	ALTER on the table	NA
ADS_DD_TRIG_CONTAINER_TYPE	ALTER on the table	NA
ADS_DD_TRIG_CONTAINER	ALTER on the table	NA
ADS_DD_TRIG_FUNCTION_NAME	ALTER on the table	NA
ADS_DD_TRIG_PRIORITY	ALTER on the table	NA
ADS_DD_TRIG_OPTIONS	ALTER on the table	NA
ADS_DD_TRIG_TABLENAME	ALTER on the table	NA

Publication Properties

Property	Read	Update
ADS_DD_PUBLICATION_OPTIONS	ALTER	ALTER

Article Properties

Note Article permissions are derived from publication permissions. To update an article property, the user must have ALTER permissions on the publication that contains the article.

Property	Read	Update
ADS_DD_ARTICLE_FILTER	ALTER on publication	ALTER on publication
ADS_DD_ARTICLE_ID_COLUMNS	ALTER on publication	ALTER on publication

Subscription Properties

Property	Read	Update
ADS_DD_SUBSCR_PUBLICATION_NAME	ALTER	ALTER
ADS_DD_SUBSCR_TARGET	ALTER	ALTER
ADS_DD_SUBSCR_USERNAME	ALTER	ALTER
ADS_DD_SUBSCR_PASSWORD	ALTER	ALTER
ADS_DD_SUBSCR_FORWARD	ALTER	ALTER
ADS_DD_SUBSCR_ENABLED	ALTER	ALTER
ADS_DD_SUBSCR_QUEUE_NAME	ALTER	ALTER
ADS_DD_SUBSCR_OPTIONS	ALTER	ALTER

Database Roles