GRANT Command in SQL

What is the Grant Command in SQL?

Four types of database languages are used to define and access a database. These languages allow users to define and update data, control access to the data, search, etc.

One of these languages is the Data Controlling language (DCL), which is used to modify and retrieve data by writing specific SQL queries. It contains two commands: grant and revoke, which are used to give and withdraw specific privileges (as defined by query) to the user in a multi-user database. In other words, these commands are used to control the rights and permissions of a user.

Let's see the definition of these commands one by one

1) Grant Command: This is a SQL command which is used to provide privileges/permissions to modify and retrieve database objects like tables, views, sequences, indexes, and synonyms. This command also gives privileges like providing the same permissions to some third user as well.

Syntax of Grant Command in SQL

Let's understand the syntax of the grant command

Parameters of Grant Command in SQL

• privilegeName: It represents the permission that is to be granted.
• objectName: It represents the name of the database object, i.e., view, table, index, etc.
• userName: It represents the user to which permission is to be provided.
• public: It represents that all database users are given permissions.
• role_name: It represents that users with a particular role are given these permissions.

In the next section, we shall learn to implement these commands in practice and give permissions to different users using the grant command.

Examples of Grant Command in SQL

Imagine building a university database where you create a table that contains the information of each student along with the course enrolled as follows:

This command will create a table that contains three columns of student name(stud_name), roll no(roll_no), and course enrolled(course_enrolled), respectively.

Let's insert some records into the table

The table generated can be viewed using the SELECT command as follows:

This will list all records in the table.

Output

1) To grant SELECT permission to a single user To grant the SELECT permission to the Director of the college, we write the following command.

2) To grant SELECT permission to public We write the following command to grant the SELECT permission to all database users.

The WITH GRANT OPTION clause:

This clause is used to permit the user who already has some permissions on a particular table so that they can give those access to other users as well. For example, in the above scenario of the university database, while giving the SELECT permission to the Director, if we use this clause, then Director can grant those permissions to any user of the database.

To grant all permissions to a particular user:

To grant all permissions to a particular user, either we can list all keywords one by one after the grant keyword, or we can use the ALL keyword to permit the given user for all operations.

Or we can also use