diff options
Diffstat (limited to 'mysql-test/main/grant.test')
| -rw-r--r-- | mysql-test/main/grant.test | 2353 |
1 files changed, 2353 insertions, 0 deletions
diff --git a/mysql-test/main/grant.test b/mysql-test/main/grant.test new file mode 100644 index 00000000..69bb0507 --- /dev/null +++ b/mysql-test/main/grant.test @@ -0,0 +1,2353 @@ +# Test of GRANT commands + +# Grant tests not performed with embedded server +--source include/not_embedded.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; +SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; +SET GLOBAL log_bin_trust_function_creators = 1; +select priv into @root_priv from mysql.global_priv where user='root' and host='localhost'; + +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; +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; +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 "Monty Program 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 max_statement_time 60; +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 max_statement_time 0; +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; +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 ER_NONEXISTING_GRANT +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 ER_WRONG_USAGE +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.global_priv (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; + +# +# Bug#3086 SHOW GRANTS doesn't follow ANSI_QUOTES +# +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 ER_REVOKE_GRANTS +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 ER_REVOKE_GRANTS +revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost, +drop_user3@localhost, drop_user4@localhost; +--error ER_CANNOT_USER +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 encoding 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,,"*NO-ONE*",); +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 ER_COLUMNACCESS_DENIED_ERROR +update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1; +--error ER_COLUMNACCESS_DENIED_ERROR +update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1; +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1; +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1; +--error ER_COLUMNACCESS_DENIED_ERROR +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 ER_TABLEACCESS_DENIED_ERROR +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 ER_TABLEACCESS_DENIED_ERROR +update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200; +--error ER_TABLEACCESS_DENIED_ERROR +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; +disconnect conn2; + +# +# 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 ER_TABLEACCESS_DENIED_ERROR +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; +connection default; +disconnect root; + +# +# check all new table 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 ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost; +GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost; +--sorted_result +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; +--sorted_result +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; +--sorted_result +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; +--sorted_result +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 ER_NONEXISTING_GRANT +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 ER_TABLEACCESS_DENIED_ERROR +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); + +# Create some users with different hostnames +create user mysqltest_8@''; +create user mysqltest_8@host8; + +# Try to create them again +--error ER_CANNOT_USER +create user mysqltest_8@''; +--error ER_CANNOT_USER +create user mysqltest_8; +--error ER_CANNOT_USER +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@''; +grant select on test.* 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@''; +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@''; +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@''; +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 ER_NONEXISTING_GRANT +show grants for mysqltest_8@''; +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error ER_ACCESS_DENIED_ERROR +connect (conn6,localhost,mysqltest_8,,); +connection master; +--error ER_NONEXISTING_GRANT +show grants for mysqltest_8; +drop user mysqltest_8@host8; +--error ER_NONEXISTING_GRANT +show grants for mysqltest_8@host8; + +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,"*NO-ONE*"); + +# 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; +disconnect mysqltest_1; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost'; +DROP USER 'mysqltest_1'@'localhost'; + +# restore the original database +USE test; +connection default; +disconnect master; + + +# +# Bug#10668 CREATE USER does not enforce username length limit +# +--error ER_WRONG_STRING_LENGTH +create user longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789; + +# +# 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 longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +--error ER_WRONG_STRING_LENGTH +REVOKE CREATE ON mysqltest.* FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +# Working with table-level privileges. + +--error ER_WRONG_STRING_LENGTH +GRANT CREATE ON t1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +--error ER_WRONG_STRING_LENGTH +REVOKE CREATE ON t1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +# Working with routine-level privileges. + +--error ER_WRONG_STRING_LENGTH +GRANT EXECUTE ON PROCEDURE p1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +--error ER_WRONG_STRING_LENGTH +REVOKE EXECUTE ON PROCEDURE p1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + + +# +# 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; +connection default; +disconnect bug23556; + + +# +# 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,,) + +# - 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 +--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) + +--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) + +--error ER_TABLEACCESS_DENIED_ERROR +CREATE TABLE t1(c INT); + +SHOW TABLES; + +# Cleanup. + +--connection default +--disconnect bug27337_con2 + +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; + +DROP USER mysqltest_1@localhost; + +# Test case 2: privileges 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) + +SHOW TABLES FROM mysqltest1; + +PREPARE stmt1 FROM 'SHOW TABLES FROM mysqltest1'; + +EXECUTE stmt1; + +--connect (bug27337_con2,localhost,mysqltest_2,,mysqltest2) + +SHOW COLUMNS FROM mysqltest2.t2; + +PREPARE stmt2 FROM 'SHOW COLUMNS FROM mysqltest2.t2'; + +EXECUTE stmt2; + +--connection default + +REVOKE SELECT ON mysqltest1.t1 FROM mysqltest_1@localhost; +REVOKE SELECT ON mysqltest2.t2 FROM mysqltest_2@localhost; + +--connection bug27337_con1 + +--error ER_DBACCESS_DENIED_ERROR +SHOW TABLES FROM mysqltest1; + +--error ER_DBACCESS_DENIED_ERROR +EXECUTE stmt1; + +--connection bug27337_con2 + +--error ER_TABLEACCESS_DENIED_ERROR +SHOW COLUMNS FROM mysqltest2.t2; + +--error ER_TABLEACCESS_DENIED_ERROR +EXECUTE stmt2; + +# Cleanup. + +--connection default + +--disconnect bug27337_con1 +--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 |; +--enable_prepare_warnings +create function test_function() returns varchar(30) +begin + declare tmp varchar(30); + select col1 from test limit 1 into tmp; + return '1'; +end| +--disable_prepare_warnings +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; + +# +# Bug#41456 SET PASSWORD hates CURRENT_USER() +# +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; + +--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; + +--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; + +--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; + +--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'; +--connect (bug36544_con1,localhost,mysqluser1,,"*NO-ONE*") +call mysqltest1.p1(); +select mysqltest1.f1(); + +--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 # +--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 + +--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'; +--connect (bug36544_con2,localhost,mysqluser1,,"*NO-ONE*") +--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(); +--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'; +--connection bug36544_con2 +call mysqltest1.p1(); +select mysqltest1.f1(); +select * from mysqltest1.t11; +select * from mysqltest1.t22; + +--connection default +rename user mysqluser1@localhost to mysqluser10@localhost; + +--echo # +--echo # Test that there are no privileges left for mysqluser1. +--echo # +--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 + +--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'; +--connect (bug36544_con3,localhost,mysqluser1,,"*NO-ONE*") +--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 + +--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'; +--connect (bug36544_con4,localhost,mysqluser10,,"*NO-ONE*") +call mysqltest1.p1(); +select mysqltest1.f1(); +select * from mysqltest1.t11; +select * from mysqltest1.t22; +--disconnect bug36544_con4 + +--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 +set names utf8; +--error ER_WRONG_STRING_LENGTH +grant select on test.* to очень_длинный_юзер890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890@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 ER_TABLEACCESS_DENIED_ERROR +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 ER_TABLEACCESS_DENIED_ERROR +create table if not exists t1 select * from t2; + +# CREATE IF NOT EXISTS...SELECT, no t3 yet, no INSERT, must fail +--error ER_TABLEACCESS_DENIED_ERROR +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 ER_TABLEACCESS_DENIED_ERROR +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 ER_TABLEACCESS_DENIED_ERROR +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 +# + +call mtr.add_suppression("Can't open and lock privilege tables"); + +--echo FLUSH PRIVILEGES without procs_priv table. +RENAME TABLE mysql.procs_priv TO mysql.procs_gone; +FLUSH PRIVILEGES; +--echo Assigning privileges without procs_priv table. +CREATE DATABASE mysqltest1; +CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER + SELECT 1; +CREATE FUNCTION mysqltest1.test() RETURNS INT RETURN 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 + +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; + + +SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; + +# +# Bug#44658 Create procedure makes server crash when user does not have ALL privilege +# +CREATE USER user1; +CREATE USER user2; +GRANT CREATE ON db1.* TO 'user1'@'localhost'; +GRANT CREATE ROUTINE ON db1.* TO 'user1'@'localhost'; +GRANT CREATE ON db1.* TO 'user2'@'%'; +GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%'; +FLUSH PRIVILEGES; +SHOW GRANTS FOR 'user1'@'localhost'; +connect (con1,localhost,user1,,"*NO-ONE*"); +--echo ** Connect as user1 and create a procedure. +--echo ** The creation will imply implicitly assigned +--echo ** EXECUTE and ALTER ROUTINE privileges to +--echo ** the current user user1@localhost. +SELECT @@GLOBAL.sql_mode; +SELECT @@SESSION.sql_mode; +CREATE DATABASE db1; +DELIMITER ||; +CREATE PROCEDURE db1.proc1(p1 INT) + BEGIN + SET @x = 0; + REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; + END ;|| +DELIMITER ;|| + +connect (con2,localhost,user2,,"*NO-ONE*"); +--echo ** Connect as user2 and create a procedure. +--echo ** Implicitly assignment of privileges will +--echo ** fail because the user2@localhost is an +--echo ** unknown user. +DELIMITER ||; +CREATE PROCEDURE db1.proc2(p1 INT) + BEGIN + SET @x = 0; + REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; + END ;|| +DELIMITER ;|| + +connection default; +SHOW GRANTS FOR 'user1'@'localhost'; +SHOW GRANTS FOR 'user2'; +disconnect con1; +disconnect con2; +DROP PROCEDURE db1.proc1; +DROP PROCEDURE db1.proc2; +REVOKE ALL ON db1.* FROM 'user1'@'localhost'; +REVOKE ALL ON db1.* FROM 'user2'@'%'; +DROP USER 'user1'; +DROP USER 'user1'@'localhost'; +DROP USER 'user2'; +DROP DATABASE db1; + + +--echo # +--echo # Bug #25863 No database selected error, but documentation +--echo # says * for global allowed +--echo # + +connect(conn1,localhost,root,,*NO-ONE*); + +--error ER_NO_DB_ERROR +GRANT ALL ON * TO mysqltest_1; + +GRANT ALL ON *.* TO mysqltest_1; +SHOW GRANTS FOR mysqltest_1; +DROP USER mysqltest_1; + +USE test; + +GRANT ALL ON * TO mysqltest_1; +SHOW GRANTS FOR mysqltest_1; +DROP USER mysqltest_1; + +GRANT ALL ON *.* TO mysqltest_1; +SHOW GRANTS FOR mysqltest_1; +DROP USER mysqltest_1; + +connection default; +disconnect conn1; + + +# +# Bug #53371: COM_FIELD_LIST can be abused to bypass table level grants. +# + +CREATE DATABASE db1; +CREATE DATABASE db2; +GRANT SELECT ON db1.* to 'testbug'@localhost; +USE db2; +CREATE TABLE t1 (a INT); +USE test; +connect (con1,localhost,testbug,,db1); +--error ER_NO_SUCH_TABLE +SELECT * FROM `../db2/tb2`; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM `../db2`.tb2; +--error ER_WRONG_TABLE_NAME +SELECT * FROM `#mysql50#/../db2/tb2`; +connection default; +disconnect con1; +DROP USER 'testbug'@localhost; +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; + +--echo ######################################################################### +--echo # +--echo # Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE. +--echo # +--echo ######################################################################### + +--echo +--echo # -- +--echo # -- Prepare the environment. +--echo # -- + +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_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; + +CREATE TABLE mysqltest_db1.t1(a INT); + +--echo +--echo # -- +--echo # -- Check that global privileges don't allow SHOW CREATE TABLE. +--echo # -- + +GRANT EVENT ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT LOCK TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT ALTER ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT CREATE ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +GRANT FILE ON *.* TO mysqltest_u1@localhost; +GRANT CREATE USER ON *.* TO mysqltest_u1@localhost; +GRANT PROCESS ON *.* TO mysqltest_u1@localhost; +GRANT RELOAD ON *.* TO mysqltest_u1@localhost; +GRANT REPLICATION CLIENT ON *.* TO mysqltest_u1@localhost; +GRANT REPLICATION SLAVE ON *.* TO mysqltest_u1@localhost; +GRANT SHOW DATABASES ON *.* TO mysqltest_u1@localhost; +GRANT SHUTDOWN ON *.* TO mysqltest_u1@localhost; +GRANT USAGE ON *.* TO mysqltest_u1@localhost; +GRANT SLAVE MONITOR ON *.* TO mysqltest_u1@localhost; + +--echo +SHOW GRANTS FOR mysqltest_u1@localhost; + +--connect (con1,localhost,mysqltest_u1,,mysqltest_db1) +--connection con1 + +--echo +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE t1; + +--connection default + +--disconnect con1 + +--echo +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; +SHOW GRANTS FOR mysqltest_u1@localhost; + +--echo +--echo # -- +--echo # -- Check that global SELECT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global INSERT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global UPDATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global DELETE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global CREATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global DROP allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global ALTER allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global INDEX allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global REFERENCES allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global GRANT OPTION allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global CREATE VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global SHOW VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level SELECT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level INSERT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level UPDATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level DELETE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level CREATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level DROP allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level ALTER allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level INDEX allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level REFERENCES allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Cleanup. +--echo # -- + +--echo +DROP DATABASE mysqltest_db1; + +DROP USER mysqltest_u1@localhost; + +--echo +--echo # End of Bug#38347. +--echo + + +--echo # +--echo # BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES +--echo # DIFFERENTLY'. +--echo # +--disable_warnings +drop database if exists mysqltest_db1; +--enable_warnings +create database mysqltest_db1; +create user mysqltest_u1; +--echo # Both GRANT statements below should fail with the same error. +--error ER_SP_DOES_NOT_EXIST +grant execute on function mysqltest_db1.f1 to mysqltest_u1; +--error ER_SP_DOES_NOT_EXIST +grant execute on procedure mysqltest_db1.p1 to mysqltest_u1; +--echo # Let us show that GRANT behaviour for routines is consistent +--echo # with GRANT behaviour for tables. Attempt to grant privilege +--echo # on non-existent table also results in an error. +--error ER_NO_SUCH_TABLE +grant select on mysqltest_db1.t1 to mysqltest_u1; +show grants for mysqltest_u1; +drop database mysqltest_db1; +drop user mysqltest_u1; + + +--echo # +--echo # Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY - +--echo # REQUIRES FLUSH PRIVILEGES +--echo # + +CREATE USER foo@'127.0.0.1'; +GRANT ALL ON *.* TO foo@'127.0.0.1'; + +--echo # First attempt, should connect successfully +connect (conn1, '127.0.0.1', foo,,test); +SELECT user(), current_user(); + +--echo # Rename the user +RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0'; + +--echo # Second attempt, should connect successfully as its valid mask +--echo # This was failing without fix +connect (conn2, '127.0.0.1', foo,,test); +SELECT user(), current_user(); + +--echo # Rename the user back to original +RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1'; + +--echo # Third attempt, should connect successfully +connect (conn3, '127.0.0.1', foo,,test); +SELECT user(), current_user(); + +--echo # Clean-up +connection default; +disconnect conn1; +disconnect conn2; +disconnect conn3; +DROP USER foo@'127.0.0.1'; + +--echo # End of Bug#12766319 + +# +# Bug#27230925: HANDLE_FATAL_SIGNAL (SIG=11) IN SHOW_ROUTINE_GRANTS +# +create user foo@localhost; +create database foodb; +grant create routine on foodb.* to foo@localhost; +connect con1,localhost,foo,,foodb; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +rename table mysql.procs_priv to mysql.procs_priv1; +flush privileges; +show grants for foo@localhost; +rename table mysql.procs_priv1 to mysql.procs_priv; +show grants for foo@localhost; +flush privileges; +show grants for foo@localhost; +drop user foo@localhost; +drop procedure foodb.fooproc; +drop database foodb; + + +--echo # +--echo # Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS +--echo # DATABASE SECURITY +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS secret; +DROP DATABASE IF EXISTS no_such_db; +--enable_warnings + +CREATE DATABASE secret; +GRANT USAGE ON *.* TO untrusted@localhost; + +connect (con1, localhost, untrusted,,"*NO-ONE*"); +SHOW GRANTS; +SHOW DATABASES; + +--echo # Both statements below should fail with the same error. +--echo # They used to give different errors, thereby +--echo # hinting that the secret database exists. +--error ER_DBACCESS_DENIED_ERROR +CREATE PROCEDURE no_such_db.foo() BEGIN END; +--error ER_DBACCESS_DENIED_ERROR +CREATE PROCEDURE secret.peek_at_secret() BEGIN END; + +--connection default +disconnect con1; +DROP USER untrusted@localhost; +DROP DATABASE secret; + +set GLOBAL sql_mode=default; +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc + +--echo # +--echo # MDEV-22755 CREATE USER leads to indirect SIGABRT in __stack_chk_fail () from fill_schema_user_privileges + *** stack smashing detected *** (on optimized builds) +--echo # + +SET NAMES utf8; +SET SQL_MODE=''; +CREATE USER 觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻@localhost; +SELECT * FROM INFORMATION_SCHEMA.user_privileges WHERE GRANTEE LIKE "'abcdefghijklmnopqrstuvwxyz'%"; +SELECT GRANTEE FROM INFORMATION_SCHEMA.user_privileges WHERE GRANTEE LIKE '%觻%'; +DROP USER 觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻@localhost; +SET SQL_MODE=DEFAULT; + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-10134 Add full support for DEFAULT +--echo # + + +CREATE TABLE t1 (a VARCHAR(30) DEFAULT USER()); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (); +GRANT ALL PRIVILEGES ON test.* TO dummy@localhost IDENTIFIED BY 'pwd'; +connect (conn1,localhost,dummy,pwd,test); +connection conn1; +INSERT INTO t1 VALUES (); +connection default; +disconnect conn1; +INSERT INTO t1 VALUES (); +SELECT * FROM t1; +DROP TABLE t1; +DROP USER dummy@localhost; + +--echo # +--echo # MDEV-23082: ER_TABLEACCESS_DENIED_ERROR error message is truncated, and +--echo # inaccurately +--echo # + +CREATE USER foo; +CREATE DATABASE db; +CREATE TABLE db.t (a INT); + +--connect (con1,localhost,foo,,"*NO-ONE*") + +--error ER_TABLEACCESS_DENIED_ERROR +GRANT ALL ON db.t TO foo; + +--error ER_TABLEACCESS_DENIED_ERROR +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, REFERENCES, +INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON db.t TO foo; + +--connection default +--disconnect con1 + +DROP USER foo; +DROP TABLE db.t; +DROP DATABASE db; + +--echo # +--echo # Bug#33578113: DROP privilege on performance_schema.* can't be revoked +--echo # +connection default; +CREATE USER bug33578113; +GRANT DROP ON performance_schema.* TO bug33578113; +REVOKE DROP ON performance_schema.* FROM bug33578113; +DROP USER bug33578113; +--source include/wait_until_count_sessions.inc + +--echo # +--echo # End of 10.2 tests +--echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-19948 'show grants' return privileges individually +--echo # + +# Let's cheat server that we are using `10.2` user table +# which doesn't have `Delete_history_priv` column + +source include/switch_to_mysql_user.inc; +CREATE USER ten2; +GRANT ALL ON *.* TO ten2; + +# Without any patching, this should show a lot of privileges, +# but without delete history. With patch it is showing `all privileges` +SHOW GRANTS FOR ten2; +FLUSH PRIVILEGES; + +# Now should show `all privileges` with/without patch +SHOW GRANTS FOR ten2; +DROP USER ten2; +source include/switch_to_mysql_global_priv.inc; + +--echo # +--echo # End of 10.3 tests +--echo # + +--echo # +--echo # MDEV-17932 : Assertion upon double RENAME USER +--echo # +CREATE USER foo@localhost; +CREATE USER bar2@localhost; +RENAME USER foo@localhost TO bar1@localhost, bar1@localhost TO bar3@localhost; +DROP USER bar2@localhost; +DROP USER bar3@localhost; + +--echo # +--echo # MDEV-17946 : Unsorted acl_dbs after RENAME USER +--echo # +CREATE USER a; +CREATE USER foo; +GRANT SELECT ON test.* TO foo; +RENAME USER 'a' TO 'name'; +GRANT UPDATE ON test.* TO foo; +RENAME USER 'name' to 'a'; +DROP USER foo; +DROP USER a; + +--echo # +--echo # End of 10.4 tests +--echo # + +update mysql.global_priv set priv=@root_priv where user='root' and host='localhost'; |