Removing Grants in MySQL

[patrick]

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.networking7by24.com |
| root | srv-1.networking7by24.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.networking7by24.com |
| root | srv-1.networking7by24.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.networking7by24.com |
| root | srv-1.networking7by24.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.networking7by24.com |
+------+---------------------------+
2 rows in set (0.00 sec)
mysql>