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
|
# ==== Purpose ====
#
# This file contains test cases to validate the behavior of system
# variable 'requires_row_format' with various permission levels
#
# ==== Requirements ====
#
# For the session require_row_format variable
# 1) No privileges are needed to enable it
# 2) The user needs SESSION_ADMIN or higher in order to disable it.
#
# ==== Implementation ====
#
# 0. Create a user and a connection with it
# 1. Test the use by a regular user
# 2. Test the use by a user with SESSION_VARIABLES_ADMIN privilege.
# 3. Test the use by a user with SYSTEM_VARIABLES_ADMIN privilege.
# 4. Test the use by a user with SUPER privilege.
# 5. Test changing the variable to invalid values
# 6. Cleanup
#
# ==== Related Worklog ====
#
# WL #12968 : Configure replication applier to require row-based replication
--echo #
--echo # 0. Create a user and a connection with it
CREATE USER u1@localhost;
GRANT ALL ON db1.* TO u1@localhost;
connect (con1, localhost, u1);
SELECT CURRENT_USER();
SHOW GRANTS FOR CURRENT_USER();
--echo #
--echo # 1. Test the use by a unprivileged user
--let $assert_text= By default the value is set to OFF
--let $assert_cond= "[SELECT @@SESSION.REQUIRE_ROW_FORMAT]" = 0
--source include/assert.inc
# Check different types of access
--error ER_LOCAL_VARIABLE
SET GLOBAL require_row_format=true;
--error ER_LOCAL_VARIABLE
SET PERSIST require_row_format=true;
--error ER_LOCAL_VARIABLE
SET PERSIST_ONLY require_row_format=true;
# Check priv errors
# No error as it is already false
SET SESSION require_row_format=false;
# No privileges are needed to set to true
SET SESSION require_row_format=true;
# Privileges are needed to set it false
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SET SESSION require_row_format=false;
# Privileges are needed to set it false
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SET SESSION require_row_format=DEFAULT;
--echo #
--echo # 2. Test the use by a user with SESSION_VARIABLES_ADMIN privilege.
connection default;
GRANT SESSION_VARIABLES_ADMIN ON *.* TO u1@localhost;
connection con1;
SELECT CURRENT_USER();
SHOW GRANTS FOR CURRENT_USER();
# No error as it has the necessary privs
SET SESSION require_row_format=false;
# No privileges are needed to set to true
SET SESSION require_row_format=true;
SET SESSION require_row_format=DEFAULT;
--echo #
--echo # 3. Test the use by a user with SYSTEM_VARIABLES_ADMIN privilege.
connection default;
REVOKE SESSION_VARIABLES_ADMIN ON *.* FROM u1@localhost;
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
connection con1;
SELECT CURRENT_USER();
SHOW GRANTS FOR CURRENT_USER();
# No privileges are needed to set to true
SET SESSION require_row_format=true;
# No error as it has the necessary privs
SET SESSION require_row_format=false;
# No privileges are needed to set to true
SET SESSION require_row_format=true;
--echo #
--echo # 4. Test the use by a user with SUPER privilege.
connection default;
disconnect con1;
REVOKE SESSION_VARIABLES_ADMIN ON *.* FROM u1@localhost;
GRANT SUPER ON *.* TO u1@localhost;
connect (con1, localhost, u1);
SELECT CURRENT_USER();
SHOW GRANTS FOR CURRENT_USER();
SET SESSION require_row_format=true;
SELECT @@SESSION.require_row_format = true;
SET SESSION require_row_format=false;
SELECT @@SESSION.require_row_format = false;
SET SESSION require_row_format=true;
SELECT @@SESSION.require_row_format = true;
--echo #
--echo # 5. Test errors when changing the variable to invalid values
--error ER_WRONG_VALUE_FOR_VAR
SET SESSION require_row_format = -1;
--error ER_WRONG_VALUE_FOR_VAR
SET SESSION require_row_format = 100000000000;
--error ER_WRONG_TYPE_FOR_VAR
SET SESSION require_row_format = 10000.01;
--error ER_WRONG_VALUE_FOR_VAR
SET SESSION require_row_format = 'test';
--error ER_WRONG_VALUE_FOR_VAR
SET SESSION require_row_format = '';
--echo #
--echo # 6. Cleanup
connection default;
disconnect con1;
DROP USER u1@localhost;
SET SESSION require_row_format=default;
|
