Database permissions and authorities overview

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.
RESOURCE No See RESOURCE authority.
VALIDATE No See VALIDATE authority.
WRITECLIENTFILE Yes See WRITECLIENTFILE authority.
Inheritance of 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 Granting and revoking REMOTE and CONSOLIDATE permissions.
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 integrated logins.
KERBEROS LOGIN No Allows the user to connect to the database using a Kerberos login See Using Kerberos authentication.
PUBLISH No Identifies the publisher of a database in SQL Remote See Granting and revoking PUBLISH permissions.
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.

See also: 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
Permissions overview