Managing users is a fundamental aspect of database administration. In this tutorial, we’ll cover the steps necessary to display all users within a MySQL database. This includes using SQL queries to fetch user details, filtering users based on specific criteria, and understanding user privileges. Additionally, we will explore how to list MySQL users using the mysqladmin command-line tool.

Prerequisites

Before we dive into listing users, make sure you have the following prerequisites:

  • MySQL installed on your system
  • Access to MySQL with administrative privileges
  • Basic understanding of MySQL commands and SQL syntax

Step 1 – Connecting to MySQL

First, let’s connect to the MySQL server. Open your terminal or command prompt and enter the following command:

mysql -u root -p

You will be prompted to enter the root user’s password. After entering the password, you’ll be logged into the MySQL shell.

Step 2 – Using the SELECT Statement

In MySQL, user information is stored in a table called mysql.user. To list all users, you need to query this table.

To display all users, execute the following SQL command:

SELECT User, Host FROM mysql.user;

This command will list all users along with the hosts from which they can connect.

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| user1            | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

Step 3 – Detailed User Information

If you need more details about each user, you can expand the query to include additional columns:

SELECT User, Host, authentication_string, plugin, password_expired FROM mysql.user;

Output:

+------------------+-----------+------------------------------------------------------------------------+-----------------------+------------------+
| User             | Host      | authentication_string                                                  | plugin                | password_expired |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+------------------+
| debian-sys-maint | localhost | $A$005$@Wdglgbtv|9j(cxJW68OpLkbqlxBgsfH0qsLhyC94qbAMa6zgVhVZA88 | caching_sha2_password | N                |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | N                |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | N                |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | N                |
| root             | localhost |                                                                        | auth_socket           | N                |
| user1            | localhost | $A$005$M)n~_ELK>wrj;@NL&wGXK82ttCQPwQelqbl8dDn6W1FQB6A21SIJozFOJsrA | caching_sha2_password | N                |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+------------------+
6 rows in set (0.00 sec)

In this output, you can see the authentication method, whether the password is expired, and other useful information.

Step 4 – Filtering Users

Sometimes, you might want to list users based on specific criteria. For example, to list only users that can connect from any host (%), use the following command.

SELECT User, Host FROM mysql.user WHERE Host = '%';

Output:

+-------+------+
| User  | Host |
+-------+------+
| user1 | %    |
+-------+------+

Step 5 – Managing User Privileges

Understanding user privileges is crucial for database security. To list the privileges of a specific user, use the SHOW GRANTS command. For example, to display the privileges of user1:

SHOW GRANTS FOR 'user1'@'%';

Output:

+-----------------------------------------------------------------+
| Grants for user1@%                                              |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%`                               |
| GRANT SELECT, INSERT ON `database1`.* TO `user1`@`%`            |
+-----------------------------------------------------------------+

Step 6 – List MySQL Users Using mysqladmin

Another way to list MySQL users is by using the mysqladmin command-line tool. This tool provides a quick way to perform various administrative tasks, including listing users.

To see the general status of the MySQL server, including user information, use the following command:

mysqladmin -u root -p extended-status

After entering the root password, you will see an extended status output. However, this command doesn’t directly list users but provides a comprehensive server status, which includes various metrics.

To see the currently active MySQL users, you can use the processlist command:

mysqladmin -u root -p processlist

Output:

+----+------+-----------+-----------+---------+------+-------+------------------+
| Id | User | Host      | db        | Command | Time | State | Info             |
+----+------+-----------+-----------+---------+------+-------+------------------+
| 2  | root | localhost | mydb      | Query   | 0    | init  | show processlist |
| 3  | user1| localhost | database1 | Sleep   | 5    |       |                  |
+----+------+-----------+-----------+---------+------+-------+------------------+

This output shows the currently active users and their corresponding processes.

Conclusion

In this article, we covered how to show or list users in MySQL. You learned how to connect to MySQL, list all users, retrieve detailed user information, filter users based on specific criteria, and display user privileges. Additionally, we explored how to use the mysqladmin tool to view user information. You can now test MySQL on dedicated server hosting from Atlantic.Net!