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
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
🐧 Get the latest tutorials on Linux, Open Source & DevOps via:
- RSS feed or Weekly email newsletter
- Share on Twitter • Facebook • 0 comments... add one ↓
Category | List of Unix and Linux commands |
---|---|
File Management | cat |
Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
Network Utilities | dig • host • ip • nmap |
OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
Package Manager | apk • apt |
Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
Searching | grep • whereis • which |
User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |