How to see/get a list of MySQL/MariaDB users accounts

See all MySQL Database Server related FAQ
I am using MariaDB mysql command. How do I see MySQL users in a MySQL/MariaDB database stored on my server?

You need to use mysql command to get a list of MySQL or MaiaDB users accounts on the system. The user table stores username/password, user privileges, and all other information. In this quick tutorial, you will learn how to find out all MySQL and MariaDB users and the permissions granted to each user.
Tutorial details
Difficulty level Easy
Root privileges No
Requirements Linux or Unix terminal
Category Database Server
Prerequisites MySQL or MariaDB
OS compatibility BSD Linux macOS Unix
Est. reading time 3 minutes

Step 1 – Login to mysql

First log into your MySQL/MariaDB server as a root user using the mysql client. Type the following command:
$ mysql -u root -p
OR
$ mysql -u root -h localhost -p mysql
For AWS RDS MySQL or MariaDB try:
$ mysql -h rds-hostname-here --ssl-ca=/path/to/rds-combined-ca-bundle.pem --ssl-mode=VERIFY_IDENTITY -u RDS-USERNAME-HERE -p
For example:
$ mysql -h ls-xxx.yyy.us-west-1.rds.amazonaws.com --ssl-ca=~/rds-combined-ca-bundle.pem --ssl-mode=VERIFY_IDENTITY -u dbmasteruser -p
Here is what you will see:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1889472
Server version: 8.0.36-0ubuntu0.22.04.1 (Ubuntu)
 
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
You are enforcing ssl connection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql> STATUS;
--------------
mysql  Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
 
Connection id:		1889472
Current database:	
Current user:		root@localhost
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.36-0ubuntu0.22.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Binary data as:		Hexadecimal
Uptime:			16 days 16 hours 21 min 57 sec
 
Threads: 2  Questions: 37286203  Slow queries: 2964416  Opens: 6918  Flush tables: 3  Open tables: 6525  Queries per second avg: 25.869
--------------

Once logged in use various SQL queries as follows to show users accounts in a MariaDB or MySQL database.

Step 2 – Show users

Type the following query at mysql> prompt to see list the users in a MySQL database:
mysql> SELECT User FROM mysql.user;
Sample outputs:

Fig.01: How to get a list of MySQL user accounts

Fig.01: How to get a list of MySQL user accounts

Step 3 – Show users along with host name where they are allowed to login

The syntax is:
mysql> SELECT host, user FROM mysql.user;
OR
mysql> SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user;
OR
mysql> SELECT host, user, password FROM mysql.user;
You may get error that read as follows from the last SQL statement:

ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’

The password column in the mysql.user table has been removed for security reasons in MySQL server version 8.x. So use the following as passwords are now stored in the authentication_string column using more secure hashing algorithms. For example:
mysql> SELECT User, Host, authentication_string FROM mysql.user;
Sample outputs:

+---------------+----------------+
| User          | Host           |
+---------------+----------------+
| vivek         | 10.175.18.1    |
| raj           | 127.0.0.1      |
| sai           | 192.168.12.6   |
| blog          | 192.168.12.6   |
| wordpress     | 192.168.12.6   |
| sales         | localhost      |
| accounting    | localhost      |
+---------------+----------------+
7 rows in set (0.00 sec)

Step 4 – How to avoid repetitions of user names?

Try the following sql query:
mysql> SELECT DISTINCT User FROM mysql.user;
OR
mysql> SELECT DISTINCT User, Host FROM mysql.user;
The SELECT DISTINCT statement is used to return only different values.

Step 5 – Get a listing of the fields in the mysql.user table

Type the following sql command to see all field names associated with mysql.user table:
mysql> DESC mysql.user;
Sample outputs:

+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(80)                          | 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)                           | NO   |     | 0       |       |
| plugin                 | char(64)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
| is_role                | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
44 rows in set (0.00 sec)

Step 6 – Finding out user rights

Type the following command:
mysql> SELECT User, Db, Host from mysql.db;
Sample outputs:

+---------------+-------------------+----------------+
| User          | Db                | Host           |
+---------------+-------------------+----------------+
| vivek         | wordpress         | 10.174.111.100 |
| raj           | forum             | 127.0.0.1      |
| sai           | cms               | 192.168.1.1    |
+---------------+-------------------+----------------+
15 rows in set (0.00 sec)

The following provide more info about mysql.db:
mysql> desc mysql.db
Sample outputs:

MariaDB [(none)]> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(80)      | NO   | PRI |         |       |
| 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       |       |
| 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       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_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       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

Step 7 – How to find the privilege(s) granted to a particular MySQL user

Type the following sql statement:
mysql> show grants for 'vivek'@'%';
OR
mysql> show grants for 'vivek'@'192.168.1.1';
OR
mysql> show grants for 'root'@'localhost';
Sample outputs:

+------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

To assist seeing grants, enter:
mysql> select distinct concat('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') as query from mysql.user;
Sample outputs:

+---------------------------------------------------+
| query                                             |
+---------------------------------------------------+
| SHOW GRANTS FOR 'vivek'@'10.175.1.5';             |
| SHOW GRANTS FOR 'raj'@'127.0.0.1';                |
| SHOW GRANTS FOR 'sai'@'192.168.1.1';              |
| SHOW GRANTS FOR 'root'@'localhost';               |
| SHOW GRANTS FOR 'wiki'@'localhost';               |
| SHOW GRANTS FOR 'forum'@'localhost';              |
+---------------------------------------------------+
6 rows in set (0.00 sec)

You can now copy and paste SHOW GRANTS FOR 'vivek'@'10.175.1.5';
You can canonicalize and print MySQL grants so you can effectively replicate, compare and version-control them using pt-show-grants. Install pt-show-grants using apt-get command or apt command (or dnf command/yum command or RHEL/CentOS/Fedora/Rocky/Alama Linux):
$ sudo apt-get install percona-toolkit
Type the command:
$ pt-show-grants

Summing up

I hope this helps someone to get a list of mysql users using the CLI.

  1. For simple user list: SELECT User FROM mysql.user;
  2. To print user and host names: SELECT User, Host FROM mysql.user;
  3. Want detailed MySQL user info: SELECT * FROM mysql.user;
  4. Get detailed MySQL user table fields descriptions to build custom SQL query: DESC mysql.user;
  5. Show grants: SHOW GRANTS FOR 'username-here'@'hostname-here';

If you have any questions or comments, leave a comment below. Make sure you read mysql documentation and man page using the help or man command:
$ man mysql
$ man pt-show-grants

🥺 Was this helpful? Please add a comment to show your appreciation or feedback.

nixCrat Tux Pixel Penguin
Hi! 🤠
I'm Vivek Gite, and I write about Linux, macOS, Unix, IT, programming, infosec, and open source. Subscribe to my RSS feed or email newsletter for updates.

6 comments… add one
  • PAUL Mar 5, 2022 @ 10:20

    informative. please keep writing more and more

  • Last In Class Jul 29, 2022 @ 1:49

    Thanks! A great piece, putting the important and useful commands all in one page. Much appreciated.

  • Stephen Mann Mar 19, 2023 @ 23:06

    “You are seeing this message because ad or script blocking software is interfering with this page.
    Disable any ad or script blocking software, then reload this page.”

    I DO NOT HAVE AN AD BLOCKER !!!!!!!!

  • a person May 15, 2024 @ 18:09

    i had to use SELECT DISTINCT User from mysql.user;

    rather than SELECT User distinct from mysql.user;

    from step 4

    • 🛡️ Vivek Gite (Author and Admin) Vivek Gite May 15, 2024 @ 19:22

      I fixed it. It was a typo on my part. Sorry about that. I appreciate your feedback.

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre> for code samples. Your comment will appear only after approval by the site admin.