Data Control Language

Data Control Language

  DCL commands are used to enforce database security in a multiple user database environment. Three  types of DCL commands are GRANT,REVOKE and DENY. Only Database Administrator's or owner's of the database object can provide/remove privileges on a database object.


i.GRANT:

  The GRANT command is used by administrators to add new permissions to a database user. It has a very simple syntax, defined as follows:

SYANTAX:

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

•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.


2.REVOKE


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:

SYANTAX:

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.

Ex:

REVOKE SELECT
ON HR.employees
FROM Josphine


3.DENY

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 Rachel would never receive the ability to delete information from the employees table, you would issue the following command:

Ex:

DENY DELETE
ON HR.employees
TO Rachel

0 comments:

Post a Comment