DCL Commands in Oracle

Data control language (DCL) is used to control privileges in database. To perform any operation on the database owned by other user,such as for creating tables, sequences or views we need privileges.

DCL commands is divided into two categories as below
1. Grant: This commands is used to give privileges to other user on database.
2. Revoke: This commands is used to get back or revert privileges from other user on database.

Syntax
GRANT object_privileges
ON object_name
TO user_name
[WITH GRANT OPTION];

Now we will try understand each part of syntax

1. Grant ---> It is keyword.
2. object_privileges ---> listed in table below.
3. ON ---> It is keyword refer to object name on which grant permission is to be given.
4. object_name ---> It refer to object name of database.
5. TO ---> It is keyword.
6. user_name ---> It refer to user to which we are giving permission.
7. [WITH GRANT OPTION] ---> It is optional part in syntax but very important.when user 1 give permission to user 2 with grant option then user 2 can also use grant keyword to give permission to user 3 or many users on same object otherwise user 2 is restricted to use grant option on that object name.

Object Privileges Table

Privileges	Allows users
ALL	To perform all the operations listed below.
ALTER	To change the table structure using ALTER command.
DELETE	To delete records from the table using DELETE command.
INDEX	To create an index on the table using CREATE INDEX command.
INSERT	To insert a records into table using INSERT INTO command.
REFERENCES	To reference table while creating foreign keys.
SELECT	To query the table using SELECT command.
UPDATE	To modify the records in the table using UPDATE command.

Now we will take one example of student table,create two user named manthan,jay and grant permission to them on student table.The procedure as shown below:

1. Create student table in system user which is our default user and display it using select query.



2. Then create user named manthan and password is 123.



3. Provide session user.



4. We use grant query on student table to give permission to manthan without grant option.



5. Login into manthan user.



6. Select student table owned by system user by manthan user.



7. Now we create another user by jay by logging into system user.



8. Then create user named jay and password is 123.



9. Provide session user.



10. Now login into manthan user.



11. Fire grant query which give permission to jay user from manthan user to access student table.



Here it is very important to note that while using grant query in system user we have not use "with grant option". Hence we manthan will not able give permission to access student table to jay.Now we will see that situation

12. Only replace Grant query in step number 4 as below.Other steps remain same and manthan give permission to jay and jay can also access student table as manthan.



Revoke Command :- Use the REVOKE statement to remove privileges from a specific user or all users, to perform actions on database Objects. You can also use the revoke statements to revoke privileges from user.

Revoke Privileges on Table
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax
REVOKE object_privileges
ON object_name
FROM user_name

Now we will try understand each part of syntax

1. Revoke ---> It is keyword.
2. object_privileges ---> listed in table below.
3. ON ---> It is keyword refer to object name on which grant permission is to be given.
4. object_name ---> It refer to object name of database.
5. From ---> It is keyword.
6. user_name ---> It refer to user to which we are giving permission.

Object Privileges Table

Privileges	Description
ALL	All privileges on table.
ALTER	Ability to ALTER TABLE to change the table definition.
DELETE	Ability to perform DELETE statements on the table.
INDEX	Ability to create an index on table with the create index statement.
INSERT	Ability to perform INSERT statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
SELECT	Ability to perform SELECT statements on the table.
UPDATE	To modify the records in the table using UPDATE command.

Now we will consider same example that we study previously.

1. From System user fire revoke query.



2. Login into manthan user.



3. Select student table owned by system user by manthan user.



Here we found that when system fire revoke command on student table then manthan user will not able to access student and find error that table or view does not exist due no privileges.
This is the simple example of grant and revoke command.

I hope this article will help you.