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
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
|
#
# Bug #23210850: VERSION_TOKEN better protected
#
# Define a UDF before installing the plugin
create function version_tokens_show returns string soname 'version_token.so';
SELECT version_tokens_show();
ERROR HY000: Can't initialize function 'version_tokens_show'; version_token plugin is not installed.
# Testing installing the plugin twice while there's a UDF defined
INSTALL PLUGIN version_tokens SONAME 'version_token.so';
UNINSTALL PLUGIN version_tokens;
Warnings:
Warning 1620 Plugin is busy and will be uninstalled on shutdown
INSTALL PLUGIN version_tokens SONAME 'version_token.so';
UNINSTALL PLUGIN version_tokens;
Warnings:
Warning 1620 Plugin is busy and will be uninstalled on shutdown
# Should not crash when unloading the so through removing the UDF
drop function version_tokens_show;
INSTALL PLUGIN version_tokens SONAME 'version_token.so';
create function version_tokens_set returns string soname 'version_token.so';
create function version_tokens_show returns string soname 'version_token.so';
create function version_tokens_edit returns string soname 'version_token.so';
create function version_tokens_delete returns string soname 'version_token.so';
create function version_tokens_lock_shared returns int soname 'version_token.so';
create function version_tokens_lock_exclusive returns int soname 'version_token.so';
create function version_tokens_unlock returns int soname 'version_token.so';
# Error checks for UDFs
select version_tokens_set("token1 = abc;token2= def",123);
ERROR HY000: Can't initialize function 'version_tokens_set'; Wrong arguments provided for the function.
select version_tokens_edit("token1= 123; token3 = asdf",123);
ERROR HY000: Can't initialize function 'version_tokens_edit'; Wrong arguments provided for the function.
select version_tokens_delete("token1;token3",123);
ERROR HY000: Can't initialize function 'version_tokens_delete'; Wrong arguments provided for the function.
select version_tokens_show("123");
ERROR HY000: Can't initialize function 'version_tokens_show'; This function does not take any arguments.
select version_tokens_set(123);
ERROR HY000: Can't initialize function 'version_tokens_set'; Wrong arguments provided for the function.
select version_tokens_edit(123);
ERROR HY000: Can't initialize function 'version_tokens_edit'; Wrong arguments provided for the function.
select version_tokens_delete(123);
ERROR HY000: Can't initialize function 'version_tokens_delete'; Wrong arguments provided for the function.
select version_tokens_lock_shared("Less arguments");
ERROR HY000: Can't initialize function 'version_tokens_lock_shared'; Requires at least two arguments: (lock(...),timeout).
select version_tokens_lock_shared(1,"Wrong argument type");
ERROR HY000: Can't initialize function 'version_tokens_lock_shared'; Wrong argument type - expected integer.
select version_tokens_lock_exclusive("Less arguments");
ERROR HY000: Can't initialize function 'version_tokens_lock_exclusive'; Requires at least two arguments: (lock(...),timeout).
select version_tokens_lock_exclusive(1,"Wrong argument type");
ERROR HY000: Can't initialize function 'version_tokens_lock_exclusive'; Wrong argument type - expected integer.
select version_tokens_unlock("Takes no arguments");
ERROR HY000: Can't initialize function 'version_tokens_unlock'; Requires no arguments.
select version_tokens_lock_shared("gizmo", -1);
ERROR 22003: timeout value is out of range in 'version_tokens_lock_shared'
select version_tokens_lock_exclusive("gizmo", -1);
ERROR 22003: timeout value is out of range in 'version_tokens_lock_exclusive'
CREATE USER vbhagi@localhost;
select version_tokens_set("token1 = abc;token2= def");
version_tokens_set("token1 = abc;token2= def")
2 version tokens set.
select version_tokens_show();
version_tokens_show()
token1=abc;token2=def;
select version_tokens_set("");
version_tokens_set("")
Version tokens list cleared.
select version_tokens_show();
version_tokens_show()
NULL
select version_tokens_set("token1 = abc;token2= def");
version_tokens_set("token1 = abc;token2= def")
2 version tokens set.
select version_tokens_show();
version_tokens_show()
token1=abc;token2=def;
select version_tokens_edit("token1= 123; token3 = asdf");
version_tokens_edit("token1= 123; token3 = asdf")
2 version tokens updated.
select version_tokens_show();
version_tokens_show()
token1=123;token2=def;token3=asdf;
do version_tokens_set("token1 = def;;;; ;invalid_token; token2= abc;;");
Warnings:
Warning 42000 Invalid version token pair encountered. The list provided is only partially updated.
select version_tokens_show();
version_tokens_show()
token1=def;
do version_tokens_set("token1 = def;; token111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111=123;; ;invalid_token; token2= abc;;");
Warnings:
Warning 42000 Lengthy version token name encountered. Maximum length allowed for a token name is 64 characters. The list provided is only partially updated.
select version_tokens_show();
version_tokens_show()
token1=def;
do version_tokens_set("token1 = none;;;; ;invalid_token; token2= 123;;");
Warnings:
Warning 42000 Invalid version token pair encountered. The list provided is only partially updated.
select version_tokens_show();
version_tokens_show()
token1=none;
do version_tokens_set("token1=def;token2= abc; ;token3=ghi");
select version_tokens_show();
version_tokens_show()
token1=def;token2=abc;token3=ghi;
select version_tokens_delete("invalid=token");
version_tokens_delete("invalid=token")
0 version tokens deleted.
select version_tokens_show();
version_tokens_show()
token1=def;token2=abc;token3=ghi;
select version_tokens_delete("token1;token3");
version_tokens_delete("token1;token3")
2 version tokens deleted.
select version_tokens_show();
version_tokens_show()
token2=abc;
select version_tokens_delete("*");
version_tokens_delete("*")
0 version tokens deleted.
select version_tokens_show();
version_tokens_show()
token2=abc;
do version_tokens_set("token1=def;token2= abc;token3=111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112");
select version_tokens_show();
version_tokens_show()
token1=def;token2=abc;token3=111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112;
# Connection: con1
# The UDFs fail due to lack of privileges.
select version_tokens_set("token1 = abc;token2= def");
ERROR HY000: Can't initialize function 'version_tokens_set'; The user is not privileged to use this function.
select version_tokens_edit("token1= 123; token3 = asdf");
ERROR HY000: Can't initialize function 'version_tokens_edit'; The user is not privileged to use this function.
select version_tokens_delete("token1;token3");
ERROR HY000: Can't initialize function 'version_tokens_delete'; The user is not privileged to use this function.
select version_tokens_show();
ERROR HY000: Can't initialize function 'version_tokens_show'; The user is not privileged to use this function.
set @@version_tokens_session= "token1=def";
select 1;
1
1
select 1;
1
1
set @@version_tokens_session= "token3=abc";
select 1;
ERROR 42000: Version token mismatch for token3. Correct value 111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112
# Connection: con1 refreshed
# Next statement goes through as the connection is refreshed.
select @@version_tokens_session;
@@version_tokens_session
NULL
set @@version_tokens_session= "token111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111=abc;;";
# Connection: con1 refreshed
# Next statement goes through as the connection is refreshed.
select @@version_tokens_session;
@@version_tokens_session
NULL
set @@version_tokens_session= ";;";
set @@version_tokens_session= NULL;
set @@version_tokens_session= "token1=def;;;;;;;;";
set @@version_tokens_session= "token1=def;;; token2 = abc ; = ;;;;";
# Connection: con1 refreshed
set @@version_tokens_session= "token1=def;;; token2 = abc ; sdf = ;;;;";
# Connection: con1 refreshed
set @@version_tokens_session= "token1=def;; ; token2 = abc ;;;;;";
# Next queries get through as the session tokens are valid
use test;
create table t1 (c1 int);
drop table t1;
set @@version_tokens_session= "token1=def;;; token2 = abc ; = sdf ;;;;";
# This query fails as " = sdf" is not a valid token.
create table t1 (c1 int);
ERROR 42000: Empty version token name/value encountered
# Connection: con1 refreshed
set @@version_tokens_session= "token100=def;;; token2 = abc ;;;;;";
create table t1 (c1 int);
ERROR 42000: Version token token100 not found.
# Connection: default
# Next statement goes through as it is from a different connection.
select 1;
1
1
# Testing VERSION_TOKEN_ADMIN
CREATE USER u1@localhost IDENTIFIED BY 'foo';
GRANT VERSION_TOKEN_ADMIN ON *.* TO u1@localhost;
# Tests related to locking
# Connection: vtcon1
SELECT version_tokens_set("a=aa;b=bb;c=cc");
version_tokens_set("a=aa;b=bb;c=cc")
3 version tokens set.
set @@version_tokens_session= "a=aa";
SELECT sleep(6); # sleep for 6 seconds in background.;
# Connection: vtcon2
SELECT version_tokens_lock_exclusive("b",20);
version_tokens_lock_exclusive("b",20)
1
# Connection: vtcon3
SELECT version_tokens_lock_exclusive("a",20);
version_tokens_lock_exclusive("a",20)
1
SELECT version_tokens_unlock();
version_tokens_unlock()
1
# Connection: vtcon1
sleep(6)
0
UNINSTALL PLUGIN version_tokens;
Warnings:
Warning 1620 Plugin is busy and will be uninstalled on shutdown
# The UDFs fail as the plugin is uninstalled.
select version_tokens_set("token1 = abc;token2= def");
ERROR HY000: Can't initialize function 'version_tokens_set'; version_token plugin is not installed.
select version_tokens_edit("token1= 123; token3 = asdf");
ERROR HY000: Can't initialize function 'version_tokens_edit'; version_token plugin is not installed.
select version_tokens_delete("token1;token3");
ERROR HY000: Can't initialize function 'version_tokens_delete'; version_token plugin is not installed.
select version_tokens_show();
ERROR HY000: Can't initialize function 'version_tokens_show'; version_token plugin is not installed.
# VERSION_TOKEN_ADMIN shouldn't be visible in SHOW PRIVILEGES
SHOW PRIVILEGES;
Privilege Context Comment
APPLICATION_PASSWORD_ADMIN Server Admin
AUDIT_ABORT_EXEMPT Server Admin
AUDIT_ADMIN Server Admin
AUTHENTICATION_POLICY_ADMIN Server Admin
Alter Tables To alter the table
Alter routine Functions,Procedures To alter or drop stored functions/procedures
BACKUP_ADMIN Server Admin
BINLOG_ADMIN Server Admin
BINLOG_ENCRYPTION_ADMIN Server Admin
CLONE_ADMIN Server Admin
CONNECTION_ADMIN Server Admin
Create Databases,Tables,Indexes To create new databases and tables
Create role Server Admin To create new roles
Create routine Databases To use CREATE FUNCTION/PROCEDURE
Create tablespace Server Admin To create/alter/drop tablespaces
Create temporary tables Databases To use CREATE TEMPORARY TABLE
Create user Server Admin To create new users
Create view Tables To create new views
Delete Tables To delete existing rows
Drop Databases,Tables To drop databases, tables, and views
Drop role Server Admin To drop roles
ENCRYPTION_KEY_ADMIN Server Admin
Event Server Admin To create, alter, drop and execute events
Execute Functions,Procedures To execute stored routines
FIREWALL_EXEMPT Server Admin
FLUSH_OPTIMIZER_COSTS Server Admin
FLUSH_STATUS Server Admin
FLUSH_TABLES Server Admin
FLUSH_USER_RESOURCES Server Admin
File File access on server To read and write files on the server
GROUP_REPLICATION_ADMIN Server Admin
GROUP_REPLICATION_STREAM Server Admin
Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess
INNODB_REDO_LOG_ARCHIVE Server Admin
INNODB_REDO_LOG_ENABLE Server Admin
Index Tables To create or drop indexes
Insert Tables To insert data into tables
Lock tables Databases To use LOCK TABLES (together with SELECT privilege)
PASSWORDLESS_USER_ADMIN Server Admin
PERSIST_RO_VARIABLES_ADMIN Server Admin
Process Server Admin To view the plain text of currently executing queries
Proxy Server Admin To make proxy user possible
REPLICATION_APPLIER Server Admin
REPLICATION_SLAVE_ADMIN Server Admin
RESOURCE_GROUP_ADMIN Server Admin
RESOURCE_GROUP_USER Server Admin
ROLE_ADMIN Server Admin
References Databases,Tables To have references on tables
Reload Server Admin To reload or refresh tables, logs and privileges
Replication client Server Admin To ask where the slave or master servers are
Replication slave Server Admin To read binary log events from the master
SENSITIVE_VARIABLES_OBSERVER Server Admin
SERVICE_CONNECTION_ADMIN Server Admin
SESSION_VARIABLES_ADMIN Server Admin
SET_USER_ID Server Admin
SHOW_ROUTINE Server Admin
SYSTEM_USER Server Admin
SYSTEM_VARIABLES_ADMIN Server Admin
Select Tables To retrieve rows from table
Show databases Server Admin To see all databases with SHOW DATABASES
Show view Tables To see views with SHOW CREATE VIEW
Shutdown Server Admin To shut down the server
Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
TABLE_ENCRYPTION_ADMIN Server Admin
TELEMETRY_LOG_ADMIN Server Admin
Trigger Tables To use triggers
Update Tables To update existing rows
Usage Server Admin No privileges - allow connect only
XA_RECOVER_ADMIN Server Admin
drop function version_tokens_set;
drop function version_tokens_show;
drop function version_tokens_edit;
drop function version_tokens_delete;
drop function version_tokens_lock_shared;
drop function version_tokens_lock_exclusive;
drop function version_tokens_unlock;
drop user vbhagi@localhost;
drop user u1@localhost;
|
