How to List Users in a MySQL Database

Try this guide with our instant dedicated server for as low as 40 Euros

list users in MySQL

MySQL is a popular open-source relational database management system (RDBMS) that stores data using tables with rows and columns. It allows for efficient data organization, retrieval, manipulation, and querying of data. The best thing about MySQL is its adaptability – it is a great fit for small personal projects to large-scale enterprise solutions and high-traffic websites. 

At times, users need to access the database for user management, including restricting users from viewing, modifying, or deleting data or listing users in MySQL, a crucial aspect of database security. 

There are various tools and methods for listing users in MySQL. This includes command-line utilities, graphical user interface tools like phpMyAdmin, and custom interfaces built-in popular programming languages.

In this tutorial, we will describe several techniques you can apply to list MySQL users. 

Let’s start with an overview of why we need to create users in MySQL servers.

Table Of Contents

  1. Why Create Users on MySQL Server
  2. Access MySQL as a Root User
  3. List Users in a MySQL Database
    1. Display All MySQL Users
    2. List mysql.user Database Fields
    3. Display MySQL User Details
    4. Display Unique MySQL Users
    5. Display the Current MySQL User
    6. Display Currently Logged-In MySQL Users
  4. Conclusion
  5. FAQs

Why Create Users on MySQL Server

Creating users on a MySQL server is essential for managing access and security within the database environment. 

User creation enables database administrators to assign specific privileges and permissions to different individuals or applications, controlling who can access, modify, or delete data. 

User policies help enforce the overall organization’s security policies and ensure that only authorized users have the necessary access rights. In addition, creating users allows for better auditing and tracking of database activities, as each user’s actions can be logged and monitored separately. 

Thus, user creation in MySQL is a fundamental aspect of database administration, enabling efficient management of resources and safeguarding sensitive information from unauthorized access or misuse.

Access MySQL as a Root User

Once MySQL is installed, usually the first user is the root user, who also doubles as the administrator. The root user is granted complete access to the MySQL database, including the ability to create, remove, and view user information.

This complete access to the MySQL database can lead to security vulnerabilities, and sharing the root user credentials among multiple users is hazardous. Therefore, accessing MySQL databases as root users should be handled with caution. 

But there are scenarios such as initial MySQL server configuration where non-root users cannot access the database, the root user might be necessary for carrying out critical operations.

Start by running the following command to access MySQL as the root user:

# sudo mysql -u root -p

This command initiates a connection to the MySQL database server as the root user. 

Here,

  • mysql: This is the command-line client for MySQL.
  • -u root: This option specifies the username for login, which is the root in this case.
  • -p: This option prompts you to enter the root user’s password.

Once you execute the command, you’ll be prompted to enter the MySQL root password. 

Once authenticated, you’ll gain access to the MySQL command-line interface (also known as shell), where you can execute SQL queries and manage the database server.

sudo mysql -u root -p

List Users in a MySQL Database

Now that you have access to the MySQL shell, you can try out one or more of the following processes to list users in a MySQL database.

Display All MySQL Users

You can execute the following SQL statement to retrieve a list of all user accounts.

mysql> SELECT user FROM mysql.user;

This command retrieves a complete list of user accounts registered on your MySQL database server.

Alternatively, you can also use a graphical user interface (such as phpMyAdmin) to interact with MySQL database to view user accounts within the user management section or a similar interface. 

SELECT user FROM mysql.user

Remember that accessing MySQL as the root user grants you extensive privileges, so exercise caution when performing administrative tasks.

List mysql.user Database Fields

The mysql.user table in a MySQL database server stores information about all user accounts that have permission to access the server, along with their global privileges. 

To retrieve a list of all fields (columns) in the mysql.user table, use the following SQL statement:

mysql> desc mysql.user;

The entries in the Field column denote the specific data fields available for retrieval from the mysql.user database.

desc mysql.user

You can extract the data in the Field column in mysql.user table by using appropriate SQL queries. This allows you to effectively retrieve information on user accounts and their permissions within the MySQL database server.

Display MySQL User Details

Now that you have a list of all fields (columns) in the mysql.user table, you can combine multiple options into a single SQL statement to obtain comprehensive user details.

A common option is to use the SELECT statement along with field names from the output to retrieve specific data. 

For instance, run this command to generate a table containing the user, host, and authentication_string columns:

mysql> SELECT User, Host, authentication_string FROM mysql.user;

The query enhances the data in the User column by appending two additional columns, offering crucial details like the user’s password and hostname.

sELECT User, Host, authentication_string FROM mysql.user

Display Unique MySQL Users

Eliminating MySQL usernames duplicated across multiple rows is primarily beneficial for efficient database management and security analysis. 

We recommend the following SQL query to list out the distinct MySQL usernames:

mysql> SELECT DISTINCT User FROM mysql.user;

SELECT-DISTINCT-User-FROM-mysql.user

The output filters out duplicate MySQL rows and displays each username only once.

You can also refer to our tutorial on removing duplicate entries from MySQL databases for more information.

Display the Current MySQL User

You can run an SQL query to display the current MySQL user. You can execute this query at the MySQL command-line interface or a MySQL client tool. This statement retrieves information about the currently authenticated user. 

We recommend user() or current_user() functions to obtain information about the currently authenticated user:

mysql> SELECT user();

SELECT user

OR

mysql> SELECT current_user();

SELECT current_user

Here, the output indicates that the current MySQL user is root@localhost.

Display Currently Logged-In MySQL Users

It is crucial to detect unauthorized access for security reasons. To verify the currently logged-in users into the MySQL database server, execute the following query:

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

SELECT-user-hostdb-command-FROM-information

The output provides details on the currently logged in users, the specific database they are interacting with, and the command(s) they have executed.

This information is essential for understanding user activity and ensuring the security of your MySQL server.

Conclusion

The ability to list MySQL users is a fundamental aspect of database management, providing administrators with essential insights into user activities and access rights. This includes everything from basic user information to more complex database-level privileges and host-based authentication settings.

Managing MySQL users is complex, but its built-in functions, like the USER and GRANT statements, make it straightforward to handle administrative users and define their privileges.

FAQs

Q. How can I list all users in a MySQL database using the MySQL show users command?

To list all users in a MySQL database, you can use the mysql show users command in the MySQL command line. This command will display a list of all user accounts in the database, along with their permissions.

Q. What information does the MySQL show users command provide about users in a MySQL database?

The mysql show users command provides information about user accounts in a MySQL database such as usernames, host names, and specific permissions assigned to each user.

Q. How can I learn more about user permissions in a MySQL database?

You can learn more about user permissions in a MySQL database by using commands like show users in mysql or show all users to view the privileges assigned to each user.

Q. Is there a tutorial available to help me understand how to list users in a MySQL database?

Yes, there are tutorials available online that can guide you on how to list users in a MySQL database using commands like mysql show users command or list users in the MySQL console.

Q. How can I remove a user from a MySQL database?

To remove a user from a MySQL database, you can use the appropriate command in MySQL, such as remove mysql user or new database user to revoke access privileges for that user.

Q. Can I list specific permissions for a user in a MySQL database?

Yes, you can list specific permissions for a user in a MySQL database by using commands like show the users or user privileges to view the exact access rights granted to that user.

Q. How do I display the currently logged-in users in a MySQL database?

To display the currently logged-in users in a MySQL database, you can enter the MySQL command line and use commands like show databases or show tables to see a list of users and their activities.

Try this guide with our instant dedicated server for as low as 40 Euros