Home How to get a list of MySQL user accounts
Reply: 14

How to get a list of MySQL user accounts

burntsugar Published in 2009-07-16 03:23:53Z

I'm using the MySQL command line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this?

I'm using MySQL version 5.4.1.

Community Reply to 2017-05-23 12:34:51Z

Use this query:

SELECT User FROM mysql.user;

Which will output a table like this:

| User  |
| root  |
| user2 |

As Matthew Scharley points out in the comments on this answer, you can group by the User column if you'd only like to see unique usernames.

Jesse Vogt
Jesse Vogt Reply to 2009-07-16 03:28:16Z

If you are referring to the actual MySQL users, try:

select User from mysql.user;
Etzeitet Reply to 2009-07-16 03:29:24Z
SELECT * FROM mysql.user;

It's a big table so you might want to be more selective on what fields you choose.

Armin Nehzat
Armin Nehzat Reply to 2012-05-05 12:19:39Z

Peter and Jesse are correct but just make sure you first select the mysql DB.

use mysql;

select User from mysql.user;

that should do your trick

spkane Reply to 2012-07-06 18:21:58Z

I find this format the most useful as it includes the host field which is important in MySQL to distinguish between user records.

select User,Host from mysql.user;
Nicolas Manzini
Nicolas Manzini Reply to 2013-09-29 22:01:52Z

to avoid repetitions of users when they connect from different origin:

select distinct User from mysql.user;
RolandoMySQLDBA Reply to 2013-09-29 22:29:11Z

A user account comprises the username and the host level access.

Therefore, this is the query that gives all user accounts

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user;
sandip divekar
sandip divekar Reply to 2014-03-04 06:46:24Z

Login to mysql as root and type following query

select User from mysql.user;

| User |
| amon |
| root |
| root |
Palec Reply to 2015-08-22 08:37:16Z

The mysql.db table is possibly more important in determining user rights. I think an entry in it is created if you mention a table in the GRANT command. In my case the mysql.users table showed no permissions for a user when it obviously was able to connect and select, etc.

mysql> select * from mysql.db;
mysql> select * from db;
| Host          | Db              | User   | Select_priv | Insert_priv | Update_priv | Del...
Balagurunathan Marimuthu
Balagurunathan Marimuthu Reply to 2016-09-09 12:16:43Z

here display List of unique users.

VPK Reply to 2017-11-27 04:31:47Z

MySQL stores the user information in its own database. The name of the database is MySQL. Inside that database, the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:

SELECT User, Host FROM mysql.user;

| User             | Host      |
| root             | localhost |
| root             | demohost  |
| root             | |
| debian-sys-maint | localhost |
|                  | %         |
Tobias Holm
Tobias Holm Reply to 2016-11-02 19:01:33Z

I use this to sort the users, so the permitted hosts are more easy to spot:

mysql> SELECT User,Host FROM mysql.user ORDER BY User,Host;
Mian Asbat Ahmad
Mian Asbat Ahmad Reply to 2017-02-21 10:04:52Z
$>  mysql -u root -p -e 'Select user from mysql.user' > allUsersOnDatabase.txt

This command on execution will prompt for password of root user in mysql and then it will print all the users to the text file.

Community Reply to 2017-12-14 16:54:32Z

I found his one more useful as it provides additional information about DML and DDL privileges

SELECT user, Select_priv, Insert_priv , Update_priv, Delete_priv, 
       Create_priv, Drop_priv, Shutdown_priv, Create_user_priv 
FROM mysql.user;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.321064 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO