How to view a list of MySQL users and their privileges

If you are running a multi-user MySQL database, handy commands that show a list of all existing MySQL users and their privileges may be on your cheat sheet. To find out all MySQL users and the permissions granted to each user, log in to your MySQL server, and run the following MySQL commands.

To get a list of MySQL users:

mysql> select user,host from mysql.user;
| user             | host         |
| root             | %            |
| root             |    |
| debian-sys-maint | localhost    |
| root             | localhost    |
| root             | stack.domain |
8 rows in set (0.01 sec)

The above output shows a list of existing MySQL accounts. Note that a MySQL account has two components: user and host. This allows the same user to use different MySQL accounts depending on which host they are connecting from. "%" is a wildcard character interpreted as "any" host.

To find the privilege(s) granted to a particular MySQL account:

mysql> show grants for 'root'@'%';
| Grants for root@%                              |
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' .... |
1 row in set (0.00 sec)

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 "@".

Subscribe to Xmodulo

Do you want to receive Linux FAQs, detailed tutorials and tips published at Xmodulo? Enter your email address below, and we will deliver our Linux posts straight to your email box, for free. Delivery powered by Google Feedburner.

Support Xmodulo

Did you find this tutorial helpful? Then please be generous and support Xmodulo!

The following two tabs change content below.
Dan Nanni is the founder and also a regular contributor of He is a Linux/FOSS enthusiast who loves to get his hands dirty with his Linux box. He likes to procrastinate when he is supposed to be busy and productive. When he is otherwise free, he likes to watch movies and shop for the coolest gadgets.

One thought on “How to view a list of MySQL users and their privileges

Leave a comment

Your email address will not be published. Required fields are marked *