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;