How can we revoke privileges from a MySQL user?

With the help of MySQL REVOKE statement, we can revoke one or more or all privileges from a MySQL user. Its syntax would be as follows −

REVOKE privilege_type [(column_list)]
   [, priv_type [(column_list)]]...
ON [object_type] privilege_level
FROM user [, user]...

Explanation of the above syntax is as follows −

First, we need to specify a list of privileges that we want to revoke from a user right after the REVOKE keyword. We need to separate privileges by commas.
Second, we need to specify the privilege level at which privileges is revoked in the ON clause.
Third, we need to specify the user account that we want to revoke the privileges in the FROM clause.

Example

In the given example we are going to revoke the grants from user abcd@localhost −

mysql> SHOW GRANTS FOR abcd@localhost;
+---------------------------------------------------------------------+
| Grants for abcd@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'abcd'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

The following query will revoke the grants from user ‘abcd@localhost’ −

mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM abcd@localhost;
Query OK, 0 rows affected (0.00 sec)

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

Srinivas Gorla

Updated on 20-Jun-2020 12:00:34

• Related Questions & Answers
• How can we grant privileges to a MySQL user?
• How can I restore the MySQL root user full privileges?
• How to create MySQL user with limited privileges?
• Check privileges (grants) for a specific user in MySQL?
• Grant all privileges of a database to a MySQL user?
• MySQL new user access denied even after giving privileges?
• How can we change MySQL user password by using the ALTER USER statement?
• Can we use the word user for a MySQL table?
• How can we create user accounts in MySQL database server?
• How can we create a MySQL user account by omitting the hostname?
• How can we remove a column from MySQL table?
• How Can we permanently define user-defined variable for a client in MySQL?
• How can we grant a user to access all stored procedures in MySQL?
• How can we change MySQL user password by using UPDATE statement?
• What are the minimum MySQL user privileges to allow optimize and repair?