MySQL "show users" - how to show/list the users in a MySQL database

標籤：

 

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

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

mysql> select * from mysql.user;

However, note that this query shows a large listing of MySQL user information, including user permission information, 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 details and background 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.

 

MySQL "show users" - how to show/list the users in a MySQL database