1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

Data Control Language (DCL)

GRANT, REVOKE and DENY Database Permissions

By

Female designer working on computer
Jamie Grill/Getty Images
The Data Control Language (DCL) is a subset of the Structured Query Lanaguge (SQL) that allows database administrators to configure security access to relational databases. It complements the Data Definition Language (DDL), which is used to add and delete database objects, and the Data Manipulation Language (DML), which is used to retrieve, insert and modify 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 an extremely granular fashion.

Adding Permissions With the GRANT Command

The GRANT command is used by administrators to add 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 may supply with this command:
  • Privilege may 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 may be any database object. The valid privilege options will vary based upon 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 may be any database user. You may 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 the ability to grant those same permissions to other database users. For this reason, you should use this clause with care.
For example, assume you wish to grant the user Joe the ability to retrieve information from the employees table in a database called HR. You might use the following SQL command:
GRANT SELECT
ON HR.employees
TO Joe
Joe will now have the ability to retrieve information from the employees table. He will not, however, be able to grant other users permission to retrieve information from that table because you did not include the WITH GRANT OPTION clause in the GRANT statement.

Revoking Database Access

The REVOKE command is used to remove 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 that you wish to remove from the identified user. The command will revoke both GRANT and DENY assertions previously made for the identified permission.
  • Object may be any database object. The valid privilege options will vary based upon 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 may be any database user. You may 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. It is important to note that if you include the GRANT OPTION FOR clause in a REVOKE statement, the primary permission is NOT revoked. This clause causes ONLY the granting ability to be revoked.
  • The CASCADE option also revokes the specified permission from any users that the specified user granted the permission.
For example, the following command would revoke the permission granted to Joe in the previous example:
REVOKE SELECT
ON HR.employees
FROM Joe

Explicitly Denying Database Access

The DENY command may be used to explicitly prevent a user from receiving a particular permission. This is helpful when a user may be a member of a role or group that is granted a permission and you want to prevent that 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, you would issue the following command:
DENY DELETE
ON HR.employees
TO Matthew
  1. About.com
  2. Technology
  3. Databases
  4. Learning SQL
  5. Advanced SQL Topics
  6. Data Control Language (DCL) - for Database Permissions

©2014 About.com. All rights reserved.