Grant Command in SQL

What is DCL (Data Control Language)?

Data Control Language (DCL) is a computer programming language with syntax intended to manage access to data kept in databases. It is a part of Structured Query Language (SQL). One of the logical groups of SQL Commands is Data Control Language.

The SQL commands known as Data Control Language (DCL) that allow a user to access, alter, or operate with various privileges in order to control the database. It enables the database owner to provide access, cancel access, and modify the permissions that have been granted as needed. The main purpose of DCL is to enforce data security.

What is the Grant command in SQL?

To define and access a database in SQL, various types of database languages are utilized. These languages enable us to update data and manage user access, among other things.

Data Control Language (DCL), one of these languages, is used to edit and retrieve data using particular SQL queries. It has two commands, revoke and grant, which are used to give and withdraw specific privileges to users in a multi-user database.

In layman's terms, we can say that we can manage a user's permissions and rights with the aid of these commands.

Grant Command

When a user needs to alter or retrieve a database object like a table, view, sequence, synonym, or index, they can use the SQL command to grant them the necessary permissions or privileges.

Another benefit of using this command is that a third user will also receive the same permissions.

Different types of privileges given by grant command

Syntax of Grant command in SQL

The syntax of the Grant command is as follows:

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

There are some parameters of the Grant command:

• privilege_name: It displays the authorization that must be given.
• object_name: The name of the database object, such as a view, table, index, etc., is represented by this.
• user name: It stands for the user who has to be granted permission.
• PUBLIC: This signifies that all database users have access.
• role_name: It indicates that users who fit a certain role have access to these resources.

Examples of Grant Command in SQL

Let's say we have a database related to the university where we can establish a table with the following information about the student and their registered course are as follows:

CREATE TABLE Student(stud_name varchar(30), roll_no varchar(10), course_enrolled varchar(30));

A table with the three columns student name (stud_name), roll number (roll_no), and course enrollment (course_enrolled) will be generated as a result of this command.

Now let's insert the values in the table:

INSERT INTO Student
VALUES("Rohit", "SG19333", "Bachelors’s of Computer Application"),
VALUES("Akash","SG19444", "Computer Science & Engineering"),
VALUES("Nikhar", "SG19999", "Information Technology"),
VALUES("Yashraj","SG15555", "Software Engineering");

Using the SELECT command, the following table can be seen:

SELECT * from Student

Output:

stud_name	roll_no	course_enrolled
Rohit	SG19333	Bachelors’s of Computer Application
Akash	SG19444	Computer Science & Engineering
Nikhar	SG19999	Information Technology
Yashraj	SG15555	Software Engineering

To grant permission to a single user: The following command is used to grant the college's director the SELECT permission.

GRANT SELECT 
ON Student
TO Director

To grant permission to the public: To give all database users the SELECT access, we use the following command.

GRANT SELECT
ON Student
TO PUBLIC

WITH GRANT OPTION Clause

With the use of the WITH GRANT OPTION clause, a user who already has access to a certain table may grant that access to additional users as well.

For instance, in the university database situation mentioned above, if we use this clause to grant the Director the SELECT permission, then the Director can grant those permissions to any user of the database.

GRANT SELECT
ON STUDENT
TO Director
WITH GRANT OPTION

To grant all permissions to a particular user

Either we can mention each keyword one by one following the grant keyword or we can use the ALL keyword to allow the specified user to do all operations in order to grant them all permissions.

GRANT SELECT, INSERT, UPDATE, DELETE
ON Student
TO Director
Or we can also use this command
GRANT ALL
ON Student
TO Director