# Test of GRANT commands # Grant tests not performed with embedded server -- source include/not_embedded.inc SET GLOBAL log_bin_trust_function_creators = 1; # Cleanup --disable_warnings drop table if exists t1; drop database if exists mysqltest; --enable_warnings connect (master,localhost,root,,); connection master; SET NAMES binary; # # Test that SSL options works properly # delete from mysql.user where user='mysqltest_1'; delete from mysql.db where user='mysqltest_1'; 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"; show grants for mysqltest_1@localhost; revoke delete on mysqltest.* from mysqltest_1@localhost; show grants for mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost require NONE; show grants for mysqltest_1@localhost; grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB"; show grants for mysqltest_1@localhost; revoke all privileges on mysqltest.* from mysqltest_1@localhost; show grants for mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; flush privileges; # # Test of GRANTS specifying user limits # 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"; 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"; show grants for mysqltest_1@localhost; # This is just to double check that one won't ignore results of selects flush privileges; show grants for mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; flush privileges; # # Test that the new db privileges are stored/retrieved correctly # grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost; show grants for mysqltest_1@localhost; flush privileges; show grants for mysqltest_1@localhost; revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost; show grants for mysqltest_1@localhost; grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION; flush privileges; show grants for mysqltest_1@localhost; revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost; show grants for mysqltest_1@localhost; revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; flush privileges; grant usage on test.* to mysqltest_1@localhost with grant option; show grants for mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; delete from mysql.db where user='mysqltest_1'; delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; --error 1141 show grants for mysqltest_1@localhost; # # Test what happens when you have same table and colum level grants # create table t1 (a int); GRANT select,update,insert on t1 to mysqltest_1@localhost; GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; show grants for mysqltest_1@localhost; select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; REVOKE select (a), update on t1 from mysqltest_1@localhost; show grants for mysqltest_1@localhost; REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost; show grants for mysqltest_1@localhost; GRANT select,references on t1 to mysqltest_1@localhost; select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; grant all on test.* to mysqltest_3@localhost with grant option; revoke all on test.* from mysqltest_3@localhost; show grants for mysqltest_3@localhost; revoke grant option on test.* from mysqltest_3@localhost; show grants for mysqltest_3@localhost; grant all on test.t1 to mysqltest_2@localhost with grant option; revoke all on test.t1 from mysqltest_2@localhost; show grants for mysqltest_2@localhost; revoke grant option on test.t1 from mysqltest_2@localhost; show grants for mysqltest_2@localhost; delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; flush privileges; drop table t1; # # Test some error conditions # --error 1221 GRANT FILE on mysqltest.* to mysqltest_1@localhost; select 1; # To test that the previous command didn't cause problems # # Bug #4898: User privileges depending on ORDER BY Settings of table db # insert into mysql.user (host, user) values ('localhost', 'test11'); insert into mysql.db (host, db, user, select_priv) values ('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y'); alter table mysql.db order by db asc; flush privileges; show grants for test11@localhost; alter table mysql.db order by db desc; flush privileges; show grants for test11@localhost; delete from mysql.user where user='test11'; delete from mysql.db where user='test11'; # # Bug#6123: GRANT USAGE inserts useless Db row # create database mysqltest1; grant usage on mysqltest1.* to test6123 identified by 'magic123'; select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1"; delete from mysql.user where user='test6123'; drop database mysqltest1; # # Test for 'drop user', 'revoke privileges, grant' # create table t1 (a int); grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION; show grants for drop_user2@localhost; revoke all privileges, grant option from drop_user2@localhost; drop user drop_user2@localhost; grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION; grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION; grant select(a) on test.t1 to drop_user@localhost; show grants for drop_user@localhost; # # Bug3086 # set sql_mode=ansi_quotes; show grants for drop_user@localhost; set sql_mode=default; set sql_quote_show_create=0; show grants for drop_user@localhost; set sql_mode="ansi_quotes"; show grants for drop_user@localhost; set sql_quote_show_create=1; show grants for drop_user@localhost; set sql_mode=""; show grants for drop_user@localhost; revoke all privileges, grant option from drop_user@localhost; show grants for drop_user@localhost; drop user drop_user@localhost; --error 1269 revoke all privileges, grant option from drop_user@localhost; grant select(a) on test.t1 to drop_user1@localhost; grant select on test.t1 to drop_user2@localhost; grant select on test.* to drop_user3@localhost; grant select on *.* to drop_user4@localhost; # Drop user now implicitly revokes all privileges. drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; --error 1269 revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; --error 1396 drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; drop table t1; grant usage on *.* to mysqltest_1@localhost identified by "password"; grant select, update, insert on test.* to mysqltest_1@localhost; show grants for mysqltest_1@localhost; drop user mysqltest_1@localhost; # # Bug #3403 Wrong encodin in SHOW GRANTS output # SET NAMES koi8r; CREATE DATABASE ��; USE ��; CREATE TABLE ��� (��� int); GRANT SELECT ON ��.* TO ����@localhost; SHOW GRANTS FOR ����@localhost; REVOKE SELECT ON ��.* FROM ����@localhost; GRANT SELECT ON ��.��� TO ����@localhost; SHOW GRANTS FOR ����@localhost; REVOKE SELECT ON ��.��� FROM ����@localhost; GRANT SELECT (���) ON ��.��� TO ����@localhost; SHOW GRANTS FOR ����@localhost; REVOKE SELECT (���) ON ��.��� FROM ����@localhost; # Revoke does not drop user. Leave a clean user table for the next tests. DROP USER ����@localhost; DROP DATABASE ��; SET NAMES latin1; # # Bug #5831: REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything # USE test; CREATE TABLE t1 (a int ); CREATE TABLE t2 LIKE t1; CREATE TABLE t3 LIKE t1; CREATE TABLE t4 LIKE t1; CREATE TABLE t5 LIKE t1; CREATE TABLE t6 LIKE t1; CREATE TABLE t7 LIKE t1; CREATE TABLE t8 LIKE t1; CREATE TABLE t9 LIKE t1; CREATE TABLE t10 LIKE t1; CREATE DATABASE testdb1; CREATE DATABASE testdb2; CREATE DATABASE testdb3; CREATE DATABASE testdb4; CREATE DATABASE testdb5; CREATE DATABASE testdb6; CREATE DATABASE testdb7; CREATE DATABASE testdb8; CREATE DATABASE testdb9; CREATE DATABASE testdb10; GRANT ALL ON testdb1.* TO testuser@localhost; GRANT ALL ON testdb2.* TO testuser@localhost; GRANT ALL ON testdb3.* TO testuser@localhost; GRANT ALL ON testdb4.* TO testuser@localhost; GRANT ALL ON testdb5.* TO testuser@localhost; GRANT ALL ON testdb6.* TO testuser@localhost; GRANT ALL ON testdb7.* TO testuser@localhost; GRANT ALL ON testdb8.* TO testuser@localhost; GRANT ALL ON testdb9.* TO testuser@localhost; GRANT ALL ON testdb10.* TO testuser@localhost; GRANT SELECT ON test.t1 TO testuser@localhost; GRANT SELECT ON test.t2 TO testuser@localhost; GRANT SELECT ON test.t3 TO testuser@localhost; GRANT SELECT ON test.t4 TO testuser@localhost; GRANT SELECT ON test.t5 TO testuser@localhost; GRANT SELECT ON test.t6 TO testuser@localhost; GRANT SELECT ON test.t7 TO testuser@localhost; GRANT SELECT ON test.t8 TO testuser@localhost; GRANT SELECT ON test.t9 TO testuser@localhost; GRANT SELECT ON test.t10 TO testuser@localhost; GRANT SELECT (a) ON test.t1 TO testuser@localhost; GRANT SELECT (a) ON test.t2 TO testuser@localhost; GRANT SELECT (a) ON test.t3 TO testuser@localhost; GRANT SELECT (a) ON test.t4 TO testuser@localhost; GRANT SELECT (a) ON test.t5 TO testuser@localhost; GRANT SELECT (a) ON test.t6 TO testuser@localhost; GRANT SELECT (a) ON test.t7 TO testuser@localhost; GRANT SELECT (a) ON test.t8 TO testuser@localhost; GRANT SELECT (a) ON test.t9 TO testuser@localhost; GRANT SELECT (a) ON test.t10 TO testuser@localhost; REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost; SHOW GRANTS FOR testuser@localhost; DROP USER testuser@localhost; DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; DROP DATABASE testdb1; DROP DATABASE testdb2; DROP DATABASE testdb3; DROP DATABASE testdb4; DROP DATABASE testdb5; DROP DATABASE testdb6; DROP DATABASE testdb7; DROP DATABASE testdb8; DROP DATABASE testdb9; DROP DATABASE testdb10; # # Bug #6932: a problem with 'revoke ALL PRIVILEGES' # create table t1(a int, b int, c int, d int); grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost; show grants for grant_user@localhost; select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name; revoke ALL PRIVILEGES on t1 from grant_user@localhost; show grants for grant_user@localhost; select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv; drop user grant_user@localhost; drop table t1; # # Bug#7391: Cross-database multi-table UPDATE security problem # create database mysqltest_1; create database mysqltest_2; create table mysqltest_1.t1 select 1 a, 2 q; create table mysqltest_1.t2 select 1 b, 2 r; create table mysqltest_2.t1 select 1 c, 2 s; create table mysqltest_2.t2 select 1 d, 2 t; #test the column privileges grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost; grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost; grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost; grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost; connect (conn1,localhost,mysqltest_3,,); connection conn1; SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE GRANTEE = '''mysqltest_3''@''localhost''' ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE; SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = '''mysqltest_3''@''localhost''' ORDER BY TABLE_NAME,PRIVILEGE_TYPE; SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE GRANTEE = '''mysqltest_3''@''localhost''' ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE; SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES WHERE GRANTEE = '''mysqltest_3''@''localhost''' ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE; --error 1143 update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1; --error 1143 update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1; --error 1142 update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1; --error 1142 update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1; --error 1143 update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2; #the following two should work update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10; update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20; connection master; select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2; select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2; revoke all on mysqltest_1.t1 from mysqltest_3@localhost; revoke all on mysqltest_1.t2 from mysqltest_3@localhost; revoke all on mysqltest_2.t1 from mysqltest_3@localhost; revoke all on mysqltest_2.t2 from mysqltest_3@localhost; #test the db/table level privileges grant all on mysqltest_2.* to mysqltest_3@localhost; grant select on *.* to mysqltest_3@localhost; # Next grant is needed to trigger bug#7391. Do not optimize! grant select on mysqltest_2.t1 to mysqltest_3@localhost; flush privileges; disconnect conn1; connect (conn2,localhost,mysqltest_3,,); connection conn2; use mysqltest_1; update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600; # the following failed before, should fail now. --error 1142 update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; use mysqltest_2; #the following used to succeed, it must fail now. --error 1142 update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; --error 1142 update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200; --error 1142 update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200; #lets see the result connection master; select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2; select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2; delete from mysql.user where user='mysqltest_3'; delete from mysql.db where user="mysqltest_3"; delete from mysql.tables_priv where user="mysqltest_3"; delete from mysql.columns_priv where user="mysqltest_3"; flush privileges; drop database mysqltest_1; drop database mysqltest_2; # # just SHOW PRIVILEGES test # SHOW PRIVILEGES; # # Rights for renaming test (Bug #3270) # connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; --disable_warnings create database mysqltest; --enable_warnings create table mysqltest.t1 (a int,b int,c int); grant all on mysqltest.t1 to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection user1; -- error 1142 alter table t1 rename t2; disconnect user1; connection root; revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; delete from mysql.user where user=_binary'mysqltest_1'; drop database mysqltest; # # check all new table priveleges # CREATE USER dummy@localhost; CREATE DATABASE mysqltest; CREATE TABLE mysqltest.dummytable (dummyfield INT); CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost; GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost; SHOW GRANTS FOR dummy@localhost; use INFORMATION_SCHEMA; SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; FLUSH PRIVILEGES; SHOW GRANTS FOR dummy@localhost; SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; SHOW FIELDS FROM mysql.tables_priv; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; DROP USER dummy@localhost; DROP DATABASE mysqltest; # check view only privileges CREATE USER dummy@localhost; CREATE DATABASE mysqltest; CREATE TABLE mysqltest.dummytable (dummyfield INT); CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost; GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost; SHOW GRANTS FOR dummy@localhost; use INFORMATION_SCHEMA; SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; FLUSH PRIVILEGES; SHOW GRANTS FOR dummy@localhost; SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; DROP USER dummy@localhost; DROP DATABASE mysqltest; CREATE USER dummy@localhost; CREATE DATABASE mysqltest; CREATE TABLE mysqltest.dummytable (dummyfield INT); CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost; GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost; SHOW GRANTS FOR dummy@localhost; use INFORMATION_SCHEMA; SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; FLUSH PRIVILEGES; SHOW GRANTS FOR dummy@localhost; SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; DROP USER dummy@localhost; DROP DATABASE mysqltest; # # Bug #11330: Entry in tables_priv with host = '' causes crash # connection default; use mysql; insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_grantor',CURRENT_TIMESTAMP,'Select','Select'); flush privileges; delete from tables_priv where host = '' and user = 'mysqltest_1'; flush privileges; use test; # # Bug #10892 user variables not auto cast for comparisons # Check that we don't get illegal mix of collations # set @user123="non-existent"; select * from mysql.db where user=@user123; set names koi8r; create database ��; grant select on ��.* to root@localhost; select hex(Db) from mysql.db where Db='��'; show grants for root@localhost; flush privileges; show grants for root@localhost; drop database ��; revoke all privileges on ��.* from root@localhost; show grants for root@localhost; set names latin1; # # Bug #15598 Server crashes in specific case during setting new password # - Caused by a user with host '' # create user mysqltest_7@; set password for mysqltest_7@ = password('systpass'); show grants for mysqltest_7@; drop user mysqltest_7@; --error 1141 show grants for mysqltest_7@; # # Bug#14385: GRANT and mapping to correct user account problems # create database mysqltest; use mysqltest; create table t1(f1 int); GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%'; GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%'; show grants for mysqltest1@'192.%'; show grants for mysqltest1@'%'; delete from mysql.user where user='mysqltest1'; delete from mysql.db where user='mysqltest1'; delete from mysql.tables_priv where user='mysqltest1'; flush privileges; drop database mysqltest; # # Bug #27515: DROP previlege is not required for RENAME TABLE # connection master; create database db27515; use db27515; create table t1 (a int); grant alter on db27515.t1 to user27515@localhost; grant insert, create on db27515.t2 to user27515@localhost; connect (conn27515, localhost, user27515, , db27515); connection conn27515; --error 1142 rename table t1 to t2; disconnect conn27515; connection master; revoke all privileges, grant option from user27515@localhost; drop user user27515@localhost; drop database db27515; --echo End of 4.1 tests # # Bug #16297 In memory grant tables not flushed when users's hostname is "" # use test; create table t1 (a int); # Backup anonymous users and remove them. (They get in the way of # the one we test with here otherwise.) create table t2 as select * from mysql.user where user=''; delete from mysql.user where user=''; flush privileges; # Create some users with different hostnames create user mysqltest_8@''; create user mysqltest_8; create user mysqltest_8@host8; # Try to create them again --error 1396 create user mysqltest_8@''; --error 1396 create user mysqltest_8; --error 1396 create user mysqltest_8@host8; select user, QUOTE(host) from mysql.user where user="mysqltest_8"; --echo Schema privileges grant select on mysqltest.* to mysqltest_8@''; show grants for mysqltest_8@''; grant select on mysqltest.* to mysqltest_8@; show grants for mysqltest_8@; grant select on mysqltest.* to mysqltest_8; show grants for mysqltest_8; select * from information_schema.schema_privileges where grantee like "'mysqltest_8'%"; connect (conn3,localhost,mysqltest_8,,); select * from t1; disconnect conn3; connection master; revoke select on mysqltest.* from mysqltest_8@''; revoke select on mysqltest.* from mysqltest_8; show grants for mysqltest_8@''; show grants for mysqltest_8; select * from information_schema.schema_privileges where grantee like "'mysqltest_8'%"; flush privileges; show grants for mysqltest_8@''; show grants for mysqltest_8@; grant select on mysqltest.* to mysqltest_8@''; flush privileges; show grants for mysqltest_8@; revoke select on mysqltest.* from mysqltest_8@''; flush privileges; --echo Column privileges grant update (a) on t1 to mysqltest_8@''; grant update (a) on t1 to mysqltest_8; show grants for mysqltest_8@''; show grants for mysqltest_8; flush privileges; show grants for mysqltest_8@''; show grants for mysqltest_8; select * from information_schema.column_privileges; connect (conn4,localhost,mysqltest_8,,); select * from t1; disconnect conn4; connection master; revoke update (a) on t1 from mysqltest_8@''; revoke update (a) on t1 from mysqltest_8; show grants for mysqltest_8@''; show grants for mysqltest_8; select * from information_schema.column_privileges; flush privileges; show grants for mysqltest_8@''; show grants for mysqltest_8; --echo Table privileges grant update on t1 to mysqltest_8@''; grant update on t1 to mysqltest_8; show grants for mysqltest_8@''; show grants for mysqltest_8; flush privileges; show grants for mysqltest_8@''; show grants for mysqltest_8; select * from information_schema.table_privileges; connect (conn5,localhost,mysqltest_8,,); select * from t1; disconnect conn5; connection master; revoke update on t1 from mysqltest_8@''; revoke update on t1 from mysqltest_8; show grants for mysqltest_8@''; show grants for mysqltest_8; select * from information_schema.table_privileges; flush privileges; show grants for mysqltest_8@''; show grants for mysqltest_8; --echo "DROP USER" should clear privileges grant all privileges on mysqltest.* to mysqltest_8@''; grant select on mysqltest.* to mysqltest_8@''; grant update on t1 to mysqltest_8@''; grant update (a) on t1 to mysqltest_8@''; grant all privileges on mysqltest.* to mysqltest_8; show grants for mysqltest_8@''; show grants for mysqltest_8; select * from information_schema.user_privileges where grantee like "'mysqltest_8'%"; connect (conn5,localhost,mysqltest_8,,); select * from t1; disconnect conn5; connection master; flush privileges; show grants for mysqltest_8@''; show grants for mysqltest_8; drop user mysqltest_8@''; --error 1141 show grants for mysqltest_8@''; show grants for mysqltest_8; select * from information_schema.user_privileges where grantee like "'mysqltest_8'%"; drop user mysqltest_8; --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT --error 1045 connect (conn6,localhost,mysqltest_8,,); connection master; --error 1141 show grants for mysqltest_8; drop user mysqltest_8@host8; --error 1141 show grants for mysqltest_8@host8; # Restore the anonymous users. insert into mysql.user select * from t2; flush privileges; drop table t2; drop table t1; # # Bug#20214: Incorrect error when user calls SHOW CREATE VIEW on non # privileged view # connection master; CREATE DATABASE mysqltest3; use mysqltest3; CREATE TABLE t_nn (c1 INT); CREATE VIEW v_nn AS SELECT * FROM t_nn; CREATE DATABASE mysqltest2; use mysqltest2; CREATE TABLE t_nn (c1 INT); CREATE VIEW v_nn AS SELECT * FROM t_nn; CREATE VIEW v_yn AS SELECT * FROM t_nn; CREATE VIEW v_gy AS SELECT * FROM t_nn; CREATE VIEW v_ny AS SELECT * FROM t_nn; CREATE VIEW v_yy AS SELECT * FROM t_nn WHERE c1=55; GRANT SHOW VIEW ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; GRANT SELECT ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; GRANT SELECT ON mysqltest2.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; connect (mysqltest_1, localhost, mysqltest_1, mysqltest_1,); # fail because of missing SHOW VIEW (have generic SELECT) --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE VIEW mysqltest2.v_nn; --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE mysqltest2.v_nn; # fail because of missing SHOW VIEW --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE VIEW mysqltest2.v_yn; --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE mysqltest2.v_yn; # succeed (despite of missing SELECT, having SHOW VIEW bails us out) SHOW CREATE TABLE mysqltest2.v_ny; # succeed (despite of missing SELECT, having SHOW VIEW bails us out) SHOW CREATE VIEW mysqltest2.v_ny; # fail because of missing (specific or generic) SELECT --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE mysqltest3.t_nn; # fail because of missing (specific or generic) SELECT (not because it's not a view!) --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE VIEW mysqltest3.t_nn; # fail because of missing missing (specific or generic) SELECT (and SHOW VIEW) --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE VIEW mysqltest3.v_nn; --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE mysqltest3.v_nn; # succeed thanks to generic SELECT SHOW CREATE TABLE mysqltest2.t_nn; # fail because it's not a view! (have generic SELECT though) --error ER_WRONG_OBJECT SHOW CREATE VIEW mysqltest2.t_nn; # succeed, have SELECT and SHOW VIEW SHOW CREATE VIEW mysqltest2.v_yy; # succeed, have SELECT and SHOW VIEW SHOW CREATE TABLE mysqltest2.v_yy; #clean-up connection master; # succeed, we're root SHOW CREATE TABLE mysqltest2.v_nn; SHOW CREATE VIEW mysqltest2.v_nn; SHOW CREATE TABLE mysqltest2.t_nn; # fail because it's not a view! --error ER_WRONG_OBJECT SHOW CREATE VIEW mysqltest2.t_nn; DROP VIEW mysqltest2.v_nn; DROP VIEW mysqltest2.v_yn; DROP VIEW mysqltest2.v_ny; DROP VIEW mysqltest2.v_yy; DROP TABLE mysqltest2.t_nn; DROP DATABASE mysqltest2; DROP VIEW mysqltest3.v_nn; DROP TABLE mysqltest3.t_nn; DROP DATABASE mysqltest3; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost'; DROP USER 'mysqltest_1'@'localhost'; # restore the original database use test; # # Bug #10668: CREATE USER does not enforce username length limit # --error ER_WRONG_STRING_LENGTH create user mysqltest1_thisisreallytoolong; # # Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause. # # These checks are intended to ensure that appropriate errors are risen when # illegal user name or hostname is specified in user-clause of GRANT/REVOKE # statements. # # # Bug #22369: Alter table rename combined with other alterations causes lost tables # CREATE DATABASE mysqltest1; CREATE TABLE mysqltest1.t1 ( int_field INTEGER UNSIGNED NOT NULL, char_field CHAR(10), INDEX(`int_field`) ); CREATE TABLE mysqltest1.t2 (int_field INT); --echo "Now check that we require equivalent grants for " --echo "RENAME TABLE and ALTER TABLE" CREATE USER mysqltest_1@localhost; GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost; connect (conn42,localhost,mysqltest_1,,mysqltest1); SELECT USER(); SHOW GRANTS; --error ER_TABLEACCESS_DENIED_ERROR RENAME TABLE t1 TO t2; --error ER_TABLEACCESS_DENIED_ERROR ALTER TABLE t1 RENAME TO t2; --disconnect conn42 --connection default GRANT DROP ON mysqltest1.t1 TO mysqltest_1@localhost; connect (conn42,localhost,mysqltest_1,,mysqltest1); --error ER_TABLEACCESS_DENIED_ERROR RENAME TABLE t1 TO t2; --error ER_TABLEACCESS_DENIED_ERROR ALTER TABLE t1 RENAME TO t2; --disconnect conn42 --connection default GRANT ALTER ON mysqltest1.t1 TO mysqltest_1@localhost; connect (conn42,localhost,mysqltest_1,,mysqltest1); SHOW GRANTS; --error ER_TABLEACCESS_DENIED_ERROR RENAME TABLE t1 TO t2; --error ER_TABLEACCESS_DENIED_ERROR ALTER TABLE t1 RENAME TO t2; --disconnect conn42 --connection default GRANT INSERT, CREATE ON mysqltest1.t1 TO mysqltest_1@localhost; connect (conn42,localhost,mysqltest_1,,mysqltest1); SHOW GRANTS; --error ER_TABLEACCESS_DENIED_ERROR --disconnect conn42 --connection default GRANT INSERT, SELECT, CREATE, ALTER, DROP ON mysqltest1.t2 TO mysqltest_1@localhost; DROP TABLE mysqltest1.t2; connect (conn42,localhost,mysqltest_1,,mysqltest1); SHOW GRANTS; RENAME TABLE t1 TO t2; RENAME TABLE t2 TO t1; ALTER TABLE t1 RENAME TO t2; ALTER TABLE t2 RENAME TO t1; --disconnect conn42 --connection default REVOKE DROP, INSERT ON mysqltest1.t1 FROM mysqltest_1@localhost; REVOKE DROP, INSERT ON mysqltest1.t2 FROM mysqltest_1@localhost; connect (conn42,localhost,mysqltest_1,,mysqltest1); SHOW GRANTS; --error ER_TABLEACCESS_DENIED_ERROR RENAME TABLE t1 TO t2; --error ER_TABLEACCESS_DENIED_ERROR ALTER TABLE t1 RENAME TO t2; --disconnect conn42 --connection default DROP USER mysqltest_1@localhost; DROP DATABASE mysqltest1; USE test; # Working with database-level privileges. --error ER_WRONG_STRING_LENGTH GRANT CREATE ON mysqltest.* TO 1234567890abcdefGHIKL@localhost; --error ER_WRONG_STRING_LENGTH GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; --error ER_WRONG_STRING_LENGTH REVOKE CREATE ON mysqltest.* FROM 1234567890abcdefGHIKL@localhost; --error ER_WRONG_STRING_LENGTH REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; # Working with table-level privileges. --error ER_WRONG_STRING_LENGTH GRANT CREATE ON t1 TO 1234567890abcdefGHIKL@localhost; --error ER_WRONG_STRING_LENGTH GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; --error ER_WRONG_STRING_LENGTH REVOKE CREATE ON t1 FROM 1234567890abcdefGHIKL@localhost; --error ER_WRONG_STRING_LENGTH REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; # Working with routine-level privileges. --error ER_WRONG_STRING_LENGTH GRANT EXECUTE ON PROCEDURE p1 TO 1234567890abcdefGHIKL@localhost; --error ER_WRONG_STRING_LENGTH GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; --error ER_WRONG_STRING_LENGTH REVOKE EXECUTE ON PROCEDURE p1 FROM 1234567890abcdefGHIKL@localhost; --error ER_WRONG_STRING_LENGTH REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; # # BUG#23556: TRUNCATE TABLE still maps to DELETE # CREATE USER bug23556@localhost; CREATE DATABASE bug23556; GRANT SELECT ON bug23556.* TO bug23556@localhost; connect (bug23556,localhost,bug23556,,bug23556); connection default; USE bug23556; CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (1),(2),(3),(4),(5); GRANT DELETE ON t1 TO bug23556@localhost; connection bug23556; USE bug23556; --error ER_TABLEACCESS_DENIED_ERROR TRUNCATE t1; connection default; USE bug23556; REVOKE DELETE ON t1 FROM bug23556@localhost; GRANT DROP ON t1 TO bug23556@localhost; connection bug23556; USE bug23556; TRUNCATE t1; connection default; USE bug23556; DROP TABLE t1; USE test; DROP DATABASE bug23556; DROP USER bug23556@localhost; # # Bug #6774: Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES # # Check if GRANT ... ON * ... fails when no database is selected connect (con1, localhost, root,,*NO-ONE*); connection con1; --error ER_NO_DB_ERROR GRANT PROCESS ON * TO user@localhost; disconnect con1; connection default; # # BUG#9504: Stored procedures: execute privilege doesn't make 'use database' # okay. # # Prepare. --disable_warnings DROP DATABASE IF EXISTS mysqltest1; DROP DATABASE IF EXISTS mysqltest2; DROP DATABASE IF EXISTS mysqltest3; DROP DATABASE IF EXISTS mysqltest4; --enable_warnings CREATE DATABASE mysqltest1; CREATE DATABASE mysqltest2; CREATE DATABASE mysqltest3; CREATE DATABASE mysqltest4; CREATE PROCEDURE mysqltest1.p_def() SQL SECURITY DEFINER SELECT 1; CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER SELECT 1; CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER RETURN 1; CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER RETURN 1; GRANT EXECUTE ON PROCEDURE mysqltest1.p_def TO mysqltest_1@localhost; GRANT EXECUTE ON PROCEDURE mysqltest2.p_inv TO mysqltest_1@localhost; GRANT EXECUTE ON FUNCTION mysqltest3.f_def TO mysqltest_1@localhost; GRANT EXECUTE ON FUNCTION mysqltest4.f_inv TO mysqltest_1@localhost; GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; # Test. --connect (bug9504_con1,localhost,mysqltest_1,,) --echo --echo ---> connection: bug9504_con1 # - Check that we can switch to the db; use mysqltest1; use mysqltest2; use mysqltest3; use mysqltest4; # - Check that we can call stored routines; use test; CALL mysqltest1.p_def(); CALL mysqltest2.p_inv(); SELECT mysqltest3.f_def(); SELECT mysqltest4.f_inv(); # Cleanup. --connection default --echo --echo ---> connection: default --disconnect bug9504_con1 DROP DATABASE mysqltest1; DROP DATABASE mysqltest2; DROP DATABASE mysqltest3; DROP DATABASE mysqltest4; DROP USER mysqltest_1@localhost; # # BUG#27337: Privileges are not restored properly. # # Actually, the patch for this bugs fixes two problems. So, here are two test # cases. # Test case 1: privileges are not restored properly after calling a stored # routine defined with SQL SECURITY INVOKER clause. # Prepare. --disable_warnings DROP DATABASE IF EXISTS mysqltest1; DROP DATABASE IF EXISTS mysqltest2; --enable_warnings CREATE DATABASE mysqltest1; CREATE DATABASE mysqltest2; GRANT ALL PRIVILEGES ON mysqltest1.* TO mysqltest_1@localhost; GRANT SELECT ON mysqltest2.* TO mysqltest_1@localhost; CREATE PROCEDURE mysqltest1.p1() SQL SECURITY INVOKER SELECT 1; # Test. --connect (bug27337_con1,localhost,mysqltest_1,,mysqltest2) --echo --echo ---> connection: bug27337_con1 --error ER_TABLEACCESS_DENIED_ERROR CREATE TABLE t1(c INT); CALL mysqltest1.p1(); --error ER_TABLEACCESS_DENIED_ERROR CREATE TABLE t1(c INT); --disconnect bug27337_con1 --connect (bug27337_con2,localhost,mysqltest_1,,mysqltest2) --echo --echo ---> connection: bug27337_con2 --error ER_TABLEACCESS_DENIED_ERROR CREATE TABLE t1(c INT); SHOW TABLES; # Cleanup. --connection default --echo --echo ---> connection: default --disconnect bug27337_con2 DROP DATABASE mysqltest1; DROP DATABASE mysqltest2; DROP USER mysqltest_1@localhost; # Test case 2: priveleges are not checked properly for prepared statements. # Prepare. --disable_warnings DROP DATABASE IF EXISTS mysqltest1; DROP DATABASE IF EXISTS mysqltest2; --enable_warnings CREATE DATABASE mysqltest1; CREATE DATABASE mysqltest2; CREATE TABLE mysqltest1.t1(c INT); CREATE TABLE mysqltest2.t2(c INT); GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost; GRANT SELECT ON mysqltest2.t2 TO mysqltest_2@localhost; # Test. --connect (bug27337_con1,localhost,mysqltest_1,,mysqltest1) --echo --echo ---> connection: bug27337_con1 SHOW TABLES FROM mysqltest1; PREPARE stmt1 FROM 'SHOW TABLES FROM mysqltest1'; EXECUTE stmt1; --connect (bug27337_con2,localhost,mysqltest_2,,mysqltest2) --echo --echo ---> connection: bug27337_con2 SHOW COLUMNS FROM mysqltest2.t2; PREPARE stmt2 FROM 'SHOW COLUMNS FROM mysqltest2.t2'; EXECUTE stmt2; --connection default --echo --echo ---> connection: default REVOKE SELECT ON mysqltest1.t1 FROM mysqltest_1@localhost; REVOKE SELECT ON mysqltest2.t2 FROM mysqltest_2@localhost; --connection bug27337_con1 --echo --echo ---> connection: bug27337_con1 --error ER_DBACCESS_DENIED_ERROR SHOW TABLES FROM mysqltest1; --error ER_DBACCESS_DENIED_ERROR EXECUTE stmt1; --connection bug27337_con2 --echo --echo ---> connection: bug27337_con2 --error ER_TABLEACCESS_DENIED_ERROR SHOW COLUMNS FROM mysqltest2.t2; --error ER_TABLEACCESS_DENIED_ERROR EXECUTE stmt2; # Cleanup. --connection default --echo --echo ---> connection: default --disconnect bug27337_con2 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 # database. # use test; CREATE TABLE t1 (f1 int, f2 int); INSERT INTO t1 VALUES(1,1), (2,2); CREATE DATABASE db27878; GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost'; GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost'; GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost'; use db27878; CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1; connect (user1,localhost,mysqltest_1,,test); connection user1; use db27878; --error 1356 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; --echo # --echo # Bug#33275 Server crash when creating temporary table mysql.user --echo # CREATE TEMPORARY TABLE mysql.user (id INT); FLUSH PRIVILEGES; DROP TABLE mysql.user; # # 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 # # Bug#21432: Database/Table name limited to 64 bytes, not chars, problems with multi-byte # set names utf8; grant select on test.* to юзер_юзер@localhost; --exec $MYSQL --default-character-set=utf8 --user=юзер_юзер -e "select user()" revoke all on test.* from юзер_юзер@localhost; 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; --echo End of 5.1 tests