How to REVOKE Permissions in MySQL

How do I remove user privileges with MySQL REVOKE?

REVOKE removes previously granted privileges or roles from one or more MySQL accounts.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use REVOKE instead of DROP USER?

REVOKE lets you strip specific privileges while keeping the account active, avoiding broken applications that still need limited access.

What is the basic REVOKE syntax?

Use REVOKE privilege_list ON object FROM user; to remove rights from a database object without touching other permissions.

How do I remove all privileges at once?

Pass ALL PRIVILEGES to instantly clear every right the user holds on the target object.

Can I revoke column-level permissions?

Yes.List columns in parentheses: REVOKE SELECT (price, stock) ON Products FROM 'sales_rep'@'%';

Step-by-step: revoking ecommerce privileges

1️⃣ Identify current grants

Run SHOW GRANTS FOR 'sales_rep'@'%'; to confirm existing rights.

2️⃣ Revoke the unwanted rights

Execute REVOKE INSERT, UPDATE ON Products FROM 'sales_rep'@'%';

3️⃣ Verify changes

Repeat SHOW GRANTS to ensure the privileges were removed.

Best practices for production databases

Audit roles monthly, use roles instead of direct user grants, and test revokes in staging before production.

Common pitfalls to avoid

Always include the host part of the account and limit transactions during privilege changes to prevent lock errors.

Why How to REVOKE Permissions in MySQL is important

How to REVOKE Permissions in MySQL Example Usage


-- A merch manager should no longer update stock levels
REVOKE UPDATE ON ecommerce.Products FROM 'merch_manager'@'%';
-- Confirm
SHOW GRANTS FOR 'merch_manager'@'%';

How to REVOKE Permissions in MySQL Syntax


REVOKE
    priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] db_name.table_name | *.* | db_name.*
    FROM user_or_role [, user_or_role] ...
    [WITH GRANT OPTION];

Examples with ecommerce context:
-- Remove INSERT from entire Orders table
REVOKE INSERT ON ecommerce.Orders FROM 'sales_rep'@'%';

-- Remove SELECT on price, stock columns of Products
REVOKE SELECT (price, stock) ON ecommerce.Products FROM 'analyst'@'10.0.0.%';

-- Remove all rights and the ability to grant further rights
REVOKE ALL PRIVILEGES, GRANT OPTION ON ecommerce.* FROM 'contractor'@'localhost';

Common Mistakes

Omitting the host in the account string. 'sales_rep' is different from 'sales_rep'@'%'. Always specify both user and host to target the correct account.
Trying to revoke privileges that were never granted. MySQL returns "There is no such grant defined." Check existing grants first with SHOW GRANTS.