MySQL database server allows you to remove privileges (permissions) from your accounts by writing the
The full privileges that you can revoke from your accounts are listed on the MySQL privileges summary table.
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
For example, here’s how to revoke the
SELECT privilege from a certain user:
REVOKE SELECT ON *.* FROM nathan@localhost;
*.* 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
For example, the following statement removes
INSERT privileges from the user on a database named
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. 😉