--disable_warnings drop table if exists t1; --enable_warnings # # 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; select * from mysql.user where user="mysqltest_1"; 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; # # 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; 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 # 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; REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost; show grants for mysqltest_1@localhost; GRANT select,references on t1 to mysqltest_1@localhost; select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; 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"; flush privileges; drop table t1; # # Test some error conditions # --error 1221 GRANT FILE on mysqltest.* to mysqltest_1@localhost; select 1; -- To test that the previous command didn't cause problems # # 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'); alter table mysql.db order by db asc; 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'; # # Bug#6123: GRANT USAGE inserts useless Db row # create database db6123; grant usage on db6123.* to test6123 identified by 'magic123'; select host,db,user,select_priv,insert_priv from mysql.db where db="db6123"; delete from mysql.user where user='test6123'; drop database db6123;