Debian Codegraph @

mysql-test/suite/rpl/t/rpl_roles.test

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