What is REVOKE Command in SQL?
FREE
The REVOKE command in SQL is used to revoke or withdraw permissions that were previously granted to an account on a database object. Therefore, we can think of REVOKE as a tool to limit a role's or user's ability to perform SELECT, INSERT, DELETE, UPDATEand CREATE statements on database objects., as well as to set constraints like foreign keys and update data records, among other operations.
The REVOKE command is the opposite of the command GRANT, which allows a user account or role access to the database object.
Syntax
This is the syntax for the writing revoke command
Arguments Used
Following are the explanation of the various arguments used in the code:
- PRIVILEGES: We provide the privilege(s) that need to be revoked for a user account in this parameter. The privileges include SELECT , INSERT, DELETE, UPDATEand CREATE commands.
- DATABASE OBJECTS: Database objects are database entities that could range from specific tables to all the tables in a schema.
- ROLE | USER: The USER or the ROLE can be used to specify the user account from which the permissions have to be revoked.
- CASCADE | RESTRICT: We can show that the revocation of the aforementioned PRIVILEGES can also remove the other privileges which derive their powers from the revoked privilege using CASCADE. In the absence of it, we can limit/ RESTRICT revocation to just the designated privilege.
Examples to REVOKE Command in SQL
Example-1: REVOKE UPDATE Privilege
Let us suppose that you want to revoke an updated privilege granted to a user ROLE_A on the data object SALES.
The above code is used to remove all the UPDATE privileges granted to the SALES table.
Example-2: REVOKE SELECT Privilege
The above code is used to revoke the SELECT privilege from a user ROLE_A.
Example-3: REVOKE INSERT Privilege
The above code is used to revoke the INSERT privilege from a user ROLE_A in the EMPLOYEE Data object.
Example-4: REVOKE DELETE Privilege
The above code is used to revoke the DELETE privilege from a user USER_A in the EMPLOYEE Data object.
Example-5: REVOKE TRUNCATE Privilege
The above code is used to revoke the TRUNCATE privilege from a user USER_A in the EMPLOYEE Data object.
Conclusion
- The REVOKE command in SQL is used to revoke or withdraw permissions that were previously granted to an account on a database object.
- REVOKE command is the opposite of the command GRANT
- Privileges, database objects, role, cascade/restrict are a few of the arguments for the revoke clause.
- REVOKE can be used to update, select, insert, delete and truncate privileges.
- One can cancel the grants of others if your SQL authorization ID has SYSADM or an equal level of authority.