How can I get a list of user accounts using the command line in MySQL?

Question

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.

• Rup: @Mustapha Why the title change? This answers here are SQL you can run from anywhere, not just a command line. And what does that tag add to the question?
• mhadidg: The purpose of my edit was to maintain consistency between the title and the description, but I think you have a good point. Make your edit Mr. @Rup

Answer 1

Use this query:

SELECT User FROM mysql.user;

Which will output a table like this:

+-------+
| User |
+-------+
| root |
+-------+
| user2 |
+-------+

An advanced and efficient answer is the use of a stored procedure to retrieve MySQL users because it simplifies this repeated query, improves readability, and makes it easier to manage access in a secure, reusable way. You type it once, then you only need to type: CALL mysql.users(); to retrieve your mysql users.

USE mysql;
DELIMITER $$
CREATE PROCEDURE users()
BEGIN
 SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) AS Users
 FROM mysql.user
 ORDER BY user, host;
END $$
DELIMITER ;
CALL mysql.users();

enter image description here

• Matthew Scharley: I think it may be necessary to group on User too, to only get unique user values, since there's a seperate row for each user@host entry.
• Divyanshu Das: How to find the same info without sql query ??
• RolandoMySQLDBA: @barrycarter DELETE FROM mysql.user; better have WHERE user='someuser' and host='somehost'; If you do DELETE FROM mysql.user;, all users are gone. Logins after the next mysql restart or FLUSH PRIVILEGES; eliminate users from memory. Here is an example of one of my posts on doing DELETE FROM mysql.user responsibly : dba.stackexchange.com/questions/4614/…
• fancyPants: @Geoffrey SHOW GRANTS FOR 'user'@'host';
• user2683246: instead of grouping you can just use DISTINCT keyword: SELECT DISTINCT user FROM mysql.user;

Answer 2

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;

• I am PK: Just curious. When will this host come into play when working with mysql databases? [ Mysql Noob]
• Ray Baxter: @Packer the host comes into play when you are connecting from a different server. It is possible to grant different access to 'packer'@'example.com' and 'packer'@'google.com'

Answer 3

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;

• Iain Samuel McLean Elder: This is basically the same as spkane's answer. What is the benefit of concatenating the user and host columns?
• Iain Samuel McLean Elder: One example: the user@host format is used for setting passwords. Omitting the host from the SET PASSWORD command produces an error. SET PASSWORD FOR wordpressuser = PASSWORD('...'); produces the error ERROR 1133 (42000): Can't find any matching row in the user table. Include the host and it works. SET PASSWORD FOR wordpressuser@localhost = PASSWORD('...'); produces Query OK, 0 rows affected (0.00 sec).
• sjas: Best answer, whatever the naysayers may complain about. Only thing i'd change were appending an ORDER BY user to it.

Answer 4

To avoid repetitions of users when they connect from a different origin:

select distinct User from mysql.user;

Answer 5

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 |
| | % |
+------------------+-----------+

Answer 6

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

select User from mysql.user;

Answer 7

SELECT * FROM mysql.user;

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

• emotality: I got 3 root users with different hosts. localhost, 127.0.0.1 and ::1. Which one must I keep and what must I delete? Thanks!
• trysis: If you don't want people to connect via the network, the old standard was to delete all of these. Nowadays, though, it seems the recommendation is to keep localhost ones, as they are not accessible over the network anyway; this means you should instead keep all of them.

Answer 8

Log in to MySQL as root and type the following query:

select User from mysql.user;
+------+
| User |
+------+
| amon |
| root |
| root |
+------+

• leenephi: I'd say +1 for mentioning logging in as root. I tried without doing so and it didn't work ;)
• sandip divekar: You need to give privileges to user1 to display user list. Without root user you will get error. So first provide the privileges. Log in as root user then type command GRANT SELECT ON mysql.user TO 'user1'@'localhost'; now login as user1 and type command select User from mysql.user; You will see user list displayed. :) +1 Enjoy

Answer 9

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...

• Developer Marius Žilėnas: In my view what is important is what users are allowed to do on the database. So "SHOW GRANTS" is quite useful since it shows who is allowed to do what.

Answer 10

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;

Answer 11

Peter and Jesse are correct, but just make sure you first select the "mysql" database.

use mysql;
select User from mysql.user;

That should do your trick.

• vitaLee: It's not necessery to use mysql; in case you scope the table to the mysql database like you did. You can just select User from mysql.user;
• Dan: Adding use mysql; is just so you can use select User from user; instead select User from mysql.user; since it is usually a one time query, there is no need to use the mysql db
• Sнаđошƒаӽ: after use mysql if you had used select user from user; than that could have been something, but instead you are using mysql.user, which makes using use mysql at the beginning unnecessary.

Answer 12

This displays the list of unique users:

SELECT DISTINCT User FROM mysql.user;

Answer 13

$> mysql -u root -p -e 'Select user from mysql.user' > allUsersOnDatabase.txt

Executing this command on a Linux command line prompt will first ask for the password of MySQL root user. On providing the correct password it will print all the database users to the text file.

Answer 14

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;

• Peter Mortensen: What are "DML" and "DDL"? Can you link to (specific) context?

Answer 15

To see your users, it would be to use the mysql database.

USE mysql;

And then make the select.

SELECT user,host FROM user;

Another option is to put the BD.Table.

For example :

SELECT user,host FROM mysql.user;

Answer 16

SELECT User FROM mysql.user;

Use the above query to get the MySQL users.

Answer 17

An advanced and efficient answer is the use of a stored procedure to retrieve MySQL users because it simplifies this repeated query, improves readability, and makes it easier to manage access in a secure, reusable way. You type it once, then you only need to type: CALL mysql.users(); to retrieve your mysql users.

USE mysql;
DELIMITER $$
CREATE PROCEDURE users()
BEGIN
 SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) AS Users
 FROM mysql.user
 ORDER BY user, host;
END $$
DELIMITER ;
CALL mysql.users();

enter image description here

Answer 18

I can get the password with this query

SELECT username, password enter code hereFROM mysql.user;

But the passwords are encrypted, what type of encryption is that and how to decrypt using dictionary attack which is best tool