How to view a list of MySQL users and their privileges

Last updated on September 28, 2020 by Dan Nanni

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.

Get a List of MySQL Users

mysql> select user,host from mysql.user;

+------------------+--------------+
| user             | host         |
+------------------+--------------+
| root             | %            |
| root             | 127.0.0.1    |
| 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.

Find the Privilege(s) Granted to a Particular MySQL Account

mysql> show grants for 'root'@'%';

+------------------------------------------------+
| Grants for [email protected]%                              |
+------------------------------------------------+
| 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 @.

Support Xmodulo

This website is made possible by minimal ads and your gracious donation via PayPal or credit card

Please note that this article is published by Xmodulo.com under a Creative Commons Attribution-ShareAlike 3.0 Unported License. If you would like to use the whole or any part of this article, you need to cite this web page at Xmodulo.com as the original source.