Showing All Grants With MySQL

[patrick]

Users are identified with both a user and a host. If you want to show all of the grants for all users, you need to first look at the mysql.user table:


[usr-1@srv-1 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10863 to server version: 4.1.10a
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
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)


Some of these permissions overlap. That is, the 10.50.100.0 entry include the other individual hosts. We can now show the grants for individual users:

mysql> show grants for are@'10.50.100.0/255.255.255.0';
+-----------------------------------------------------------------+
| Grants for are@10.50.100.0/255.255.255.0 |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'are'@'10.50.100.0/255.255.255.0'
IDENTIFIED BY PASSWORD '4bc9fc1d367a0b23' |
| GRANT ALL PRIVILEGES ON `arewedown`.* TO 'are'@'10.50.100.0/255.255.255.0' |
| GRANT ALL PRIVILEGES ON `mysql`.`arewedown` TO 'are'@'10.50.100.0/255.255.255.0'|
+-----------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show grants for are@10.10.10.10;
+--------------------------------------------------------------+
| Grants for are@10.10.10.10 |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'are'@'10.10.10.10' |
| GRANT ALL PRIVILEGES ON `arewedown`.* TO 'are'@'10.10.10.10' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>