# ==== Purpose ====
#
# To verify that `PRIVILEGE_CHECKS_USER` option is settable for GR channels and
# that checks are enforced while applying the events from the replication
# stream.
#
# ==== Implementation ====
#
# 1) Create a new database and a table on the group.
# 2) Stop the applier for `server1`.
# 3) Create a new user, grant it `REPLICATION_APPLIER` privileges and set it as
#    the `PRIVILEGE_CHECKS_USER` user.
# 4) Execute an `INSERT` statement on `server2`.
# 5) Start `server1` and expect an error as the user doesn't have INSERT
#    privilege.
# 4) Stop slave and grant INSERT privilege.
# 5) Start server1
# 6) Ensure that table was properly replicated
#
# ==== References ====
#
# WL#12966: Replication with Restricted Privileges
#

--source include/have_group_replication_plugin.inc
--source include/group_replication.inc

--echo
--echo # 1) Create a new database and a table on the group.

CREATE DATABASE db1;
CREATE TABLE db1.t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;

SET @@sql_log_bin = 0;
CALL mtr.add_suppression(".*Slave SQL for channel 'group_replication_applier': INSERT command denied to user 'u1'@'localhost' for table 't1'.*");
CALL mtr.add_suppression(".*Worker .* failed executing transaction .* INSERT command denied to user 'u1'@'localhost' for table 't1'.*");
CALL mtr.add_suppression(".*Plugin group_replication reported: 'The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group.'.*");
CALL mtr.add_suppression(".*Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'.*");
CALL mtr.add_suppression(".*Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'.*");
CALL mtr.add_suppression(".*Plugin group_replication reported: 'Skipping leave operation: concurrent attempt to leave the group is on-going.'.*");
CALL mtr.add_suppression(".*The slave coordinator and worker threads are stopped.*");
SET @@sql_log_bin = 1;

--source include/rpl_sync.inc

--echo
--echo # 2) Stop the applier for `server1`.
--source include/gr_stop_applier_sql_thread.inc

--echo
--echo # 3) Create a new user, grant it `REPLICATION_APPLIER` privileges and set
--echo #    it as the `PRIVILEGE_CHECKS_USER` user.
SET @@sql_log_bin = 0;
--let $applier_user = 'u1'@'localhost'
--eval CREATE USER $applier_user
--eval GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN ON *.* TO $applier_user
SET @@sql_log_bin = 1;
--eval CHANGE MASTER TO PRIVILEGE_CHECKS_USER = $applier_user FOR CHANNEL "group_replication_applier"

--let $count = `SELECT COUNT(*) FROM performance_schema.replication_applier_configuration WHERE Privilege_Checks_User = "$applier_user"`
--let $assert_text = Privilege_checks_user column in performance_schema.replication_applier_configuration is set to $applier_user
--let $assert_cond = 1 = $count
--source include/assert.inc

--echo
--echo # 4) Execute an `INSERT` statement on `server2`.
--let $rpl_connection_name = server2
--source include/rpl_connection.inc
INSERT INTO db1.t1 VALUES (1), (2);

--echo
--echo # 5) Start `server1` and expect an error as the user doesn't have INSERT
--echo #    privilege.
--let $rpl_connection_name = server1
--source include/rpl_connection.inc
--let $explicit_default_wait_timeout = 12000
--source include/gr_start_applier_sql_thread.inc
--let $group_replication_member_state = ERROR
--source include/gr_wait_for_member_state.inc

--echo
--echo # 4) Stop slave and grant INSERT privilege.
--source include/stop_group_replication.inc
SET @@sql_log_bin = 0;
--eval GRANT INSERT ON *.* TO $applier_user
SET @@sql_log_bin = 1;

--echo
--echo # 5) Start server1
--source include/start_group_replication.inc

--echo
--echo # 6) Ensure that table was properly replicated
--source include/rpl_sync.inc
--let $count = `SELECT COUNT(*) FROM db1.t1`
--let $assert_text = Table properly replicated in server1
--let $assert_cond = 2 = $count
--source include/assert.inc

--echo
--echo # Cleanup
DROP DATABASE db1;
--source include/rpl_sync.inc

--let $rpl_connection_name = server1
--source include/rpl_connection.inc
SET @@sql_log_bin = 0;
--eval DROP USER $applier_user
SET @@sql_log_bin = 1;
--source include/group_replication_end.inc