grant2.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:7k
- # Grant tests not performed with embedded server
- -- source include/not_embedded.inc
- SET NAMES binary;
- #
- # GRANT tests that require several connections
- # (usually it's GRANT, reconnect as another user, try something)
- #
- # prepare playground before tests
- --disable_warnings
- drop database if exists mysqltest;
- drop database if exists mysqltest_1;
- --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;
- #
- # 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();
- grant all privileges on `my_1`.* to mysqltest_2@localhost with grant option;
- --error 1044
- grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option;
- disconnect user1;
- connection default;
- show grants for mysqltest_1@localhost;
- show grants for mysqltest_2@localhost;
- --error 1141
- show grants for mysqltest_3@localhost;
- delete from mysql.user where user like 'mysqltest_%';
- delete from mysql.db where user like 'mysqltest_%';
- flush privileges;
- #
- # wild_compare part two - acl_cache
- #
- create database mysqltest_1;
- grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option;
- connect (user2,localhost,mysqltest_1,,);
- connection user2;
- select current_user();
- show databases;
- --error 1044
- grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option;
- disconnect user2;
- connection default;
- show grants for mysqltest_1@localhost;
- delete from mysql.user where user like 'mysqltest_%';
- delete from mysql.db where user like 'mysqltest_%';
- drop database mysqltest_1;
- flush privileges;
- #
- # 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));
- connect (mrbad, localhost, mysqltest_1,,mysqltest);
- 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;
- 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;
- connection default;
- drop table t1;
- 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;
- drop database mysqltest;
- use test;
- # Bug #3309: Test IP addresses with netmask
- create database mysqltest_1;
- create table mysqltest_1.t1 (i int);
- insert into mysqltest_1.t1 values (1),(2),(3);
- GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0';
- connect (n1,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,$MASTER_MYSOCK);
- connection n1;
- show grants for current_user();
- select * from t1;
- disconnect n1;
- connection default;
- REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0';
- delete from mysql.user where user like 'mysqltest_1';
- flush privileges;
- drop table mysqltest_1.t1;
- #
- # Bug #12302: 'SET PASSWORD = ...' didn't work if connecting hostname !=
- # hostname the current user is authenticated as. Note that a test for this
- # was also added to the test above.
- #
- grant all on mysqltest_1.* to mysqltest_1@'127.0.0.1';
- connect (b12302,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,);
- connection b12302;
- select current_user();
- set password = password('changed');
- disconnect b12302;
- connection default;
- select host, length(password) from mysql.user where user like 'mysqltest_1';
- revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.1';
- delete from mysql.user where user like 'mysqltest_1';
- flush privileges;
- grant all on mysqltest_1.* to mysqltest_1@'127.0.0.0/255.0.0.0';
- connect (b12302_2,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,);
- connection b12302_2;
- select current_user();
- set password = password('changed');
- disconnect b12302_2;
- connection default;
- select host, length(password) from mysql.user where user like 'mysqltest_1';
- revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.0/255.0.0.0';
- delete from mysql.user where user like 'mysqltest_1';
- flush privileges;
- drop database mysqltest_1;
- # But anonymous users can't change their password
- connect (n5,localhost,test,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
- connection n5;
- --error 1044
- set password = password("changed");
- disconnect n5;
- connection default;
- # Bug #12423 "Deadlock when doing FLUSH PRIVILEGES and GRANT in
- # multi-threaded environment". We should be able to execute FLUSH
- # PRIVILEGES and SET PASSWORD simultaneously with other account
- # management commands (such as GRANT and REVOKE) without causing
- # deadlocks. To achieve this we should ensure that all account
- # management commands take table and internal locks in the same order.
- connect (con2root,localhost,root,,);
- connect (con3root,localhost,root,,);
- # Check that we can execute FLUSH PRIVILEGES and GRANT simultaneously
- # This will check that locks are taken in proper order during both
- # user/db-level and table/column-level privileges reloading.
- connection default;
- lock table mysql.user write;
- connection con2root;
- send flush privileges;
- connection con3root;
- send grant all on *.* to 'mysqltest_1'@'localhost';
- connection default;
- unlock tables;
- connection con2root;
- reap;
- connection con3root;
- reap;
- # Check for simultaneous SET PASSWORD and REVOKE.
- connection default;
- lock table mysql.user write;
- connection con2root;
- send set password for 'mysqltest_1'@'localhost' = password('');
- connection con3root;
- send revoke all on *.* from 'mysqltest_1'@'localhost';
- connection default;
- unlock tables;
- connection con2root;
- reap;
- connection con3root;
- reap;
- connection default;
- # Clean-up
- drop user 'mysqltest_1'@'localhost';
- disconnect con2root;
- disconnect con3root;
- # End of 4.1 tests