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

Posted on in Categories , , last updated February 6, 2017

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 database. 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.

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
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;
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 User distinct 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

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 [email protected]                                                                      |
+------------------------------------------------------------------------------------------------+
| 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:
$ sudo apt-get install percona-toolkit
Type the command:
$ pt-show-grants

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.