How to revoke privileges from your MySQL accounts

MySQL database server allows you to remove privileges (permissions) from your accounts by writing the REVOKE statement.

The full privileges that you can revoke from your accounts are listed on the MySQL privileges summary table.

The REVOKEstatement syntax is as shown below:

REVOKE privilege [, privilege] ... 
  ON [database_name.table_name] 
  FROM user [user] [, user] ...

You can remove multiple privileges from multiple users with one REVOKE statement.

For example, here’s how to revoke the SELECT privilege from a certain user:

REVOKE SELECT 
  ON *.* 
  FROM nathan@localhost;

The *.* symbol means “all databases” and “all tables” in MySQL. When you replace the database name with an asterisk, you need to use an asterisk for the table name as well.

To revoke permissions from all tables in a specific database, you can write database_name.* for the ON clause.

For example, the following statement removes SELECT and INSERT privileges from the user on a database named school_db:

REVOKE SELECT, INSERT 
  ON school_db.* 
  FROM nathan@localhost;

If you want to revoke only from a specific table, then you can replace the asterisk symbol above with the table name (like school_db.students to revoke permissions from students table only)

To remove all permissions from all databases and tables, you can use REVOKE ALL statement followed with ON *.* as follows:

REVOKE ALL PRIVILEGES 
  ON *.* 
  FROM nathan@localhost;

The statement above will effectively remove all permissions from the user nathan@localhost, but the user account can still login and connect to your database server.

To remove privileges from multiple user accounts, you can write the user accounts separated by comma as follows:

REVOKE ALL PRIVILEGES 
  ON *.* 
  FROM nathan@localhost, jeffrey@localhost;

Please note that the REVOKE statement doesn’t remove the user from your MySQL database server. The account can still be used to connect to your database instance although it can’t do anything inside.

To completely remove the user, you need to use the DROP USER statement as follows:

DROP USER nathan@localhost, jeffrey@localhost;

Now the user(s) are effectively removed from your database server, denying them from accessing your server completely.

And that’s how you revoke privileges from your MySQL user accounts. 😉