Debian Codegraph @

mysql-test/suite/binlog_nogtid/t/binlog_replication_applier_priv_checks.test

raw
1 # ==== Purpose ====
2 #
3 # This script tests error handling regarding `BINLOG` statement execution while
4 # checking for proper privileges regarding `REPLICATION_APPLIER` privilege.
5 #
6 # ==== Requirements ====
7 #
8 # R1. `BINLOG` statement must fail if the user in session doesn't have `SUPER`,
9 # `BINLOG_ADMIN` or `REPLICATION_APPLIER` privilege.
10 #
11 # R2. Necessary privileges for applying the underlying `BINLOG` statement are
12 # enforced if the user in session has `REPLICATION_APPLIER`.
13 #
14 # ==== Implementation ====
15 #
16 # TC1. Apply a `Format_description_event` without any privileges
17 # --------------------------------------------------------------
18 # 1) Start a session with an unprivileged user.
19 # 2) Execute a `BINLOG` statement holding a `Format_description_event` and
20 # expect an error.
21 #
22 # TC2. Apply a `Write_row_event` without any privileges needed by the event
23 # -------------------------------------------------------------------------
24 # 1) Start a session with a user with only `REPLICATION_APPLIER` privilege.
25 # 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect an
26 # error.
27 #
28 # TC3. Apply a `Write_row_event` with `INSERT` privilege
29 # ------------------------------------------------------
30 # 1) Start a session with a user with only `REPLICATION_APPLIER` and `INSERT`
31 # privileges.
32 # 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
33 # success.
34 # 3) Confirm the INSERT was executed
35 #
36 # TC4. Apply a `Write_row_event` with `BINLOG_ADMIN` privilege
37 # ------------------------------------------------------------
38 # 1) Start a session with a user with only `BINLOG_ADMIN` privilege.
39 # 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
40 # success.
41 # 3) Confirm the INSERT was executed
42 #
43 # TC5. Apply a `Write_row_event` with `SUPER` privilege
44 # -----------------------------------------------------
45 # 1) Start a session with a user with only `SUPER` privilege.
46 # 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
47 # success.
48 # 3) Confirm the INSERT was executed
49 #
50 # ==== References ====
51 #
52 # WL#12966 Replication with privilege checks
53 #
54
55 --source include/have_binlog_format_row.inc
56
57 CREATE USER 'u1'@'localhost' IDENTIFIED BY 'pass';
58 GRANT CREATE, SELECT ON *.* TO 'u1'@'localhost';
59
60 CREATE DATABASE rpl_priv;
61 USE rpl_priv;
62 CREATE TABLE t1 (a INT);
63
64 CALL mtr.add_suppression("INSERT command denied to user 'u1'@'localhost' for table 't1'");
65
66 --echo #
67 --echo # TC1. Apply a `Format_description_event` without any privileges
68 --echo # --------------------------------------------------------------
69 --echo #
70
71 # 1) Start a session with an unprivileged user.
72 --connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
73 --connection other_conn
74
75 # 2) Execute a `BINLOG` statement holding a `Format_description_event` and
76 # expect an error.
77 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
78 BINLOG '
79 jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
80 AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
81 CgGQNwbk';
82 --disconnect other_conn
83
84 --connection default
85 GRANT REPLICATION_APPLIER ON *.* TO 'u1'@'localhost';
86
87 --echo #
88 --echo # TC2. Apply a `Write_row_event` without any privileges needed by the
89 --echo # event
90 --echo # -------------------------------------------------------------------
91 --echo #
92
93 # 1) Start a session with a user with only `REPLICATION_APPLIER` privilege.
94 --connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
95 --connection other_conn
96 BINLOG '
97 jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
98 AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
99 CgGQNwbk';
100
101 # 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect an
102 # error.
103 --error ER_UNKNOWN_ERROR
104 BINLOG '
105 g2YSXRMBAAAANAAAAG0BAAAAAJoAAAAAAAEACHJwbF9wcml2AAJ0MQABAwABAQEAdXCMmg==
106 g2YSXR4BAAAAMgAAAJ8BAAAAAJoAAAAAAAEAAgAB/wAKAAAAAAsAAAAADAAAAGmCVP4=
107 ';
108 --disconnect other_conn
109
110 --connection default
111 GRANT INSERT ON *.* TO 'u1'@'localhost';
112
113 --echo #
114 --echo # TC3. Apply a `Write_row_event` with `INSERT` privilege
115 --echo # ------------------------------------------------------
116 --echo #
117
118 # 1) Start a session with a user with only `REPLICATION_APPLIER` and `INSERT`
119 # privileges.
120 --connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
121 --connection other_conn
122 BINLOG '
123 jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
124 AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
125 CgGQNwbk';
126
127 # 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
128 # success.
129 BINLOG '
130 g2YSXRMBAAAANAAAAG0BAAAAAJoAAAAAAAEACHJwbF9wcml2AAJ0MQABAwABAQEAdXCMmg==
131 g2YSXR4BAAAAMgAAAJ8BAAAAAJoAAAAAAAEAAgAB/wAKAAAAAAsAAAAADAAAAGmCVP4=
132 ';
133
134 # 3) Confirm the INSERT was executed
135 --let $assert_text= INSERT succeeded
136 --let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "3"
137 --source include/assert.inc
138 --disconnect other_conn
139
140 --connection default
141 DELETE FROM t1;
142 REVOKE INSERT, REPLICATION_APPLIER ON *.* FROM 'u1'@'localhost';
143 GRANT BINLOG_ADMIN ON *.* TO 'u1'@'localhost';
144
145 --echo #
146 --echo # TC4. Apply a `Write_row_event` with `BINLOG_ADMIN` privilege
147 --echo # ------------------------------------------------------------
148 --echo #
149
150 # 1) Start a session with a user with only `BINLOG_ADMIN` privilege.
151 --connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
152 --connection other_conn
153 BINLOG '
154 jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
155 AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
156 CgGQNwbk';
157
158 # 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
159 # success.
160 BINLOG '
161 g2YSXRMBAAAANAAAAG0BAAAAAJoAAAAAAAEACHJwbF9wcml2AAJ0MQABAwABAQEAdXCMmg==
162 g2YSXR4BAAAAMgAAAJ8BAAAAAJoAAAAAAAEAAgAB/wAKAAAAAAsAAAAADAAAAGmCVP4=
163 ';
164
165 # 3) Confirm the INSERT was executed
166 --let $assert_text= INSERT succeeded
167 --let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "3"
168 --source include/assert.inc
169 --disconnect other_conn
170
171 --connection default
172 DELETE FROM t1;
173 REVOKE BINLOG_ADMIN ON *.* FROM 'u1'@'localhost';
174 GRANT SUPER ON *.* TO 'u1'@'localhost';
175
176 --echo #
177 --echo # TC5. Apply a `Write_row_event` with `SUPER` privilege
178 --echo # -----------------------------------------------------
179 --echo #
180
181 # 1) Start a session with a user with only `SUPER` privilege.
182 --connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
183 --connection other_conn
184 BINLOG '
185 jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
186 AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
187 CgGQNwbk';
188
189 # 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
190 # success.
191 BINLOG '
192 g2YSXRMBAAAANAAAAG0BAAAAAJoAAAAAAAEACHJwbF9wcml2AAJ0MQABAwABAQEAdXCMmg==
193 g2YSXR4BAAAAMgAAAJ8BAAAAAJoAAAAAAAEAAgAB/wAKAAAAAAsAAAAADAAAAGmCVP4=
194 ';
195
196 # 3) Confirm the INSERT was executed
197 --let $assert_text= INSERT succeeded
198 --let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "3"
199 --source include/assert.inc
200 --disconnect other_conn
201
202 --connection default
203 DROP DATABASE rpl_priv;
204 DROP USER 'u1'@'localhost';