1
-- source include/have_ndb.inc
2
# Test of GRANT commands
6
drop table if exists t1;
12
# Alter mysql system tables to ndb
13
# make sure you alter all back in the end
16
alter table columns_priv engine=ndb;
17
alter table db engine=ndb;
18
alter table func engine=ndb;
19
alter table help_category engine=ndb;
20
alter table help_keyword engine=ndb;
21
alter table help_relation engine=ndb;
22
alter table help_topic engine=ndb;
23
alter table host engine=ndb;
24
alter table tables_priv engine=ndb;
25
alter table time_zone engine=ndb;
26
alter table time_zone_leap_second engine=ndb;
27
alter table time_zone_name engine=ndb;
28
alter table time_zone_transition engine=ndb;
29
alter table time_zone_transition_type engine=ndb;
30
alter table user engine=ndb;
34
# Test that SSL options works properly
36
delete from mysql.user where user='mysqltest_1';
37
delete from mysql.db where user='mysqltest_1';
40
grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
42
show grants for mysqltest_1@localhost;
44
grant delete on mysqltest.* to mysqltest_1@localhost;
46
select * from mysql.user where user="mysqltest_1";
47
show grants for mysqltest_1@localhost;
49
revoke delete on mysqltest.* from mysqltest_1@localhost;
51
show grants for mysqltest_1@localhost;
53
grant select on mysqltest.* to mysqltest_1@localhost require NONE;
55
show grants for mysqltest_1@localhost;
57
grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB";
59
show grants for mysqltest_1@localhost;
61
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
63
show grants for mysqltest_1@localhost;
64
delete from mysql.user where user='mysqltest_1';
68
# Test that the new db privileges are stored/retrieved correctly
72
grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost;
74
show grants for mysqltest_1@localhost;
76
show grants for mysqltest_1@localhost;
78
revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost;
80
show grants for mysqltest_1@localhost;
82
grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION;
85
show grants for mysqltest_1@localhost;
87
revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost;
89
show grants for mysqltest_1@localhost;
91
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
93
delete from mysql.user where user='mysqltest_1';
96
grant usage on test.* to mysqltest_1@localhost with grant option;
98
show grants for mysqltest_1@localhost;
99
delete from mysql.user where user='mysqltest_1';
100
delete from mysql.db where user='mysqltest_1';
101
delete from mysql.tables_priv where user='mysqltest_1';
102
delete from mysql.columns_priv where user='mysqltest_1';
105
show grants for mysqltest_1@localhost;
108
# Test what happens when you have same table and colum level grants
111
create table t1 (a int);
113
GRANT select,update,insert on t1 to mysqltest_1@localhost;
114
GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
116
show grants for mysqltest_1@localhost;
117
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
119
REVOKE select (a), update on t1 from mysqltest_1@localhost;
121
show grants for mysqltest_1@localhost;
123
REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;
125
show grants for mysqltest_1@localhost;
127
GRANT select,references on t1 to mysqltest_1@localhost;
129
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
131
grant all on test.* to mysqltest_3@localhost with grant option;
132
revoke all on test.* from mysqltest_3@localhost;
134
show grants for mysqltest_3@localhost;
136
revoke grant option on test.* from mysqltest_3@localhost;
138
show grants for mysqltest_3@localhost;
140
grant all on test.t1 to mysqltest_2@localhost with grant option;
141
revoke all on test.t1 from mysqltest_2@localhost;
143
show grants for mysqltest_2@localhost;
145
revoke grant option on test.t1 from mysqltest_2@localhost;
147
show grants for mysqltest_2@localhost;
148
delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
149
delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
150
delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
151
delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
156
# Test some error conditions
160
GRANT FILE on mysqltest.* to mysqltest_1@localhost;
162
select 1; -- To test that the previous command didn't cause problems
165
# Bug#6123: GRANT USAGE inserts useless Db row
167
create database mysqltest1;
169
grant usage on mysqltest1.* to test6123 identified by 'magic123';
171
select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1";
172
delete from mysql.user where user='test6123';
173
drop database mysqltest1;
176
# Test for 'drop user', 'revoke privileges, grant'
179
create table t1 (a int);
181
grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION;
183
show grants for drop_user2@localhost;
185
revoke all privileges, grant option from drop_user2@localhost;
187
drop user drop_user2@localhost;
190
grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION;
191
grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION;
192
grant select(a) on test.t1 to drop_user@localhost;
194
show grants for drop_user@localhost;
199
set sql_mode=ansi_quotes;
200
show grants for drop_user@localhost;
201
set sql_mode=default;
203
set sql_quote_show_create=0;
204
show grants for drop_user@localhost;
205
set sql_mode="ansi_quotes";
206
show grants for drop_user@localhost;
207
set sql_quote_show_create=1;
208
show grants for drop_user@localhost;
210
show grants for drop_user@localhost;
212
revoke all privileges, grant option from drop_user@localhost;
213
show grants for drop_user@localhost;
214
drop user drop_user@localhost;
217
revoke all privileges, grant option from drop_user@localhost;
221
grant select(a) on test.t1 to drop_user1@localhost;
225
grant select on test.t1 to drop_user2@localhost;
226
grant select on test.* to drop_user3@localhost;
227
grant select on *.* to drop_user4@localhost;
230
# Drop user now implicitly revokes all privileges.
231
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
232
drop_user4@localhost;
235
revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost,
236
drop_user3@localhost, drop_user4@localhost;
240
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
241
drop_user4@localhost;
244
grant usage on *.* to mysqltest_1@localhost identified by "password";
245
grant select, update, insert on test.* to mysqltest_1@localhost;
247
show grants for mysqltest_1@localhost;
248
drop user mysqltest_1@localhost;
251
# Bug #3403 Wrong encodin in SHOW GRANTS output
256
CREATE TABLE ��� (��� int);
259
GRANT SELECT ON ��.* TO ����@localhost;
261
SHOW GRANTS FOR ����@localhost;
263
REVOKE SELECT ON ��.* FROM ����@localhost;
267
GRANT SELECT ON ��.��� TO ����@localhost;
269
SHOW GRANTS FOR ����@localhost;
271
REVOKE SELECT ON ��.��� FROM ����@localhost;
275
GRANT SELECT (���) ON ��.��� TO ����@localhost;
277
SHOW GRANTS FOR ����@localhost;
279
REVOKE SELECT (���) ON ��.��� FROM ����@localhost;
286
# Bug #5831: REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything
289
CREATE TABLE t1 (a int );
290
CREATE TABLE t2 LIKE t1;
291
CREATE TABLE t3 LIKE t1;
292
CREATE TABLE t4 LIKE t1;
293
CREATE TABLE t5 LIKE t1;
294
CREATE TABLE t6 LIKE t1;
295
CREATE TABLE t7 LIKE t1;
296
CREATE TABLE t8 LIKE t1;
297
CREATE TABLE t9 LIKE t1;
298
CREATE TABLE t10 LIKE t1;
299
CREATE DATABASE testdb1;
300
CREATE DATABASE testdb2;
301
CREATE DATABASE testdb3;
302
CREATE DATABASE testdb4;
303
CREATE DATABASE testdb5;
304
CREATE DATABASE testdb6;
305
CREATE DATABASE testdb7;
306
CREATE DATABASE testdb8;
307
CREATE DATABASE testdb9;
308
CREATE DATABASE testdb10;
310
GRANT ALL ON testdb1.* TO testuser@localhost;
311
GRANT ALL ON testdb2.* TO testuser@localhost;
312
GRANT ALL ON testdb3.* TO testuser@localhost;
313
GRANT ALL ON testdb4.* TO testuser@localhost;
314
GRANT ALL ON testdb5.* TO testuser@localhost;
315
GRANT ALL ON testdb6.* TO testuser@localhost;
316
GRANT ALL ON testdb7.* TO testuser@localhost;
317
GRANT ALL ON testdb8.* TO testuser@localhost;
318
GRANT ALL ON testdb9.* TO testuser@localhost;
319
GRANT ALL ON testdb10.* TO testuser@localhost;
320
GRANT SELECT ON test.t1 TO testuser@localhost;
321
GRANT SELECT ON test.t2 TO testuser@localhost;
322
GRANT SELECT ON test.t3 TO testuser@localhost;
323
GRANT SELECT ON test.t4 TO testuser@localhost;
324
GRANT SELECT ON test.t5 TO testuser@localhost;
325
GRANT SELECT ON test.t6 TO testuser@localhost;
326
GRANT SELECT ON test.t7 TO testuser@localhost;
327
GRANT SELECT ON test.t8 TO testuser@localhost;
328
GRANT SELECT ON test.t9 TO testuser@localhost;
329
GRANT SELECT ON test.t10 TO testuser@localhost;
330
GRANT SELECT (a) ON test.t1 TO testuser@localhost;
331
GRANT SELECT (a) ON test.t2 TO testuser@localhost;
332
GRANT SELECT (a) ON test.t3 TO testuser@localhost;
333
GRANT SELECT (a) ON test.t4 TO testuser@localhost;
334
GRANT SELECT (a) ON test.t5 TO testuser@localhost;
335
GRANT SELECT (a) ON test.t6 TO testuser@localhost;
336
GRANT SELECT (a) ON test.t7 TO testuser@localhost;
337
GRANT SELECT (a) ON test.t8 TO testuser@localhost;
338
GRANT SELECT (a) ON test.t9 TO testuser@localhost;
339
GRANT SELECT (a) ON test.t10 TO testuser@localhost;
342
REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost;
344
SHOW GRANTS FOR testuser@localhost;
345
DROP USER testuser@localhost;
346
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
347
DROP DATABASE testdb1;
348
DROP DATABASE testdb2;
349
DROP DATABASE testdb3;
350
DROP DATABASE testdb4;
351
DROP DATABASE testdb5;
352
DROP DATABASE testdb6;
353
DROP DATABASE testdb7;
354
DROP DATABASE testdb8;
355
DROP DATABASE testdb9;
356
DROP DATABASE testdb10;
359
# just SHOW PRIVILEGES test
364
# Alter mysql system tables back to myisam
367
alter table columns_priv engine=myisam;
368
alter table db engine=myisam;
369
alter table func engine=myisam;
370
alter table help_category engine=myisam;
371
alter table help_keyword engine=myisam;
372
alter table help_relation engine=myisam;
373
alter table help_topic engine=myisam;
374
alter table host engine=myisam;
375
alter table tables_priv engine=myisam;
376
alter table time_zone engine=myisam;
377
alter table time_zone_leap_second engine=myisam;
378
alter table time_zone_name engine=myisam;
379
alter table time_zone_transition engine=myisam;
380
alter table time_zone_transition_type engine=myisam;
381
alter table user engine=myisam;