Debian Codegraph @

mysql-test/suite/group_replication/t/gr_applier_privileged_user.test

raw
1 # ==== Purpose ====
2 #
3 # To verify that `PRIVILEGE_CHECKS_USER` option is settable for GR channels and
4 # that checks are enforced while applying the events from the replication
5 # stream.
6 #
7 # ==== Implementation ====
8 #
9 # 1) Create a new database and a table on the group.
10 # 2) Stop GR on `server1`.
11 # 3) Verify that `REQUIRE_ROW_FORMAT` is set for the GR channels.
12 # 4) Create a new user, grant it `REPLICATION_APPLIER` privileges and set it as
13 # the `PRIVILEGE_CHECKS_USER` user.
14 # 5) Start `server1`.
15 # 6) Execute an `INSERT` statement on `server2`.
16 # 7) Expect an error as the user doesn't have INSERT privilege.
17 # 8) Stop slave and grant INSERT privilege.
18 # 9) Start server1
19 # 10) Ensure that table was properly replicated
20 #
21 # ==== References ====
22 #
23 # WL#12966: Replication with Restricted Privileges
24 # WL#12968: Configure replication applier to require row-based replication
25 #
26
27 --source include/have_group_replication_plugin.inc
28 --source include/group_replication.inc
29
30 --echo
31 --echo # 1) Create a new database and a table on the group.
32
33 CREATE DATABASE db1;
34 CREATE TABLE db1.t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
35
36 SET @@sql_log_bin = 0;
37 CALL mtr.add_suppression(".*Replica SQL for channel 'group_replication_applier': INSERT command denied to user 'u1'@'localhost' for table 't1'.*");
38 CALL mtr.add_suppression(".*Worker .* failed executing transaction .* INSERT command denied to user 'u1'@'localhost' for table 't1'.*");
39 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.'.*");
40 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.'.*");
41 CALL mtr.add_suppression(".*Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'.*");
42 CALL mtr.add_suppression(".*Plugin group_replication reported: 'Skipping leave operation: concurrent attempt to leave the group is on-going.'.*");
43 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
44 SET @@sql_log_bin = 1;
45
46 --source include/rpl_sync.inc
47
48 --echo
49 --echo # 2) Stop GR on `server1`.
50 --source include/stop_group_replication.inc
51
52 --echo
53 --echo # 3) Verify that `REQUIRE_ROW_FORMAT` is set for the GR channels.
54 --let $count = `SELECT COUNT(*) FROM performance_schema.replication_applier_configuration WHERE Require_row_format = "YES" AND Channel_name = "group_replication_applier"`
55 --let $assert_text = Require_row_format column in performance_schema.replication_applier_configuration is set to YES for GR applier channel
56 --let $assert_cond = 1 = $count
57 --source include/assert.inc
58
59 --let $count = `SELECT COUNT(*) FROM performance_schema.replication_applier_configuration WHERE Require_row_format = "YES" AND Channel_name = "group_replication_recovery"`
60 --let $assert_text = Require_row_format column in performance_schema.replication_applier_configuration is set to YES for GR recovery channel
61 --let $assert_cond = 1 = $count
62 --source include/assert.inc
63
64 --echo
65 --echo # 4) Create a new user, grant it `REPLICATION_APPLIER` privileges and set
66 --echo # it as the `PRIVILEGE_CHECKS_USER` user.
67 SET @@sql_log_bin = 0;
68 --let $applier_user = 'u1'@'localhost'
69 --eval CREATE USER $applier_user
70 --eval GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN ON *.* TO $applier_user
71 SET @@sql_log_bin = 1;
72 --eval CHANGE REPLICATION SOURCE TO PRIVILEGE_CHECKS_USER = $applier_user FOR CHANNEL "group_replication_applier"
73
74 --let $count = `SELECT COUNT(*) FROM performance_schema.replication_applier_configuration WHERE Privilege_Checks_User = "$applier_user"`
75 --let $assert_text = Privilege_checks_user column in performance_schema.replication_applier_configuration is set to $applier_user
76 --let $assert_cond = 1 = $count
77 --source include/assert.inc
78
79 --echo
80 --echo # 5) Start `server1`.
81 --let $rpl_connection_name = server1
82 --source include/rpl_connection.inc
83 --source include/start_group_replication.inc
84
85 --let $count = `SELECT COUNT(*) FROM performance_schema.replication_applier_configuration WHERE Privilege_Checks_User = "$applier_user"`
86 --let $assert_text = Privilege_checks_user column in performance_schema.replication_applier_configuration is set to $applier_user
87 --let $assert_cond = 1 = $count
88 --source include/assert.inc
89
90 --echo
91 --echo # 6) Execute an `INSERT` statement on `server2`.
92 --let $rpl_connection_name = server2
93 --source include/rpl_connection.inc
94 INSERT INTO db1.t1 VALUES (1), (2);
95
96 --echo
97 --echo # 7) Expect an error as the user doesn't have INSERT privilege.
98 --let $rpl_connection_name = server1
99 --source include/rpl_connection.inc
100 --let $explicit_default_wait_timeout = 12000
101 --let $group_replication_member_state = ERROR
102 --source include/gr_wait_for_member_state.inc
103
104 --echo
105 --echo # 8) Stop slave and grant INSERT privilege.
106 --source include/stop_group_replication.inc
107 SET @@sql_log_bin = 0;
108 --eval GRANT INSERT ON *.* TO $applier_user
109 SET @@sql_log_bin = 1;
110
111 --echo
112 --echo # 9) Start server1
113 --source include/start_group_replication.inc
114
115 --echo
116 --echo # 10) Ensure that table was properly replicated
117 --source include/rpl_sync.inc
118 --let $count = `SELECT COUNT(*) FROM db1.t1`
119 --let $assert_text = Table properly replicated in server1
120 --let $assert_cond = 2 = $count
121 --source include/assert.inc
122
123 --echo
124 --echo # Cleanup
125 DROP DATABASE db1;
126 --source include/rpl_sync.inc
127
128 --let $rpl_connection_name = server1
129 --source include/rpl_connection.inc
130 SET @@sql_log_bin = 0;
131 --eval DROP USER $applier_user
132 SET @@sql_log_bin = 1;
133 --source include/group_replication_end.inc