Percona-Tookit工具包之pt-show-grants

 

Preface

 

    User privileges regulation is pretty important in DBAs routine job.As we all know,it's the less the better to reduce risks when someone who has no privileges manipulates data by malevolence.On the other hand,when we implement HA,replication or the other tools,it's significant and efficient to grant proper privileges to specific users.

 

Introduce

 

    pt-show-grants is the right tool which can directly provide an overview of grants to all users on connected MySQL db server what is rather convenient for us.There're also several advartages of using it.At first,you can dump user grants from one server to another one simply. Secondly,you can organize version control by placing the user grants with it since it will sort the grant statements in order what "show grants;" may not do.Thirdly,now that it provide a normalized format than "show grants;" does,you can distinguish the different user grants between two servers efficiently.

 

Procedure

 

Usage

1 pt-show-grants [OPTIONS] [DSN]

 

Parameters

 1 //Regular parameters.
 2 --drop //Add "DROP USER" before each user of output,which can be used to get a ddl of droping user.
 3 --flush //Add "FLUSH PRIVILEGES" after output(version ahead 4.1.1 need).
 4 --ignore //Speicify the ignore user.
 5 --only //on the contrary of "--ignore" does,specify the only user you want.
 6 --include-unused-roles //This options merely for MySQL 8.0 + version which support roles.
 7 --seperate //List the grant and revoke statement respectively.
 8 
 9 //Dump hearder relevant.
10 --no-header //Don't print head information of dump.
11 --no-timestamp //Don't add timestam to the head of dump.

 

Examples

 

Execute without  any parameters.(it will read connection options in defatul my.cnf )

 1 [root@zlm1 06:17:09 ~]
 2 #pt-show-grants
 3 -- Grants dumped by pt-show-grants
 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 07:59:32
 5 -- Grants for 'bkuser'@'localhost'
 6 CREATE USER IF NOT EXISTS 'bkuser'@'localhost';
 7 ALTER USER 'bkuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3DE5D9E4FBC1E464DA1B1172D6333CE89FDE5C61' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
 8 GRANT LOCK TABLES, PROCESS, RELOAD, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost';
 9 -- Grants for 'mysql.session'@'localhost'
10 CREATE USER IF NOT EXISTS 'mysql.session'@'localhost';
11 ALTER USER 'mysql.session'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
12 GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';
13 GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
14 GRANT SUPER ON *.* TO 'mysql.session'@'localhost';
15 -- Grants for 'mysql.sys'@'localhost'
16 CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';
17 ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
18 GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';
19 GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
20 GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
21 -- Grants for 'repl'@'192.168.56.%'
22 CREATE USER IF NOT EXISTS 'repl'@'192.168.56.%';
23 ALTER USER 'repl'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
24 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%';
25 -- Grants for 'root'@'localhost'
26 CREATE USER IF NOT EXISTS 'root'@'localhost';
27 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
28 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
29 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

 

Specify the only user "root".

1 [root@zlm1 08:38:28 ~]
2 #pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only=root
3 -- Grants dumped by pt-show-grants
4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:38:30
5 -- Grants for 'root'@'localhost'
6 CREATE USER IF NOT EXISTS 'root'@'localhost';
7 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
8 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
9 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

 

Specify the only user "root" with revoke statement separated with grant statement.

 1 [root@zlm1 08:38:30 ~]
 2 #pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only=root --separate --revoke 
 3 -- Grants dumped by pt-show-grants
 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:38:46
 5 -- Revoke statements for 'root'@'localhost'
 6 REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'localhost';
 7 REVOKE GRANT OPTION ON *.* FROM 'root'@'localhost';
 8 REVOKE PROXY ON ''@'' FROM 'root'@'localhost';
 9 REVOKE GRANT OPTION ON *.* FROM 'root'@'localhost';
10 -- Grants for 'root'@'localhost'
11 CREATE USER IF NOT EXISTS 'root'@'localhost';
12 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
13 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
14 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

 

Specify the only user "root" with drop statement.

 1 [root@zlm1 08:38:46 ~]
 2 # pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only=root --drop
 3 -- Grants dumped by pt-show-grants
 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:39:03
 5 DROP USER 'root'@'localhost';
 6 DELETE FROM `mysql`.`user` WHERE `User`='root' AND `Host`='localhost';
 7 -- Grants for 'root'@'localhost'
 8 CREATE USER IF NOT EXISTS 'root'@'localhost';
 9 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
10 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
11 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

 

Specify the only user "repl" with no timestamp.

1 [root@zlm1 08:40:57 ~]
2 #pt-show-grants -h192.168.56.100 -P3306 -urepl -prepl4slave --only repl --no-timestamp
3 -- Grants dumped by pt-show-grants
4 -- Dumped from server 192.168.56.100 via TCP/IP, MySQL 5.7.21-log
5 -- Grants for 'repl'@'192.168.56.%'
6 CREATE USER IF NOT EXISTS 'repl'@'192.168.56.%';
7 ALTER USER 'repl'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
8 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%';

 

Specify the only user "repl" with no header.

1 [root@zlm1 08:41:46 ~]
2 #pt-show-grants -h192.168.56.100 -P3306 -urepl -prepl4slave --only repl --no-header
3 -- Grants for 'repl'@'192.168.56.%' //Only message of annotation this time.
4 CREATE USER IF NOT EXISTS 'repl'@'192.168.56.%';
5 ALTER USER 'repl'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
6 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%';

 

Distinguish the difference of user privileges between zlm1 & zlm2 and make it be same.

 1 //Dump the user grans into a file on server zlm1
 2 [root@zlm1 08:43:15 ~]
 3 #pt-show-grants -h192.168.56.100 -P3306 -urepl -prepl4slave --only repl > repl_grants.sql 
 4 
 5 //Copy the file to zlm2.
 6 [root@zlm1 08:43:18 ~]
 7 #scp repl_grants.sql 192.168.56.101:~
 8 repl_grants.sql                                                                                                    100%  436     0.4KB/s   00:00    
 9 
10 [root@zlm1 08:43:40 ~]
11 
12 //Show user infomation on zlm2.
13 root@localhost:mysql.sock [(none)]>select user,host from mysql.user;
14 +---------------+--------------+
15 | user          | host         |
16 +---------------+--------------+
17 | repl          | 192.168.56.% |
18 | bkuser        | localhost    |
19 | mysql.session | localhost    |
20 | mysql.sys     | localhost    |
21 | root          | localhost    |
22 +---------------+--------------+
23 5 rows in set (0.00 sec)
24 
25 //Show user grants infomation.
26 root@localhost:mysql.sock [(none)]>show grants for repl@'192.168.56.%';
27 +---------------------------------------------------------+
28 | Grants for repl@192.168.56.%                            |
29 +---------------------------------------------------------+
30 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.56.%' |
31 +---------------------------------------------------------+
32 1 row in set (0.00 sec)
33 
34 //Revoke the privileges from repl user to mimic difference.
35 root@localhost:mysql.sock [(none)]>revoke all on *.* from repl@'192.168.56.%';
36 Query OK, 0 rows affected (0.00 sec)
37 
38 root@localhost:mysql.sock [(none)]>show grants for repl@'192.168.56.%';
39 +---------------------------------------------+
40 | Grants for repl@192.168.56.%                |
41 +---------------------------------------------+
42 | GRANT USAGE ON *.* TO 'repl'@'192.168.56.%' |
43 +---------------------------------------------+
44 1 row in set (0.00 sec)
45 
46 //Check difference with zlm1.
47 [root@zlm2 08:47:56 ~]
48 #pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only repl | diff repl_grants.sql -
49 2c2
50 < -- Dumped from server 192.168.56.100 via TCP/IP, MySQL 5.7.21-log at 2018-06-29 08:43:18
51 ---
52 > -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:48:00
53 6c6
54 < GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%'; //User privileges in dump file.
55 ---
56 > GRANT USAGE ON *.* TO 'repl'@'192.168.56.%'; //User privileges in local server.
57 
58 [root@zlm2 08:48:00 ~]
59 #mysql -hlocalhost -S /var/lib/mysql/mysql.sock -uroot -pPassw0rd < repl_grants.sql 2>/dev/null //Import the user grants from dump file.
60 
61 //Check the privileges of user repl again.
62 [root@zlm2 08:48:29 ~]
63 #mysql
64 Welcome to the MySQL monitor.  Commands end with ; or \g.
65 Your MySQL connection id is 31
66 Server version: 5.7.21-log MySQL Community Server (GPL)
67 
68 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
69 
70 Oracle is a registered trademark of Oracle Corporation and/or its
71 affiliates. Other names may be trademarks of their respective
72 owners.
73 
74 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
75 
76 root@localhost:mysql.sock [(none)]>show grants for repl@'192.168.56.%';
77 +------------------------------------------------------+
78 | Grants for repl@192.168.56.%                         |
79 +------------------------------------------------------+
80 | GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%' | //The privileges are same with zlm1 now.
81 +------------------------------------------------------+
82 1 row in set (0.00 sec)

 

Summary

• pt-show-grants helps us dba to manager user privileges better than MySQL command line "show grant for ... ;" does.
• pt-show-grants is convenient to use even with any options specified.
• pt-show-grants can be used to check the difference of user privileges between the servers.
• pt-show-grants is not a intrusive tool like pt-pmp,you can run it at any time you need.