The key difference between grant and revoke is that grant gives a privilege to the user while revoke takes back the privilege granted to the user.
SQL provides different authorization levels for users. Grant and revoke are two such commands. Grant command allows giving an authorization to a user while revoke command allows withdrawing the authorization level from the user.
CONTENTS
1. Overview and Key Difference
2. What is grant
3. What is revoke
4. Side by Side Comparison – grant and revoke in Tabular Form
5. Summary
What is grant?
DBMS is a system software to create and manage databases. Furtheremore, SQL or the Structured Query Language is the language to handle databases. Therefore, it allows to insert, modify and retrieve data in a database. There are also various categories in SQL, such as DDL, DML and DCL. Data Definition Language (DDL) allows to create and restructure database objects. The create, alter, drop are some DDL commands. Data Manipulating Language (DML) allows operating data in the database. The select, insert, update and delete are some examples of DML commands. Data Control Language (DCL) allows controlling access to data within the database. The grant and revoke are two DCL commands that provide database security.
Figure 01: SQL Database
The grant command provides access or privileges on the database objects for the users. The syntax is as follows.
grantprivilege_name on object_name
to {username} [grant option];
According to the above, the privilege_name is the access right or privilege granted to the user. The object_name is the name of the database object. It can be a table, view etc. The username is the name of the user who gets the access right. The grant option is optional. It allows users to provide access rights for other users.
The command to grant permission to create table is as follows.
grant create table to username
The command to grand permission to drop table is as follows.
grant drop table to username
Those are few SQL statements with grant command.
What is revoke?
The revoke command takes away the users access rights or privileges to the database object. The syntax is as follows.
revoke privilege_name on object_name
from username
Following is an example of taking back the privilege of creating tables from a specific user.
revoke create table from username
In brief, the given two statements elaborate the use of grant and revoke. The below statement grants a select privilege on student table to user1.
grant select on student to user1
The below statement, revoke the select privilege on student table from user1.
revoke select on student from user1
What is the Difference Between grant and revoke?
grant vs revoke |
|
| grant is a DCL command that allows providing privileges for the users on the database objects. | revoke is a DCL command that allows taking back the permission assigned to a user. |
| In Decentralized Control | |
| The grant is easier. | The revoke is complex. |
| Usage | |
| Allows assigning access rights to the users. | Allows removing the access rights from the users. |
Summary – grant vs revoke
grant and revoke are two essential DCL commands. DCL is a sub category of SQL. The difference between grant and revoke is that grant gives a privilege to the user while revoke takes back the privilege granted by the user.
Reference:
1.“Using GRANT and REVOKE.” Python Relational and Logical Operators | Studytonight. Available here
Image Courtesy:
1.’Sql-server-ce-4-logo’ By Microsoft – Microsoft Website, (Public Domain) via Commons Wikimedia