by
SQL Tutorial › Security › REVOKE

SQL REVOKE Statement

IntermediateLesson 68 of 697 min read5 queries run on MySQL 8.4

REVOKE is the opposite of GRANT: it takes privileges away from an account or role. You need it when someone changes team, when an application no longer needs write access, or after an audit finds an account with too much power. The privileges are removed immediately.

Advertisement
In this lesson
  • Revoke individual privileges and all privileges
  • Revoke roles and the grant option
  • Verify with SHOW GRANTS
  • Avoid the “revoke at a different level” trap

REVOKE syntax

MySQL
REVOKE privilege [, privilege ...]ON levelFROM 'user'@'host';

The level must match how the privilege was granted (company.*, company.orders, …).

Revoke one privilege

The shop application should no longer delete rows (it uses soft deletes now):

MySQL
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p#App2026';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'shop_app'@'localhost';REVOKE DELETE ON company.* FROM 'shop_app'@'localhost';SHOW GRANTS FOR 'shop_app'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p
Query OK, 0 rows affected
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'shop_app'@'local…
Query OK, 0 rows affected
mysql> REVOKE DELETE ON company.* FROM 'shop_app'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'shop_app'@'localhost'
Grants for shop_app@localhost
GRANT USAGE ON *.* TO `shop_app`@`localhost`
GRANT SELECT, INSERT, UPDATE ON `company`.* TO `shop_app`@`localhost`
2 rows in set
How this query works, step by step
  1. The account starts with four privileges on company.*.
  2. REVOKE DELETE removes one; SHOW GRANTS now lists SELECT, INSERT, UPDATE.
  3. The change applies to new statements immediately, even in sessions that are already connected.
Advertisement

Revoke everything

MySQL
CREATE USER 'contractor'@'localhost' IDENTIFIED BY 'C0ntract#2026';GRANT SELECT, INSERT ON company.* TO 'contractor'@'localhost';GRANT SELECT ON company.employees TO 'contractor'@'localhost';REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'contractor'@'localhost';SHOW GRANTS FOR 'contractor'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'contractor'@'localhost' IDENTIFIED BY 'C0ntract
Query OK, 0 rows affected
mysql> GRANT SELECT, INSERT ON company.* TO 'contractor'@'localhost'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.employees TO 'contractor'@'localhost'
Query OK, 0 rows affected
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'contractor'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'contractor'@'localhost'
Grants for contractor@localhost
GRANT USAGE ON *.* TO `contractor`@`localhost`
1 row in set
How this query works, step by step
  1. The contractor had privileges at two levels: database and table.
  2. REVOKE ALL PRIVILEGES, GRANT OPTION FROM ... (without ON) removes them at every level.
  3. Only USAGE remains: the account can log in but do nothing. To remove the account completely, DROP USER.
Advertisement

The level trap

A privilege granted at database level cannot be revoked for one table:

MySQL
CREATE USER 'analyst1'@'localhost' IDENTIFIED BY 'An@lyst2026';GRANT SELECT ON company.* TO 'analyst1'@'localhost';REVOKE SELECT ON company.employees FROM 'analyst1'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'analyst1'@'localhost' IDENTIFIED BY 'An@lyst2026'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.* TO 'analyst1'@'localhost'
Query OK, 0 rows affected
mysql> REVOKE SELECT ON company.employees FROM 'analyst1'@'localhost'
ERROR 1147 (42000): There is no such grant defined for user 'analyst1' on host 'localhost' on table 'employees'
How this query works, step by step
  1. SELECT was granted on company.*, not on company.employees.
  2. There is no table-level grant to remove, so MySQL reports error 1147.
  3. To give “all tables except employees”, revoke the database grant and grant SELECT table by table (or through views).

Revoke a role

MySQL
CREATE ROLE 'analyst';GRANT SELECT ON company.* TO 'analyst';CREATE USER 'priya'@'localhost' IDENTIFIED BY 'Pr1ya#2026';GRANT 'analyst' TO 'priya'@'localhost';REVOKE 'analyst' FROM 'priya'@'localhost';SHOW GRANTS FOR 'priya'@'localhost';
ResultMySQL 8.4
mysql> CREATE ROLE 'analyst'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.* TO 'analyst'
Query OK, 0 rows affected
mysql> CREATE USER 'priya'@'localhost' IDENTIFIED BY 'Pr1ya
Query OK, 0 rows affected
mysql> GRANT 'analyst' TO 'priya'@'localhost'
Query OK, 0 rows affected
mysql> REVOKE 'analyst' FROM 'priya'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'priya'@'localhost'
Grants for priya@localhost
GRANT USAGE ON *.* TO `priya`@`localhost`
1 row in set

Changing the role itself (REVOKE INSERT ON company.* FROM 'developer';) changes the access of every account that has the role.

Advertisement

Revoke the grant option

MySQL
CREATE USER 'team_lead'@'localhost' IDENTIFIED BY 'L3ad#2026';GRANT SELECT ON company.* TO 'team_lead'@'localhost' WITH GRANT OPTION;REVOKE GRANT OPTION ON company.* FROM 'team_lead'@'localhost';SHOW GRANTS FOR 'team_lead'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'team_lead'@'localhost' IDENTIFIED BY 'L3ad
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.* TO 'team_lead'@'localhost' WITH GRANT OPTION
Query OK, 0 rows affected
mysql> REVOKE GRANT OPTION ON company.* FROM 'team_lead'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'team_lead'@'localhost'
Grants for team_lead@localhost
GRANT USAGE ON *.* TO `team_lead`@`localhost`
GRANT SELECT ON `company`.* TO `team_lead`@`localhost`
2 rows in set

The team lead keeps SELECT but can no longer give it to others.

Quick quiz

1. How do you remove all privileges of an account at every level?

2. SELECT was granted ON company.*. What happens with REVOKE SELECT ON company.orders?

3. Does a REVOKE affect sessions that are already connected?

Advertisement

REVOKE FAQ

What is the difference between GRANT and REVOKE?

GRANT gives privileges to an account or role; REVOKE removes them.

How do I remove all privileges from a MySQL user?

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; The account keeps only USAGE. Use DROP USER to delete it.

Why does REVOKE give error 1141 or 1147?

The privilege was not granted at the level you named. Check with SHOW GRANTS and revoke at the same level (db.*, db.table or global).