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.
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.
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.
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.
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.)
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. |
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 |
Below are tables that show what permissions are required to read or update database object 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 |
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 |
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 |
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 |
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 |
Property |
Read |
Update |
ADS_DD_LINK_PATH |
ALTER |
ALTER |
ADS_DD_LINK_OPTIONS |
ALTER |
ALTER |
ADS_DD_LINK_USERNAME |
ALTER |
ALTER |
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 |
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 |
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 |
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 |
Property |
Read |
Update |
ADS_DD_COMMENT |
READ |
ALTER |
ADS_DD_VIEW_STMT |
ALTER |
ALTER |
ADS_DD_VIEW_STMT_LEN |
ALTER |
NA |
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 |
Property |
Read |
Update |
ADS_DD_PUBLICATION_OPTIONS |
ALTER |
ALTER |
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 |
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 |
See Also