The grant and revoke commands control the Adaptive Server command and object protection system. You can give various kinds of permissions to users, groups, and roles with the grant command and rescind them with the revoke command including:
Creating databases
Creating objects in a database
Accessing tables, views, and columns
Executing stored procedures
Some commands can be used at any time by any user, with no permissions required. Others can be used only by users of certain status (for example, a System Administrator) and cannot be transferred.
The ability to assign permissions for commands that can be granted and revoked is determined by each user’s status (as System Administrator, Database Owner, or database object owner) and by whether a particular user has been granted a permission with the option to grant that permission to other users.
Owners do not automatically receive permissions on objects that are owned by other users. But a Database Owner or System Administrator can acquire any permission by using the setuser command to temporarily assume the identity of the object owner, and then writing the appropriate grant or revoke statement.
You can assign two kinds of permissions with grant and revoke: object access permissions and object creation permissions.
Object access permissions regulate the use of certain commands that access certain database objects. For example, you must be granted permission to use the select command on the authors table. Object access permissions are granted and revoked by object owners.
To grant Mary and Joe the object access permission to insert into and delete from the titles table:
grant insert, delete on titles to mary, joe
Object creation permissions regulate the use of commands that create objects. These permissions can be granted only by a System Administrator or Database Owner.
For example, to revoke from Mary permission to create tables and rules in the current database:
revoke create table, create rule from mary
For complete information about using grant and revoke, see the Reference Manual: Commands and Chapter 17, “Managing User Permissions,” in the System Administration Guide: Volume 1.
A System Security Officer can also use roles to simplify the task of granting and revoking access permissions.
For example, instead of having object owners grant privileges on each object individually to each employee, the System Security Officer can create roles, request object owners to grant privileges to each role, and grant these user-defined roles to individual employees, based on the functions they perform in the organization. The System Security Officer can also revoke user-defined roles granted to the employee.
For complete information about user-defined roles, see Chapter 17, “Managing User Permissions,” in the System Administration Guide: Volume 1.
