How to List All Users in a MySQL Database

Introduction

Before we begin talking about how to list all users in a mysql database, let’s briefly understand - What is a Database?

A database's main purpose is to store data in a structured manner so that it may be easily accessed. When working with a MySQL database, it is often necessary to view a list of all users present in the database. This information is crucial for managing user access, permissions, and overall database security.

In this tutorial, you will explore how to list all users in a mysql database and, we will also address FAQs on how to list all users in a MySQL Database.

Prerequisites

Command line/terminal window
MySQL or MariaDB installed
User with sudo or root privileges

How to Access MySQL as Root

Use the following command in your terminal to log in as the root user and access the MySQL server:

sudo mysql --user=root mysql -p

or:

sudo mysql -u root -p

Only if you have a predetermined password for your root account do you need to use the -p option. Use the command without the -p option if no password is specified.

The mysql.user database can now be accessed via a MySQL query to retrieve data.

💡

Note: If you are using a remote connection, you can also connect to your server using SSH.

How to Show All MySQL Users

The command below displays a list of usernames with server access:

SELECT user FROM mysql.user;

This command tells MySQL to make a table. The information is retrieved from the User column in the mysql.user database.

In this example, the output displays a table with four rows:

The list we were given is of limited value. We may widen the search criteria and add other columns to get a more thorough breakdown of the MySQL users and their permissions.

How to List mysql.user Database Fields

Let us list the fields available in the mysql.user database before expanding the query. All the available columns are listed with the following command:

desc mysql.user;

The options in the Field column correspond to the data we are able to request from the mysql.user database.

To obtain comprehensive user information, it is possible to combine many options into a single command.

How to Show MySQL User Information

A table with the columns User, Host, and authentication_string is produced by the following query:

SELECT User, Host, authentication_string FROM mysql.user;

The query expands the User column by two extra columns and offers useful details like the user's hostname and password.

💡

Note: As of MySQL 5.7, the Password field in the mysql.user table has been renamed. User passwords are now listed in a field called authentication_string.

How to List Only Unique MySQL Users

Remove MySQL usernames that show up in numerous rows is an additional helpful choice. Use the following command to display only unique MySQL usernames:

SELECT DISTINCT User FROM mysql.user;

The output eliminates duplicate MySQL rows and displays certain usernames just once.

Show Current MySQL User

To learn more about the current MySQL user, use the user() or current_user() functions:

SELECT user();

Or:

SELECT current_user();

The output in both cases reveals that the current MySQL user is root@localhost.

Show Logged In MySQL Users

You can check the users who are signed in to the MySQL database server currently at any moment. When checking your MySQL server for unauthorized use, this function offers invaluable insight.

To view a list of the users who are currently logged in to MySQL, enter the following query:

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

The database, the command being executed, and the logged-in users are all listed in the output.

FAQ's on how to list all users in MySQL Database

Can I list users from a specific database only?

No, the default MySQL user table stores users for the entire MySQL server, not specific databases. Listing users will display users from all databases.

Can I filter and list users based on specific criteria?

Yes, you can use conditions in the SQL query to filter and list users based on specific criteria. For example, SELECT User FROM mysql.user WHERE Host='localhost';

How do I differentiate between regular users and superusers in the user listing?

Superusers generally have elevated privileges compared to regular users. You can identify superusers by their specific privileges listed in the user privileges columns.

Is there a command-line tool to list users in MySQL?

Yes, you can use the MySQL command-line utility, along with the appropriate SQL query, to list users. For example, mysql -u <username> -p -e "SELECT User FROM mysql.user;"

Can I list users from a remote MySQL server?

Yes, you can connect to a remote MySQL server using the appropriate host, port, username, and password in your connection string, and then execute the user listing query.

How can I update user information or delete a user from the user listing?

You can modify or delete user accounts using SQL commands like GRANT or REVOKE to change privileges or DROP USER to delete a user.

Are there any security risks in listing users in a MySQL database?

Listing users in a MySQL database poses minimal security risks.

Conclusion

By executing a simple SQL query, you can retrieve a comprehensive list of users present in the MySQL server. In addition to usernames, you can view privileges, sort the listing, and filter based on specific criteria.

It is important to ensure that only authorized users have access to the user listing and to implement strong security measures to protect this sensitive information.

Regularly reviewing and updating user accounts is essential for maintaining a secure and well-managed MySQL database environment.

If you have any queries, please leave a comment below, and we’ll be happy to respond to them for sure.