Discretionary access controls (DAC) allow you to restrict access to objects and commands based on a user’s identity or group membership. The controls are “discretionary” because a user with a certain access permission, such as an object owner, can choose whether to pass that access permission on to other users.
System Administrators operate outside the DAC system and have access permissions on all database objects at all times. System Security Officers can always access the audit trail tables in the sybsecurity database.
Database Owners do not automatically receive permissions on objects owned by other users; however, they can:
Temporarily acquire all permissions of a user in the database by using the setuser command to assume the identity of that user.
Permanently acquire permission on a specific object by using the setuser command to assume the identity of the object owner, and then using grant commands to grant the permissions.
For details on assuming another user’s identity to acquire permissions on a database or object, see “Acquiring the permissions of another user”.
Object Owners can grant access to those objects to other users and can also grant other users the ability to pass the access permission to other users. You can give various permissions to users, groups, and roles with the grant command, and rescind them with the revoke command. Use these commands to give users permission to create databases, to create objects within a database, execute certain commands such as set proxy, and to access specified tables, views, and columns. For permissions that default to “public,” no grant or revoke statements are needed.
Some commands can be used at any time by any user, with no permission required. Others can be used only by users of a particular status and they are not transferable.
The ability to assign permissions for the commands that can be granted and revoked is determined by each user’s role or status (as System Administrator, Database Owner, or database object owner), and by whether the user was granted a role with permission that includes the option to grant that permission to other users.
You can also use views and stored procedures as security mechanisms. See “Using views and stored procedures as security mechanisms”.
