diff options
Diffstat (limited to 'mysql-test/t/grant.test')
| -rw-r--r-- | mysql-test/t/grant.test | 189 |
1 files changed, 189 insertions, 0 deletions
diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index e33712e1ebf..d00881bad99 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -1235,6 +1235,7 @@ DROP DATABASE mysqltest1; DROP DATABASE mysqltest2; DROP USER mysqltest_1@localhost; +DROP USER mysqltest_2@localhost; # # Bug#27878: Unchecked privileges on a view referring to a table from another @@ -1257,10 +1258,36 @@ UPDATE v1 SET f2 = 4; SELECT * FROM test.t1; disconnect user1; connection default; +REVOKE UPDATE (f1) ON `test`.`t1` FROM 'mysqltest_1'@'localhost'; +REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost'; +REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost'; +DROP USER mysqltest_1@localhost; DROP DATABASE db27878; use test; DROP TABLE t1; +# +# Bug #33201 Crash occurs when granting update privilege on one column of a view +# +drop table if exists test; +drop function if exists test_function; +drop view if exists v1; +create table test (col1 varchar(30)); +delimiter |; +create function test_function() returns varchar(30) +begin + declare tmp varchar(30); + select col1 from test limit 1 into tmp; + return '1'; +end| +delimiter ;| +create view v1 as select test.* from test where test.col1=test_function(); +grant update (col1) on v1 to 'greg'@'localhost'; +drop user 'greg'@'localhost'; +drop view v1; +drop table test; +drop function test_function; + --echo End of 5.0 tests # @@ -1274,3 +1301,165 @@ drop user юзер_юзер@localhost; --error ER_WRONG_STRING_LENGTH grant select on test.* to очень_длинный_юзер@localhost; set names default; + +# +# Bug #20901 - CREATE privilege is enough to insert into a table +# + +create database mysqltest; +use mysqltest; + +grant create on mysqltest.* to mysqltest@localhost; +create table t1 (i INT); + +connect (user1,localhost,mysqltest,,mysqltest); +connection user1; +# show we don't have INSERT +--error 1142 +insert into t1 values (1); +# show we have CREATE +create table t2 (i INT); +create table t4 (i INT); + +connection default; +grant select, insert on mysqltest.t2 to mysqltest@localhost; +grant insert on mysqltest.t4 to mysqltest@localhost; +# to specify ACLs for non-existent objects, must explictly |CREATE +grant create, insert on mysqltest.t5 to mysqltest@localhost; +grant create, insert on mysqltest.t6 to mysqltest@localhost; +flush privileges; + +connection user1; +insert into t2 values (1); + + +# CREATE IF NOT EXISTS...SELECT, t1 exists, no INSERT, must fail +--error 1142 +create table if not exists t1 select * from t2; + +# CREATE IF NOT EXISTS...SELECT, no t3 yet, no INSERT, must fail +--error 1142 +create table if not exists t3 select * from t2; + +# CREATE IF NOT EXISTS...SELECT, t4 exists, have INSERT, must succeed +create table if not exists t4 select * from t2; + +# CREATE IF NOT EXISTS...SELECT, no t5 yet, have INSERT, must succeed +create table if not exists t5 select * from t2; + + +# CREATE...SELECT, no t6 yet, have INSERT, must succeed +create table t6 select * from t2; + +# CREATE...SELECT, no t7 yet, no INSERT, must fail +--error 1142 +create table t7 select * from t2; + +# CREATE...SELECT, t4 exists, have INSERT, must still fail (exists) +--error 1050 +create table t4 select * from t2; + +# CREATE...SELECT, t1 exists, no INSERT, must fail +--error 1142 +create table t1 select * from t2; + + +connection default; +drop table t1,t2,t4,t5,t6; + +revoke create on mysqltest.* from mysqltest@localhost; +revoke select, insert on mysqltest.t2 from mysqltest@localhost; +revoke insert on mysqltest.t4 from mysqltest@localhost; +revoke create, insert on mysqltest.t5 from mysqltest@localhost; +revoke create, insert on mysqltest.t6 from mysqltest@localhost; +drop user mysqltest@localhost; + +disconnect user1; +drop database mysqltest; +use test; + + +# +# Bug #16470 crash on grant if old grant tables +# +--echo FLUSH PRIVILEGES without procs_priv table. +RENAME TABLE mysql.procs_priv TO mysql.procs_gone; +--error ER_NO_SUCH_TABLE +FLUSH PRIVILEGES; +--echo Assigning privileges without procs_priv table. +CREATE DATABASE mysqltest1; +CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER + SELECT 1; +--error ER_NO_SUCH_TABLE +GRANT EXECUTE ON FUNCTION mysqltest1.test TO mysqltest_1@localhost; +GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; +CALL mysqltest1.test(); +DROP DATABASE mysqltest1; +RENAME TABLE mysql.procs_gone TO mysql.procs_priv; +DROP USER mysqltest_1@localhost; +FLUSH PRIVILEGES; + + +# +# Bug#33464: DROP FUNCTION caused a crash. +# +CREATE DATABASE dbbug33464; +CREATE USER 'userbug33464'@'localhost'; + +GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost'; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (connbug33464, localhost, userbug33464, , dbbug33464); +--source suite/funcs_1/include/show_connection.inc + +--disable_warnings +DROP PROCEDURE IF EXISTS sp3; +DROP FUNCTION IF EXISTS fn1; +--enable_warnings + +delimiter //; +CREATE PROCEDURE sp3(v1 char(20)) +BEGIN + SELECT * from dbbug33464.t6 where t6.f2= 'xyz'; +END// +delimiter ;// + +delimiter //; +CREATE FUNCTION fn1() returns char(50) SQL SECURITY INVOKER +BEGIN + return 1; +END// +delimiter ;// + +delimiter //; +CREATE FUNCTION fn2() returns char(50) SQL SECURITY DEFINER +BEGIN + return 2; +END// +delimiter ;// + +disconnect connbug33464; + +# cleanup +connection default; +USE dbbug33464; +--source suite/funcs_1/include/show_connection.inc + +SELECT fn1(); +SELECT fn2(); + +--error 0, ER_CANNOT_USER +DROP USER 'userbug33464'@'localhost'; + +DROP FUNCTION fn1; +DROP FUNCTION fn2; +DROP PROCEDURE sp3; + +--error 0, ER_CANNOT_USER +DROP USER 'userbug33464'@'localhost'; + +use test; +DROP DATABASE dbbug33464; + + +--echo End of 5.1 tests |