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:
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