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