View all users and permissions in the MySQL database

View all users in the MySQL database

mysql> SELECT DISTINCT CONCAT (' User: ', user, ' @ ', host, '; ') as query from Mysql.user;
+---------------------------------------+
| Query |
+---------------------------------------+
| User:' cactiuser ' @ '% '; |
| User:' common_user ' @ '% '; |
| User:' dba ' @ '% '; |
| User:' root ' @ '% '; |
| User:' slave ' @ '% '; |
| User:' wtgame ' @ '% '; |
| User:' root ' @ ' 127.0.0.1 '; |
| User:' webman ' @ ' 127.0.0.1 '; |
| User:' root ' @ ' 192.168.166.200 '; |
| User:' cactiuser ' @ ' 192.168.166.202 '; |
| User:' root ' @ ' 192.168.166.202 '; |
| User:' @ ' localhost '; |
| User:' cacti ' @ ' localhost '; |
| User:' cactiuser ' @ ' localhost '; |
| User:' dba ' @ ' localhost '; |
| User:' extmail ' @ ' localhost '; |
| User:' javauser ' @ ' localhost '; |
| User:' root ' @ ' localhost '; |
| User:' webman ' @ ' localhost '; |
| User:' @ ' Localhost.localdomain '; |
| User:' root ' @ ' Localhost.localdomain '; |
+---------------------------------------+
Rows in Set (0.01 sec)

to view the permissions of a specific user in a database
mysql> Show grants for' cactiuser ' @ '% ';
+-------------------------------------------------------------------------------------------------------------- -----+
| Grants for [email protected]%|
+-------------------------------------------------------------------------------------------------------------- -----+
| GRANT PROCESS, SUPER on *. * to' cactiuser ' @ '% 'Identified by PASSWORD ' *daff917b80e3314b1abecba9df8785afd342ce89 ' |
| GRANT all privileges on ' cacti '.' cactiuser ' @ '% '                                                              |
+-------------------------------------------------------------------------------------------------------------- -----+
2 rows in Set (0.00 sec)

Mysql>SELECT * from Mysql.user where user= ' Cactiuser ' \g
1. Row ***************************
Host:%
User:cactiuser
Password: *daff917b80e3314b1abecba9df8785afd342ce89
Select_priv:n
Insert_priv:n
Update_priv:n
Delete_priv:n
Create_priv:n
Drop_priv:n
Reload_priv:n
Shutdown_priv:n
Process_priv:y
File_priv:n
Grant_priv:n
References_priv:n
Index_priv:n
Alter_priv:n
Show_db_priv:n
Super_priv:y
Create_tmp_table_priv:n
Lock_tables_priv:n
Execute_priv:n
Repl_slave_priv:n
Repl_client_priv:n
Create_view_priv:n
Show_view_priv:n
Create_routine_priv:n
Alter_routine_priv:n
Create_user_priv:n
Event_priv:n
Trigger_priv:n
Ssl_type:
Ssl_cipher:
X509_issuer:
X509_subject:
max_questions:0
max_updates:0
max_connections:0
max_user_connections:0

Viewing the user table structure requires specific items to be queried in conjunction with the table structure
mysql> desc Mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char (60) | NO |         PRI |       | |
| User | char (16) | NO |         PRI |       | |
| Password | char (41) |     NO |         |       | |
| Select_priv | Enum (' N ', ' Y ') |     NO | |       N | |
******************
******************
+-----------------------+-----------------------------------+------+-----+---------+-------+
All rows in Set (0.00 sec)

This article from "Dongdong Homeland" blog, declined reprint!

View all users and permissions in the MySQL database