206 lines
8.0 KiB
Plaintext
206 lines
8.0 KiB
Plaintext
###############################################################################
|
|
# Validate if granting privileges with 'Roles' works with group_replication
|
|
|
|
# 0.Test uses 2 servers: server1, server2
|
|
# 1.Verify that ROLES are replicated successfully.
|
|
# 1.1 Create role, user on server1.
|
|
# 1.2 Grant privileges to roles and grant roles to users.
|
|
# 2.Verify execution of GR commands is successful by the user when
|
|
# different privileges are granted by roles.
|
|
# 2.1 Error as user does not have privilege to execute command.
|
|
# 2.2 Assign SYSTEM_VARIABLES_ADMIN privilege to user by enabling the role
|
|
# and test that command is executed successfully.
|
|
# 3.Verify that ROLES on replication users used in 'CHANGE MASTER TO' work
|
|
# as expected for GR.
|
|
# 3.1 Error in START GR as master_user used in CHM does not have privilege.
|
|
# 3.2 Assign REPLICATION_SLAVE privilege to user by enabling the role and
|
|
# test that start group_replication succeeds.
|
|
# 4.Verify that Activating the role by 'ALTER USER' is successful on
|
|
# all GR members.
|
|
# 5.Clean Up.
|
|
###############################################################################
|
|
--source include/have_group_replication_plugin.inc
|
|
--let $rpl_server_count= 2
|
|
--let $rpl_skip_group_replication_start= 1
|
|
--source include/group_replication.inc
|
|
|
|
# Test 1 : ROLES are replicated successfully
|
|
|
|
# Create role, user on server1
|
|
CREATE ROLE 'gr_user','gr_recovery_user';
|
|
# Grant privileges to roles and grant roles to users.
|
|
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'gr_user';
|
|
GRANT REPLICATION SLAVE ON *.* TO 'gr_recovery_user';
|
|
CREATE USER 'alpha' IDENTIFIED BY 'alpha';
|
|
GRANT gr_user TO alpha;
|
|
|
|
--echo # Check that 'gr_user' role is assigned to 'alpha' user
|
|
--let $wait_condition=SELECT FROM_USER='gr_user' FROM mysql.role_edges WHERE TO_USER='alpha'
|
|
--source include/wait_condition_or_abort.inc
|
|
|
|
# Test 2 : Verify execution of GR commands is successful by the user when
|
|
# different privileges are granted by roles.
|
|
|
|
# 'alpha' user is not granted any privileges, it will be denied to set GR related
|
|
# variables or START GROUP_REPLICATION
|
|
--echo [connection alpha]
|
|
connect(alpha_con, localhost, alpha, alpha);
|
|
--disable_query_log
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
--eval SET GLOBAL group_replication_group_name= "$group_replication_group_name"
|
|
--enable_query_log
|
|
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
SET GLOBAL group_replication_bootstrap_group= 1;
|
|
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
START GROUP_REPLICATION;
|
|
|
|
--disconnect alpha_con
|
|
--let $rpl_connection_name= server1
|
|
--source include/rpl_connection.inc
|
|
|
|
# Enable 'gr_user' ROLE for USER 'alpha'
|
|
ALTER USER alpha DEFAULT ROLE 'gr_user';
|
|
|
|
--echo # Check that 'gr_user' role is enabled for 'alpha' user
|
|
--let $wait_condition= SELECT DEFAULT_ROLE_USER='gr_user' FROM mysql.default_roles WHERE USER='alpha'
|
|
--source include/wait_condition_or_abort.inc
|
|
|
|
--let $wait_condition= SELECT FROM_USER='gr_user' FROM mysql.role_edges WHERE TO_USER='alpha';
|
|
--source include/wait_condition_or_abort.inc
|
|
|
|
--echo [connection alpha]
|
|
connect(alpha_con, localhost, alpha, alpha,*NO-ONE*);
|
|
SELECT CURRENT_ROLE();
|
|
|
|
# Execution of commands should be successful.
|
|
--replace_result $group_replication_group_name GROUP_REPLICATION_GROUP_NAME
|
|
--eval SET GLOBAL group_replication_group_name= "$group_replication_group_name"
|
|
SET GLOBAL group_replication_bootstrap_group= 1;
|
|
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
START GROUP_REPLICATION;
|
|
|
|
--disconnect alpha_con
|
|
|
|
--let $rpl_connection_name= server1
|
|
--source include/rpl_connection.inc
|
|
|
|
GRANT GROUP_REPLICATION_ADMIN ON *.* TO 'gr_user';
|
|
|
|
--echo [connection alpha]
|
|
connect(alpha_con, localhost, alpha, alpha,*NO-ONE*);
|
|
START GROUP_REPLICATION;
|
|
SET GLOBAL group_replication_bootstrap_group= 0;
|
|
|
|
--disconnect alpha_con
|
|
|
|
--let $rpl_connection_name= server1
|
|
--source include/rpl_connection.inc
|
|
--echo # Wait until server1 is ONLINE
|
|
--let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_group_members WHERE member_state='ONLINE'
|
|
--source include/wait_condition_or_abort.inc
|
|
|
|
# Test 3 : Verify that ROLES on replication users used in 'CHANGE MASTER TO' work
|
|
# as expected for GR.
|
|
--let $rpl_connection_name= server2
|
|
--source include/rpl_connection.inc
|
|
|
|
SET @group_replication_recovery_retry_count_save= @@GLOBAL.group_replication_recovery_retry_count;
|
|
SET @group_replication_recovery_reconnect_interval_save= @@GLOBAL.group_replication_recovery_reconnect_interval;
|
|
|
|
set sql_log_bin=0;
|
|
call mtr.add_suppression(".*Slave I/O for channel 'group_replication_recovery': Master command COM_REGISTER_SLAVE failed.*");
|
|
call mtr.add_suppression(".*Slave I/O thread couldn't register on master");
|
|
call mtr.add_suppression("Maximum number of retries when*");
|
|
call mtr.add_suppression("Fatal error during the incremental recovery process of Group Replication. The server will leave the group.");
|
|
call mtr.add_suppression("Skipping leave operation: concurrent attempt to leave the group is on-going.");
|
|
call mtr.add_suppression("There was an error when connecting to the donor*");
|
|
call mtr.add_suppression("For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.");
|
|
call mtr.add_suppression("The server was automatically set into read only mode after an error was detected.");
|
|
set sql_log_bin=1;
|
|
|
|
# Execute CHM where master_user='alpha'
|
|
--replace_result $group_replication_group_name GROUP_REPLICATION_GROUP_NAME
|
|
--eval SET GLOBAL group_replication_group_name= "$group_replication_group_name"
|
|
|
|
SET GLOBAL group_replication_recovery_retry_count= 1;
|
|
--echo # Set the reconnect interval to one. To prevent timeout on recovery.
|
|
SET GLOBAL group_replication_recovery_reconnect_interval= 1;
|
|
|
|
CHANGE MASTER TO MASTER_USER='alpha', MASTER_PASSWORD='alpha' FOR CHANNEL 'group_replication_recovery';
|
|
|
|
# START GR on server2 will fail
|
|
--let $group_replication_start_member_state= ERROR
|
|
--source include/start_group_replication.inc
|
|
|
|
# Error ER_SLAVE_MASTER_COM_FAILURE : as USER 'alpha' doesn't have
|
|
# REPLICATION_SLAVE_ADMIN privilege
|
|
--let $grep_pattern= Master command COM_REGISTER_SLAVE failed
|
|
--let $grep_file= $MYSQLTEST_VARDIR/log/mysqld.2.err
|
|
--let $grep_output= boolean
|
|
--source include/grep_pattern.inc
|
|
|
|
--let $grep_pattern= Slave I/O thread couldn't register on master
|
|
--let $grep_file= $MYSQLTEST_VARDIR/log/mysqld.2.err
|
|
--let $grep_output= boolean
|
|
--source include/grep_pattern.inc
|
|
|
|
--source include/stop_group_replication.inc
|
|
|
|
# Test 4 : Verify that Activating the role by 'ALTER USER' is successful on
|
|
# all GR members.
|
|
--let $rpl_connection_name= server1
|
|
--source include/rpl_connection.inc
|
|
# Enable 'REPLICATION_SLAVE_ADMIN' for 'alpha'
|
|
GRANT gr_recovery_user TO alpha;
|
|
ALTER USER alpha DEFAULT ROLE ALL;
|
|
|
|
--echo # Check that all roles assigned to 'alpha' user are enabled
|
|
SELECT DEFAULT_ROLE_USER FROM mysql.default_roles WHERE USER='alpha';
|
|
SELECT FROM_USER FROM mysql.role_edges WHERE TO_USER='alpha';
|
|
|
|
--echo [connection alpha]
|
|
connect(alpha_con, localhost, alpha, alpha,*NO-ONE*);
|
|
SELECT CURRENT_ROLE();
|
|
SHOW GRANTS;
|
|
|
|
# Creation of new database fails
|
|
--error ER_DBACCESS_DENIED_ERROR
|
|
CREATE DATABASE newtest;
|
|
|
|
--disconnect alpha_con
|
|
|
|
--let $rpl_connection_name= server2
|
|
--source include/rpl_connection.inc
|
|
--let $group_replication_start_member_state= ONLINE
|
|
--source include/start_group_replication.inc
|
|
|
|
GRANT ALL ON newtest.* TO gr_recovery_user;
|
|
|
|
--echo [connection alpha]
|
|
connect(alpha_con, localhost, alpha, alpha,*NO-ONE*,$SLAVE_MYPORT,$SLAVE_MYSOCK);
|
|
|
|
# Replicate data
|
|
CREATE DATABASE newtest;
|
|
use newtest;
|
|
CREATE TABLE t1(a INT PRIMARY KEY);
|
|
INSERT INTO t1 VALUES (1),(2);
|
|
UPDATE t1 SET a=4 WHERE a=1;
|
|
DELETE FROM t1;
|
|
|
|
# Cleanup
|
|
--let $rpl_connection_name= server2
|
|
--source include/rpl_connection.inc
|
|
|
|
SET GLOBAL group_replication_recovery_retry_count= @group_replication_recovery_retry_count_save;
|
|
SET GLOBAL group_replication_recovery_reconnect_interval= @group_replication_recovery_reconnect_interval_save;
|
|
|
|
DROP TABLE newtest.t1;
|
|
DROP DATABASE newtest;
|
|
DROP ROLE gr_user, gr_recovery_user;
|
|
DROP USER alpha;
|
|
|
|
--source include/group_replication_end.inc
|