How to do MySQL “show users”

In MySQL, users want to manage databases. In this situation, they need to view all the available user accounts in the database. However, MySQL does not have the “SHOW USERS” command, like “SHOW DATABASES” or “SHOW TABLES” statements, for listing all local database users. It can be performed with the “SELECT” statement.

This write-up discusses:

How to View Users in MySQL Using “user()” Function?
How to View All Users in MySQL?
How to View All Users in Descending Order With Detail in MySQL?
How to View Users in MySQL With Host, Account, and Password Detail?
How to View Users in MySQL With Host, Database Name, and Command?

How to View Users in MySQL Using “user()” Function?

To get the just current username with the hostname by utilizing the “user()” function, first, open the Windows terminal and connect with the MySQL server through the below-listed command:

mysql -u root -p

Here:

“-u” option indicates the user.
“root” is our username.
“-p” option represents the password.

According to the following output, the terminal is connected to the MySQL server:

Then, run the “SELECT” statement with the “user()” function:

SELECT user();

As you can see, the current username and hostname have been displayed below:

How to View All Users in MySQL?

To get all the users in MySQL, run the below-stated command:

SELECT user FROM mysql.user;

In the above query:

“SELECT” statement is used for selecting the records from the database.
“user” is the resultant column name.
“FROM” clause is used for getting the required records from the tables.
“mysql.user” is the default table name that stores all created tables.

As you can see, all existing local database users have been displayed below:

How to View All Users in Descending Order With Detail in MySQL?

If you want to show all users in descending order with additional detail in MySQL, the “DESC” command can be used:

DESC mysql.user;

It can be observed that all users in the “mysql.user” table are shown in the below table with more detail in descending order:

How to View Users in MySQL With Host, Account, and Password Detail?

Suppose you need to get all the local users along with the root, host, account, and password details. In such a situation, provided command can be used:

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

The output of the above-executed query contains all local usernames, hostnames, their account-locked details, and password expiry status:

How to View Users in MySQL With Host, Database Name, and Command?

To find the username, the hostname with their respective database’s name, and commands from the “information_schema.processlist”, the “SELECT” statement can be used:

SELECT user, host, db, command FROM information_schema.processlist;

You have learned the different ways to show the user’s name with multiple options in MySQL.

Conclusion

To show users multiple commands and options are used in MySQL, such as the “SELECT user()” for current users only. The “SELECT user FROM mysql.user” for local database users, and the “DESC mysql.user” for the usernames in descending order with other details. Moreover, we can also get the username with hostname, account details, password expiry status, their respective databases name, and commands. This write-up illustrated the several commands to show the user’s name with multiple options in MySQL.