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.

ADVERTISEMENTS

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:

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