This tutorial shows you how to list all users in a MySQL database server. In MySQL, users are stored in the user table in a special database called mysql.
MySQL has special commands that allow you to list databases and tables. Users’ information on the other hand is handled just like any other data stored in the database.
List all Users in MySQL
With the above information, we can see that all we need is a SELECT query as shown here:
SELECT
user
FROM
mysql.user;The output will look something similar to this.
+----------------+
| user |
+----------------+
| nate |
| mysql.sesssion |
| mysql.sys |
| root |
+----------------+
4 rows in set (0.00 sec) From the response, we can see that there are four users in this MySQL database server.
In most cases, you might want to look at more information other than just the users’ names. For that you will need to get the columns list using the following command:
mysql> DESC mysql.user;With the following as the result.
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| 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) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.03 sec)Show the Current User
To get the details about the current user, you can use the user() function as shown here:
mysql> SELECT user();
+-----------------+
| user() |
+-----------------+
| nate@localhost |
+-----------------+
1 row in set (0.00 sec)The same results can be achieved using the current_user() function.
mysql> SELECT current_user();
+-----------------+
| current_user() |
+-----------------+
| nate@localhost |
+-----------------+
1 row in set (0.00 sec)Show Current Logged Users
You can list all users that are currently logged in to the MySQL database server with the following statement:
SELECT
user,
host,
db,
command
FROM
information_schema.processlist;The result will be something similar to this.
+------+-----------------+-------+---------+
| user | host | db | command |
+------+-----------------+-------+---------+
| root | localhost:58679 | NULL | Query |
| nate | localhost:65338 | dbpos | Sleep |
+------+-----------------+-------+---------+
2 rows in set (0.00 sec)You can see the users root and nate are signed into the MySQL database server. One of the users is using dbpos database but not executing any query (sleep) while root user is not using any specific database but is running a query.
Conclusion
Here, you have learned how to list all users in a MySQL database server by querying data from the user table in the mysql database. You can find out more about MySQL from the official vendor site here.
Found this article interesting? Follow Brightwhiz on Facebook, Twitter, and YouTube to read and watch more content we post.
