SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries What are single row and multiple row subqueries?

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL How to get current year in SQL server 2012? User Input in PL/SQL

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.

Grant Command in SQL

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_nameroll_nocourse_enrolled
RohitSG19333Bachelors’s of Computer Application
AkashSG19444Computer Science & Engineering
NikharSG19999Information Technology
YashrajSG15555Software 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