In MySQL, user management plays a crucial role in making database access secure and efficient. But how do you display existing users? In this blog post you will learn how to quickly and easily list all MySQL users, what information you will get and what you should look out for.
Step 1: Login to MySQL
To display the users in MySQL, you must first log in with a user account with sufficient permissions (e.g. B. root). You can do this from the command line:
root@testhost:~# mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12135
Server version: 10.11.8-MariaDB-0ubuntu0.24.04.1-log Ubuntu 24.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL: root [(none)]>Step 2: Retrieve user information
The information about the MySQL user accounts is stored in the table mysql.user, which is part of the internal database mysql. To display all users, you can use the following SQL command:
SELECT User, Host FROM mysql.user;This command displays a list of all user accounts and the associated hosts from which they are allowed to log in.
MySQL: root [(none)]> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| wordpress_nps_ro | % |
| mariadb.sys | localhost |
| mysql | localhost |
| netdata | localhost |
| root | localhost |
| wordpress_nps | localhost |
+------------------+-----------+
6 rows in set (0.003 sec)
MySQL: root [(none)]>The User column shows the user(s).
The Host column shows the host name or IP address from which the user can log in. The % sign means that the user can connect from any host.
Step 3: Verify user rights and permissions
What permissions does a user have? You can check this with the following command:
SHOW GRANTS FOR 'wordpress_nps'@'localhost';If you want to view the permissions for your current user, the following command makes it easier:
SHOW GRANTS FOR CURRENT_USER();Conclusion
Viewing users in MySQL is a common task for system engineers and administrators, but it is very simple.
If you have any further questions about MySQL or need help running servers, please contact our outsourcing team. You can reach us at the e-mail address support@netways.de.
