MySQL: Select and Show all MySQL Users

To show all MySQL databases, you can simply type SHOW DATABASES; or SHOW TABLES; to show all tables of the current database. However, if you want to list and display all the MySQL users, SHOW USERS; won’t work.

So how to display a list of the MySQL users?

As all user information such as user name and privilege data are stored in the table user of the database mysql that comes automatically created and populated after MySQL installation. You can show all the MySQL users of the current host by the following select query:

SELECT user FROM mysql.user;

Which would output a list of rows of all the active users, in the same format of what a SHOW DATABASES; would do.

8 comments

  1. Tom Harrison

    I think “show grants” is also an option. MySQL is quirky :-)

    • Yang Yang

      You can say that again!

    • Yang Yang

      Tell me about it. :)

  2. Sandeep

    Thanks,,! that helped..! New with Mysql, was trying show users; show users(); select users() and all sort of things..!

  3. Victor Rivarola

    Or, more correctly:

    select user,host,grant_priv from mysql.user;

  4. shaik

    thanks it is useful

  5. Raja Guru Moorthi

    Wow,….Very helpful

  6. Raja Guru Moorthi

    very helpfull