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 CLI ENT, then run this MySQL query:

SELECT * from Mysql.user;

However, note that this query shows a large listing of MySQL user information, including user permission Informat Ion, so as a practical matter-want to trim-some of the fields to display, something like this:

Select Host, user, password from Mysql.user;

The next section provides details and background information on 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 PNS 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 (one) unsigned |     NO | |       0 | | | Max_updates | int (one) unsigned |     NO | |       0 | | | max_connections | int (one) unsigned |     NO | |       0 | | | max_user_connections | int (one) 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 FE W important columns, something like this:

Select Host, user, password from Mysql.user;

　　

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

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