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

See Also

Effective Permissions vs Explicit Permissions

Database Roles