GRANT & REVOKE are popular members of the SQL family. These are the types of DCL commands that can be used to assign permission to the users to perform a different task. While the GRANT command can be used to permit the users, the REVOKE command is used to remove the authorisation.
In this article, we will dive deeper into the GRANT and REVOKE in SQL according to the GATE Syllabus for (Computer Science Engineering) CSE. Keep reading ahead to learn more.
Table of Contents
What are GRANT and REVOKE in SQL?
The Data Control Language (DCL) can be used to manage database privileges. A user needs privileges to do any database activity, such as creating tables, views, or sequences. There are two categories of privileges. They are:
1. Object – It includes permissions for the commands or queries in order to perform the operations on the database tables.
2. System – It includes permissions for the creation of a table, session, etc., and all other types of system privileges.
We have two major commands in DCL, namely:
1. GRANT – It is used in order to provide a user with access privileges or other privileges for the DB.
2. REVOKE – It is used in order to take permissions back from a user.
Allowing a User to Create a Session
Whenever we create a user in SQL, we are not permitted to log in or create a session until the user has been granted the appropriate permissions/privileges.
To grant session creation privileges, use the following command:
GRANT CREATE SESSION TO name_of_user;
Allowing a User to Create a Table
In order to allow a user for creating tables in the DB, we can use this command given below:
GRANT CREATE TABLE TO name_of_user;
Providing a User with a Space on the Tablespace to Store Tables
Allowing a user to create a table isn’t enough to allow them to begin storing data in it. Additionally, we must provide the user permission to use the allotted tablespace for their tables and data.
ALTER USER name_of_user QUOTA UNLIMITED ON SYSTEM;
The command given above would alter the details of a user and will provide it with access to unlimited tablespace on the system.
NOTE – The unlimited quota is generally provided to the admin users. Read more on the difference between Grant and Revoke here.
Granting all the Privileges to a User
sysdba refers to a set of privileges that has all the permissions in it. Thus, in case we want to provide privileges to a user, we can grant them this sysdba permission.
GRANT sysdba TO name_of_user
Granting Permission for Creating any Table
A user is sometimes restricted from creating tables along with names that are reserved for the system tables. However, we can grant privileges to any user in order to create a table using the command given below:
GRANT CREATE ANY TABLE TO name_of_user
Granting Permission for Dropping a Table
Just like the title suggests, in case we want to allow a user to drop a table from the DB, then grant this very privilege to a user:
GRANT DROP ANY TABLE TO name_of_user
Taking Back any Permissions
And, in case we want to take the privileges back from any user, we use the REVOKE command. It goes as follows:
REVOKE CREATE TABLE FROM name_of_user
Keep learning and stay tuned to get the latest updates on the GATE Exam along with Eligibility Criteria, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.
Also Explore,
Comments