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
- MariaDB installed
- privileges
MySQL or
User with sudo or root
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.
