Debian Codegraph @

mysql-test/suite/binlog/t/binlog_priv_checks_user_set_vars.test

raw
1 # ==== Purpose ====
2 #
3 # This script tests error handling regarding privilege checks for setting
4 # session and global variables related to and needed by the binary log.
5 #
6 # ==== Requirements ====
7 #
8 # R1. Variables `auto_increment_increment`, `auto_increment_offset`,
9 # `collation_database`, `explicit_defaults_for_timestamp`,
10 # `default_collation_for_utf8mb4` and `rbr_exec_mode` are allowed to be set
11 # without privileges.
12 # R2. Variables `gtid_next`, `original_commit_timestamp`,
13 # `original_server_version`, `immediate_server_version` and
14 # `pseudo_replica_mode` are allowed to be set when having `SUPER`,
15 # `SYSTEM_VARIABLES_ADMIN`, `SESSION_VARIABLES_ADMIN` or
16 # `REPLICATION_APPLIER` privileges.
17 # R3. Variables `pseudo_thread_id` and `sql_require_primary_key` are allowed to
18 # be set when having `SUPER`, `SYSTEM_VARIABLES_ADMIN`,
19 # `SESSION_VARIABLES_ADMIN` privileges.
20 # R4. Variable `default_table_encryption` is allowed to be set when having
21 # `SUPER` or both `SYSTEM_VARIABLES_ADMIN` and `TABLE_ENCRYPTION_ADMIN`
22 # privileges.
23 #
24 # ==== Implementation ====
25 #
26 # TC1. Check unprivileged setting of sub-set of variables
27 # -------------------------------------------------------
28 # 1) Create a client session using an unprivileged user.
29 # 2) Change values for:
30 # a) `auto_increment_increment`
31 # b) `auto_increment_offset`
32 # c) `collation_database`
33 # d) `explicit_defaults_for_timestamp`
34 # e) `default_collation_for_utf8mb4`
35 # f) `rbr_exec_mode`
36 # and expect success.
37 #
38 # TC2. Check `REPLICATION_APPLIER` privilege sufficient for setting sub-set of
39 # variables
40 # ----------------------------------------------------------------------------
41 # 1) Create a client session using an unprivileged user.
42 # 2) Change values for:
43 # a) `gtid_next`
44 # c) `original_commit_timestamp`
45 # d) `original_server_version`
46 # e) `immediate_server_version`
47 # f) `pseudo_replica_mode`
48 # and expect failure.
49 # 3) Disconnect session and grant `REPLICATION_APPLIER` privilege to the user.
50 # 4) Create a client session using the privileged user.
51 # 5) Change values for:
52 # a) `gtid_next`
53 # c) `original_commit_timestamp`
54 # d) `original_server_version`
55 # e) `immediate_server_version`
56 # f) `pseudo_replica_mode`
57 # and expect success.
58 #
59 # TC3. Check privileges are kept the same while setting sub-set of variables
60 # --------------------------------------------------------------------------
61 # 1) Create a client session using the `REPLICATION_APPLIER` privileged user.
62 # 2) Change values for:
63 # a) `pseudo_thread_id`
64 # b) `sql_require_primary_key`
65 # c) `default_table_encryption`
66 # and expect failure.
67 #
68 # ==== References ====
69 #
70 # WL#12966 Replication with privilege checks
71 #
72
73 --source include/have_binlog_format_row.inc
74
75 CREATE USER 'u1'@'localhost';
76
77 --echo #
78 --echo # TC1. Check unprivileged setting of sub-set of variables
79 --echo # -------------------------------------------------------
80 --echo #
81
82 # 1) Create a client session using an unprivileged user.
83 --connect(conn, localhost, 'u1'@'localhost', '', test, $MYPORT, $MYSOCK)
84 --connection conn
85
86 # 2) Change values for:
87 # a) `auto_increment_increment`
88 # b) `auto_increment_offset`
89 # c) `collation_database`
90 # d) `explicit_defaults_for_timestamp`
91 # e) `default_collation_for_utf8mb4`
92 # f) `rbr_exec_mode`
93 # and expect success.
94 SET auto_increment_increment = 1;
95 SET auto_increment_offset = 1;
96 SET collation_database = 1;
97 SET explicit_defaults_for_timestamp = 1;
98 SET default_collation_for_utf8mb4 = utf8mb4_0900_ai_ci;
99 SET rbr_exec_mode = IDEMPOTENT;
100
101 --echo #
102 --echo # TC2. Check `REPLICATION_APPLIER` privilege sufficient for setting
103 --echo # sub-set of variables
104 --echo # -----------------------------------------------------------------
105 --echo #
106
107 # 1) Create a client session using an unprivileged user.
108 # 2) Change values for:
109 # a) `gtid_next`
110 # c) `original_commit_timestamp`
111 # d) `original_server_version`
112 # e) `immediate_server_version`
113 # f) `pseudo_replica_mode`
114 # and expect failure.
115 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
116 SET gtid_next = AUTOMATIC;
117 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
118 SET original_commit_timestamp = 1;
119 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
120 SET original_server_version = 1;
121 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
122 SET immediate_server_version = 1;
123 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
124 SET pseudo_replica_mode = 1;
125
126 # 3) Disconnect session and grant `REPLICATION_APPLIER` privilege to the user.
127 --disconnect conn
128 --connection default
129 GRANT REPLICATION_APPLIER ON *.* TO 'u1'@'localhost';
130
131 # 4) Create a client session using the privileged user.
132 --connect(conn, localhost, 'u1'@'localhost', '', test, $MYPORT, $MYSOCK)
133 --connection conn
134
135 # 5) Change values for:
136 # a) `gtid_next`
137 # c) `original_commit_timestamp`
138 # d) `original_server_version`
139 # e) `immediate_server_version`
140 # f) `pseudo_replica_mode`
141 # and expect success.
142 SET gtid_next = AUTOMATIC;
143 SET original_commit_timestamp = 1;
144 SET original_server_version = 1;
145 SET immediate_server_version = 1;
146 SET pseudo_replica_mode = 1;
147
148 --echo #
149 --echo # TC3. Check privileges are kept the same while setting sub-set of
150 --echo # variables
151 --echo # ----------------------------------------------------------------
152 --echo #
153
154 # 1) Create a client session using the `REPLICATION_APPLIER` privileged user.
155 # 2) Change values for:
156 # a) `pseudo_thread_id`
157 # b) `sql_require_primary_key`
158 # c) `default_table_encryption`
159 # and expect failure.
160 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
161 SET pseudo_thread_id = 1;
162 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
163 SET sql_require_primary_key = 1;
164 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
165 SET default_table_encryption = 1;
166 --disconnect conn
167
168 # Clean up
169 --connection default
170 DROP USER 'u1'@'localhost';