#
# Testing SQL SECURITY of stored procedures
#

# Save the initial number of concurrent sessions
--source include/count_sessions.inc
# We can't create ''@'%' unless we delete row from mysql.db
create table mysql.db_copy as select * from mysql.db;
delete from mysql.db where host='%';
flush privileges;

set @orig_sql_mode_session= @@SESSION.sql_mode;
set @orig_sql_mode_global= @@GLOBAL.sql_mode;
set @orig_partial_revokes = @@global.partial_revokes;
# Turn Off the partial_revokes to test with wildcard grants
SET GLOBAL partial_revokes= OFF;

connect (con1root,localhost,root,,);

connection con1root;
use test;

# Create user user1 with no particular access rights
create user user1@localhost;
create user ''@'%';
create user user1;
delete from mysql.db;
# Restore mysql.db to its original state
insert into mysql.db select * from mysql.db_copy;
flush privileges;

--disable_warnings
drop table if exists t1;
drop database if exists db1_secret;
--enable_warnings
# Create our secret database
create database db1_secret;

# Can create a procedure in other db
create procedure db1_secret.dummy() begin end;
drop procedure db1_secret.dummy;

use db1_secret;

create table t1 ( u varchar(64), i int );
insert into t1 values('test', 0);

# A test procedure and function
create procedure stamp(i int)
  insert into db1_secret.t1 values (user(), i);
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status like 'stamp';

delimiter |;
create function db() returns varchar(64)
begin
  declare v varchar(64);

  select u into v from t1 limit 1;

  return v;
end|
delimiter ;|
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show function status like 'db';

# root can, of course
call stamp(1);
select * from t1;
select db();

grant execute on procedure db1_secret.stamp to user1@'%';
grant execute on function db1_secret.db to user1@'%';
grant execute on procedure db1_secret.stamp to ''@'%';
grant execute on function db1_secret.db to ''@'%';

connect (con2user1,localhost,user1,,);
connect (con3anon,localhost,anon,,);


#
# User1 can
#
connection con2user1;

# This should work...
call db1_secret.stamp(2);
select db1_secret.db();

# ...but not this
--error ER_TABLEACCESS_DENIED_ERROR
select * from db1_secret.t1;

# ...and not this
--error ER_DBACCESS_DENIED_ERROR
create procedure db1_secret.dummy() begin end;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure db1_secret.dummy;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure db1_secret.stamp;
--error ER_PROCACCESS_DENIED_ERROR
drop function db1_secret.db;


#
# Anonymous can
#
connection con3anon;

# This should work...
call db1_secret.stamp(3);
select db1_secret.db();

# ...but not this
--error ER_TABLEACCESS_DENIED_ERROR
select * from db1_secret.t1;

# ...and not this
--error ER_DBACCESS_DENIED_ERROR
create procedure db1_secret.dummy() begin end;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure db1_secret.dummy;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure db1_secret.stamp;
--error ER_PROCACCESS_DENIED_ERROR
drop function db1_secret.db;


#
# Check it out
#
connection con1root;
select * from t1;

#
# Change to invoker's rights
#
alter procedure stamp sql security invoker;
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status like 'stamp';

alter function db sql security invoker;
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show function status like 'db';

# root still can
call stamp(4);
select * from t1;
select db();

#
# User1 cannot
#
connection con2user1;

# This should not work
--error ER_TABLEACCESS_DENIED_ERROR
call db1_secret.stamp(5);
--error ER_TABLEACCESS_DENIED_ERROR
select db1_secret.db();

#
# Anonymous cannot
#
connection con3anon;

# This should not work
--error ER_TABLEACCESS_DENIED_ERROR
call db1_secret.stamp(6);
--error ER_TABLEACCESS_DENIED_ERROR
select db1_secret.db();

#
# Bug#2777 Stored procedure doesn't observe definer's rights
#

connection con1root;
--disable_warnings
drop database if exists db2;
--enable_warnings
create database db2;

use db2;

create table t2 (s1 int);
insert into t2 values (0);

create user user2@localhost;

grant usage on db2.* to user1@localhost;
grant select on db2.* to user1@localhost;
grant usage on db2.* to user2@localhost;
grant select,insert,update,delete,create routine on db2.* to user2@localhost;
grant create routine on db2.* to user1@localhost;
flush privileges;

connection con2user1;
use db2;

create procedure p () insert into t2 values (1);

# Check that this doesn't work.
--error ER_TABLEACCESS_DENIED_ERROR
call p();

connect (con4user2,localhost,user2,,);

connection con4user2;
use db2;

# This should not work, since p is executed with definer's (user1's) rights.
--error ER_PROCACCESS_DENIED_ERROR
call p();
select * from t2;

create procedure q () insert into t2 values (2);

call q();
select * from t2;

connection con1root;
grant usage on procedure db2.q to user2@localhost with grant option;

connection con4user2;
grant execute on procedure db2.q to user1@localhost;

connection con2user1;
use db2;

# This should work
call q();
select * from t2;

#
# Bug#6030 Stored procedure has no appropriate DROP privilege
# (or ALTER for that matter)

# still connection con2user1 in db2

# This should work:
alter procedure p modifies sql data;
drop procedure p;

# This should NOT work
--error ER_PROCACCESS_DENIED_ERROR
alter procedure q modifies sql data;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure q;

connection con1root;
use db2;
# But root always can
alter procedure q modifies sql data;
drop procedure q;


# Clean up
# Still connection con1root;
disconnect con2user1;
disconnect con3anon;
disconnect con4user2;
use test;
select routine_type, routine_schema, routine_name
from information_schema.routines where routine_schema like 'db%'
order by routine_type, routine_name;
drop database db1_secret;
drop database db2;
# Make sure the routines are gone
select routine_type, routine_schema, routine_name
from information_schema.routines where routine_schema like 'db%';
# Get rid of the users
delete from mysql.user where user='user1' or user='user2';
delete from mysql.user where user='' and host='%';
# And any routine privileges
delete from mysql.procs_priv where user='user1' or user='user2';
# Delete the grants to user ''@'%' that was created above
delete from mysql.procs_priv where user='' and host='%';
delete from mysql.db where user='user1' or user='user2';
flush privileges;
#
# Test the new security acls
#
create user usera@localhost;
create user userb@localhost;
create user userc@localhost;
create database sptest;
create table t1 ( u varchar(64), i int );
create procedure sptest.p1(i int) insert into test.t1 values (user(), i);
grant insert on t1 to usera@localhost;
grant execute on procedure sptest.p1 to usera@localhost;
show grants for usera@localhost;
grant execute on procedure sptest.p1 to userc@localhost with grant option;
show grants for userc@localhost;

connect (con2usera,localhost,usera,,);
connect (con3userb,localhost,userb,,);
connect (con4userc,localhost,userc,,);

connection con2usera;
call sptest.p1(1);
--error ER_PROCACCESS_DENIED_ERROR
grant execute on procedure sptest.p1 to userb@localhost;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure sptest.p1;

connection con3userb;
--error ER_PROCACCESS_DENIED_ERROR
call sptest.p1(2);
--error ER_PROCACCESS_DENIED_ERROR
grant execute on procedure sptest.p1 to userb@localhost;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure sptest.p1;

connection con4userc;
call sptest.p1(3);
grant execute on procedure sptest.p1 to userb@localhost;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure sptest.p1;

connection con3userb;
call sptest.p1(4);
--error ER_PROCACCESS_DENIED_ERROR
grant execute on procedure sptest.p1 to userb@localhost;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure sptest.p1;

connection con1root;
select * from t1;

grant all privileges on procedure sptest.p1 to userc@localhost;
show grants for userc@localhost;
show grants for userb@localhost;

connection con4userc;
revoke all privileges on procedure sptest.p1 from userb@localhost;

connection con1root;
show grants for userb@localhost;

#cleanup
disconnect con4userc;
disconnect con3userb;
disconnect con2usera;
use test;
drop database sptest;
delete from mysql.user where user='usera' or user='userb' or user='userc';
delete from mysql.procs_priv where user='usera' or user='userb' or user='userc';
delete from mysql.tables_priv where user='usera';
flush privileges;
drop table t1;

#
# Bug#9503 reseting correct parameters of thread after error in SP function
#
connect (root,localhost,root,,test);
connection root;

create user user1@localhost;
--disable_warnings
drop function if exists bug_9503;
--enable_warnings
delimiter //;
create database mysqltest//
use mysqltest//
create table t1 (s1 int)//
grant select on t1 to user1@localhost//
create function bug_9503 () returns int sql security invoker begin declare v int;
select min(s1) into v from t1; return v; end//
delimiter ;//

connect (user1,localhost,user1,,test);
connection user1;
use mysqltest;
-- error ER_PROCACCESS_DENIED_ERROR
select bug_9503();

connection root;
grant execute on function bug_9503 to user1@localhost;

connection user1;
do 1;
use test;

disconnect user1;
connection root;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
drop function bug_9503;
use test;
drop database mysqltest;
connection default;
disconnect root;

#
# correct value from current_user() in function run from "security definer"
# (Bug#7291 Stored procedures: wrong CURRENT_USER value)
#
connection con1root;
use test;

select current_user();
select user();
create procedure bug7291_0 () sql security invoker select current_user(), user();
create procedure bug7291_1 () sql security definer call bug7291_0();
create procedure bug7291_2 () sql security invoker call bug7291_0();
grant execute on procedure bug7291_0 to user1@localhost;
grant execute on procedure bug7291_1 to user1@localhost;
grant execute on procedure bug7291_2 to user1@localhost;

connect (user1,localhost,user1,,);
connection user1;

call bug7291_2();
call bug7291_1();

connection con1root;
drop procedure bug7291_1;
drop procedure bug7291_2;
drop procedure bug7291_0;
disconnect user1;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
drop user user1@localhost;

#
# Bug#12318 Wrong error message when accessing an inaccessible stored
# procedure in another database when the current database is
# information_schema.
#

--disable_warnings
drop database if exists mysqltest_1;
--enable_warnings

create database mysqltest_1;
delimiter //;
create procedure mysqltest_1.p1()
begin
   select 1 from dual;
end//
delimiter ;//

create user mysqltest_1@localhost;

connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK);
connection n1;
--error ER_PROCACCESS_DENIED_ERROR
call mysqltest_1.p1();
disconnect n1;
# Test also without a current database
connect (n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK);
connection n2;
--error ER_PROCACCESS_DENIED_ERROR
call mysqltest_1.p1();
disconnect n2;

connection default;

drop procedure mysqltest_1.p1;
drop database mysqltest_1;

revoke usage on *.* from mysqltest_1@localhost;
drop user mysqltest_1@localhost;

#
# Bug#12812 create view calling a function works without execute right
#           on function
delimiter |;
--disable_warnings
drop function if exists bug12812|
--enable_warnings
create function bug12812() returns char(2)
begin
  return 'ok';
end;
create user user_bug12812@localhost IDENTIFIED BY 'ABC'|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (test_user_12812,localhost,user_bug12812,ABC,test)|
--error ER_PROCACCESS_DENIED_ERROR
SELECT test.bug12812()|
--error ER_PROCACCESS_DENIED_ERROR
CREATE VIEW v1 AS SELECT test.bug12812()|
# Cleanup
connection default|
disconnect test_user_12812|
DROP USER user_bug12812@localhost|
drop function bug12812|
delimiter ;|


#
# Bug#14834 Server denies to execute Stored Procedure
#
# The problem here was with '_' in the database name.
#
create database db_bug14834;

create user user1_bug14834@localhost identified by '';
# The exact name of the database (no wildcard)
grant all on `db\_bug14834`.* to user1_bug14834@localhost;

create user user2_bug14834@localhost identified by '';
# The exact name of the database (no wildcard)
grant all on `db\_bug14834`.* to user2_bug14834@localhost;

create user user3_bug14834@localhost identified by '';
# Wildcards in the database name
grant all on `db__ug14834`.* to user3_bug14834@localhost;

connect (user1_bug14834,localhost,user1_bug14834,,db_bug14834);
# Create the procedure and check that we can call it
create procedure p_bug14834() select user(), current_user();
call p_bug14834();

connect (user2_bug14834,localhost,user2_bug14834,,db_bug14834);
# This didn't work before
call p_bug14834();

connect (user3_bug14834,localhost,user3_bug14834,,db_bug14834);
# Should also work
call p_bug14834();

# Cleanup
connection default;
disconnect user1_bug14834;
disconnect user2_bug14834;
disconnect user3_bug14834;
drop user user1_bug14834@localhost;
drop user user2_bug14834@localhost;
drop user user3_bug14834@localhost;
drop database db_bug14834;


#
# Bug#14533 'desc tbl' in stored procedure causes error
# ER_TABLEACCESS_DENIED_ERROR
#
create database db_bug14533;
use db_bug14533;
create table t1 (id int);
create user user_bug14533@localhost identified by '';

create procedure bug14533_1()
    sql security definer
  desc db_bug14533.t1;

create procedure bug14533_2()
    sql security definer
   select * from db_bug14533.t1;

grant execute on procedure db_bug14533.bug14533_1 to user_bug14533@localhost;
grant execute on procedure db_bug14533.bug14533_2 to user_bug14533@localhost;

connect (user_bug14533,localhost,user_bug14533,,test);

# These should work
call db_bug14533.bug14533_1();
call db_bug14533.bug14533_2();

# For reference, these should not work
--error ER_TABLEACCESS_DENIED_ERROR
desc db_bug14533.t1;
--error ER_TABLEACCESS_DENIED_ERROR
select * from db_bug14533.t1;

# Cleanup
connection default;
disconnect user_bug14533;
drop user user_bug14533@localhost;
drop database db_bug14533;


#
# WL#2897 Complete definer support in the stored routines.
#
# The following cases are tested:
#   1. check that if DEFINER-clause is not explicitly specified, stored routines
#     are created with CURRENT_USER privileges;
#   2. check that if DEFINER-clause specifies non-current user, SUPER privilege
#     is required to create a stored routine;
#   3. check that if DEFINER-clause specifies non-existent user, a warning is
#     emitted.
#   4. check that SHOW CREATE PROCEDURE | FUNCTION works correctly;
#
# The following cases are tested in other test suites:
#   - check that mysqldump dumps new attribute correctly;
#   - check that slave replicates CREATE-statements with explicitly specified
#     DEFINER correctly.
#

# Setup the environment.

--echo
--echo ---> connection: root
--connection con1root

--disable_warnings
DROP DATABASE IF EXISTS mysqltest;
--enable_warnings

CREATE DATABASE mysqltest;

CREATE USER mysqltest_1@localhost;
GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;

CREATE USER mysqltest_2@localhost;
GRANT SUPER ON *.* TO mysqltest_2@localhost;
GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;

--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)
--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)

# test case (1).

--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

USE mysqltest;

CREATE PROCEDURE wl2897_p1() SELECT 1;

CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1;

# test case (2).

--echo
--echo ---> connection: mysqltest_1_con
--connection mysqltest_1_con

USE mysqltest;

--error ER_SPECIFIC_ACCESS_DENIED_ERROR
CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2;

--error ER_SPECIFIC_ACCESS_DENIED_ERROR
CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2;

# test case (3).

--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

use mysqltest;

CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3;

CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3;

# test case (4).

--echo
--echo ---> connection: con1root
--connection con1root

USE mysqltest;

SHOW CREATE PROCEDURE wl2897_p1;
SHOW CREATE PROCEDURE wl2897_p3;

SHOW CREATE FUNCTION wl2897_f1;
SHOW CREATE FUNCTION wl2897_f3;

# Cleanup.

DROP USER mysqltest_1@localhost;
DROP USER mysqltest_2@localhost;

DROP DATABASE mysqltest;

--disconnect mysqltest_1_con
--disconnect mysqltest_2_con


#
# Bug#13198 SP executes if definer does not exist
#

# Prepare environment.

--echo
--echo ---> connection: root
--connection con1root

--disable_warnings
DROP DATABASE IF EXISTS mysqltest;
--enable_warnings

CREATE DATABASE mysqltest;

CREATE USER mysqltest_1@localhost;
GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;

CREATE USER mysqltest_2@localhost;
GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;

--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)
--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)

# Create a procedure/function under u1.

--echo
--echo ---> connection: mysqltest_1_con
--connection mysqltest_1_con

USE mysqltest;

CREATE PROCEDURE bug13198_p1()
  SELECT 1;

CREATE FUNCTION bug13198_f1() RETURNS INT
  RETURN 1;

CALL bug13198_p1();

SELECT bug13198_f1();

# Check that u2 can call the procedure/function.

--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

USE mysqltest;

CALL bug13198_p1();

SELECT bug13198_f1();

# Drop user u1 (definer of the object);

--echo
--echo ---> connection: root
--connection con1root

--disconnect mysqltest_1_con

DROP USER mysqltest_1@localhost;

# Check that u2 can not call the procedure/function.

--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

USE mysqltest;

--error ER_NO_SUCH_USER
CALL bug13198_p1();

--error ER_NO_SUCH_USER
SELECT bug13198_f1();

# Cleanup.

--echo
--echo ---> connection: root
--connection con1root

--disconnect mysqltest_2_con

DROP USER mysqltest_2@localhost;

DROP DATABASE mysqltest;

#
# Bug#19857 When a user with CREATE ROUTINE priv creates a routine,
#           it results in NULL p/w
#


CREATE USER user19857@localhost IDENTIFIED BY 'meow';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO
user19857@localhost;
SELECT Host,User FROM mysql.user WHERE User='user19857';

--connect (mysqltest_2_con,localhost,user19857,meow,test)
--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

USE test;

DELIMITER //;
  CREATE PROCEDURE sp19857() DETERMINISTIC
  BEGIN
    DECLARE a INT;
    SET a=1;
    SELECT a;
  END //
DELIMITER ;//

SHOW CREATE PROCEDURE test.sp19857;

--disconnect mysqltest_2_con
--connect (mysqltest_2_con,localhost,user19857,meow,test)
--connection mysqltest_2_con

DROP PROCEDURE IF EXISTS test.sp19857;

--echo
--echo ---> connection: root
--connection con1root

--disconnect mysqltest_2_con

SELECT Host,User FROM mysql.user WHERE User='user19857';

DROP USER user19857@localhost;

--disconnect con1root
--connection default
use test;

#
# Bug#18630 Arguments of suid routine calculated in wrong security context
#
# Arguments of suid routines were calculated in definer's security
# context instead of caller's context thus creating security hole.
#
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
DROP FUNCTION IF EXISTS f_suid;
DROP PROCEDURE IF EXISTS p_suid;
DROP FUNCTION IF EXISTS f_evil;
--enable_warnings
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;

CREATE TABLE t1 (i INT);
CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0;
CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0;

CREATE USER mysqltest_u1@localhost;
# Thanks to this grant statement privileges of anonymous users on
# 'test' database are not applicable for mysqltest_u1@localhost.
GRANT EXECUTE ON test.* TO mysqltest_u1@localhost;

delimiter |;
CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT
  SQL SECURITY INVOKER
BEGIN
  SET @a:= CURRENT_USER();
  SET @b:= (SELECT COUNT(*) FROM t1);
  RETURN @b;
END|
delimiter ;|

CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil();

connect (conn1, localhost, mysqltest_u1,,);

--error ER_TABLEACCESS_DENIED_ERROR
SELECT COUNT(*) FROM t1;

--error ER_TABLEACCESS_DENIED_ERROR
SELECT f_evil();
SELECT @a, @b;

--error ER_TABLEACCESS_DENIED_ERROR
SELECT f_suid(f_evil());
SELECT @a, @b;

--error ER_TABLEACCESS_DENIED_ERROR
CALL p_suid(f_evil());
SELECT @a, @b;

--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM v1;
SELECT @a, @b;

disconnect conn1;
connection default;

DROP VIEW v1;
DROP FUNCTION f_evil;
DROP USER mysqltest_u1@localhost;
DROP PROCEDURE p_suid;
DROP FUNCTION f_suid;
DROP TABLE t1;

--echo #
--echo # Bug #48872 : Privileges for stored functions ignored if function name 
--echo #  is mixed case
--echo #

CREATE DATABASE B48872;
USE B48872;
CREATE TABLE `TestTab` (id INT);
INSERT INTO `TestTab` VALUES (1),(2);
CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123;
CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123;
CREATE USER 'tester';
CREATE USER 'Tester';
GRANT SELECT ON TABLE `TestTab` TO 'tester';
GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester';
GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester';

SELECT f_Test();
SELECT * FROM TestTab;

CONNECT (con_tester,localhost,tester,,B48872);
CONNECT (con_tester_denied,localhost,Tester,,B48872);
CONNECTION con_tester;

SELECT * FROM TestTab;
SELECT `f_Test`();
SELECT `F_TEST`();
SELECT f_Test();
SELECT F_TEST();

CONNECTION con_tester_denied;

--disable_result_log
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM TestTab;
--error ER_PROCACCESS_DENIED_ERROR
SELECT `f_Test`();
--error ER_PROCACCESS_DENIED_ERROR
SELECT `F_TEST`();
--error ER_PROCACCESS_DENIED_ERROR
SELECT f_Test();
--error ER_PROCACCESS_DENIED_ERROR
SELECT F_TEST();
--enable_result_log
SELECT `f_Test_denied`();
SELECT `F_TEST_DENIED`();

CONNECTION default;
DISCONNECT con_tester;
DISCONNECT con_tester_denied;
DROP TABLE `TestTab`;
DROP FUNCTION `f_Test`;
DROP FUNCTION `f_Test_denied`;

USE test;
DROP USER 'tester';
DROP USER 'Tester';
DROP DATABASE B48872;

--echo End of 5.0 tests.


--echo #
--echo # Test for bug#57061 "User without privilege on routine can discover
--echo # its existence."
--echo #
--disable_warnings
drop database if exists mysqltest_db;
--enable_warnings
create database mysqltest_db;
--echo # Create user with no privileges on mysqltest_db database.
create user bug57061_user@localhost;
create function mysqltest_db.f1() returns int return 0;
create procedure mysqltest_db.p1() begin end;
--echo # Connect as user 'bug57061_user@localhost'
connect (conn1, localhost, bug57061_user,,);
--echo # Attempt to drop routine on which user doesn't have privileges
--echo # should result in the same 'access denied' type of error whether
--echo # routine exists or not.
--error ER_PROCACCESS_DENIED_ERROR
drop function if exists mysqltest_db.f_does_not_exist;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure if exists mysqltest_db.p_does_not_exist;
--error ER_PROCACCESS_DENIED_ERROR
drop function if exists mysqltest_db.f1;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure if exists mysqltest_db.p1;
--echo # Connection 'default'.
connection default;
disconnect conn1;
drop user bug57061_user@localhost;
drop database mysqltest_db;


--echo #
--echo # Test for bug#12602983 - User without privilege on routine can discover
--echo # its existence by executing "select non_existing_func();" or by 
--echo # "call non_existing_proc()";
--echo #
--disable_warnings
drop database if exists mysqltest_db;
--enable_warnings
create database mysqltest_db;
create function mysqltest_db.f1() returns int return 0;
create procedure mysqltest_db.p1() begin end;

--echo # Create user with no privileges on mysqltest_db database.
create user bug12602983_user@localhost;

--echo # Connect as user 'bug12602983_user@localhost'
connect (conn1, localhost, bug12602983_user,,);

--echo # Attempt to execute routine on which user doesn't have privileges
--echo # should result in the same 'access denied' error whether
--echo # routine exists or not.
--error ER_PROCACCESS_DENIED_ERROR
select mysqltest_db.f_does_not_exist();
--error ER_PROCACCESS_DENIED_ERROR
call mysqltest_db.p_does_not_exist();

--error ER_PROCACCESS_DENIED_ERROR
select mysqltest_db.f1();
--error ER_PROCACCESS_DENIED_ERROR
call mysqltest_db.p1();

--error ER_PROCACCESS_DENIED_ERROR
create view bug12602983_v1 as select mysqltest_db.f_does_not_exist();
--error ER_PROCACCESS_DENIED_ERROR
create view bug12602983_v1 as select mysqltest_db.f1();

--echo # Connection 'default'.
connection default;
disconnect conn1;
drop user bug12602983_user@localhost;
drop database mysqltest_db;

--echo #
--echo # WL#7897 Use DD API for Stored Routines.
--echo #    Test case to verify I_S.ROUTINES and I_S.PARAMETERS result set when
--echo #    user has NO/ROUTINE LEVEL/GLOBAL SELECT Privilege(s).
--echo #

--enable_connect_log

CREATE DATABASE mysqltest_db;
CREATE PROCEDURE mysqltest_db.p1(IN f1 INT) SELECT 1;

let $select_routines=
SELECT routine_schema, routine_name, routine_type, routine_definition
FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_schema = 'mysqltest_db';
let $select_parameters=
SELECT specific_schema, specific_name, parameter_name
FROM INFORMATION_SCHEMA.PARAMETERS WHERE specific_schema = 'mysqltest_db';

--echo # Create user with no privileges on mysqltest_db database.
CREATE USER user@localhost;

--echo # Connect as user 'user@localhost'.
--connect (conn1, localhost, user,,)

--echo # I_S.Routine and I_S.parameters shows empty result set for
--echo # user@localhost as user does not have any privileges.
eval $select_routines;
eval $select_parameters;

--echo # Grant procedure execute privilege to user@localhost.
--connection default
GRANT EXECUTE ON PROCEDURE mysqltest_db.p1 TO user@localhost;

--echo # I_S.Routine and I_S.parameters shows result set
--echo # for user@localhost as user has routine level privilege.
--connection conn1
eval $select_routines;
eval $select_parameters;

--echo # Revoke procedure execute privilege from user@localhost.
CONNECTION default;
REVOKE EXECUTE ON PROCEDURE mysqltest_db.p1 FROM user@localhost;

--echo # I_S.Routine and I_S.parameters shows empty result set for
--echo # user@localhost as user does not have any privileges.
--connection conn1
eval $select_routines;
eval $select_parameters;

--echo # Grant GLOBAL SELECT privilege to user@localhost.
--connection default
GRANT SELECT ON *.* TO user@localhost;

--echo # I_S.Routine and I_S.parameters shows result set for
--echo # user@localhost as user has global select privilege.
--connect (conn2, localhost, user,,)
eval $select_routines;
eval $select_parameters;

--echo #
--echo # Wl8131 DYNAMIC PRIVILEGS
--echo # SET_USER_ID is a replacement for SUPER which enable a user to 
--echo # create a definer routine executing as some other user.
--echo # This privielge can be used for privilege escalation.
connection default;
CREATE USER u1@localhost IDENTIFIED BY 'foo';
GRANT SYSTEM_USER,SET_USER_ID, EXECUTE ON *.* TO u1@localhost;
--connect (con1,localhost,u1,foo,)
CREATE DEFINER=root@localhost PROCEDURE p1() SELECT current_user();
CALL p1();
SELECT CURRENT_USER();
connection default;
disconnect con1;
DROP PROCEDURE p1;
DROP USER u1@localhost;

# Cleanup
--connection default
--disconnect conn1
--disconnect conn2
DROP USER user@localhost;
DROP DATABASE mysqltest_db;

--echo #
--echo # PASSWORD EXPIRATION CHECKED FOR LOCKED ACCOUNTS USED AS SP DEFINERS
--echo #
CREATE ROLE r1;
GRANT EXECUTE, SELECT ON *.* TO r1;
CREATE DEFINER=r1 PROCEDURE p1() SELECT current_user();
CALL p1();
DROP PROCEDURE p1;
DROP ROLE r1;


--echo
--echo #############################################################
--echo #  WL#9049 Add a dynamic privilege for stored routine backup.
--echo #
--echo #    Test Cases to verify that user with SHOW_ROUTINE can
--echo #    view all the SP/SF code and maintain behaviour of
--echo #    statements for user with global SELECT privilege
--echo #    and for user who is the routine DEFINER.
--echo #
--echo #############################################################
--echo

--enable_connect_log

CREATE SCHEMA testdb;

--echo
--echo # Create users.
--echo
CREATE USER usr_no_priv@localhost, usr_show_routine@localhost, usr_global_select@localhost, usr_definer@localhost, usr_role@localhost, usr_create_routine@localhost, usr_alter_routine@localhost, usr_execute@localhost;

--echo
--echo # Create and grant role.
--echo
CREATE ROLE role_show_routine;
GRANT role_show_routine to usr_role@localhost;

--echo
--echo # Grant privileges.
--echo
GRANT SHOW_ROUTINE ON *.* TO usr_show_routine@localhost, role_show_routine;
GRANT SELECT ON *.* TO usr_global_select@localhost;
GRANT EXECUTE ON *.* TO usr_execute@localhost;
GRANT CREATE ROUTINE ON *.* TO usr_create_routine@localhost;
GRANT ALTER ROUTINE ON *.* TO usr_alter_routine@localhost;

--echo
--echo # Create routines whose definer is root@localhost.
--echo
CREATE PROCEDURE testdb.proc_root() SELECT "ProcRoot";
CREATE FUNCTION testdb.func_root() RETURNS VARCHAR(8) DETERMINISTIC RETURN "FuncRoot";

--echo
--echo # Create routines whose definer is usr_definer@localhost.
--echo
CREATE DEFINER = `usr_definer`@`localhost` PROCEDURE testdb.proc_definer() SELECT "ProcDefiner";
CREATE DEFINER = `usr_definer`@`localhost` FUNCTION testdb.func_definer() RETURNS VARCHAR(11) DETERMINISTIC RETURN "FuncDefiner";

--echo
--echo # Connect as user with no privileges.
--echo
--connect (conn1, localhost, usr_no_priv,,)

--echo
--echo # Should not return any rows.
--echo
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="testdb" ORDER BY ROUTINE_DEFINITION;

--echo
--echo # Should pretend that routine does not exist.
--echo
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE PROCEDURE testdb.proc_root;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE PROCEDURE testdb.proc_definer;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE FUNCTION testdb.func_root;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE FUNCTION testdb.func_definer;

--echo
--echo # Should not return any rows.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--disconnect conn1

--echo
--echo # Connect as user with SHOW_ROUTINE privilege.
--echo
--connect (conn1, localhost, usr_show_routine,,)

--echo
--echo # Should show the routine_definition of all 4 routines.
--echo
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="testdb" ORDER BY ROUTINE_DEFINITION;

--echo
--echo # Should show the Create Procedure string.
--echo
SHOW CREATE PROCEDURE testdb.proc_root;
SHOW CREATE PROCEDURE testdb.proc_definer;

--echo
--echo # Should show the Create Function string.
--echo
SHOW CREATE FUNCTION testdb.func_root;
SHOW CREATE FUNCTION testdb.func_definer;

--echo
--echo # Should show properties of proc_root and proc_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--echo
--echo # Should show properties of func_root and func_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--disconnect conn1

--echo
--echo # Connect as user with role assigned.
--echo
--connect (conn1, localhost, usr_role,,)

--echo
--echo # Without activating role.
--echo

--echo
--echo # Should not return any rows.
--echo
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="testdb" ORDER BY ROUTINE_DEFINITION;

--echo
--echo # Should pretend that routine does not exist.
--echo
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE PROCEDURE testdb.proc_root;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE PROCEDURE testdb.proc_definer;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE FUNCTION testdb.func_root;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE FUNCTION testdb.func_definer;

--echo
--echo # Should not return any rows.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--echo
--echo # Activate role.
--echo
SET ROLE role_show_routine;

--echo
--echo # Should show the routine_definition of all 4 routines.
--echo
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="testdb" ORDER BY ROUTINE_DEFINITION;

--echo
--echo # Should show the Create Procedure string.
--echo
SHOW CREATE PROCEDURE testdb.proc_root;
SHOW CREATE PROCEDURE testdb.proc_definer;

--echo
--echo # Should show the Create Function string.
--echo
SHOW CREATE FUNCTION testdb.func_root;
SHOW CREATE FUNCTION testdb.func_definer;


--echo
--echo # Should show properties of proc_root and proc_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--echo
--echo # Should show properties of func_root and func_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--disconnect conn1

--echo
--echo # Connect as user with global SELECT privilege.
--echo
--connect (conn1, localhost, usr_global_select,,)

--echo
--echo # Should show the Create Procedure string.
--echo
SHOW CREATE PROCEDURE testdb.proc_root;
SHOW CREATE PROCEDURE testdb.proc_definer;

--echo
--echo # Should show the Create Function string.
--echo
SHOW CREATE FUNCTION testdb.func_root;
SHOW CREATE FUNCTION testdb.func_definer;

--echo
--echo # Should show properties of proc_root and proc_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--echo
--echo # Should show properties of func_root and func_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--disconnect conn1

--echo
--echo # Connect as user who is DEFINER of routines proc_definer and func_definer.
--echo
--connect (conn1, localhost, usr_definer,,)

--echo
--echo # Should show the routine_definition of only proc_definer and func_definer.
--echo
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="testdb" ORDER BY ROUTINE_DEFINITION;

--echo
--echo # Should pretend that proc_root and func_root do not exist.
--echo
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE PROCEDURE testdb.proc_root;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE FUNCTION testdb.func_root;

--echo
--echo # Should show the Create Procedure string.
--echo
SHOW CREATE PROCEDURE testdb.proc_definer;
--echo
--echo # Should show the Create Function string.
--echo
SHOW CREATE FUNCTION testdb.func_definer;

--echo # Should show properties of proc_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--echo
--echo # Should show properties of func_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--disconnect conn1

--echo
--echo # Connect as user who has EXECUTE privilege.
--echo
--connect (conn1, localhost, usr_execute,,)

--echo
--echo # Should show NULL routine_definition for all 4 routines.
--echo
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="testdb" ORDER BY ROUTINE_DEFINITION;

--echo
--echo # Should show NULL Create Procedure string.
--echo
SHOW CREATE PROCEDURE testdb.proc_root;
SHOW CREATE PROCEDURE testdb.proc_definer;

--echo
--echo # Should show NULL Create Function string.
--echo
SHOW CREATE FUNCTION testdb.func_root;
SHOW CREATE FUNCTION testdb.func_definer;

--echo
--echo # Should show properties of proc_root and proc_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--echo
--echo # Should show properties of func_root and func_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--disconnect conn1

--echo
--echo # Connect as user who has CREATE ROUTINE privilege.
--echo
--connect (conn1, localhost, usr_create_routine,,)

--echo
--echo # Should show NULL routine_definition for all 4 routines.
--echo
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="testdb" ORDER BY ROUTINE_DEFINITION;

--echo
--echo # Should show NULL Create Procedure string.
--echo
SHOW CREATE PROCEDURE testdb.proc_root;
SHOW CREATE PROCEDURE testdb.proc_definer;

--echo
--echo # Should show NULL Create Function string.
--echo
SHOW CREATE FUNCTION testdb.func_root;
SHOW CREATE FUNCTION testdb.func_definer;

--echo
--echo # Should show properties of proc_root and proc_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--echo
--echo # Should show properties of func_root and func_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--disconnect conn1

--echo
--echo # Connect as user who has ALTER ROUTINE privilege.
--echo
--connect (conn1, localhost, usr_alter_routine,,)

--echo
--echo # Should show NULL routine_definition for all 4 routines.
--echo
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="testdb" ORDER BY ROUTINE_DEFINITION;

--echo
--echo # Should show NULL Create Procedure string.
--echo
SHOW CREATE PROCEDURE testdb.proc_root;
SHOW CREATE PROCEDURE testdb.proc_definer;

--echo
--echo # Should show NULL Create Function string.
--echo
SHOW CREATE FUNCTION testdb.func_root;
SHOW CREATE FUNCTION testdb.func_definer;

--echo
--echo # Should show properties of proc_root and proc_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--echo
--echo # Should show properties of func_root and func_definer.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--disconnect conn1

--echo
--echo # Test showing properties after partial revoke of SELECT privilege.
--echo
--connection default

--echo
--echo # Set partial revokes to ON and save the current value.
--echo
SET @start_partial_revokes = @@global.partial_revokes;
SET @@global.partial_revokes=ON;

REVOKE SELECT ON testdb.* FROM usr_global_select@localhost;
--connect (conn1, localhost, usr_global_select,,)

--echo
--echo # Should not return any rows.
--echo
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="testdb" ORDER BY ROUTINE_DEFINITION;

--echo
--echo # Should pretend that routine does not exist.
--echo
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE PROCEDURE testdb.proc_root;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE PROCEDURE testdb.proc_definer;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE FUNCTION testdb.func_root;
--error ER_SP_DOES_NOT_EXIST
SHOW CREATE FUNCTION testdb.func_definer;

--echo
--echo # Should not return any rows.
--echo
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PROCEDURE STATUS WHERE db="testdb";
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW FUNCTION STATUS WHERE db="testdb";

--disconnect conn1

--echo
--echo # Restore partial_revokes to its previous value.
--echo
--connection default
DROP USER usr_global_select@localhost;
SET @@global.partial_revokes = @start_partial_revokes;

--echo
--echo # Cleanup.
--echo
DROP USER usr_no_priv@localhost, usr_show_routine@localhost, usr_definer@localhost, usr_role@localhost, usr_create_routine@localhost, usr_alter_routine@localhost, usr_execute@localhost;
DROP ROLE role_show_routine;
DROP SCHEMA testdb;

# Restore mysql.db to its original state
delete from mysql.db;
insert into mysql.db select * from mysql.db_copy;
flush privileges;
drop table mysql.db_copy;

SET GLOBAL sql_mode= @orig_sql_mode_global;
SET SESSION sql_mode= @orig_sql_mode_session;
--disable_warnings
SET GLOBAL partial_revokes = @orig_partial_revokes;
--enable_warnings

# Wait till all disconnects are completed.
--source include/wait_until_count_sessions.inc