262 lines
8.1 KiB
Plaintext
262 lines
8.1 KiB
Plaintext
# Tests of grants and users
|
|
|
|
--source include/not_group_replication_plugin.inc
|
|
source include/master-slave.inc;
|
|
|
|
--echo **** On Master ****
|
|
connection master;
|
|
|
|
CREATE USER dummy@localhost;
|
|
CREATE USER dummy1@localhost, dummy2@localhost;
|
|
|
|
SELECT user, host FROM mysql.user WHERE user like 'dummy%';
|
|
SELECT COUNT(*) FROM mysql.user WHERE user like 'dummy%';
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo **** On Slave ****
|
|
SELECT user,host FROM mysql.user WHERE user like 'dummy%';
|
|
SELECT COUNT(*) FROM mysql.user WHERE user like 'dummy%';
|
|
|
|
--echo **** On Master ****
|
|
connection master;
|
|
|
|
# No user exists
|
|
error ER_CANNOT_USER;
|
|
DROP USER nonexisting@localhost;
|
|
|
|
# At least one user exists, but not all
|
|
error ER_CANNOT_USER;
|
|
DROP USER nonexisting@localhost, dummy@localhost;
|
|
|
|
# All users exist
|
|
DROP USER dummy@localhost, dummy1@localhost, dummy2@localhost;
|
|
|
|
SELECT user, host FROM mysql.user WHERE user like 'dummy%';
|
|
SELECT COUNT(*) FROM mysql.user WHERE user like 'dummy%';
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo **** On Slave ****
|
|
SELECT user,host FROM mysql.user WHERE user like 'dummy%';
|
|
SELECT COUNT(*) FROM mysql.user WHERE user like 'dummy%';
|
|
|
|
|
|
######################################################
|
|
|
|
#
|
|
# BUG#54866: Partially failed REVOKE not binlogged, causes inconsistency or replication abort
|
|
#
|
|
|
|
--echo ##########
|
|
--echo ########## setup (PART I)
|
|
--echo ##########
|
|
-- source include/rpl_reset.inc
|
|
-- connection master
|
|
|
|
--let $dbname= b54866
|
|
--let $dbuser= b54866_user
|
|
|
|
--eval CREATE DATABASE $dbname
|
|
--eval use $dbname
|
|
CREATE TABLE t1 ( c1 INT, c2 INT, c3 INT );
|
|
|
|
--echo ##########
|
|
--echo ########## GRANT ALL
|
|
--echo ##########
|
|
|
|
## create user, grant and revoke (the last command fails partially)
|
|
--eval CREATE USER '$dbuser'@'localhost'
|
|
--eval GRANT ALL ON $dbname.* TO '$dbuser'@'localhost'
|
|
--error ER_NONEXISTING_GRANT
|
|
--eval REVOKE ALL ON $dbname.* FROM '$dbuser'@'localhost', 'b54866_fake_user'
|
|
|
|
--echo ****** Checking grants on the master for user: $dbuser ******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--connection master
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
## assert that grants are the same on master and on slave for $dbuser
|
|
--connection master
|
|
--echo ****** Checking grants on the master for user: $dbuser ******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo ****** Checking grants on the slave for user: $dbuser *******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--echo ##########
|
|
--echo ########## TABLE GRANTS
|
|
--echo ##########
|
|
|
|
--connection master
|
|
--eval GRANT ALTER,CREATE,DROP ON TABLE $dbname.t1 TO '$dbuser'@'localhost'
|
|
--echo ****** Checking grants on the master for user: $dbuser ******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo ****** Checking grants on the slave for user: $dbuser *******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
## revoke should now fail and log a incident event
|
|
--connection master
|
|
--error ER_NONEXISTING_TABLE_GRANT
|
|
--eval REVOKE ALTER ON TABLE $dbname.t1 FROM '$dbuser'@'localhost', 'b54866_fake_user'
|
|
|
|
--connection master
|
|
--echo ****** Checking grants on the master for user: $dbuser ******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--connection master
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo ****** Checking grants on the slave for user: $dbuser *******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--echo ##########
|
|
--echo ########## setup (PART II)
|
|
--echo ##########
|
|
|
|
-- connection master
|
|
DELIMITER |;
|
|
CREATE PROCEDURE b54866_p() BEGIN SELECT 1; END|
|
|
CREATE FUNCTION b54866_f() RETURNS INT BEGIN RETURN 1; END|
|
|
DELIMITER ;|
|
|
|
|
--eval GRANT EXECUTE ON PROCEDURE $dbname.b54866_p TO '$dbuser'@'localhost'
|
|
--eval GRANT EXECUTE ON FUNCTION $dbname.b54866_f TO '$dbuser'@'localhost'
|
|
|
|
--echo ****** Checking grants on the master for user: $dbuser ******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo ****** Checking grants on the slave for user: $dbuser *******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--echo ##########
|
|
--echo ########## PROCEDURE
|
|
--echo ##########
|
|
|
|
-- connection master
|
|
|
|
-- echo #### PROCEDURE ASSERTION
|
|
--error ER_NONEXISTING_PROC_GRANT
|
|
--eval REVOKE EXECUTE ON PROCEDURE $dbname.b54866_p FROM '$dbuser'@'localhost', 'b54866_fake_user'
|
|
|
|
--echo ****** Checking grants on the master for user: $dbuser ******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--connection master
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo ****** Checking grants on the slave for user: $dbuser *******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--echo ##########
|
|
--echo ########## FUNCTION
|
|
--echo ##########
|
|
-- connection master
|
|
|
|
--error ER_NONEXISTING_PROC_GRANT
|
|
--eval REVOKE EXECUTE ON FUNCTION $dbname.b54866_f FROM '$dbuser'@'localhost', 'b54866_fake_user'
|
|
|
|
--connection master
|
|
--echo ****** Checking grants on the master for user: $dbuser ******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--connection master
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo ****** Checking grants on the slave for user: $dbuser *******
|
|
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
|
|
--echo *************************************************************
|
|
|
|
--echo ##########
|
|
--echo ########## empty revokes should not be binlogged
|
|
--echo ##########
|
|
|
|
-- connection master
|
|
|
|
## assert that failing revoke on non existing user will not
|
|
## be binlogged
|
|
-- error ER_NONEXISTING_PROC_GRANT
|
|
REVOKE EXECUTE ON PROCEDURE b54866_p FROM 'fake_user'@'localhost';
|
|
-- error ER_NONEXISTING_PROC_GRANT
|
|
REVOKE EXECUTE ON FUNCTION b54866_f FROM 'fake_user'@'localhost';
|
|
-- error ER_REVOKE_GRANTS
|
|
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'fake_user'@'localhost';
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo ##########
|
|
--echo ########## Cleanup
|
|
--echo ##########
|
|
--connection master
|
|
DROP PROCEDURE b54866_p;
|
|
DROP FUNCTION b54866_f;
|
|
--eval DROP USER '$dbuser'@'localhost'
|
|
--eval DROP DATABASE $dbname
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
#
|
|
# BUG#11827392: 60082: EVEN THOUGH IT FAILS, 'CREATE USER' STATEMENT SI STILL BINLOGGED.
|
|
#
|
|
|
|
#
|
|
# The test case is based on the one included in the
|
|
# original report. It works as follows:
|
|
#
|
|
# 1. We issue a failing statement on the master
|
|
# 2. Then we synchronize the slave
|
|
# - this asserts that there is no side-effect
|
|
# on the replication stream
|
|
# 3. We then dump the contents of the binlog and
|
|
# search for the CREATE USER entry. There should
|
|
# not be any, so we print the # of entries found.
|
|
#
|
|
|
|
--connection master
|
|
--source include/rpl_reset.inc
|
|
--connection master
|
|
|
|
--error ER_PLUGIN_IS_NOT_LOADED
|
|
CREATE USER foo IDENTIFIED WITH 'my_plugin';
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--connection master
|
|
|
|
--disable_query_log
|
|
|
|
--let $MYSQLD_DATADIR= `select @@datadir`
|
|
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
|
|
--let $prefix=`SELECT UUID()`
|
|
--let $out_file=$MYSQLTEST_VARDIR/tmp/$prefix.out
|
|
--exec $MYSQL_BINLOG --verbose $MYSQLD_DATADIR/$binlog_file > $out_file
|
|
|
|
--let OUTF=$out_file
|
|
|
|
--enable_query_log
|
|
|
|
--echo # Search for occurrences of CREATE USER in the output from mysqlbinlog
|
|
|
|
perl;
|
|
use strict;
|
|
my $outf= $ENV{'OUTF'} or die "OUTF not set";
|
|
open(FILE, "$outf") or die("Unable to open $outf: $!\n");
|
|
my $count = () = grep(/create user 'foo'/gi,<FILE>);
|
|
print "- Occurrences: $count\n";
|
|
close(FILE);
|
|
EOF
|
|
|
|
--remove_file $out_file
|
|
|
|
--source include/rpl_end.inc
|