Data Control Language (DCL)

GRANT, REVOKE and DENY Database Permissions

The Data Control Language (DCL) is a subset of the Structured Query Language (SQL) and 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) 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 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 the ability to 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 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 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. NoteIf 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.

    For example, 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 is used to explicitly prevent a user from receiving a particular permission. This 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