Friday, May 2, 2014

DCL - Data Control Language

DCL commands are used to granting and revoking the permissions.

GRANT

This is used to grant the privileges to other users.

Syntax:
     Grant <privileges> on <object_name> to <user_name> [with grant option];

Ex:
     SQL> grant select on student to sudha;         -- you can give individual privilege
      SQL> grant select, insert on student to sudha;       -- you can give set of privileges
     SQL> grant all on student to sudha;                -- you can give all privileges
    
     The sudha user has to use dot method to access the object.
     SQL> select * from saketh.student;
     The sudha user can not grant permission on student table to other users. To get this type of
     option use the following.
     SQL> grant all on student to sudha with grant option;             
     Now sudha user also grant permissions on student table.

REVOKE

This is used to revoke the privileges from the users to which you granted the privileges.

Syntax:
     Revoke <privileges> on <object_name> from <user_name>;

Ex:
     SQL> revoke select on student form sudha; -- you can revoke individual privilege
     SQL> revoke select, insert on student from sudha;            -- you can revoke set of privileges
      SQL> revoke all on student from sudha;       -- you can revoke all privileges

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.