Database permissions and authorities

Each user of a database has a name they enter when connecting to the database (user ID), and they belong to at least one group. Users and groups also have authorities and permissions attributed to them that allow them to perform their tasks while maintaining the security and privacy of information within the database.

A permission grants the ability to perform a create, modify, query, use, or delete database objects such as tables, views, users, and so on. An authority grants the ability to perform a task at the database level, such as backing up the database, or performing diagnostic tracing. SQL Anywhere allows you to grant permissions and authorities to user and groups.

While all permissions are inheritable (from the groups to which the user belongs), only some authorities are inheritable.

Inheriting authorities

The following table lists the authorities you can assign to users, and whether they are inherited through group membership:

Authority	Inherited through group membership	More information
BACKUP	No	See BACKUP authority.
DBA	No	See DBA authority.
PROFILE	Yes	See PROFILE authority.
READCLIENTFILE	Yes	See READCLIENTFILE authority.
READFILE	Yes	See READFILE authority.
REMOTE DBA		See REMOTE DBA authority.
RESOURCE	No	See RESOURCE authority.
VALIDATE	No	See VALIDATE authority.
WRITECLIENTFILE	Yes	See WRITECLIENTFILE authority.

Inheriting permissions

The following table lists the permissions you can assign to users, and whether they are inherited through group membership:

Permission	Inherited through group membership		More information
ALL	Yes	Allows the user to perform all tasks associated with a database object (equivalent to granting ALTER, DELETE, INSERT, REFERENCES, SELECT, and UPDATE)	See GRANT statement.
ALTER	Yes	Allows the user to alter a database object	See Permissions inherited through group membership.
CONNECT	No	Allows the user to connect to the database	See Creating new users.
CONSOLIDATE	No	Identifies a consolidated database in SQL Remote	See CONSOLIDATE permission.
DELETE	Yes	Allows the user to delete a database object	See GRANT statement.
INSERT	Yes	Allows the user to insert data into a database object	See GRANT statement.
INTEGRATED LOGIN	No	Allows the user to connect to the database using an integrated login	See Using Windows integrated logins.
KERBEROS LOGIN	No	Allows the user to connect to the database using a Kerberos login	See Kerberos authentication.
PUBLISH	No	Identifies the publisher of a database in SQL Remote	See PUBLISH permission.
REFERENCES	Yes	Allows the user to create indexes on a table, and create foreign keys that reference the table	See GRANT statement.
REMOTE	No	Identifies a remote database in SQL Remote and MobiLink	See GRANT REMOTE DBA statement [MobiLink] [SQL Remote].
SELECT	Yes	Allows the user to query a database object	See GRANT statement.
UPDATE	Yes	Allows the user to update a database object	See GRANT statement.

Negative permissions

SQL Anywhere does not support negative permissions. This means that you cannot revoke a permission that was not explicitly granted.

For example, suppose user bob is a member of a group called sales. If a user grants DELETE permission on a table, T, to sales, then bob can delete rows from T. If you want to prevent bob from deleting from T, you cannot simply execute a REVOKE DELETE on T from bob, since the DELETE ON T permission was never granted directly to bob. In this case, you would have to revoke bob's membership in the sales group.

See:

Authorities overview
Understanding permissions

Discuss this page in DocCommentXchange.