REVOKE Command in SQL

The REVOKE command in SQL helps control who can access or modify data by removing previously granted permissions. It ensures that only authorized users can perform actions like reading, inserting, or deleting records. This is especially important in sectors like finance or healthcare, where data security is critical. As roles change, REVOKE allows quick updates to access rights. It is an important tool for managing evolving user roles and minimizing risk. In this blog, we will learn about the REVOKE command in SQL and how to use it in detail with examples.

Table of Contents:

• What is the REVOKE Command in SQL?
• Arguments Used in the REVOKE Command in SQL
• Examples of the REVOKE Command in SQL
• Advanced Use Cases of the REVOKE Command in SQL
• Difference Between REVOKE and GRANT in SQL
• Common Errors While Using the REVOKE Command in SQL
• Best Practices for Using the REVOKE Command in SQL
• Conclusion

What is the REVOKE Command in SQL?

The REVOKE command in SQL plays a key role in database security because it lets you remove permissions that were previously given to a user or group. Think of it like giving someone a key to your house, and then changing the lock when they no longer need access. REVOKE helps control who can access sensitive data or run commands that might harm your data or system.

For example, if you had allowed someone to view or edit a table and later decided they should no longer have that access, you can revoke it right away. Whether it is reading data with SELECT, adding it with INSERT, changing it with UPDATE, or removing it with DELETE, you can take back that access at any time. This command is especially useful in environments where data privacy and access control are critical.

Syntax of the REVOKE Command in SQL:

Understanding how the REVOKE command works in SQL is crucial. Let’s learn the syntax for the REVOKE command.

REVOKE privilege_type ON object_name FROM user_name;

Master SQL Like a Pro – Enroll Today!

Unlock real-world SQL skills with hands-on projects, expert guidance, and lifetime access. Invest in your data career now!

Explore Program

Arguments Used in the REVOKE Command in SQL

There are a few important parts of the REVOKE command in SQL that you should have a deeper understanding of:

1. privilege_type

• Includes permissions like SELECT, INSERT, UPDATE, DELETE, EXECUTE, and ALL.
• Defines what actions the user was allowed to perform before revoking the access.

2. object_name

• Can be a table, view, sequence, stored procedure, or other database object.
• It tells SQL which object’s access you are restricting.
• user_name or role_name: identifies who is affected by the REVOKE command.
• It can also be a role if you are using role-based access control.

3. CASCADE / RESTRICT

• CASCADE: Automatically revokes any dependent privileges created by the affected user.
• RESTRICT: Refuses to revoke if there are dependencies

Although never required, these optional arguments can provide you with more fine-grained control over your access management.

Examples of the Revoke Command in SQL

There are many examples that can be used with the REVOKE command in SQL. Before starting with examples, let’s create a dataset.

CREATE TABLE BankAccounts (
    AccountID INT PRIMARY KEY,
    AccountHolderName VARCHAR(100),
    Balance DECIMAL(10, 2)
);
INSERT INTO BankAccounts VALUES (101, 'Rahul Verma', 15000.00);
INSERT INTO BankAccounts VALUES (102, 'Neha Sharma', 22000.00);
INSERT INTO BankAccounts VALUES (103, 'Arjun Mehta', 18000.00);
GRANT SELECT, UPDATE ON BankAccounts TO Rahul_Verma;
GRANT SELECT, INSERT ON BankAccounts TO Neha_Sharma;
GRANT ALL ON BankAccounts TO Arjun_Mehta;

1. REVOKE UPDATE Privilege in SQL

This is a common case in banks and organizations where employees are rotated across departments or certain UPDATE privileges are removed for audit or compliance reasons. Revoking such access prevents unauthorized modifications to the data and ensures only approved users can perform UPDATE operations. This aligns with the principle of least privilege in cybersecurity, where users are given only the permissions necessary for their roles.
Let’s say the role of Meena is changing. She no longer needs to update any account balance. We can use the REVOKE command in SQL to take away her update rights.

Example:

REVOKE UPDATE ON BankAccounts FROM Meena;

Explanation: Here, Meena will have the selected permission, which is read permission of the data. So, Meena can’t make any other changes to the data. Even if she tries to do so, the system will return an error.

UPDATE BankAccounts SET Balance = 17000.00 WHERE AccountID = 101;

This is useful when employees shift roles or when access is restricted for audit or compliance. It helps prevent data tampering and ensures only authorized users make changes, following the principle of least privilege in cybersecurity.

2. REVOKE SELECT Privilege in SQL

A SELECT statement helps identify users whose roles have changed, making it easier to manage permissions. For example, if Vikram no longer needs read-only access, the REVOKE command can remove his SELECT privilege.

REVOKE SELECT ON BankAccounts FROM Vikram;

Once you execute this command, Vikram can no longer use the SELECT statement. But he can insert new records into the existing data.

Note: But if he uses the SELECT statement, it will raise an error as his permission has been revoked. This is mostly used where the data contains sensitive information. As a file’s read access is very crucial if that data is sensitive.

3. REVOKE INSERT Privilege in SQL

Revoking the INSERT privilege is useful when users switch roles or when adding new data is no longer part of their responsibility. It ensures that only authorized users can insert records into a table. For instance, Vikram can be restricted to viewing data by revoking his INSERT privilege.

REVOKE INSERT ON BankAccounts FROM Vikram;

Command Example:

INSERT INTO BankAccounts VALUES (104, 'Kavita Nair', 19000.00);

Explanation: Here, Vikram cannot add new records because his INSERT access was removed. If he tries, it will show a permission denied error.

In short, the REVOKE command in SQL is used to maintain the data accuracy and integrity that prevents unauthorized access to the data. This is very useful when you want to maintain a large organisation. This will ensure proper control over the data in an organisation.

4. REVOKE DELETE Privilege in SQL

Removing data from a database is a serious matter. Most of the time, it is irreversible. If you accidentally delete the wrong data, the loss can be significant. Let’s say that the branch manager, Amit, has complete ownership of the BankAccounts table and may DELETE any BankAccounts table record. But now the change is for the company to position only senior auditors to DELETE records.

To change this, use:

REVOKE DELETE ON BankAccounts FROM Amit;

Even though Amit still has other privileges (like SELECT, UPDATE, and INSERT), he can no longer execute:

DELETE FROM BankAccounts WHERE AccountID = 102;

This level of protection is crucial in larger companies that have employees whose roles are in a constant state of change. Without safeguards such as the REVOKE command, there is a risk of improper access being left unattended. By revoking the DELETE privilege, you prevent accidental or intentional loss of data. In SQL environments, DELETE access is one of the most sensitive permissions. In many cases, archiving data is a better option than deleting it. Revoking DELETE access helps ensure safer and more reliable data management.

5. REVOKE TRUNCATE Privilege in SQL

TRUNCATE is more serious than DELETE because it removes all rows without logging individual deletions and often cannot be rolled back. Just imagine providing that access to someone who runs TRUNCATE on a production table by error, in mere seconds, all critical data could be removed.
Some SQL dialects, like PostgreSQL, don’t allow explicit GRANT or REVOKE on TRUNCATE, as it’s typically covered under ownership or DELETE privileges. Always refer to your DBMS documentation. As an alternative, you can manage access with a command like this.

REVOKE TRUNCATE ON BankAccounts FROM Amit;

If your DBMS does not support explicit REVOKE on TRUNCATE, revoke ALL privileges first, then re-grant only the specific ones needed.

REVOKE ALL PRIVILEGES ON BankAccounts FROM Amit;
GRANT SELECT, UPDATE, INSERT ON BankAccounts TO Amit;

As a solution, these commands will give Amit limited access to the data so that he won’t erase the entire table accidentally.

Advanced Use Cases of the REVOKE Command in SQL

The REVOKE command in SQL will not only be used for limited basic use, but it can also be used to handle large, complex queries and role-based access, hierarchical permissions, and integration with stored procedures.

1. REVOKE Command for Multiple Privileges

One of the conveniences of using REVOKE in SQL is that many privileges can be revoked at once to a user or role. This is an advantage when entitlements need to be decreased for a user or role in a bulk approach instead of slowly receding a user or role’s privileges.

For example, consider a branch manager named Amit. If the company wants him to only view data from the BankAccounts table, his INSERT, UPDATE, and DELETE permissions should be revoked to restrict any changes.

REVOKE INSERT, UPDATE, DELETE ON BankAccounts FROM Amit;

After the execution of this command, Amit can only use the SELECT privilege. This will be very useful when you want to revoke more than one privilege. You can use the command like this also if you don’t want to put it in commas:

REVOKE INSERT ON BankAccounts FROM Amit;
REVOKE UPDATE ON BankAccounts FROM Amit;
REVOKE DELETE ON BankAccounts FROM Amit;

You can also use this format for roles. Let’s say we have role auditors and the role’s initial capabilities were to have full access to review, update, and insert data. Now the company is deciding that the auditors will only have read-only access.

REVOKE INSERT, UPDATE ON BankAccounts FROM auditors;

This approach ensures a swift and consistent way of adjusting access control across multiple users or roles. Especially in larger databases with hundreds of tables and users, batch revokes like this improve maintainability and security.

2. REVOKE Command from Multiple Users

You can also revoke access from multiple users using the REVOKE command in SQL. This has an added benefit when multiple employees in similar roles experience a change to their responsibilities.
For example, say the users Meena, Vikram, and Arjun all had SELECT access to the BankAccounts table. Now, a policy change requires only Amit to have this access. To REVOKE SELECT from all three users using one command:

REVOKE SELECT ON BankAccounts FROM Meena, Vikram, Arjun;

This means you do not need to run the REVOKE statement three times. The same is true when eliminating temporary users altogether after the conclusion of a project. You can revoke access with:

REVOKE INSERT, UPDATE ON BankAccounts FROM Priyanka, Ravi, Sheetal;
-- SQL also allows you to combine user and role-based revokes
REVOKE SELECT ON BankAccounts FROM vip_team, Tarun, Kavita;

When there are multiple users in an organization, this technique will make sure to revoke their access correctly and consistently. It helps to keep the organisation in order, which will reduce human error.

3. REVOKE Command in Stored Procedures

Stored Procedures are a set of SQL statements that you have written to accomplish a task in SQL. Procedures can sometimes perform operations on sensitive tables. Consequently, it is crucial to manage user access to procedures.
Here, let’s consider an example where that bank is storing all the account information and balances in its database. In this, we will update the new balance of a customer account.

At first, we will give change access to Meena:

CREATE PROCEDURE UpdateBalance
    @account_id INT,
    @new_balance DECIMAL
AS
BEGIN
    UPDATE BankAccounts SET Balance = @new_balance WHERE AccountID = @account_id;
END;
GRANT EXECUTE ON PROCEDURE UpdateBalance TO meena;

As per the new policy that only managers can perform balance updates, we revoke Meena’s access.

REVOKE EXECUTE ON PROCEDURE UpdateBalance FROM Meena;
-- This stops Meena from being able to run
EXEC UpdateBalance(101, 20000.00);

Again, this can help prevent users from misusing or accidentally executing crucial updates. The REVOKE keyword will ensure that your stored procedures are only runnable by users who are supposed to.
In any applications where stored procedures form the bulk of the business logic, revoking access to stored procedures should be part of the overall system hardening.

4. REVOKE Temporary or Time-Limited Access in SQL

In many cases, access to a system is given temporarily. For example, a contractor or intern may be allowed to use the database, but their access must be removed later to keep the system secure.

You can manually revoke access using the following syntax:

REVOKE ALL ON BankAccounts FROM intern_sita;

Modern SQL systems like PostgreSQL and MySQL (with plugins or automation) can also support scheduled REVOKE operations using procedures or external scripts.

A simple automation can be set up using CRON in Linux or a scheduled task in Windows to run a command like:

REVOKE SELECT INSERT ON BankAccounts FROM intern_sita

In platforms that support built-in roles with expiration, such as Oracle or SQL Server with custom policies, access can be revoked automatically based on session or role settings.

Benefits of revoking temporary access include:

• Ensuring compliance with security policies and procedures
• Enabling smooth automated offboarding processes
• Reducing risks from inactive or forgotten users

Even if your SQL version does not support time-based access control directly, you can still use REVOKE effectively by integrating it with scheduled scripts or manual reviews for managing temporary users.

Difference Between REVOKE and GRANT in SQL

To simplify the difference table:

• GRANT is like giving someone the keys to a room.
• REVOKE is like taking away the keys.

Both GRANT and REVOKE statements are necessary when managing user access. When you know how and when to use them, it will help with managing a secure and efficient database.

Common Errors While Using the REVOKE Command in SQL

The REVOKE command in SQL is a powerful tool, but it is easy to make mistakes with it. Developers and DBAs often run into issues that cause errors or unexpected outcomes, so here are some of the most common mistakes and how to avoid them.

1. Attempting to REVOKE Privileges that Never Existed: Trying to revoke a privilege not granted to a user can either throw an error or do nothing, depending on the SQL engine, so always verify the access before issuing REVOKE.

2. Not Referring to the Exact Object: Using the wrong table name or incorrect case, like bankaccounts instead of BankAccounts in a case-sensitive system, will cause the REVOKE command to fail completely.

3. Revoking from the Wrong User: Mistaking similar usernames or roles and revoking from the wrong one could leave the real user with access, so confirm using SHOW GRANTS FOR user_name before executing.

4. Use of REVOKE Without Sufficient Admin Privileges: Only users with proper authority, like DBAs or owners, can revoke permission, so issuing REVOKE as a normal user will result in permission errors.

5. Syntax Errors in Multi-User Revokes: Missing commas or incorrect spacing, such as REVOKE FROM Meena Vikram will fail, so use correct syntax like REVOKE FROM Meena, Vikram for success.

Best Practices for Using the REVOKE Command in SQL

Understanding how to use the REVOKE command is not only about learning the syntax. It is more about knowing how to build sensible, secure, and scalable access policies for your database. Below are some best practices to follow to use this command correctly.

1. Use the Principle of Least Privilege: Grant only the minimum access needed. If a user’s role changes and you need to reduce access, REVOKE makes it easy. This helps reduce the chance of users having more access than required.

2. Conduct Regular Audits: Check access levels regularly to see who has access and what permissions they hold. Use tools like SHOW GRANTS FOR user_name to view a user’s permissions and ensure they match your policies.

3. Use Roles instead of Individual Users: Assign permissions to a role rather than individual users. Add users to roles and revoke role access if needed. This simplifies permission tracking and reduces complexity in large teams.

4. REVOKE Access when the Employee Exits: Have a checklist for employee exits that includes revoking access to databases.

5. Do Not Grant ALL: Avoid granting ALL unless absolutely necessary. Specify only the permissions needed. This helps make REVOKE simpler since you do not need to track every individual permission later.

6. Use Scripts for a Larger Dataset: For large tables or many users, use SQL scripts or automation to generate REVOKE statements. This improves consistency and lowers the chance of manual mistakes that affect users.

Conclusion

For SQL administrators, the REVOKE command is a vital tool for managing security and user access, especially in enterprise-level environments. While it may seem minor in smaller applications, its importance grows with the scale of the system. By understanding who can use it, knowing the correct syntax, and applying it to remove outdated or unnecessary privileges, you ensure only the right people have access to the right data. Whether revoking access for one user or many, using REVOKE effectively ensures data integrity and protects your organization from unauthorized access.

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions, prepared by industry experts.

SQL Simplified: Learn for Free, Grow for Life

Master the language of data and start your journey toward a data-centric career!

Explore Program

REVOKE Command in SQL – FAQs