Data Control Language (DCL)

The Data Control Language is a subset of the Structured Query Language. Database administrators use DCL to configure security access to relational databases. It complements the Data Definition Language, which adds and deletes database objects, and the Data Manipulation Language, which retrieves, inserts, and modifies the contents of a database.

DCL is the simplest of the SQL subsets, as it consists of only three commands: GRANT, REVOKE, and DENY. Combined, these three commands provide administrators with the flexibility to set and remove database permissions in granular fashion.

Adding Permissions With the GRANT Command

The GRANT command adds new permissions to a database user. It has a very simple syntax, defined as follows:

GRANT [privilege]
ON [object]
TO [user]
[WITH GRANT OPTION]

Here's the rundown on each of the parameters you can supply with this command:

• Privilege — can be either the keyword ALL (to grant a wide variety of permissions) or a specific database permission or set of permissions. Examples include CREATE DATABASE, SELECT, INSERT, UPDATE, DELETE, EXECUTE and CREATE VIEW.
• Object — can be any database object. The valid privilege options vary based on the type of database object you include in this clause. Typically, the object will be either a database, function, stored procedure, table or view.
• User — can be any database user. You can also substitute a role for the user in this clause if you wish to make use of role-based database security.
• If you include the optional WITH GRANT OPTION clause at the end of the GRANT command, you not only grant the specified user the permissions defined in the SQL statement but also give the user permission to further grant those same permissions to other database users. For this reason, use this clause with care.

For example, assume you wish to grant the user Joe the ability to retrieve information from the employee table in a database called HR. Use the following SQL command:

GRANT SELECT
ON HR.employees
TO Joe

Joe can retrieve information from the employees' table. He will not, however, be able to grant other users permission to retrieve information from that table because the DCL script did not include the WITH GRANT OPTION clause.

Revoking Database Access

The REVOKE command removes database access from a user previously granted such access. The syntax for this command is defined as follows:

REVOKE [GRANT OPTION FOR] [permission]
ON [object]
FROM [user]
[CASCADE]

Here's the rundown on the parameters for the REVOKE command:

• Permission — specifies the database permissions to remove from the identified user. The command revokes both GRANT and DENY assertions previously made for the identified permission.
• Object — can be any database object. The valid privilege options vary based on the type of database object you include in this clause. Typically, the object will be either a database, function, stored procedure, table, or view.
• User — can be any database user. You can also substitute a role for the user in this clause if you wish to make use of role-based database security.
• The GRANT OPTION FOR clause removes the specified user's ability to grant the specified permission to other users. If you include the GRANT OPTION FOR clause in a REVOKE statement, the primary permission is not revoked. This clause revokes only the granting ability.
• The CASCADE option also revokes the specified permission from any users that the specified user granted the permission.

The following command revokes the permission granted to Joe in the previous example:

REVOKE SELECT
ON HR.employees
FROM Joe

Explicitly Denying Database Access

The DENY command explicitly prevents a user from receiving a particular permission. This feature is helpful when a user is a member of a role or group that is granted a permission, and you want to prevent that individual user from inheriting the permission by creating an exception. The syntax for this command is as follows:

DENY [permission]
ON [object]
TO [user]

The parameters for the DENY command are identical to those used for the GRANT command. For example, if you wished to ensure that Matthew would never receive the ability to delete information from the employees' table, issue the following command:

DENY DELETE
ON HR.employees
TO Matthew