Difference between Grant and Revoke in SQL
FREE
Overview
The most well-known members of the SQL family are GRANT & REVOKE. These are the DCL command kinds that are used to grant users the ability to carry out various tasks. The REVOKE command revokes authorization, whereas the GRANT command is used to authorize users. Let's explore additional differences between GRANT and REVOKE.
What is Grant?
The SQL Grant command is used especially to grant users access to database objects. Users can also provide permissions to other users using this command.
Syntax:
Here, the public is used to allow access to all users, object_name is the name of the database object, user_name is the user to whom access should be granted, and privilege_name is the permission that needs to be granted.
Example:
GRANT choose ON employee TO user1; This command gives user1 the ability to choose data from the employee table. You should be careful when using the WITH provide option because, for instance, if you provide user1 the SELECT privilege on the employee table using the WITH GRANT option, user1 can grant the same access to other users, such as user2 and so forth. In the future, user2 will still have access to the employee table even if you REVOKE user1's SELECT privilege.
Using the previous command, the user Ram has been given access to the accounts database object, allowing him to query or insert data into accounts.
What is Revoke?
If any granted user credentials have been revoked, they will be removed from database objects. It performs actions that are counter to the Grant command. The rights granted to all other users by a specific user U are also withdrawn when that user's privileges are revoked.
Syntax:
Here, the public allows access to all users, object_name is the name of the database object, user_name is the user to whom access should be revoked, and privilege_name is the permission granted or revoked.
Example:
REVOKE SELECT ON employee FROM user1; This command will revoke the user's ability to select data from the employee table. A user's ability to SELECT data from a table will be removed when you REVOKE their SELECT privilege. However, if the user has been granted several users' SELECT privileges on that table, they can SELECT from that table until each user who awarded the privileges revokes them. If privileges were initially given by you, you can keep them.
User Ram's features to query or insert data into the accounts database object have been disabled by the previous command.
Difference between Grant and Revoke
| Sr. no | Grant | Revoke |
|---|---|---|
| 1 | The user is given permissions on the database objects via this DCL command. | If any permissions have been granted to users on database objects, they are removed using this DCL operation. |
| 2 | Users are given access rights by it. | Users' user access rights have been revoked as a result. |
| 3 | The permissions must be specified for each user. | If access is denied to one user, all the specific permissions the user granted to other users will also be denied. |
| 4 | The process of granting permissions will be simple once access is decentralized. | If decentralized access, it may be challenging to revoke the permissions issued. |
Conclusion
We discussed the Grant and Revoke SQL Server commands in this article. I hope you now have a better understanding of this subject. If you know anything more about this, kindly share. We appreciate your suggestions. Do let us know in the comments below.