diff options
Diffstat (limited to 'mysql-test/r/grant2.result')
| -rw-r--r-- | mysql-test/r/grant2.result | 126 |
1 files changed, 112 insertions, 14 deletions
diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index bd14f21010b..d447bc10dc7 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -11,15 +11,27 @@ flush privileges; grant all privileges on `my\_1`.* to mysqltest_1@localhost with grant option; grant create user on *.* to mysqltest_1@localhost; create user mysqltest_2@localhost; +connect user_a,localhost,mysqltest_1,,; +connection user_a; grant select on `my\_1`.* to mysqltest_2@localhost; grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysql' +disconnect user_a; +connection default; grant update on mysql.* to mysqltest_1@localhost; +connect user_b,localhost,mysqltest_1,,; +connection user_b; grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; grant select on `my\_1`.* to mysqltest_3@localhost; +disconnect user_b; +connection default; grant insert on mysql.* to mysqltest_1@localhost; +connect user_c,localhost,mysqltest_1,,; +connection user_c; grant select on `my\_1`.* to mysqltest_3@localhost; grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass'; +disconnect user_c; +connection default; 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\_%'; @@ -27,6 +39,8 @@ delete from mysql.columns_priv where user like 'mysqltest\_%'; flush privileges; grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option; grant create user on *.* to mysqltest_1@localhost; +connect user1,localhost,mysqltest_1,,; +connection user1; select current_user(); current_user() mysqltest_1@localhost @@ -41,6 +55,8 @@ grant select on `my\_1`.* to mysqltest_4@localhost with grant option; ERROR 28000: Can't find any matching row in the user table grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass' with grant option; +disconnect user1; +connection default; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT CREATE USER ON *.* TO 'mysqltest_1'@'localhost' @@ -56,6 +72,8 @@ delete from mysql.db where user like 'mysqltest\_%'; flush privileges; 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(); current_user() mysqltest_1@localhost @@ -66,6 +84,8 @@ mysqltest_1 test grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option; ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest_1' +disconnect user2; +connection default; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' @@ -79,6 +99,8 @@ 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(); Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' @@ -91,16 +113,22 @@ ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table ' select * from t1; id data 1 I can't change it! +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; grant select (a,b) on t1 to mysqltest_2@localhost; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't1' grant select on t1 to mysqltest_3@localhost; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't1' +disconnect mrugly; +connection default; drop table t1; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; @@ -118,14 +146,24 @@ create user mysqltest_6@host6; create user mysqltest_7@host7; flush privileges; drop user mysqltest_3@host3; +connect con8,127.0.0.1,root,,test,$MASTER_MYPORT,; +disconnect con8; +connection default; drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4, mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7; +connect con9,127.0.0.1,root,,test,$MASTER_MYPORT,; +disconnect con9; +connection default; create database mysqltest_1; grant select, insert, update on `mysqltest\_1`.* to mysqltest_1@localhost; +connect con10,localhost,mysqltest_1,,; +connection con10; set sql_log_off = 1; ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation set sql_log_bin = 0; ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +disconnect con10; +connection default; delete from mysql.user where user like 'mysqltest\_1'; delete from mysql.db where user like 'mysqltest\_1'; drop database mysqltest_1; @@ -270,14 +308,20 @@ GRANT SELECT ON "mysql".* TO '%@a'@'a' drop user '%@a'@'a'; create user mysqltest_2@localhost; grant create user on *.* to mysqltest_2@localhost; +connect user3,localhost,mysqltest_2,,; +connection user3; select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 'user' create user mysqltest_A@'%'; rename user mysqltest_A@'%' to mysqltest_B@'%'; drop user mysqltest_B@'%'; +disconnect user3; +connection default; drop user mysqltest_2@localhost; create user mysqltest_3@localhost; grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost; +connect user4,localhost,mysqltest_3,,; +connection user4; show grants; Grants for mysqltest_3@localhost GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost' @@ -294,12 +338,16 @@ create user mysqltest_A@'%'; rename user mysqltest_B@'%' to mysqltest_C@'%'; drop user mysqltest_C@'%'; drop user mysqltest_A@'%'; +disconnect user4; +connection default; drop user mysqltest_3@localhost; set @@sql_mode=''; 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(); Grants for mysqltest_1@127.0.0.0/255.0.0.0 GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.0/255.0.0.0' @@ -309,15 +357,21 @@ i 1 2 3 +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; 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(); current_user() mysqltest_1@127.0.0.1 set password = password('changed'); +disconnect b12302; +connection default; select host, length(password) from mysql.user where user like 'mysqltest\_1'; host length(password) 127.0.0.1 41 @@ -325,10 +379,14 @@ 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(); current_user() mysqltest_1@127.0.0.0/255.0.0.0 set password = password('changed'); +disconnect b12302_2; +connection default; select host, length(password) from mysql.user where user like 'mysqltest\_1'; host length(password) 127.0.0.0/255.0.0.0 41 @@ -336,17 +394,38 @@ 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; +connect n5,localhost,test,,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection n5; set password = password("changed"); ERROR 42000: You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings +disconnect n5; +connection default; +connect con2root,localhost,root,,; +connect con3root,localhost,root,,; +connection default; lock table mysql.user write; +connection con2root; flush privileges; +connection con3root; grant all on *.* to 'mysqltest_1'@'localhost'; +connection default; unlock tables; +connection con2root; +connection con3root; +connection default; lock table mysql.user write; +connection con2root; set password for 'mysqltest_1'@'localhost' = password(''); +connection con3root; revoke all on *.* from 'mysqltest_1'@'localhost'; +connection default; unlock tables; +connection con2root; +connection con3root; +connection default; drop user 'mysqltest_1'@'localhost'; +disconnect con2root; +disconnect con3root; create database TESTDB; create table t2(a int); create temporary table t1 as select * from mysql.user; @@ -361,8 +440,11 @@ Warning 1364 Field 'authentication_string' doesn't have a default value INSERT INTO mysql.db (host, db, user, select_priv) VALUES ('%','TESTDB','mysqltest_1','Y'); FLUSH PRIVILEGES; +connect con1,localhost,mysqltest_1,password,TESTDB; create database TEStdb; Got one of the listed errors +connection default; +disconnect con1; delete from mysql.user; delete from mysql.db where host='%' and user='mysqltest_1' and db='TESTDB'; insert into mysql.user select * from t1; @@ -373,6 +455,8 @@ SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creato SET GLOBAL log_bin_trust_function_creators = 1; GRANT ALL PRIVILEGES ON test.* TO `a@`@localhost; GRANT EXECUTE ON * TO `a@`@localhost; +connect bug13310,localhost,'a@',,test; +connection bug13310; CREATE TABLE t2 (s1 INT); INSERT INTO t2 VALUES (1); DROP FUNCTION IF EXISTS f2; @@ -383,6 +467,8 @@ f2() 1 DROP FUNCTION f2; DROP TABLE t2; +disconnect bug13310; +connection default; REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost; DROP USER `a@`@localhost; SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; @@ -394,35 +480,48 @@ create database mysqltest_2; grant all on mysqltest_1.* to mysqltest_u1@localhost; use mysqltest_2; create table t1 (i int); +connect user1,localhost,mysqltest_u1,,mysqltest_1; +connection user1; show create table mysqltest_2.t1; ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't1' create table t1 like mysqltest_2.t1; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +connection default; grant select on mysqltest_2.t1 to mysqltest_u1@localhost; +connection user1; show create table mysqltest_2.t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 create table t1 like mysqltest_2.t1; +connection default; +disconnect user1; use test; drop database mysqltest_1; drop database mysqltest_2; drop user mysqltest_u1@localhost; grant all on `mysqltest\_%`.* to mysqltest_1@localhost with grant option; grant usage on *.* to mysqltest_2@localhost; +connect con18600_1,localhost,mysqltest_1,,; create database mysqltest_1; use mysqltest_1; create table t1 (f1 int); grant create on `mysqltest\_1`.* to mysqltest_2@localhost; grant select on mysqltest_1.t1 to mysqltest_2@localhost; +connect con3,localhost,mysqltest_2,,; +connection con3; create database mysqltest_3; ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest_3' use mysqltest_1; create table t2(f1 int); select * from t1; f1 +connection default; drop database mysqltest_1; +connection default; +disconnect con3; +disconnect con18600_1; revoke all privileges, grant option from mysqltest_1@localhost; revoke all privileges, grant option from mysqltest_2@localhost; drop user mysqltest_1@localhost; @@ -435,6 +534,8 @@ CREATE TABLE t2 (b INT, c INT); INSERT INTO t2 VALUES (1,100),(2,200); GRANT SELECT ON t1 TO mysqltest1@localhost; GRANT SELECT (b) ON t2 TO mysqltest1@localhost; +connect conn1,localhost,mysqltest1,,; +connection conn1; USE db1; SELECT c FROM t2; ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' @@ -442,6 +543,8 @@ SELECT * FROM t2; ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for table 't2' SELECT * FROM t1 JOIN t2 USING (b); ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' +connection default; +disconnect conn1; USE test; DROP TABLE db1.t1, db1.t2; DROP USER mysqltest1@localhost; @@ -530,8 +633,7 @@ CREATE TABLE mysqltest_db2.t2_1(a INT); # SQL-statement against temporary tables (loosely follow order in # sql_command enum). ########################################################################### - -# -- connect con1, mysqltest_u1@localhost, mysqltest_db1 +connect con1,localhost,mysqltest_u1,,mysqltest_db1; # # Variants of CREATE TABLE. # @@ -770,8 +872,7 @@ DROP TEMPORARY TABLE t1; # - Check that even outside of SUID context we can access temporary # table once it is created. ########################################################################### - -# -- connect con2, mysqltest_u2@localhost, mysqltest_db1 +connect con2,localhost,mysqltest_u2,,mysqltest_db1; CREATE TEMPORARY TABLE t2(a INT); ERROR 42000: Access denied for user 'mysqltest_u2'@'localhost' to database 'mysqltest_db1' CALL p1(); @@ -796,8 +897,7 @@ DROP TEMPORARY TABLE t4; # - Check that once table is created it can be accessed from within any # context, even by user without any privileges on tables. ########################################################################### - -# -- connect con3, mysqltest_u4@localhost, mysqltest_db1 +connect con3,localhost,mysqltest_u4,,mysqltest_db1; CALL p1(); INSERT INTO t4 VALUES (4); UPDATE t4 SET x = 10 WHERE x = 1; @@ -812,8 +912,7 @@ DROP TEMPORARY TABLE t4; # - SELECT_ACL, UPDATE_ACL and DELETE_ACL are required to include # a temporary table into the underlying-table-list. ########################################################################### - -# -- connect con4, mysqltest_u5@localhost, mysqltest_db1 +connect con4,localhost,mysqltest_u5,,mysqltest_db1; CREATE TEMPORARY TABLE t7(a INT); CREATE TEMPORARY TABLE t8(a INT); CREATE TEMPORARY TABLE t9(a INT); @@ -832,13 +931,12 @@ DROP TEMPORARY TABLE t9; ########################################################################### # That's all. Cleanup. ########################################################################### - -# -- connection: default -# -- disconnect con1 +connection default; # All remaining temporary tables are automatically dropped. -# -- disconnect con2 -# -- disconnect con3 -# -- disconnect con4 +disconnect con1; +disconnect con2; +disconnect con3; +disconnect con4; SET GLOBAL keycache1.key_buffer_size = 0; DROP DATABASE mysqltest_db1; DROP DATABASE mysqltest_db2; |