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

Published on 20-Feb-2018 13:23:18

Previous Page Print Page