How to view MySQL users and authorizations

1) View the users who are present in MySQL

mysql> select user,host from mysql.user;+------------+--------------------------+|  user       | host                      |+------------+----------- ---------------+| oldgirl    | %                         | |  wan        | %                         | |  wanlong    | %                         | |  rep        | 10.10.10.%               | |  root       | 10.10.10.%                | |  wan        | 10.10.10.%                | |  wanlong    | 10.10.10.%                | |  oldgril123 | 10.10.10.0/255.255.255.0 | |  root       | 127.0.0.1                 | |  root       | ::1                       | |  root       | c67-x64-a8                | |  backup     | localhost                 | |  root       | localhost                 |+------------+--------------------------+13 rows  in set  (0.00 SEC)

2) How to view the user's authorization

mysql> show grants for  ' Wanlong ' @ ' 10.10.10.% '; +---------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------+| grants for  [email protected]%                                                                                                                                                                                                                                                                                                                                               |+------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------+| grant select, update ,  delete, create, drop, reload, shutdown, process, file, references,  INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,  lock tables,&nbsp Execute, replication slave, replication client, create view, show view , create routine, alter routine, create user, event, trigger,  create tablespace on *.* to  ' Wanlong ' @ ' 10.10.10.% '  |+--------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------+1 row  in set  (0.00 SEC)

3) What permissions do all privileges have?

A. Create a test account and authorize it:

Mysql> create user Wanlong identified by ' Wanlong '; Query OK, 0 rows affected (0.01 sec) Mysql> Grant all privileges on *. * to ' wanlong ' @ ' 10.10.10.% '; Query OK, 0 rows Affected (0.00 sec) mysql> Show grants for ' Wanlong ' @ ' 10.10.10.% '; +---------------------------------- ---------------------+| Grants for [email protected]% |+-------------------------------------------------------+| GRANT all privileges on * * to ' wanlong ' @ ' 10.10.10.% ' |+-------------------------------------------------------+1 row In Set (0.00 sec)

B. Reclaim the Insert permission and view the user's permissions

mysql> revoke insert on *.* from  ' Wanlong ' @ ' 10.10.10.% '; query ok, 0 rows affected  (0.00 sec) mysql> flush privileges; query ok, 0 rows affected  (0.01 sec) mysql> show grants for   ' Wanlong ' @ ' 10.10.10.% '; +-------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------------+| grants for [email protected]%                                                                                                                                                                                                                                                                                                                                               |+------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---+| grant&nbsp Select, update, delete, create, drop, reload, shutdown, process, file,  references, index, alter, show databases, super, create temporary  tables, lock tables, execute, replication slave, replication client,  create view, show view, create routine, alter routine, create  USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO  ' Wanlong ' @ ' 10.10.10.% '  |+------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----------------------------------+1 row in set  (0.00 SEC)

C, export the file, and view the authorization (all privileges also need to add the insert authorization that you just canceled with revoke)

# mysql-uroot-predhat12345-s/DATA/3306/MYSQL.SOCK-E "show grants for ' Wanlong ' @ ' 10.10.10.% ';" | Grep-i grant|tail-1|tr ', ' \ n ' >all.privileges View all Privileges permissions: # Cat All.privileges Grant SELECT UPDATE DELETE C reate DROP RELOAD SHUTDOWN PROCESS FILE REFERENCES INDEX ALTER SHOW DATABASES SUPER CREATE temporary TABLES LOCK TABLES EX Ecute REPLICATION SLAVE REPLICATION CLIENT CREATE view SHOW view CREATE ROUTINE ALTER ROUTINE CREATE USER EVENT TRIGGER CR Eate tablespace on * * to ' wanlong ' @ ' 10.10.10.% '

This article is from the "Frozen vs watermelon" blog, so be sure to keep this source http://molewan.blog.51cto.com/287340/1861834

How to view MySQL users and authorizations