path: root/mysql-test/main/grant.test

# 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';