How to List All Users in a MySQL Database – phoenixNAP

The

main function of a database is to store information in a structured way to allow easy access to that information. Having many users with permission to access and work on your MySQL server can become a security issue.

A short set of queries in the MySQL client can provide you with valuable information to help prevent potential problems. Use the commands described in this tutorial to learn how to enumerate all user accounts on a MySQL server.

Prerequisites

• Command line/terminal window

MySQL or

• MariaDB installed

User with sudo or root

• privileges

How to access

MySQL as root

Access the MySQL server as root user by entering the following command in your terminal

: sudo mysql -user=root mysql -p

or:

sudo mysql -u root -p

The -p It is mandatory only if you have a predefined password for your root user. If no password is set, use the command without the -p option.

The above output shows that we accessed the MySQL client as root.

Now, you can run a MySQL query and retrieve information from the mysql.user database.

How to display all MySQL users

The following command lists the user names that have access to the server:

SELECT user FROM mysql.user;

This command instructs MySQL to create a table. The system retrieves the information from the User column of the mysql.user database.

The

output of this example shows a table with four rows: The

list we receive has a limited value. To retrieve a more detailed description of MySQL users and their permissions, we can expand the search parameters and add additional columns.

How to enumerate

the mysql.user database fields

Before expanding the query, let’s list the fields available in the mysql.user database. The following command lists all available columns:

desc mysql.user;

The options in the Field column represent the information we can request from the mysql.user database.

You can combine multiple options into a single command to get detailed user information.

How to display MySQL user information

The following query provides a table with User, Host, and authentication_string columns: SELECT User, Host, authentication_string

FROM mysql.user;

The query adds two more columns to the User column and provides valuable information, such as the user’s password and host name.

How to list only unique

MySQL users

Another useful option is to remove MySQL usernames that appear in multiple rows. To display only unique MySQL usernames, enter the following command:

SELECT DISTINCT User FROM mysql.user;

The output removes duplicate MySQL rows and displays specific usernames only once.

Show current MySQL user Use the user()

or current_user() function to get the details of the current MySQL user: SELECT user(

);

or:

SELECT current_user();

In both cases, the output shows that the current MySQL user is root@localhost.

Show

connected MySQL users

At any time, you can verify the users who are currently connected to the MySQL database server. This feature provides invaluable information when monitoring your MySQL server for unauthorized use.

Enter this query to display the list of MySQL users who are currently logged in:

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

The output lists the logged-on users, the database, and the command that is running.

Conclusion

By combining the commands presented in this article, you can now structure user information from the mysql.user database with ease.

Instead of simply listing users, you can modify search results and search for user-specific information. A detailed and customizable overview allows you to make an informed decision regarding the security of your MySQL server.