grant2.test 2.63 KB
Newer Older
1 2
SET NAMES binary;

3 4 5 6 7 8
#
# GRANT tests that require several connections
# (usually it's GRANT, reconnect as another user, try something)
#


9 10 11 12 13 14 15 16 17 18 19
# prepare playground before tests
--disable_warnings
drop database if exists mysqltest;
--enable_warnings
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
flush privileges;


20 21 22 23 24 25 26 27
#
# wild_compare fun
#

grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option;
connect (user1,localhost,mysqltest_1,,);
connection user1;
select current_user();
28
select current_user;
29
grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option;
30
--error 1044
31
grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option;
32
disconnect user1;
unknown's avatar
unknown committed
33
connection default;
34 35 36 37
show grants for mysqltest_1@localhost;
show grants for mysqltest_2@localhost;
--error 1141
show grants for mysqltest_3@localhost;
38 39 40 41
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
flush privileges;

42 43 44 45 46 47 48 49 50 51 52

#
# Bug #6173: One can circumvent missing UPDATE privilege if he has SELECT
# and INSERT privilege for table with primary key
#
create database mysqltest;
grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost;
flush privileges;
use mysqltest;
create table t1 (id int primary key, data varchar(255));

unknown's avatar
unknown committed
53
connect (mrbad, localhost, mysqltest_1,,mysqltest);
54 55 56 57 58 59 60 61 62
connection mrbad;
show grants for current_user();
insert into t1 values (1, 'I can''t change it!');
--error 1044
update t1 set data='I can change it!' where id = 1;
# This should not be allowed since it too require UPDATE privilege.
--error 1044
insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!';
select * from t1;
unknown's avatar
unknown committed
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
disconnect mrbad;

connection default;
drop table t1;
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
flush privileges;

create table t1 (a int, b int);
grant select (a) on t1 to mysqltest_1@localhost with grant option;
connect (mrugly, localhost, mysqltest_1,,mysqltest);
connection mrugly;
--error 1143
grant select (a,b) on t1 to mysqltest_2@localhost;
--error 1142
grant select on t1 to mysqltest_3@localhost;
disconnect mrugly;
80 81 82 83 84

connection default;
drop table t1;
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
unknown's avatar
unknown committed
85 86
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
87
flush privileges;
unknown's avatar
unknown committed
88 89 90 91

drop database mysqltest;
use test;