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

How to get a list of MySQL user accounts

burntsugar
1#
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
2#
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
3#
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
4#
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
5#
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
6#
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
7#
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
8#
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
9#
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
10#
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
11#
Balagurunathan Marimuthu Reply to 2016-09-09 12:16:43Z

here display List of unique users.

SELECT DISTINCT User FROM mysql.user;
VPK
12#
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             | 127.0.0.1 |
| debian-sys-maint | localhost |
|                  | %         |
+------------------+-----------+
Tobias Holm
13#
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
14#
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
15#
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