How to Revoke Privileges in MySQL

If you’re a database administrator for MySQL, you probably want to remove user access rights to database objects from time to time. This guide covers how to do so.

Understanding MySQL privileges

MySQL manages access through a set of privileges that define what a user can and cannot do. These privileges include actions like SELECT, INSERT, UPDATE, DELETE, and more, applicable at different levels such as global, database, table, column, or routine.

Identifying privileges to revoke

Before revoking privileges, use the SHOW GRANTS command to review a user's current privileges.

SHOW GRANTS FOR 'username'@'host';

Replace username and host with the specific user account and host details.

Revoking global privileges

To revoke global privileges, use the REVOKE statement. Global privileges are revoked from the mysql.user table.

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';

Revoking database-level privileges

Database-level privileges are revoked from the mysql.db table. Specify the database name in the query.

REVOKE ALL PRIVILEGES ON `database_name`.* FROM 'username'@'host';

Revoking table-level privileges

To remove privileges on a specific table, mention the database and table name.

REVOKE ALL PRIVILEGES ON `database_name`.`table_name` FROM 'username'@'host';

Revoking column-level privileges

Column-level privileges are removed by specifying the database, table, and column.

REVOKE ALL PRIVILEGES ON `database_name`.`table_name`(`column_name`) FROM 'username'@'host';

Revoking routine-level privileges

For stored routines like procedures and functions, specify the routine type and name.

REVOKE EXECUTE ON PROCEDURE `database_name`.`procedure_name` FROM 'username'@'host';

Flushing privileges

After revoking privileges, run the FLUSH PRIVILEGES command to reload the privilege tables and apply changes immediately.

FLUSH PRIVILEGES;

Monitoring and adjusting user privileges

Regularly monitor user privileges and adjust them as needed. This ensures that users have only the necessary privileges, enhancing database security.

For more advanced user management and monitoring tools, consider using Basedash. Basedash allows you to generate an admin panel, share access with your team with controlled permissions, and create charts and dashboards from your data. Learn more at Basedash.