# Tests of grants and users

--source include/not_group_replication_plugin.inc
source include/not_gtid_enabled.inc;
source include/not_embedded.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 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'

call mtr.add_suppression("\\[ERROR\\] REVOKE/GRANT failed while granting/revoking privileges in databases");
--error ER_NONEXISTING_GRANT
--eval REVOKE ALL ON $dbname.* FROM '$dbuser'@'localhost', 'b54866_fake_user'

--connection slave
call mtr.add_suppression("Slave SQL.*The incident LOST_EVENTS occured on the master.*");
--let $slave_sql_errno= convert_error(ER_SLAVE_INCIDENT)
--source include/wait_for_slave_sql_error.inc
# Skip incident event caused by failed REVOKE
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
--source include/start_slave_sql.inc
--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

call mtr.add_suppression("\\[ERROR\\] REVOKE/GRANT failed while storing table level and column level grants in the privilege tables.");
--error ER_NONEXISTING_GRANT
--eval REVOKE ALTER ON TABLE $dbname.t1 FROM '$dbuser'@'localhost', 'b54866_fake_user'

## assert that ALTER grant is revoked on slave and slave does not stop
--connection master
--echo ****** Checking grants on the master for user: $dbuser ******
--eval SHOW GRANTS FOR '$dbuser'@'localhost'
--echo *************************************************************

--connection slave
--let $slave_sql_errno= convert_error(ER_SLAVE_INCIDENT)
--source include/wait_for_slave_sql_error.inc
# Skip incident event caused by failed REVOKE
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
--source include/start_slave_sql.inc
--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
call mtr.add_suppression("\\[ERROR\\] REVOKE/GRANT failed while storing routine level grants in the privilege tables");
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_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 slave
--let $slave_sql_errno= convert_error(ER_SLAVE_INCIDENT)
--source include/wait_for_slave_sql_error.inc
# Skip incident event caused by failed REVOKE
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
--source include/start_slave_sql.inc
--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_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 slave
--let $slave_sql_errno= convert_error(ER_SLAVE_INCIDENT)
--source include/wait_for_slave_sql_error.inc
# Skip incident event caused by failed REVOKE
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
--source include/start_slave_sql.inc
--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_GRANT
REVOKE EXECUTE ON PROCEDURE b54866_p FROM 'fake_user'@'localhost';
-- error ER_NONEXISTING_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