Debian Codegraph @

mysql-test/suite/sys_vars/t/require_row_format_basic.test

raw
1 # ==== Purpose ====
2 #
3 # This file contains test cases to validate the behavior of system
4 # variable 'requires_row_format' with various permission levels
5 #
6 # ==== Requirements ====
7 #
8 # For the session require_row_format variable
9 # 1) No privileges are needed to enable it
10 # 2) The user needs SESSION_ADMIN or higher in order to disable it.
11 #
12 # ==== Implementation ====
13 #
14 # 0. Create a user and a connection with it
15 # 1. Test the use by a regular user
16 # 2. Test the use by a user with SESSION_VARIABLES_ADMIN privilege.
17 # 3. Test the use by a user with SYSTEM_VARIABLES_ADMIN privilege.
18 # 4. Test the use by a user with SUPER privilege.
19 # 5. Test changing the variable to invalid values
20 # 6. Cleanup
21 #
22 # ==== Related Worklog ====
23 #
24 # WL #12968 : Configure replication applier to require row-based replication
25
26 --echo #
27 --echo # 0. Create a user and a connection with it
28
29 CREATE USER u1@localhost;
30
31 GRANT ALL ON db1.* TO u1@localhost;
32 connect (con1, localhost, u1);
33 SELECT CURRENT_USER();
34 SHOW GRANTS FOR CURRENT_USER();
35
36 --echo #
37 --echo # 1. Test the use by a unprivileged user
38
39 --let $assert_text= By default the value is set to OFF
40 --let $assert_cond= "[SELECT @@SESSION.REQUIRE_ROW_FORMAT]" = 0
41 --source include/assert.inc
42
43 # Check different types of access
44
45 --error ER_LOCAL_VARIABLE
46 SET GLOBAL require_row_format=true;
47
48 --error ER_LOCAL_VARIABLE
49 SET PERSIST require_row_format=true;
50
51 --error ER_LOCAL_VARIABLE
52 SET PERSIST_ONLY require_row_format=true;
53
54 # Check priv errors
55
56 # No error as it is already false
57 SET SESSION require_row_format=false;
58
59 # No privileges are needed to set to true
60 SET SESSION require_row_format=true;
61
62 # Privileges are needed to set it false
63 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
64 SET SESSION require_row_format=false;
65
66 # Privileges are needed to set it false
67 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
68 SET SESSION require_row_format=DEFAULT;
69
70 --echo #
71 --echo # 2. Test the use by a user with SESSION_VARIABLES_ADMIN privilege.
72
73 connection default;
74 GRANT SESSION_VARIABLES_ADMIN ON *.* TO u1@localhost;
75 connection con1;
76 SELECT CURRENT_USER();
77 SHOW GRANTS FOR CURRENT_USER();
78
79 # No error as it has the necessary privs
80 SET SESSION require_row_format=false;
81
82 # No privileges are needed to set to true
83 SET SESSION require_row_format=true;
84
85 SET SESSION require_row_format=DEFAULT;
86
87 --echo #
88 --echo # 3. Test the use by a user with SYSTEM_VARIABLES_ADMIN privilege.
89
90 connection default;
91 REVOKE SESSION_VARIABLES_ADMIN ON *.* FROM u1@localhost;
92 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
93 connection con1;
94 SELECT CURRENT_USER();
95 SHOW GRANTS FOR CURRENT_USER();
96
97 # No privileges are needed to set to true
98 SET SESSION require_row_format=true;
99
100 # No error as it has the necessary privs
101 SET SESSION require_row_format=false;
102
103 # No privileges are needed to set to true
104 SET SESSION require_row_format=true;
105
106 --echo #
107 --echo # 4. Test the use by a user with SUPER privilege.
108
109 connection default;
110 disconnect con1;
111 REVOKE SESSION_VARIABLES_ADMIN ON *.* FROM u1@localhost;
112 GRANT SUPER ON *.* TO u1@localhost;
113 connect (con1, localhost, u1);
114 SELECT CURRENT_USER();
115 SHOW GRANTS FOR CURRENT_USER();
116
117 SET SESSION require_row_format=true;
118 SELECT @@SESSION.require_row_format = true;
119
120 SET SESSION require_row_format=false;
121 SELECT @@SESSION.require_row_format = false;
122
123 SET SESSION require_row_format=true;
124 SELECT @@SESSION.require_row_format = true;
125
126 --echo #
127 --echo # 5. Test errors when changing the variable to invalid values
128
129 --error ER_WRONG_VALUE_FOR_VAR
130 SET SESSION require_row_format = -1;
131
132 --error ER_WRONG_VALUE_FOR_VAR
133 SET SESSION require_row_format = 100000000000;
134
135 --error ER_WRONG_TYPE_FOR_VAR
136 SET SESSION require_row_format = 10000.01;
137
138 --error ER_WRONG_VALUE_FOR_VAR
139 SET SESSION require_row_format = 'test';
140
141 --error ER_WRONG_VALUE_FOR_VAR
142 SET SESSION require_row_format = '';
143
144 --echo #
145 --echo # 6. Cleanup
146
147 connection default;
148 disconnect con1;
149
150 DROP USER u1@localhost;
151
152 SET SESSION require_row_format=default;