Removing Grants in MySQL

Grants are how MySQL handles user permissions to databases. See our article here for information on showing the grants. To delete grants, you can directly modify the mysql.user table:

mysql> select User,Host from mysql.user;
+------+---------------------------+
| User | Host                      |
+------+---------------------------+
| are  | 10.10.10.10               |
| are  | 10.10.10.11               |
| are  | 10.50.100.0/255.255.255.0 |
| are  | 10.50.100.112             |
| are  | 10.50.100.2               |
|      | localhost                 |
| are  | localhost                 |
| root | localhost                 |
|      | srv-1.example.com |
| root | srv-1.example.com |
+------+---------------------------+
10 rows in set (0.00 sec)
mysql> DELETE FROM mysql.user WHERE User='are' 
and host='10.50.100.0/255.255.255.0';
Query OK, 1 row affected (0.06 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> select User,Host from mysql.user;
+------+---------------------------+
| User | Host                      |
+------+---------------------------+
| are  | 10.10.10.10               |
| are  | 10.10.10.11               |
| are  | 10.50.100.112             |
| are  | 10.50.100.2               |
|      | localhost                 |
| are  | localhost                 |
| root | localhost                 |
|      | srv-1.example.com |
| root | srv-1.example.com |
+------+---------------------------+
9 rows in set (0.00 sec)
mysql>

We can delete all access for the user are with this command:

mysql> DELETE FROM mysql.user WHERE User='are'; 
Query OK, 5 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> select User,Host from mysql.user;
+------+---------------------------+
| User | Host                      |
+------+---------------------------+
|      | localhost                 |
| root | localhost                 |
|      | srv-1.example.com |
| root | srv-1.example.com |
+------+---------------------------+
4 rows in set (0.00 sec)
mysql>

Notice the ones with blank User names? These are anonymous accounts. We can delete all of these as well if we want with another DELETE command:

mysql> DELETE FROM mysql.user WHERE User='';
Query OK, 2 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> select User,Host from mysql.user;
+------+---------------------------+
| User | Host                      |
+------+---------------------------+
| root | localhost                 |
| root | srv-1.example.com |
+------+---------------------------+
2 rows in set (0.00 sec)
mysql>

For more information on the GRANT command for MySQL and associated commands, see this page.

Information

About