Mysql username and password list

MySQL FAQ: How do I show/list MySQL users, i.e., the user accounts in a MySQL or MariaDB database?

Table of Contents Show

How to reduce the amount of ‘user’ information shown
How do I find MySQL username and password?
How do I find my MySQL username?
How can I see all user privileges in MySQL?

Solution

To show the users in a MySQL database, first log into your MySQL server as an administrative user using the mysql command line client, then run this MySQL query:

mysql> select * from mysql.user;

However, note that this query shows all of the columns from the mysql.user table. This makes for a lot of output, so as a practical matter you may want to trim down some of the fields to display, something like this:

mysql> select host, user, password from mysql.user;

The next section provides more details and information about this second query.

How to reduce the amount of ‘user’ information shown

You can get a listing of the fields in the mysql.user table by running this MySQL query:

mysql> desc mysql.user;

On my current server this shows the following 37 columns of MySQL user information, as shown here:

mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       | 
| User                  | char(16)                          | NO   | PRI |         |       | 
| Password              | char(41)                          | NO   |     |         |       | 
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       | 
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| File_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| References_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       | 
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       | 
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       | 
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       | 
| ssl_cipher            | blob                              | NO   |     | NULL    |       | 
| x509_issuer           | blob                              | NO   |     | NULL    |       | 
| x509_subject          | blob                              | NO   |     | NULL    |       | 
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       | 
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       | 
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       | 
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       | 
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.10 sec)

So for most cases where you want to show MySQL user accounts you'll probably want to limit your MySQL users' query to a few important columns, something like this:

select host, user, password from mysql.user;

In summary, if you need to list the users in a MySQL database, I hope this has been helpful.

How do I find MySQL username and password?

So for example, to show MySQL users' username, password and host, we'll modify the sql query to accordingly as such: mysql> select user, password, host from mysql. user; The above sql query will present you with a list of users and their respective user name, password and database host.

How do I find my MySQL username?

Try the CURRENT_USER() function. This returns the username that MySQL used to authenticate your client connection. It is this username that determines your privileges.