DBMS DCL Commands

DCL Commands

Oracle Provides two commands - GRANT and REVOKE - to control the access of various database objects.

GRANT Commands

Grants a privilege to a user It means that giving authority to other user by administrator If you are administrator then only you have authority for grating the other authority to other user Can grant privilege only if you have been granted that privilege

Syntax :

GRANT < Object Privileges > ON <ObjectName> TO <UserName> [WITH GRANT OPTION];

OBJECT PRIVILEGES

Each object privilege that is granted authorizes the grantee to perform some operation on the object. A user can grant all the privileges or grant only specific object privileges.
The list of object privileges is as follows:
ALTER : Allows the grantee to change the table definition with the ALTER TABLE command
DELETE : Allows the grantee to remove the records from the table with the DELETE command
INDEX : Allows the grantee to create an index on the table with the CREATE INDEX command
INSERT : Allows the grantee to add records to the table with the INSERT command
SELECT : Allows the grantee to query the table with the SELECT command
UPDATE : Allows the grantee to modify the records in the tables with the UPDATE command

Example:

Give the user rahul permission to only view and modify records in the table client_master.
 
Run SQL Command Line
SQL>GRANT SELECT, UPDATE ON client_master TO rahul;

Grant succeeded.

REVOKE Commands 

The REVOKE statement is used to deny the grant given on an object. Revokes a privilege from a user It is use to taking off or remove of authority or say getting back authority from user

Syntax:

REVOKE < Object Privileges > ON <Object Name> FROM <Username>;

Example:

All privileges on the table salesman_master have been granted to rahul. Take back the Delete privilege on the table.
 
Run SQL Command Line
SQL>REVOKE DELETE ON salesman_master FROM Rahul;

Revoke succeeded.

 



Share This Page on:


Ask Question