diff options
Diffstat (limited to 'mysql-test/t/view_grant.test')
| -rw-r--r-- | mysql-test/t/view_grant.test | 34 |
1 files changed, 31 insertions, 3 deletions
diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 8907c726ae3..6d0cd8e5c28 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -11,6 +11,7 @@ drop view if exists v1,v2,v3; # simple test of grants +create user test@localhost; grant create view on test.* to test@localhost; show grants for test@localhost; revoke create view on test.* from test@localhost; @@ -29,6 +30,7 @@ create database mysqltest; create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); +create user mysqltest_1@localhost; grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; @@ -90,7 +92,7 @@ select d from mysqltest.v1; connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; +drop user mysqltest_1@localhost; drop database mysqltest; # TEMPORARY TABLE algorithm @@ -100,6 +102,7 @@ create database mysqltest; create table mysqltest.t1 (a int, b int); create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; +create user mysqltest_1@localhost; grant select (c) on mysqltest.v1 to mysqltest_1@localhost; connection user1; @@ -110,7 +113,7 @@ select d from mysqltest.v1; connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -129,6 +132,7 @@ create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; # v5: SHOW VIEW, but no SELECT create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1; +create user mysqltest_1@localhost; grant select on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.v2 to mysqltest_1@localhost; grant select on mysqltest.v3 to mysqltest_1@localhost; @@ -225,7 +229,7 @@ show create view mysqltest.v4; connection root; revoke all privileges on mysqltest.* from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -244,6 +248,7 @@ create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; +create user mysqltest_1@localhost; grant update (a) on mysqltest.v2 to mysqltest_1@localhost; grant update on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; @@ -274,6 +279,7 @@ update v3 set a=a+c; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -291,6 +297,7 @@ insert into mysqltest.t2 values (3), (4), (5), (6); create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; +create user mysqltest_1@localhost; grant delete on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; @@ -310,6 +317,7 @@ delete from v2 where c < 4; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -327,6 +335,7 @@ insert into mysqltest.t2 values (3,4); create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; +create user mysqltest_1@localhost; grant insert on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; @@ -346,6 +355,7 @@ insert into v2 select x,y from t2; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -359,6 +369,7 @@ create database mysqltest; create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); +create user mysqltest_1@localhost; grant update on mysqltest.t1 to mysqltest_1@localhost; grant update(b) on mysqltest.t2 to mysqltest_1@localhost; grant create view,update on test.* to mysqltest_1@localhost; @@ -407,6 +418,7 @@ create view v4 as select b+1 from mysqltest.t2; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; drop view v1,v2,v4; @@ -418,6 +430,7 @@ connection root; create database mysqltest; --enable_warnings create table mysqltest.t1 (a int); +create user mysqltest_1@localhost; grant all privileges on mysqltest.* to mysqltest_1@localhost; connection user1; @@ -427,6 +440,7 @@ use test; connection root; revoke all privileges on mysqltest.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -439,6 +453,7 @@ create database mysqltest; create table mysqltest.t1 (a int, b int); +create user mysqltest_1@localhost; grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; @@ -455,6 +470,7 @@ select * from v1; grant select on mysqltest.t1 to mysqltest_1@localhost; select * from v1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop view v1; drop database mysqltest; @@ -482,6 +498,7 @@ create algorithm=MERGE view v2 as select f2() from t1; create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; create SQL SECURITY INVOKER view v5 as select * from v4; +create user mysqltest_1@localhost; grant select on v1 to mysqltest_1@localhost; grant select on v2 to mysqltest_1@localhost; grant select on v3 to mysqltest_1@localhost; @@ -506,6 +523,7 @@ drop function f2; drop table t1, t2; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -529,6 +547,7 @@ delimiter //; create function f2 () returns int begin declare v int; select s1 from t2 into v; return v; end// delimiter ;// +create user mysqltest_1@localhost; grant select on t1 to mysqltest_1@localhost; grant execute on function f2 to mysqltest_1@localhost; grant create view on mysqltest.* to mysqltest_1@localhost; @@ -558,6 +577,7 @@ drop function f2; drop table t1, t2; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -572,6 +592,7 @@ use mysqltest; create table t1 (a int); create table v1 (a int); insert into t1 values (1); +create user mysqltest_1@localhost; grant select on t1 to mysqltest_1@localhost; grant select on v1 to mysqltest_1@localhost; grant create view on mysqltest.* to mysqltest_1@localhost; @@ -601,6 +622,7 @@ select * from v5; drop table t1; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; @@ -617,6 +639,7 @@ create algorithm=MERGE view v2 as select *, a as b from t1; create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; create SQL SECURITY INVOKER view v5 as select * from v4; +create user mysqltest_1@localhost; grant select on v1 to mysqltest_1@localhost; grant select on v2 to mysqltest_1@localhost; grant select on v3 to mysqltest_1@localhost; @@ -640,6 +663,7 @@ drop view v1, v2, v3, v4, v5; drop table t1; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -657,6 +681,7 @@ delete from mysql.user where user=''; flush privileges; # Create the test user +create user 'test14256'@'%'; grant all on test.* to 'test14256'@'%'; connect (test14256,localhost,test14256,,test); @@ -702,6 +727,7 @@ use mysqltest; CREATE TABLE t1 (i INT); CREATE VIEW v1 AS SELECT * FROM t1; SHOW CREATE VIEW v1; +create user mysqltest_1@localhost; GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; connection user1; @@ -1162,6 +1188,7 @@ INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24); INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34); INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44); +CREATE USER mysqltest_u1@localhost; GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost; GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost; GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost; @@ -1237,6 +1264,7 @@ USE db1; CREATE TABLE t1(f1 INT, f2 INT); CREATE VIEW v1 AS SELECT f1, f2 FROM t1; +CREATE USER foo; GRANT SELECT (f1) ON t1 TO foo; GRANT SELECT (f1) ON v1 TO foo; |