diff options
Diffstat (limited to 'mysql-test/t/grant.test')
| -rw-r--r-- | mysql-test/t/grant.test | 299 |
1 files changed, 291 insertions, 8 deletions
diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index b1cd9145e42..20284ec9ae9 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -29,7 +29,7 @@ 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"; +query_vertical 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; @@ -48,10 +48,10 @@ flush privileges; delete from mysql.user where user='mysqltest_1'; flush privileges; grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10; -select * from mysql.user where user="mysqltest_1"; +query_vertical select * from mysql.user where user="mysqltest_1"; show grants for mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30; -select * from mysql.user where user="mysqltest_1"; +query_vertical select * from mysql.user where user="mysqltest_1"; show grants for mysqltest_1@localhost; # This is just to double check that one won't ignore results of selects flush privileges; @@ -1295,6 +1295,284 @@ SELECT CURRENT_USER(); SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin"); SET PASSWORD FOR CURRENT_USER() = PASSWORD(""); +# +# Bug#57952: privilege change is not taken into account by EXECUTE. +# + +--echo +--echo # Bug#57952 +--echo + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +--enable_warnings + +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; + +use mysqltest1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 1); + +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (2); + +CREATE TABLE mysqltest2.t3(a INT); +INSERT INTO mysqltest2.t3 VALUES (4); + +CREATE USER testuser@localhost; +GRANT CREATE ROUTINE, EXECUTE ON mysqltest1.* TO testuser@localhost; +GRANT SELECT(b) ON t1 TO testuser@localhost; +GRANT SELECT ON t2 TO testuser@localhost; +GRANT SELECT ON mysqltest2.* TO testuser@localhost; + +--echo +--echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1) +--connect (bug57952_con1,localhost,testuser,,mysqltest1) +PREPARE s1 FROM 'SELECT b FROM t1'; +PREPARE s2 FROM 'SELECT a FROM t2'; +PREPARE s3 FROM 'SHOW TABLES FROM mysqltest2'; + +CREATE PROCEDURE p1() SELECT b FROM t1; +CREATE PROCEDURE p2() SELECT a FROM t2; +CREATE PROCEDURE p3() SHOW TABLES FROM mysqltest2; + +CALL p1; +CALL p2; +CALL p3; + +--echo +--echo # Connection: default +--connection default +REVOKE SELECT ON t1 FROM testuser@localhost; +GRANT SELECT(a) ON t1 TO testuser@localhost; +REVOKE SELECT ON t2 FROM testuser@localhost; +REVOKE SELECT ON mysqltest2.* FROM testuser@localhost; + +--echo +--echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1) +--connection bug57952_con1 +--echo # - Check column-level privileges... +--error ER_COLUMNACCESS_DENIED_ERROR +EXECUTE s1; + +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT b FROM t1; + +--error ER_COLUMNACCESS_DENIED_ERROR +EXECUTE s1; + +--error ER_COLUMNACCESS_DENIED_ERROR +CALL p1; + +--echo # - Check table-level privileges... +--error ER_TABLEACCESS_DENIED_ERROR +SELECT a FROM t2; + +--error ER_TABLEACCESS_DENIED_ERROR +EXECUTE s2; + +--error ER_TABLEACCESS_DENIED_ERROR +CALL p2; + +--echo # - Check database-level privileges... +--error ER_DBACCESS_DENIED_ERROR +SHOW TABLES FROM mysqltest2; + +--error ER_DBACCESS_DENIED_ERROR +EXECUTE s3; + +--error ER_DBACCESS_DENIED_ERROR +CALL p3; + +--echo +--echo # Connection: default +--connection default +--disconnect bug57952_con1 +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +DROP USER testuser@localhost; +use test; +--echo + + +--echo # +--echo # Test for bug #36544 "DROP USER does not remove stored function +--echo # privileges". +--echo # +create database mysqltest1; +create function mysqltest1.f1() returns int return 0; +create procedure mysqltest1.p1() begin end; +--echo # +--echo # 1) Check that DROP USER properly removes privileges on both +--echo # stored procedures and functions. +--echo # +create user mysqluser1@localhost; +grant execute on function mysqltest1.f1 to mysqluser1@localhost; +grant execute on procedure mysqltest1.p1 to mysqluser1@localhost; + +--echo # Quick test that granted privileges are properly reflected +--echo # in privilege tables and in in-memory structures. +show grants for mysqluser1@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; +--echo # +--echo # Create connection 'bug_36544_con1' as 'mysqluser1@localhost'. +--connect (bug36544_con1,localhost,mysqluser1,,) +call mysqltest1.p1(); +select mysqltest1.f1(); + +--echo # +--echo # Switch to connection 'default'. +--connection default +drop user mysqluser1@localhost; + +--echo # +--echo # Test that dropping of user is properly reflected in +--echo # both privilege tables and in in-memory structures. +--echo # +--echo # Switch to connection 'bug36544_con1'. +--connection bug36544_con1 +--echo # The connection cold be alive but should not be able to +--echo # access to any of the stored routines. +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest1.p1(); +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest1.f1(); +--disconnect bug36544_con1 + +--echo # +--echo # Switch to connection 'default'. +--connection default +--echo # +--echo # Now create user with the same name and check that he +--echo # has not inherited privileges. +create user mysqluser1@localhost; +show grants for mysqluser1@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; +--echo # +--echo # Create connection 'bug_36544_con2' as 'mysqluser1@localhost'. +--connect (bug36544_con2,localhost,mysqluser1,,) +--echo # Newly created user should not be able to access any of the routines. +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest1.p1(); +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest1.f1(); +--echo # +--echo # Switch to connection 'default'. +--connection default + +--echo # +--echo # 2) Check that RENAME USER properly updates privileges on both +--echo # stored procedures and functions. +--echo # +grant execute on function mysqltest1.f1 to mysqluser1@localhost; +grant execute on procedure mysqltest1.p1 to mysqluser1@localhost; +--echo # +--echo # Create one more user to make in-memory hashes non-trivial. +--echo # User names 'mysqluser11' and 'mysqluser10' were selected +--echo # to trigger bug discovered during code inspection. +create user mysqluser11@localhost; +grant execute on function mysqltest1.f1 to mysqluser11@localhost; +grant execute on procedure mysqltest1.p1 to mysqluser11@localhost; +--echo # Also create a couple of tables to test for another bug +--echo # discovered during code inspection (again table names were +--echo # chosen especially to trigger the bug). +create table mysqltest1.t11 (i int); +create table mysqltest1.t22 (i int); +grant select on mysqltest1.t22 to mysqluser1@localhost; +grant select on mysqltest1.t11 to mysqluser1@localhost; + +--echo # Quick test that granted privileges are properly reflected +--echo # in privilege tables and in in-memory structures. +show grants for mysqluser1@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; +select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost'; +--echo # +--echo # Switch to connection 'bug36544_con2'. +--connection bug36544_con2 +call mysqltest1.p1(); +select mysqltest1.f1(); +select * from mysqltest1.t11; +select * from mysqltest1.t22; + +--echo # +--echo # Switch to connection 'default'. +--connection default +rename user mysqluser1@localhost to mysqluser10@localhost; + +--echo # +--echo # Test that there are no privileges left for mysqluser1. +--echo # +--echo # Switch to connection 'bug36544_con2'. +--connection bug36544_con2 +--echo # The connection cold be alive but should not be able to +--echo # access to any of the stored routines or tables. +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest1.p1(); +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest1.f1(); +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest1.t11; +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest1.t22; +--disconnect bug36544_con2 + +--echo # +--echo # Switch to connection 'default'. +--connection default +--echo # +--echo # Now create user with the old name and check that he +--echo # has not inherited privileges. +create user mysqluser1@localhost; +show grants for mysqluser1@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; +select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost'; +--echo # +--echo # Create connection 'bug_36544_con3' as 'mysqluser1@localhost'. +--connect (bug36544_con3,localhost,mysqluser1,,) +--echo # Newly created user should not be able to access to any of the +--echo # stored routines or tables. +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest1.p1(); +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest1.f1(); +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest1.t11; +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest1.t22; +--disconnect bug36544_con3 + +--echo # +--echo # Switch to connection 'default'. +--connection default +--echo # +--echo # Now check that privileges became associated with a new user +--echo # name - mysqluser10. +--echo # +show grants for mysqluser10@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser10' and host='localhost'; +select db, table_name, table_priv from mysql.tables_priv where user='mysqluser10' and host='localhost'; +--echo # +--echo # Create connection 'bug_36544_con4' as 'mysqluser10@localhost'. +--connect (bug36544_con4,localhost,mysqluser10,,) +call mysqltest1.p1(); +select mysqltest1.f1(); +select * from mysqltest1.t11; +select * from mysqltest1.t22; +--disconnect bug36544_con4 + +--echo # +--echo # Switch to connection 'default'. +--connection default +--echo # +--echo # Clean-up. +drop user mysqluser1@localhost; +drop user mysqluser10@localhost; +drop user mysqluser11@localhost; +drop database mysqltest1; + + --echo End of 5.0 tests # @@ -1419,11 +1697,6 @@ GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost'; 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 @@ -1578,6 +1851,16 @@ DROP TABLE db2.t1; DROP DATABASE db1; DROP DATABASE db2; +--echo # +--echo # Bug #36742 +--echo # +grant usage on Foo.* to myuser@Localhost identified by 'foo'; +grant select on Foo.* to myuser@localhost; +select host,user from mysql.user where User='myuser'; +revoke select on Foo.* from myuser@localhost; +delete from mysql.user where User='myuser'; +flush privileges; + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc |