MySQL: Select and Show all MySQL Users

by Yang Yang on September 3, 2009

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.

Tom Harrison October 16, 2009 at 10:08 pm

I think “show grants” is also an option. MySQL is quirky 🙂

Yang Yang October 17, 2009 at 7:10 pm

You can say that again!

Yang Yang January 26, 2010 at 1:38 pm

Tell me about it. 🙂

Sandeep December 23, 2011 at 3:42 am

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

Victor Rivarola November 22, 2012 at 7:10 am

Or, more correctly:

select user,host,grant_priv from mysql.user;

shaik November 26, 2012 at 7:32 pm

thanks it is useful

Raja Guru Moorthi February 6, 2013 at 12:33 pm

Wow,….Very helpful

Raja Guru Moorthi February 6, 2013 at 12:34 pm

very helpfull

Comments on this entry are closed.

Previous post:

Next post: