FB

A common question when using MySQL is how to show all users on the database.

In this guide, you’ll learn how to see a list of users. You’ll also be able to see a list of logged-in users as well as the current user.

Let’s take a look.

MySQL Show Users

To show a list of all users on the MySQL database, query the mysql.user table:

SELECT user, host
FROM mysql.user;

The results are:

user host
root 127.0.0.1
root ::1
root devbook.local
mysql.session localhost
mysql.sys localhost
root localhost

Some usernames are repeated, as they have different host values, and possibly different values for other columns.

To find a list of usernames only, or a distinct list of users, use the DISTINCT keyword on the user column.

SELECT DISTINCT user
FROM mysql.user;
user
root
mysql.session
mysql.sys

In both of these examples, there’s a root user and two built-in mysql users.

 

Find Locked and Password Expired Users in MySQL

There are a couple of columns you can check in the user table to see if a user is locked or has their password expired:

SELECT
user,
host,
account_locked,
password_expired
FROM mysql.user;

Here’s what you’ll see:

user host account_locked password_expired
root localhost N N
root devbook.local N N
root 127.0.0.1 N N
root ::1 N N
mysql.session localhost Y N
mysql.sys localhost Y N

 

Show the Current User

If you need to see the current logged in user, you can use the USER() function or the CURRENT_USER function.

SELECT
USER(),
CURRENT_USER();
USER() CURRENT_USER()
[email protected] [email protected]

I was logged in as root when I ran this command, so that’s the user shown.

 

Show Currently Logged In Users

The mysql.user table shows a list of users in the database.

To see a list of currently logged in users, you can query a different table:

SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist;

A range of information is shown about currently logged in users in MySQL:

id user host db command time state info
3 root localhost:62909 movies Query 0 executing SELECT …
2 root localhost:62908 movies Sleep 24

We can see that only the root user is logged in. The INFO column shows the recently run query, and the db column shows which database the users are logged in to. The table can be filtered using a WHERE clause as well.

 

What Else Can You Find Out About MySQL Users?

The mysql.user table has a lot of information about users. To find out what information is available, run the desc command:

DESC mysql.user;
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
x509_issuer blob NO
x509_subject blob NO
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
password_expired enum(‘N’,’Y’) NO N
password_last_changed timestamp YES
password_lifetime smallint(5) unsigned YES
account_locked enum(‘N’,’Y’) NO N

 

Conclusion

Using either the mysql.users table or the processlist table will help you get more information about the users in a MySQL database, whether you are looking for all users or currently logged in users.