1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
################# suite/rpl/t/rpl_roles.test #########################
# #
# Testing of roles feature in replication setup #
# #
# #
# Creation: #
# 2016-08-26 prabprad Added this test as part of WL#988 Roles #
# #
# WL#10886 : Add/Extend mtr tests for Replication/GR for roles #
# This test was extended to verify starting of replication #
# by using roles to grant privileges to user. #
######################################################################
--source include/set_privilege_checks_user_as_system_user.inc
--let $rpl_privilege_checks_user_grant_option = 1
--source include/master-slave.inc
# Create roles, user and role hierarchy on master
CREATE ROLE r1, r2, r3;
CREATE USER u1@localhost;
GRANT r1 TO r2;
GRANT r2 TO u1@localhost;
GRANT r3 TO u1@localhost;
ALTER USER u1@localhost DEFAULT ROLE ALL;
--source include/sync_slave_sql_with_master.inc
# On slave, check if all objects created on master are
# replicated to slave or not. Also do basic operations on
# master and ensure that they are replicated as expected
# All the operations done in master will affect the
# mysql.roles_edges and mysql.default_roles table only.
# Hence only this table is checked on slave.
SELECT * FROM mysql.default_roles;
SELECT * FROM mysql.role_edges;
--source include/rpl_connection_master.inc
REVOKE r3 FROM u1@localhost;
GRANT r1 TO u1@localhost;
ALTER USER u1@localhost DEFAULT ROLE r1;
--source include/sync_slave_sql_with_master.inc
SELECT * FROM mysql.default_roles;
SELECT * FROM mysql.role_edges;
--source include/rpl_connection_master.inc
ALTER USER u1@localhost DEFAULT ROLE NONE;
--source include/sync_slave_sql_with_master.inc
SELECT * FROM mysql.default_roles;
SELECT * FROM mysql.role_edges;
--source include/rpl_connection_master.inc
REVOKE r1 FROM r2;
--source include/sync_slave_sql_with_master.inc
SELECT * FROM mysql.default_roles;
SELECT * FROM mysql.role_edges;
# Start replication by using roles to grant priviliges to user
# STOP SLAVE
--source include/stop_slave.inc
set sql_log_bin=0;
call mtr.add_suppression(".*Replica I/O for channel '': Source command COM_REGISTER_REPLICA failed.*");
call mtr.add_suppression(".*Replica I/O thread couldn't register on source");
set sql_log_bin=1;
# Create role,user on master for replication
--source include/rpl_connection_master.inc
CREATE ROLE r4;
GRANT REPLICATION SLAVE ON *.* TO r4;
GRANT r4 to u1@localhost;
SELECT * FROM mysql.default_roles;
SELECT * FROM mysql.role_edges;
--source include/rpl_connection_slave.inc
CHANGE REPLICATION SOURCE TO SOURCE_USER='u1';
START SLAVE;
# Wait until IO_THREAD is inactive
--let $wait_condition=SELECT COUNT(*)=1 FROM performance_schema.replication_connection_status WHERE service_state='OFF'
--source include/wait_condition_or_abort.inc
# Error ER_REPLICA_SOURCE_COM_FAILURE as user 'u1' doesn't have REPLICATION SLAVE privilege
--let $slave_io_errno=convert_error(ER_REPLICA_SOURCE_COM_FAILURE)
--source include/wait_for_slave_io_error.inc
# Enable role 'r4' for user 'u1'
--source include/rpl_connection_master.inc
ALTER USER u1@localhost DEFAULT ROLE ALL;
SELECT * FROM mysql.default_roles;
SELECT * FROM mysql.role_edges;
--source include/rpl_connection_slave.inc
START SLAVE IO_THREAD;
--source include/wait_for_slave_io_to_start.inc
--echo
--echo # Verify if 'DEFAULT ROLE' clause is bin-logged.
--echo
--enable_connect_log
--source include/rpl_connection_master.inc
CREATE USER u2@localhost DEFAULT ROLE r1, r2;
CREATE USER u3@localhost DEFAULT ROLE r1;
--echo
SHOW GRANTS FOR u2@localhost;
--echo
SHOW GRANTS FOR u3@localhost;
--source include/sync_slave_sql_with_master.inc
# Check if roles are granted to the users on slave
SHOW GRANTS FOR u2@localhost;
SHOW GRANTS FOR u3@localhost;
--echo
SHOW CREATE USER u2@localhost;
SHOW CREATE USER u3@localhost;
--source include/rpl_connection_master.inc
--echo
ALTER USER u2@localhost DEFAULT ROLE NONE;
SHOW GRANTS FOR u3@localhost;
ALTER USER u2@localhost DEFAULT ROLE r1;
GRANT r2 TO u3@localhost;
ALTER USER u3@localhost DEFAULT ROLE ALL;
SHOW GRANTS FOR u3@localhost;
ALTER USER u3@localhost DEFAULT ROLE r2;
# Now, u2 and u3 should have only one role on master
SHOW GRANTS FOR u2@localhost;
SHOW GRANTS FOR u3@localhost;
--echo
--source include/sync_slave_sql_with_master.inc
# u2 and u3 should have only one role on slave as well
SHOW GRANTS FOR u2@localhost;
SHOW GRANTS FOR u3@localhost;
--echo
SHOW CREATE USER u2@localhost;
SHOW CREATE USER u3@localhost;
--disable_connect_log
--echo
--echo # Cleanup Statement
--source include/rpl_connection_master.inc
DROP ROLE r1, r2, r3,r4;
DROP USER u1@localhost, u2@localhost, u3@localhost;
--source include/sync_slave_sql_with_master.inc
SELECT * FROM mysql.default_roles;
SELECT * FROM mysql.role_edges;
--let $mask_grant_as_events=1
source include/show_binlog_events.inc;
--source include/stop_slave.inc
CHANGE REPLICATION SOURCE TO SOURCE_USER='root';
--source include/start_slave.inc
--source include/rpl_end.inc
|
