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

# Test grants for various objects (especially variables) related to
# the binary log

source include/have_log_bin.inc;

connection default;
--disable_warnings
reset master;
--enable_warnings

set @saved_binlog_format = @@global.binlog_format;
create user mysqltest_1@localhost;
GRANT SELECT on test.* to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;

connect (plain,localhost,mysqltest_1,,test);
connect (root,localhost,root,,test);

# Testing setting session SQL_LOG_BIN variable both as
# root and as plain user.

--echo **** Variable SQL_LOG_BIN ****

connection root;
--echo [root]
set session sql_log_bin = 1;

connection plain;
--echo [plain]
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
set session sql_log_bin = 1;


# Testing setting both session and global BINLOG_FORMAT variable both
# as root and as plain user.

--echo **** Variable BINLOG_FORMAT ****

connection root;
--echo [root]
set global binlog_format = row;
set session binlog_format = row;

connection plain;
--echo [plain]
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
set global binlog_format = row;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
set session binlog_format = row;

--echo **** Clean up ****
disconnect plain;
disconnect root;

connection default;
set global binlog_format = @saved_binlog_format;
drop user mysqltest_1@localhost;


# Testing if REPLICATION CLIENT privilege is enough to execute
# SHOW MASTER LOGS and SHOW BINARY.
CREATE USER 'mysqltest_1'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqltest_1'@'localhost';
--connect(rpl,localhost,mysqltest_1,,"*NO-ONE*")

--connection rpl
# We are only interested if the following commands succeed and not on
# their output.
--disable_result_log
SHOW MASTER LOGS;
SHOW BINARY LOGS;
SHOW BINLOG STATUS;
--enable_result_log 

# clean up
--disconnect rpl
connection default;
DROP USER 'mysqltest_1'@'localhost';


--echo #
--echo # Start of 10.5 test
--echo #

--echo #
--echo # MDEV-21743 Split up SUPER privilege to smaller privileges 
--echo #

--echo # Test that REPLICATION CLIENT is an alias for BINLOG MONITOR

CREATE USER user1@localhost;
GRANT REPLICATION CLIENT ON *.* TO user1@localhost;
SHOW GRANTS FOR user1@localhost;
REVOKE REPLICATION CLIENT ON *.* FROM user1@localhost;
SHOW GRANTS FOR user1@localhost;
DROP USER user1@localhost;


--echo # Test if SHOW BINARY LOGS and SHOW BINGLOG STATUS are not allowed without REPLICATION CLIENT or SUPER
CREATE USER user1@localhost;
GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
REVOKE REPLICATION CLIENT, SUPER ON *.* FROM user1@localhost;
--connect(user1,localhost,user1,,)
--connection user1
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SHOW MASTER LOGS;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SHOW BINARY LOGS;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SHOW BINLOG STATUS;
--disconnect user1
--connection default
DROP USER user1@localhost;


--echo # Test if PURGE BINARY LOGS is not allowed without BINLOG ADMIN or SUPER
CREATE USER user1@localhost;
GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
REVOKE BINLOG ADMIN, SUPER ON *.* FROM user1@localhost;
--connect(user1,localhost,user1,,)
--connection user1
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00';
--disconnect user1
--connection default
DROP USER user1@localhost;


--echo # Test if PURGE BINLOG is allowed with BINLOG ADMIN
CREATE USER user1@localhost;
GRANT BINLOG ADMIN ON *.* TO user1@localhost;
--connect(user1,localhost,user1,,"*NO-ONE*")
--connection user1
PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00';
--disconnect user1
connection default;
DROP USER user1@localhost;


--echo # Test if PURGE BINLOG is allowed with SUPER
CREATE USER user1@localhost;
GRANT SUPER ON *.* TO user1@localhost;
--connect(user1,localhost,user1,,"*NO-ONE*")
--connection user1
PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00';
--disconnect user1
connection default;
DROP USER user1@localhost;


--echo # Test if SHOW BINLOG EVENTS is not allowed without BINLOG MONITOR
CREATE USER user1@localhost;
GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
REVOKE BINLOG MONITOR ON *.* FROM user1@localhost;
--connect(user1,localhost,user1,,)
--connection user1
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SHOW BINLOG EVENTS;
--disconnect user1
--connection default
DROP USER user1@localhost;


--echo # Test if SHOW BINLOG EVENTS is allowed with BINLOG MONITOR
CREATE USER user1@localhost;
GRANT BINLOG MONITOR ON *.* TO user1@localhost;
--connect(user1,localhost,user1,,"*NO-ONE*")
--connection user1
--disable_result_log
SHOW BINLOG EVENTS;
--enable_result_log
--disconnect user1
connection default;
DROP USER user1@localhost;

--echo #
--echo # MDEV-21975 Add BINLOG REPLAY privilege and bind new privileges to
--echo #             gtid_seq_no, preudo_thread_id, server_id, gtid_domain_id
--echo #
--echo # Test combinations of BINLOG REPLAY guarded features which typically
--echo # arise in mysqlbinlog output replay on server.
--echo #

CREATE USER user1@localhost;
GRANT BINLOG REPLAY ON *.* TO user1@localhost;
GRANT ALL ON test.* TO user1@localhost;
RESET MASTER;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
--connect(user1,localhost,user1,,)
# Genuine mysqlbinlog output
--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
RENAME TABLE t1 to t2;

--exec $MYSQL --user=user1 test < $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql

--connection default
REVOKE BINLOG REPLAY ON *.* FROM user1@localhost;
call mtr.add_suppression("Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation");
--echo # Privilege errors are expected now:
--connection user1
--error 1
--exec $MYSQL --user=user1 test < $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql

--connection default
--let $diff_tables=t1,t2
--source include/diff_tables.inc

--echo #  Test cleanup
--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
DROP TABLE t2,t1;
DROP USER user1@localhost;

--echo #
--echo # End of 10.5 test
--echo #