grant.test 4.32 KB
Newer Older
1 2 3 4
--disable_warnings
drop table if exists t1;
--enable_warnings

5 6 7 8 9 10 11 12 13 14
#
# Test that SSL options works properly
#

delete from mysql.user where user='mysqltest_1';
delete from mysql.db where user='mysqltest_1';
flush privileges;
grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
show grants for mysqltest_1@localhost;
grant delete on mysqltest.* to mysqltest_1@localhost;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
15
select * from mysql.user where user="mysqltest_1";
16 17 18 19 20 21 22 23 24 25 26
show grants for mysqltest_1@localhost;
revoke delete on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
grant select on mysqltest.* to mysqltest_1@localhost require NONE;
show grants for mysqltest_1@localhost;
grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB";
show grants for mysqltest_1@localhost;
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
flush privileges;
27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

#
# Test that the new db privileges are stored/retrieved correctly
#

grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
flush privileges;
show grants for mysqltest_1@localhost;
revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION;
flush privileges;
show grants for mysqltest_1@localhost;
revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
flush privileges;
46 47 48 49 50 51 52 53 54 55 56
grant usage on test.* to mysqltest_1@localhost with grant option;
show grants for mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
delete from mysql.db where user='mysqltest_1';
delete from mysql.tables_priv where user='mysqltest_1';
delete from mysql.columns_priv where user='mysqltest_1';
flush privileges;

#
# Test what happens when you have same table and colum level grants
#
57

58 59 60 61 62 63 64
create table t1 (a int);
GRANT select,update,insert on t1 to mysqltest_1@localhost;
GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
REVOKE select (a), update on t1 from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
65
REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;
66
show grants for mysqltest_1@localhost;
67
GRANT select,references on t1 to mysqltest_1@localhost;
68
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
69 70 71 72 73 74 75 76 77 78 79 80 81 82
grant all on test.* to mysqltest_3@localhost with grant option;
revoke all on test.* from mysqltest_3@localhost;
show grants for mysqltest_3@localhost;
revoke grant option on test.* from mysqltest_3@localhost;
show grants for mysqltest_3@localhost;
grant all on test.t1 to mysqltest_2@localhost with grant option;
revoke all on test.t1 from mysqltest_2@localhost;
show grants for mysqltest_2@localhost;
revoke grant option on test.t1 from mysqltest_2@localhost;
show grants for mysqltest_2@localhost;
delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
83 84
flush privileges;
drop table t1;
85 86 87 88

#
# Test some error conditions
#
89
--error  1221
90
GRANT FILE on mysqltest.*  to mysqltest_1@localhost;
91
select 1;	-- To test that the previous command didn't cause problems
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106


#
# Bug #4898: User privileges depending on ORDER BY Settings of table db
#
insert into mysql.user (host, user) values ('localhost', 'test11');
insert into mysql.db (host, db, user, select_priv) values 
('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
flush privileges;
show grants for test11@localhost;
alter table mysql.db order by db desc;
flush privileges;
show grants for test11@localhost;
delete from mysql.user where user='test11';
delete from mysql.db where user='test11';