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
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
|
# ==== Purpose ====
#
# This script tests error handling regarding `BINLOG` statement execution while
# checking for proper privileges regarding `REPLICATION_APPLIER` privilege.
#
# ==== Requirements ====
#
# R1. `BINLOG` statement must fail if the user in session doesn't have `SUPER`,
# `BINLOG_ADMIN` or `REPLICATION_APPLIER` privilege.
#
# R2. Necessary privileges for applying the underlying `BINLOG` statement are
# enforced if the user in session has `REPLICATION_APPLIER`.
#
# ==== Implementation ====
#
# TC1. Apply a `Format_description_event` without any privileges
# --------------------------------------------------------------
# 1) Start a session with an unprivileged user.
# 2) Execute a `BINLOG` statement holding a `Format_description_event` and
# expect an error.
#
# TC2. Apply a `Write_row_event` without any privileges needed by the event
# -------------------------------------------------------------------------
# 1) Start a session with a user with only `REPLICATION_APPLIER` privilege.
# 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect an
# error.
#
# TC3. Apply a `Write_row_event` with `INSERT` privilege
# ------------------------------------------------------
# 1) Start a session with a user with only `REPLICATION_APPLIER` and `INSERT`
# privileges.
# 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
# success.
# 3) Confirm the INSERT was executed
#
# TC4. Apply a `Write_row_event` with `BINLOG_ADMIN` privilege
# ------------------------------------------------------------
# 1) Start a session with a user with only `BINLOG_ADMIN` privilege.
# 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
# success.
# 3) Confirm the INSERT was executed
#
# TC5. Apply a `Write_row_event` with `SUPER` privilege
# -----------------------------------------------------
# 1) Start a session with a user with only `SUPER` privilege.
# 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
# success.
# 3) Confirm the INSERT was executed
#
# ==== References ====
#
# WL#12966 Replication with privilege checks
#
--source include/have_binlog_format_row.inc
CREATE USER 'u1'@'localhost' IDENTIFIED BY 'pass';
GRANT CREATE, SELECT ON *.* TO 'u1'@'localhost';
CREATE DATABASE rpl_priv;
USE rpl_priv;
CREATE TABLE t1 (a INT);
CALL mtr.add_suppression("INSERT command denied to user 'u1'@'localhost' for table 't1'");
--echo #
--echo # TC1. Apply a `Format_description_event` without any privileges
--echo # --------------------------------------------------------------
--echo #
# 1) Start a session with an unprivileged user.
--connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
--connection other_conn
# 2) Execute a `BINLOG` statement holding a `Format_description_event` and
# expect an error.
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
BINLOG '
jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgGQNwbk';
--disconnect other_conn
--connection default
GRANT REPLICATION_APPLIER ON *.* TO 'u1'@'localhost';
--echo #
--echo # TC2. Apply a `Write_row_event` without any privileges needed by the
--echo # event
--echo # -------------------------------------------------------------------
--echo #
# 1) Start a session with a user with only `REPLICATION_APPLIER` privilege.
--connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
--connection other_conn
BINLOG '
jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgGQNwbk';
# 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect an
# error.
--error ER_UNKNOWN_ERROR
BINLOG '
g2YSXRMBAAAANAAAAG0BAAAAAJoAAAAAAAEACHJwbF9wcml2AAJ0MQABAwABAQEAdXCMmg==
g2YSXR4BAAAAMgAAAJ8BAAAAAJoAAAAAAAEAAgAB/wAKAAAAAAsAAAAADAAAAGmCVP4=
';
--disconnect other_conn
--connection default
GRANT INSERT ON *.* TO 'u1'@'localhost';
--echo #
--echo # TC3. Apply a `Write_row_event` with `INSERT` privilege
--echo # ------------------------------------------------------
--echo #
# 1) Start a session with a user with only `REPLICATION_APPLIER` and `INSERT`
# privileges.
--connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
--connection other_conn
BINLOG '
jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgGQNwbk';
# 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
# success.
BINLOG '
g2YSXRMBAAAANAAAAG0BAAAAAJoAAAAAAAEACHJwbF9wcml2AAJ0MQABAwABAQEAdXCMmg==
g2YSXR4BAAAAMgAAAJ8BAAAAAJoAAAAAAAEAAgAB/wAKAAAAAAsAAAAADAAAAGmCVP4=
';
# 3) Confirm the INSERT was executed
--let $assert_text= INSERT succeeded
--let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "3"
--source include/assert.inc
--disconnect other_conn
--connection default
DELETE FROM t1;
REVOKE INSERT, REPLICATION_APPLIER ON *.* FROM 'u1'@'localhost';
GRANT BINLOG_ADMIN ON *.* TO 'u1'@'localhost';
--echo #
--echo # TC4. Apply a `Write_row_event` with `BINLOG_ADMIN` privilege
--echo # ------------------------------------------------------------
--echo #
# 1) Start a session with a user with only `BINLOG_ADMIN` privilege.
--connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
--connection other_conn
BINLOG '
jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgGQNwbk';
# 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
# success.
BINLOG '
g2YSXRMBAAAANAAAAG0BAAAAAJoAAAAAAAEACHJwbF9wcml2AAJ0MQABAwABAQEAdXCMmg==
g2YSXR4BAAAAMgAAAJ8BAAAAAJoAAAAAAAEAAgAB/wAKAAAAAAsAAAAADAAAAGmCVP4=
';
# 3) Confirm the INSERT was executed
--let $assert_text= INSERT succeeded
--let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "3"
--source include/assert.inc
--disconnect other_conn
--connection default
DELETE FROM t1;
REVOKE BINLOG_ADMIN ON *.* FROM 'u1'@'localhost';
GRANT SUPER ON *.* TO 'u1'@'localhost';
--echo #
--echo # TC5. Apply a `Write_row_event` with `SUPER` privilege
--echo # -----------------------------------------------------
--echo #
# 1) Start a session with a user with only `SUPER` privilege.
--connect(other_conn, localhost, u1, pass, rpl_priv, $MYPORT, $MYSOCK)
--connection other_conn
BINLOG '
jbMGXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACNswZdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgGQNwbk';
# 2) Execute a `BINLOG` statement holding a `Write_rows_event` and expect
# success.
BINLOG '
g2YSXRMBAAAANAAAAG0BAAAAAJoAAAAAAAEACHJwbF9wcml2AAJ0MQABAwABAQEAdXCMmg==
g2YSXR4BAAAAMgAAAJ8BAAAAAJoAAAAAAAEAAgAB/wAKAAAAAAsAAAAADAAAAGmCVP4=
';
# 3) Confirm the INSERT was executed
--let $assert_text= INSERT succeeded
--let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "3"
--source include/assert.inc
--disconnect other_conn
--connection default
DROP DATABASE rpl_priv;
DROP USER 'u1'@'localhost';
|
