DCL for Handling Privileges

DCL stands for Data Control Language. DCL statements can give or take away privileges to database objects or privileges to perform certain actions. At a minimum, most users are granted the right to connect to the database. Many users may not need to create tables, so they are not granted that privilege.

DCL (Data Control Language)

Includes statements such as GRANT and REVOKE to provide or deny users or roles system or object privileges.

Privileges can also be granted to a role. A role is a way to bundle together multiple privileges into a single entity. This makes it easier to grant a group of privileges to one or more users in one easy step, rather than needing to enumerate each of those privileges every time you want to grant them to a new user (or to another role). The converse is also true: It’s easier to revoke a role from a user than to remove the individual privileges that make up the role. System privileges, object privileges, and roles are discussed in more detail in Chapter 11. The following sections provide an overview of the GRANT and REVOKE statements.

role

A group of related privileges that is referenced by a single name. Privileges can be assigned to a role, and a role can be assigned to a database user or to another role. Roles ease the maintenance issues with managing privileges for a large number of users who can be grouped into a relatively small number of categories based on job function.

The GRANT Statement

The GRANT statement is almost self-explanatory. GRANT will give a privilege (either object or system) to a user, a role, or to all users. The basic syntax for granting both system and object privileges is as follows:

GRANT sys_privilege [, sys_privilege ...]    TO user | role | PUBLIC [, user | role | PUBLIC ...]; GRANT obj_privilege [(column_list)] ON object    TO user | role | PUBLIC [WITH GRANT OPTION];

Granting object privileges with the [WITH GRANT OPTION] clause allows the user or users granted that role the ability to pass those rights onto yet another user or role.

Suppose that Scott has acquired additional responsibilities and now must help to maintain the tables in the order-entry system, specifically the ORDER_ITEMS table owned by the user OE. The DBA grants the rights on this table to user SCOTT using the following command:

GRANT INSERT, UPDATE, DELETE, SELECT ON    OE.ORDER_ITEMS TO SCOTT; Grant succeeded.

Scott can now add, delete, update, and view rows in the OE.ORDER_ITEMS table. He cannot, however, grant these privileges to other users or roles, since the WITH GRANT OPTION clause was not used by the DBA.

The REVOKE Statement

As you would expect, the REVOKE statement is the opposite of the GRANT statement. Either system privileges or object privileges can be revoked with the following basic syntax:

REVOKE obj_privilege | ALL [, obj_privilege] ON object    FROM user | role | PUBLIC [, user | role | PUBLIC ...]; REVOKE sys_privilege | ALL [, sys_privilege ...]    FROM user | role | PUBLIC [, user | role | PUBLIC ...];

When the DBA granted the rights to SCOTT to work with the ORDER_ITEMS table, he noticed that the user OE had the DBA role assigned! This was obviously an oversight, so he corrected the situation immediately by using the REVOKE statement to remove the DBA role from OE:

REVOKE DBA FROM OE; Revoke succeeded.

The user OE retains all other object and system privileges granted by the DBA and other users.