How to view a list of MySQL users and their privileges
From Brian Nelson Ramblings
How to view a list of MySQL users and their privileges
If you are anything like me, you like to have multiple mysql users that can only access specific databases.
Sometimes, you will need to remind yourself of which users you already have on your system.
Check MySQL Users on your System
To check what users you have on your system run the following command after logging into mysql as root
select user,host from mysql.user;
Output:
mysql> select user,host from mysql.user; +-----------+----------------------+ | user | host | +-----------+----------------------+ | brian | % | | root | 127.0.0.1 | | wordpress | 127.0.0.1 | | root | ::1 | | | localhost | | brian | localhost | | root | localhost | | wodpress | localhost | | wordpress | localhost | +-----------+----------------------+
Check MySQL user Permissions
Now that you have the list of users, you can check there permissions
Lets check the user Brian, this can be done with show grants
show grants for 'brian'@'localhost';
Output:
mysql> mysql> show grants for 'brian'@'%'; +---------------------------------------------------------------------------------------------------------------------------------+ | Grants for brian@% | +---------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'brian'@'%' IDENTIFIED BY PASSWORD '*559DE16ABE963F9891B02533C9AF9CC82AB7216D' WITH GRANT OPTION | | GRANT SELECT, INSERT, UPDATE ON `demo`.* TO 'brian'@'%' | +---------------------------------------------------------------------------------------------------------------------------------+
As above, when you specify a particular MySQL account to check for privileges, use an account name constructed by user name and host name concatenated by "@".
