We have already learned about the DDL and DML Statements in the previous articles "SQL For Beginners - DDL Statements" and "SQL For Beginners - DML Statements" respectively. In this article, we are going to learn about the DCL Commands of SQL.
We have already seen a small overview about the DCL Commands in the introductory article of this series "SQL For Beginners - Introduction". Let us learn about these DCL Commands in detail.
SQL provides us with 2 DCL Commands - GRANT and REVOKE. DCL Commands are the Data Control Language Commands and as the name suggests, they are used to control access to the various database objects. Data stored in our databases is very important to us. Companies store a huge chunk of operational data in the databases. Also, in today's world, data has a lot of value. There might be some sensitive data or confidential data too in the database. So, unauthorized access to the data should be prevented in order to achieve security in our database.
This security can be achieved with the help of DCL Commands. The DCL Commands allows us to give permission to a specific user to perform only specific operations on the database and deny him/her from performing the operations which are not meant to be performed by him/her.
GRANT Command:
The GRANT Command is used to give permission to a user to perform a particular operation on a particular object.
Syntax:- GRANT Privilege_Name
- ON Object_Name
- TO User_Name;
where,
- Privilege_Name is the access which is to be given to user. It can be SELECT, INSERT, DELETE, UPDATE, ALL, etc.
- Object_Name can be any database object like table, view, sequence, etc.
- User_Name is the name of the user to whom the privilege is to be given.
Example:
GRANT SELECT ON Students To Raju;
This query will grant the privilege of selecting from Students table to Raju. In short, it will allow Raju to use SELECT Statement to query Students table.
GRANT ALL ON Students To Peter;
This query will grant all the privileges (INSERT, UPDATE, DELETE, SELECT and REFERENCES) to Peter for Students table. This means Peter can insert data into Students table, update existing data, delete data, select data and even create REFERENCES constraint on the table.
In this way, we can give privileges to the users so that the access over the database is controlled.
REVOKE Command:
The REVOKE Command is used to take away privilege from a particular user.
Syntax:- REVOKE Privilege_Name
- ON Object_Name
- FROM User_Name;
REVOKE SELECT ON Students From Raju;
This will do exactly opposite of the GRANT Statement. This will take away the previously granted SELECT privilege on Students table from Raju.
REVOKE ALL ON Students From Peter;
This will revoke all the privileges (INSERT, UPDATE, DELETE, SELECT and REFERENCES) from Peter for Students table.
This was an overview to the DCL commands of SQL.