How to SHOW USERS in MySQL Database on Linux

MySQL Sql statement 

•  select * from mysql.user;
• select user,host, command from information_schema.processlist;
• select current_user();
• select user, account_locked, password_expired from mysql.user;
• select distinct user from mysql.user;
• desc mysql.user;

Unlike the SHOW DATABASES or SHOW TABLES commands that display all databases or tables right away, the SHOW USERS command does not exist in MySQL. Even though there is no such command, users can use a MySQL query and get a full list of users in a given MySQL database server.

Follow the steps below for more information.

2. Use the MySQL SHOW USERS Query

Use the following query to show MySQL users created in the database server:

SELECT user FROM mysql.user;

Take note that there might be duplicate users. This is because MySQL filters access to a server according to the IP address it comes from.

You can also add a host column to see even more information using the following command:

SELECT user,host FROM mysql.user;

With this, you will be able to see the MySQL users and which host or IP address they have permission to access. In our case, all of the users are from a local database:

3. See More MySQL User Info (Optional)

If you need more information about MySQL users, the search command can be expanded with the help of MySQL queries.

For example, the following command will print out all possible information from the user table:

SELECT * FROM mysql.user;

However, such output might look too messy to figure out. Thus, we recommend narrowing the search by using more specific queries.

Here are some of the more popular use-cases:

Preview Table Columns

The following query will display a preview of user table columns. It is especially helpful if users want to check information about a specific table.

DESC mysql.user

Display Only Unique Usernames

To skip repeating usernames, the following query can be used:

SELECT DISTINCT user FROM mysql.user;

Conclusion

The administration of a database server can be challenging work. Thus, database administrators must be careful when creating and administering user permissions.

The MySQL SHOW USERS command allows administrators to view MySQL users alongside other important information.

To recap, we’ve covered the basics of how to show MySQL users tied to a database and learned how to:

• List all users created in a given MySQL database
• Preview MySQL database table columns
• Display only the unique usernames in a table
• Check password expiration and account locking status
• Show current and currently logged-in users in a MySQL database