Each DBMS always has the ability to view the list of users registered on the DBMS server. MySQL is also not without this feature. In addition to displaying a list of all users registered in MySQL, you can additionally display the name of the current user and a list of all users who are currently connected to the MySQL server.
This article will cover how to view the list of MySQL users present on the server in the Ubuntu 20.04 operating system.
How to View a List of MySQL Users
As in any other DBMS in MySQL, you can see a list of all available users.
Step 1. Logging into the MySQL Console
First you need to log into the MySQL console. To do this, enter the following command in the terminal, where alex is your username:
mysql -u alex -p
If you are logging into the shell for the first time, you must log in as root. Next, a phrase will be displayed prompting you to enter the user’s password. At this stage, it is necessary to enter the password of the database user, and not the user present in the operating system. As a security, password is not displayed when typed into the terminal.
Step 2. View available fields
Users in MySQL are stored in a database called mysql in the user table . In order to get a list of MySQL users, you need to display the contents of this table. However, due to the large number of columns, the output of the command is not displayed correctly. In this case, you can display a list of users in a readable format by setting the required columns.
The user table has 51 columns. By executing the SQL query below, you can get the names and descriptions of all the columns in this table.
DESCRIBE mysql.user;
In this case, to view all available users, you can display only one column – user using an SQL query:
SELECT user FROM mysql.user;
How to view the current user
To find out under which user the current session is running in the DBMS, you must execute the following SQL query:
SELECT user();
You can also run a special command present in the MySQL shell – status . This command displays information such as the MySQL server version, connection number, whether SSL is enabled, and also contains the Current user parameter – which displays the current user. The output of the command can be seen below:
status
How to view active users
To display all users who are currently connected to the DBMS, execute the following SQL query:
SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short, GROUP_CONCAT(DISTINCT user) AS users, COUNT(*) AS threads FROM information_schema.processlist GROUP BY host_short ORDER BY COUNT(*), host_short;
Conclusions
This article has covered how to display a list of MySQL users. This can be done in several ways. This operation is one of the most common when administering a DBMS server. If you have any questions, ask them in the comments!