CALL 1.800.975.6814    LIVE CHAT

@dailyrazor Blog

How to do MySQL “show users” – learn to retrieve/display the list of users in a MySQL database

December 28 2016 0
MySQL Show Users

When managing a MySQL database, you will often come across the need to view the list of MySQL user accounts in the database. The common assumption is that there’s a mysql show users command in MySQL but unfortunately that’s not the case. So many novice MySQL administrators especially those with experience using other Database Management Systems (DBMS) are left scratching their heads wondering why there isn’t any mysql show users type of command.

Well, although developers of MySQL does not make available a mysql show users command, the good news is that MySQL provides a handy and simple way to do this and it’s pretty straight forward. To begin, you need to make sure you have MySQL server properly installed and running. Then you need to login as an administrative users via the mysql> prompt using either Command prompt or an SSH client such at Putty.

Once logged in, run the following command on the mysql> prompt:

mysql> select * from mysql.user;

Given that we’re running a SELECT ALL sql query with the select * from mysql.user; statement, the query returns a large amount of information – both useful and unuseful. So it’s much better and efficient to modify the sql query to reflect as much information as really needed.

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. So you can basically play around with the sql query and adjust it accordingly to reflect the information you want to retrieve from the database as it pertains to MySQL and showing all MySQL users.

The next question we’re sure will come to your mind is “how do I know the different columns that makes up the “user” table in MySQL, well it’s pretty simple. MySQL provides a simple comment to get this information. While still logged in, run the following command on the mysql> prompt:

mysql> desc mysql.user;

and depending on your MySQL database version, you’ll get an output similar to:


+------------------------+-----------------------------------+------+-----+---------+-------+
| 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       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_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       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.00 sec)

The above output tells you the tells you all the relevant columns in the “user” table of your MySQL database install and is vital to assisting you when you need to show MySQL user information.

So as you can see MySQL provides a way around the lack of a mysql show users command. The code snippets we’ve provided on this short tutorial will enable you perform the task of showing all users in any MySQL database.