Debian Codegraph @

mysql-test/suite/binlog/t/binlog_grant.test

raw
1 # Test grants for various objects (especially variables) related to
2 # the binary log
3
4 source include/have_log_bin.inc;
5
6 connection default;
7 --disable_warnings
8 reset master;
9 --enable_warnings
10
11 set @saved_binlog_format = @@global.binlog_format;
12 create user mysqltest_1@localhost;
13 GRANT SELECT on test.* to mysqltest_1@localhost;
14 show grants for mysqltest_1@localhost;
15
16 connect (plain,localhost,mysqltest_1,,test);
17 connect (root,localhost,root,,test);
18
19 # Testing setting session SQL_LOG_BIN variable both as
20 # root and as plain user.
21
22 --echo **** Variable SQL_LOG_BIN ****
23
24 connection root;
25 --echo [root]
26 set session sql_log_bin = 1;
27
28 connection plain;
29 --echo [plain]
30 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
31 set session sql_log_bin = 1;
32
33 # Testing setting both session and global BINLOG_FORMAT variable both
34 # as root and as plain user.
35
36 --echo **** Variable BINLOG_FORMAT ****
37
38 connection root;
39 --echo [root]
40 set global binlog_format = row;
41 set session binlog_format = row;
42
43 connection plain;
44 --echo [plain]
45 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
46 set global binlog_format = row;
47 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
48 set session binlog_format = row;
49
50 --echo **** Clean up ****
51 disconnect plain;
52 disconnect root;
53
54 connection default;
55 set global binlog_format = @saved_binlog_format;
56 drop user mysqltest_1@localhost;
57
58 # Testing if REPLICATION CLIENT privilege is enough to execute
59 # SHOW MASTER LOGS and SHOW BINARY.
60 CREATE USER 'mysqltest_1'@'localhost';
61 GRANT REPLICATION CLIENT ON *.* TO 'mysqltest_1'@'localhost';
62 --connect(rpl,localhost,mysqltest_1,,"*NO-ONE*")
63
64 --connection rpl
65 # We are only interested if the following commands succeed and not on
66 # their output.
67 --disable_result_log
68 SHOW MASTER LOGS;
69 SHOW BINARY LOGS;
70 SHOW BINLOG STATUS;
71 --enable_result_log
72
73 # clean up
74 --disconnect rpl
75 connection default;
76 DROP USER 'mysqltest_1'@'localhost';
77
78 --echo #
79 --echo # End of 10.4 tests
80 --echo #
81
82 --echo #
83 --echo # Start of 10.5 tests
84 --echo #
85
86 --echo #
87 --echo # MDEV-21743 Split up SUPER privilege to smaller privileges
88 --echo #
89
90 --echo # Test that REPLICATION CLIENT is an alias for BINLOG MONITOR
91
92 CREATE USER user1@localhost;
93 GRANT REPLICATION CLIENT ON *.* TO user1@localhost;
94 SHOW GRANTS FOR user1@localhost;
95 REVOKE REPLICATION CLIENT ON *.* FROM user1@localhost;
96 SHOW GRANTS FOR user1@localhost;
97 DROP USER user1@localhost;
98
99 --echo # Test if SHOW BINARY LOGS and SHOW BINGLOG STATUS are not allowed without REPLICATION CLIENT
100 CREATE USER user1@localhost;
101 GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
102 REVOKE REPLICATION CLIENT ON *.* FROM user1@localhost;
103 --connect(user1,localhost,user1,,)
104 --connection user1
105 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
106 SHOW MASTER LOGS;
107 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
108 SHOW BINARY LOGS;
109 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
110 SHOW BINLOG STATUS;
111 --disconnect user1
112 --connection default
113 DROP USER user1@localhost;
114
115 --echo # Test if PURGE BINARY LOGS is not allowed without BINLOG ADMIN
116 CREATE USER user1@localhost;
117 GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
118 REVOKE BINLOG ADMIN ON *.* FROM user1@localhost;
119 --connect(user1,localhost,user1,,)
120 --connection user1
121 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
122 PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00';
123 --disconnect user1
124 --connection default
125 DROP USER user1@localhost;
126
127 --echo # Test if PURGE BINLOG is allowed with BINLOG ADMIN
128 CREATE USER user1@localhost;
129 GRANT BINLOG ADMIN ON *.* TO user1@localhost;
130 --connect(user1,localhost,user1,,"*NO-ONE*")
131 --connection user1
132 PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00';
133 --disconnect user1
134 connection default;
135 DROP USER user1@localhost;
136
137 --echo # Test if SHOW BINLOG EVENTS is not allowed without BINLOG MONITOR
138 CREATE USER user1@localhost;
139 GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
140 REVOKE BINLOG MONITOR ON *.* FROM user1@localhost;
141 --connect(user1,localhost,user1,,)
142 --connection user1
143 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
144 SHOW BINLOG EVENTS;
145 --disconnect user1
146 --connection default
147 DROP USER user1@localhost;
148
149 --echo # Test if SHOW BINLOG EVENTS is allowed with BINLOG MONITOR
150 CREATE USER user1@localhost;
151 GRANT BINLOG MONITOR ON *.* TO user1@localhost;
152 --connect(user1,localhost,user1,,"*NO-ONE*")
153 --connection user1
154 --disable_result_log
155 SHOW BINLOG EVENTS;
156 --enable_result_log
157 --disconnect user1
158 connection default;
159 DROP USER user1@localhost;
160
161 --echo #
162 --echo # MDEV-21975 Add BINLOG REPLAY privilege and bind new privileges to
163 --echo # gtid_seq_no, preudo_thread_id, server_id, gtid_domain_id
164 --echo #
165 --echo # Test combinations of BINLOG REPLAY guarded features which typically
166 --echo # arise in mysqlbinlog output replay on server.
167 --echo #
168
169 CREATE USER user1@localhost;
170 GRANT BINLOG REPLAY ON *.* TO user1@localhost;
171 GRANT ALL ON test.* TO user1@localhost;
172 RESET MASTER;
173 CREATE TABLE t1 (a INT);
174 INSERT INTO t1 VALUES (1),(2),(3);
175 --connect(user1,localhost,user1,,)
176 # Genuine mysqlbinlog output
177 --exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
178 RENAME TABLE t1 to t2;
179
180 --exec $MYSQL --user=user1 test < $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
181
182 --connection default
183 REVOKE BINLOG REPLAY ON *.* FROM user1@localhost;
184 call mtr.add_suppression("Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation");
185 --echo # Privilege errors are expected now:
186 --connection user1
187 --error 1
188 --exec $MYSQL --user=user1 test < $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
189
190 --connection default
191 --let $diff_tables=t1,t2
192 --source include/diff_tables.inc
193
194 --echo # Test cleanup
195 --remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
196 DROP TABLE t2,t1;
197 DROP USER user1@localhost;
198
199 --echo #
200 --echo # End of 10.5 test
201 --echo #