How to remove permissions from MySQL or MariaDB users

Reducing database access is a fast way to cut blast radius when an application no longer needs a capability, a credential is suspected of misuse, or a user’s responsibilities change. Removing stale privileges enforces least privilege and helps prevent accidental writes or unexpected reads in sensitive schemas.

In MySQL and MariaDB, privileges are granted to an account identified by both a username and a host pattern in the form 'user'@'host'. Effective access can come from direct grants on *.*, db.*, or specific objects, and it can also be inherited through assigned roles, so reviewing current grants is the safest starting point.

Revocations made with REVOKE take effect immediately, and the most common “nothing happened” outcome is targeting the wrong 'user'@'host' entry or leaving a role in place that still provides the permission. A broad revoke can break production workloads, so prefer schema-scoped revocations unless a full lock-down is required.

1. Log in to the server with an administrative account.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 77
   Server version: 8.0.44 MySQL Community Server - GPL
   
   Copyright (c) 2000, 2025, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

   On systems where root uses socket authentication, sudo mysql may be the expected entry point.

2. List matching accounts to confirm the exact 'username'@'host' entry to modify.

   mysql> SELECT User, Host FROM mysql.user WHERE User = 'revokeuser';
   +------------+-----------+
   | User       | Host      |
   +------------+-----------+
   | revokeuser | %         |
   | revokeuser | localhost |
   +------------+-----------+
   2 rows in set (0.00 sec)

   Revokes apply only to the exact account row, so 'username'@'localhost' and 'username'@'%' are different targets.

3. Review the current grants for the target account.

   mysql> SHOW GRANTS FOR 'revokeuser'@'%';
   +---------------------------------------------------------------+
   | Grants for revokeuser@%                                       |
   +---------------------------------------------------------------+
   | GRANT USAGE ON *.* TO `revokeuser`@`%`                        |
   | GRANT SELECT, INSERT, UPDATE ON `appdb`.* TO `revokeuser`@`%` |
   +---------------------------------------------------------------+
   2 rows in set (0.00 sec)

   Role-based access can appear as GRANT 'role' and SET DEFAULT ROLE lines, and that access remains until the role is revoked or changed.

4. Revoke specific privileges from the account for a schema.

   mysql> REVOKE SELECT, INSERT ON `appdb`.* FROM 'revokeuser'@'%';
   Query OK, 0 rows affected (0.00 sec)

   Changes from GRANT and REVOKE apply immediately, so FLUSH PRIVILEGES is only needed after direct edits to grant tables.

5. Revoke all privileges from the account for a schema when a complete schema lockout is required.

   mysql> REVOKE ALL PRIVILEGES ON `appdb`.* FROM 'revokeuser'@'%';
   Query OK, 0 rows affected (0.00 sec)

   Applications depending on this schema will fail immediately after the revoke.

6. Revoke all privileges plus GRANT OPTION from the account to remove access at all scopes.

   mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'revokeuser'@'%';
   Query OK, 0 rows affected (0.01 sec)

   This removes global, schema, table, routine, and proxy privileges for the account, and it can impact multiple databases.

7. Verify the effective grants after revocation.

   mysql> SHOW GRANTS FOR 'revokeuser'@'%';
   +----------------------------------------+
   | Grants for revokeuser@%                |
   +----------------------------------------+
   | GRANT USAGE ON *.* TO `revokeuser`@`%` |
   +----------------------------------------+
   1 row in set (0.00 sec)

8. Test access with the affected account when a runtime proof is needed.

   $ mysql -u revokeuser -p
   Enter password:
   mysql> USE appdb;
   ERROR 1044 (42000): Access denied for user 'revokeuser'@'localhost' to database 'appdb'

9. Exit the mysql client.

   mysql> exit
   Bye