Today we are going to guide you on how to show all users in the MySQL users Database.
A common question that most beginner MySQL users ask is “How do I see all of the users in my MySQL server?” Most of them assume that there is a show users command in MySQL, but there isn’t one. This is a common mistake because there are other MySQL commands for displaying information about the database. For example, SHOW DATABASES will show us all of the databases that are present in our MySQL Server, and SHOW TABLES will show us all the tables in the MySQL database that you have selected.
It’s not unusual for people to assume that there should be a SHOW USERS command in MySQL. Even though there isn’t a specific command for it, there are several ways to actually see the list of users and even filter them to see exactly what you need. This can all be done with the MySQL command line tool – let’s get started.
Once you are logged in to your Linux server, execute the following command to log in to the MySQL command line interface.
Table of Contents
Log in to your MySQL Server
Log in to your MySQL ServerLog in as root to your MySQL server using the following command:
mysql -u root -p
Then enter your MySQL root password. It is not set by default, so all you need to do is press the [Enter] key if you never set it. However, if you have set the password (whether with the mysql_secure_installation script or something else), you should enter that password now.
MySQL Show Users Command
We’ll just to query the User table to show all MySQL Users with the following command:
SELECT User, Host, Password FROM mysql.user;
The list of all MySQL users should be similar to the one below:
+------------------+--------------+--------------+ | user | host | password | +------------------+--------------+--------------+ | root | localhost | 37as%#8123fs | | debian-test-user | localhost | HmBEqPjC5Y | | johnsm | localhost | | | brian | localhost | | | root | 111.111.111.1| | | guest | % | | | adrianr | 10.11.12.13 | RFsgY6aiVg | +------------------+--------------+--------------+ 7 rows in set (0.01 sec)
If you want to add more columns or exclude some, just edit the command with the columns you need. You may only need the names of the users, so you can use SELECT User FROM mysql.user;
Another way to see all users is to simply use the asterisk (*) wildcard when selecting fields from the user table. It should look like this:
SELECT * FROM mysql.user;
This will return all possible field information for each user. Be aware that the number of fields is typically very long/extensive, so the output likely won’t be readable and you probably don’t need all of the information available for each user. This is why we suggest specifying the field names, that way the output will be easily readable, and you’ll only see the information that you need.
Some of the fields are related to privileges and permissions that each user has (such as “Insert_priv” or “Drop_priv”), and some fields are for the general properties of the user account, such as “max_connections” or “max_updates”.
Display Only Unique Usernames in MySQL Database
In some cases, selecting the user table can have duplicate results (e.g. one user for different hosts, but the users have the same name) – this can create clutter, so you may want to display only unique usernames.
If you want to display only unique usernames that won’t be repeated in more rows, you can use SELECT DISTINCT User FROM mysql.user;, which should give you this output:
+------------------+ | user | +------------------+ | root | | debian-test-user | | johnsm | | brian | | guest | | adrianr | +------------------+ 6 rows in set (0.01 sec)
No repeated rows, just like we wanted. This can give you a clean look at what users exist on the database server. If you want, check out our guide on how to install MySQL database on Ubuntu 20.04.
MySQL User table Information
As we mentioned in Step 2, there are many fields that you could use to see specific properties of your users. With this next command, you’ll be able to see all of the fields that your MySQL server supports. To see all the fields in the mysql.user table containing a description related to the user table, run the following MySQL command to query the database.
mysql> desc mysql.user;
The output will look similar to the one below. There are a lot of options!:
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.00 sec)
you can now combine this with the example given in Step 2. For example, if you want to return the list of users and their update privileges, you can run this command:
SELECT User, Update_priv FROM mysql.user;
Just replace “Update_priv” with any fields that you might need.
Show Information About Current User
You can use a built-in function of MySQL to see the name and host of the user that you used to log into the MySQL command line. It’s the “user()” function, and all you have to do is select it:
SELECT user();
The output should give you information on the user running the query.
And that’s it, with the completion of this tutorial, you have learned how to show MySQL user accounts via the command line.
If you have any additional questions on the MySQL database, feel free to leave a comment below. You can always get a Managed MySQL hosting from us and our fully managed support will help you with problems related to MySQL or will explain how to show all users in the MySQL database.
PS. If you liked this post on making MySQL show all users, please share it with your friends via social media networks by using our share shortcuts. Thanks.
Either you can just simply use * (asterisk) after SELECT word. It’ll choose all the columns
SELECT User FROM mysql.user;
thank you