set @@global.collation_server=@@session.collation_server;
connect  con1root,localhost,root,,;
connection con1root;
use test;
create user user1@localhost;
grant usage on *.* to user1@localhost;
flush privileges;
drop table if exists t1;
drop database if exists db1_secret;
create database db1_secret;
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);
create procedure stamp(i int)
insert into db1_secret.t1 values (user(), i);
show procedure status like 'stamp';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
db1_secret	stamp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
create function db() returns varchar(64)
begin
declare v varchar(64);
select u into v from t1 limit 1;
return v;
end|
show function status like 'db';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
db1_secret	db	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
call stamp(1);
select * from t1;
u	i
test	0
root@localhost	1
select db();
db()
test
create user user1@'%';
grant execute on procedure db1_secret.stamp to user1@'%';
grant execute on function db1_secret.db to user1@'%';
set sql_mode='';
grant execute on procedure db1_secret.stamp to ''@'%';
grant execute on function db1_secret.db to ''@'%';
set sql_mode=default;
connect  con2user1,localhost,user1,,"*NO-ONE*";
connect  con3anon,localhost,anon,,"*NO-ONE*";
connection con2user1;
call db1_secret.stamp(2);
select db1_secret.db();
db1_secret.db()
test
select * from db1_secret.t1;
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1_secret`.`t1`
create procedure db1_secret.dummy() begin end;
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret'
drop procedure db1_secret.dummy;
ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.dummy'
drop procedure db1_secret.stamp;
ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.stamp'
drop function db1_secret.db;
ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.db'
connection con3anon;
call db1_secret.stamp(3);
select db1_secret.db();
db1_secret.db()
test
select * from db1_secret.t1;
ERROR 42000: SELECT command denied to user ''@'localhost' for table `db1_secret`.`t1`
create procedure db1_secret.dummy() begin end;
ERROR 42000: Access denied for user ''@'%' to database 'db1_secret'
drop procedure db1_secret.dummy;
ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.dummy'
drop procedure db1_secret.stamp;
ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.stamp'
drop function db1_secret.db;
ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.db'
connection con1root;
select * from t1;
u	i
test	0
root@localhost	1
user1@localhost	2
anon@localhost	3
alter procedure stamp sql security invoker;
show procedure status like 'stamp';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
db1_secret	stamp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
alter function db sql security invoker;
show function status like 'db';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
db1_secret	db	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
call stamp(4);
select * from t1;
u	i
test	0
root@localhost	1
user1@localhost	2
anon@localhost	3
root@localhost	4
select db();
db()
test
connection con2user1;
call db1_secret.stamp(5);
ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table `db1_secret`.`t1`
select db1_secret.db();
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1_secret`.`t1`
connection con3anon;
call db1_secret.stamp(6);
ERROR 42000: INSERT command denied to user ''@'localhost' for table `db1_secret`.`t1`
select db1_secret.db();
ERROR 42000: SELECT command denied to user ''@'localhost' for table `db1_secret`.`t1`
connection con1root;
drop database if exists db2;
create database db2;
use db2;
create table t2 (s1 int);
insert into t2 values (0);
grant usage on db2.* to user1@localhost;
grant select on db2.* to user1@localhost;
create user user2@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);
call p();
ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table `db2`.`t2`
connect  con4user2,localhost,user2,,"*NO-ONE*";
connection con4user2;
use db2;
call p();
ERROR 42000: execute command denied to user 'user2'@'localhost' for routine 'db2.p'
select * from t2;
s1
0
create procedure q () insert into t2 values (2);
call q();
select * from t2;
s1
0
2
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;
call q();
select * from t2;
s1
0
2
2
alter procedure p modifies sql data;
drop procedure p;
alter procedure q modifies sql data;
ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db2.q'
drop procedure q;
ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db2.q'
connection con1root;
use db2;
alter procedure q modifies sql data;
drop procedure q;
disconnect con2user1;
disconnect con3anon;
disconnect con4user2;
use test;
select type,db,name from mysql.proc where db like 'db%';
type	db	name
FUNCTION	db1_secret	db
PROCEDURE	db1_secret	stamp
drop database db1_secret;
drop database db2;
select type,db,name from mysql.proc where db like 'db%';
type	db	name
delete from mysql.user where user='user1' or user='user2';
delete from mysql.user where user='' and host='%';
delete from mysql.procs_priv where user='user1' or user='user2';
delete from mysql.procs_priv where user='' and host='%';
delete from mysql.db where user='user2';
flush privileges;
create user usera@localhost;
grant usage on *.* to usera@localhost;
create user userb@localhost;
grant usage on *.* to userb@localhost;
create user userc@localhost;
grant usage on *.* to 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;
Grants for usera@localhost
GRANT USAGE ON *.* TO `usera`@`localhost`
GRANT INSERT ON `test`.`t1` TO `usera`@`localhost`
GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO `usera`@`localhost`
grant execute on procedure sptest.p1 to userc@localhost with grant option;
show grants for userc@localhost;
Grants for userc@localhost
GRANT USAGE ON *.* TO `userc`@`localhost`
GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO `userc`@`localhost` WITH GRANT OPTION
connect  con2usera,localhost,usera,,"*NO-ONE*";
connect  con3userb,localhost,userb,,"*NO-ONE*";
connect  con4userc,localhost,userc,,"*NO-ONE*";
connection con2usera;
call sptest.p1(1);
grant execute on procedure sptest.p1 to userb@localhost;
ERROR 42000: grant command denied to user 'usera'@'localhost' for routine 'sptest.p1'
drop procedure sptest.p1;
ERROR 42000: alter routine command denied to user 'usera'@'localhost' for routine 'sptest.p1'
connection con3userb;
call sptest.p1(2);
ERROR 42000: execute command denied to user 'userb'@'localhost' for routine 'sptest.p1'
grant execute on procedure sptest.p1 to userb@localhost;
ERROR 42000: execute command denied to user 'userb'@'localhost' for routine 'sptest.p1'
drop procedure sptest.p1;
ERROR 42000: alter routine command denied to user 'userb'@'localhost' for routine 'sptest.p1'
connection con4userc;
call sptest.p1(3);
grant execute on procedure sptest.p1 to userb@localhost;
drop procedure sptest.p1;
ERROR 42000: alter routine command denied to user 'userc'@'localhost' for routine 'sptest.p1'
connection con3userb;
call sptest.p1(4);
grant execute on procedure sptest.p1 to userb@localhost;
ERROR 42000: grant command denied to user 'userb'@'localhost' for routine 'sptest.p1'
drop procedure sptest.p1;
ERROR 42000: alter routine command denied to user 'userb'@'localhost' for routine 'sptest.p1'
connection con1root;
select * from t1;
u	i
usera@localhost	1
userc@localhost	3
userb@localhost	4
grant all privileges on procedure sptest.p1 to userc@localhost;
show grants for userc@localhost;
Grants for userc@localhost
GRANT USAGE ON *.* TO `userc`@`localhost`
GRANT EXECUTE, ALTER ROUTINE, SHOW CREATE ROUTINE ON PROCEDURE `sptest`.`p1` TO `userc`@`localhost` WITH GRANT OPTION
show grants for userb@localhost;
Grants for userb@localhost
GRANT USAGE ON *.* TO `userb`@`localhost`
GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO `userb`@`localhost`
connection con4userc;
revoke all privileges on procedure sptest.p1 from userb@localhost;
connection con1root;
show grants for userb@localhost;
Grants for userb@localhost
GRANT USAGE ON *.* TO `userb`@`localhost`
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;
connect  root,localhost,root,,test;
connection root;
drop function if exists bug_9503;
drop user if exists user1@localhost;
create database mysqltest//
use mysqltest//
create table t1 (s1 int)//
create user user1@localhost//
grant select on t1 to user1@localhost//
grant select on test.* 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//
connect  user1,localhost,user1,,test;
connection user1;
use mysqltest;
select bug_9503();
ERROR 42000: execute command denied to user 'user1'@'localhost' for routine 'mysqltest.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;
connection con1root;
use test;
select current_user();
current_user()
root@localhost
select user();
user()
root@localhost
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();
current_user()	user()
user1@localhost	user1@localhost
call bug7291_1();
current_user()	user()
root@localhost	user1@localhost
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;
drop database if exists mysqltest_1;
create database mysqltest_1;
create procedure mysqltest_1.p1()
begin
select 1 from dual;
end//
create user mysqltest_1@localhost;
grant usage on *.* to mysqltest_1@localhost;
connect  n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK;
connection n1;
call mysqltest_1.p1();
ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1'
disconnect n1;
connect  n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK;
connection n2;
call mysqltest_1.p1();
ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine '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;
drop function if exists bug12812|
create function bug12812() returns char(2)
begin
return 'ok';
end;
|
create user user_bug12812@localhost IDENTIFIED BY 'ABC'|
grant select,create view on test.* to user_bug12812@localhost|
connect  test_user_12812,localhost,user_bug12812,ABC,test;
SELECT test.bug12812()|
ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812'
CREATE VIEW v1 AS SELECT test.bug12812()|
ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812'
connection default;
disconnect test_user_12812;
DROP USER user_bug12812@localhost|
drop function bug12812|
create database db_bug14834;
create user user1_bug14834@localhost identified by '';
grant all on `db\_bug14834`.* to user1_bug14834@localhost;
create user user2_bug14834@localhost identified by '';
grant all on `db\_bug14834`.* to user2_bug14834@localhost;
create user user3_bug14834@localhost identified by '';
grant all on `db__ug14834`.* to user3_bug14834@localhost;
connect  user1_bug14834,localhost,user1_bug14834,,db_bug14834;
create procedure p_bug14834() select user(), current_user();
call p_bug14834();
user()	current_user()
user1_bug14834@localhost	user1_bug14834@localhost
connect  user2_bug14834,localhost,user2_bug14834,,db_bug14834;
call p_bug14834();
user()	current_user()
user2_bug14834@localhost	user1_bug14834@localhost
connect  user3_bug14834,localhost,user3_bug14834,,db_bug14834;
call p_bug14834();
user()	current_user()
user3_bug14834@localhost	user1_bug14834@localhost
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;
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;
grant select on test.* to user_bug14533@localhost;
connect  user_bug14533,localhost,user_bug14533,,test;
call db_bug14533.bug14533_1();
Field	Type	Null	Key	Default	Extra
id	int(11)	YES		NULL	
call db_bug14533.bug14533_2();
id
desc db_bug14533.t1;
ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table `db_bug14533`.`t1`
select * from db_bug14533.t1;
ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table `db_bug14533`.`t1`
connection default;
disconnect user_bug14533;
drop user user_bug14533@localhost;
drop database db_bug14533;
connection con1root;
DROP DATABASE IF EXISTS mysqltest;
CREATE DATABASE mysqltest;
CREATE USER mysqltest_1@localhost;
GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
CREATE USER mysqltest_2@localhost;
GRANT SET USER 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;
connection mysqltest_2_con;
USE mysqltest;
CREATE PROCEDURE wl2897_p1() SELECT 1;
CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1;
connection mysqltest_1_con;
USE mysqltest;
CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2;
ERROR 42000: Access denied; you need (at least one of) the SET USER privilege(s) for this operation
CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2;
ERROR 42000: Access denied; you need (at least one of) the SET USER privilege(s) for this operation
connection mysqltest_2_con;
use mysqltest;
CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3;
Warnings:
Note	1449	The user specified as a definer ('a @ b @ c'@'localhost') does not exist
CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3;
Warnings:
Note	1449	The user specified as a definer ('a @ b @ c'@'localhost') does not exist
connection con1root;
USE mysqltest;
SHOW CREATE PROCEDURE wl2897_p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
wl2897_p1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`()
SELECT 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW CREATE PROCEDURE wl2897_p3;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
wl2897_p3	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`()
SELECT 3	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW CREATE FUNCTION wl2897_f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
wl2897_f1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11)
RETURN 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW CREATE FUNCTION wl2897_f3;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
wl2897_f3	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11)
RETURN 3	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
DROP USER mysqltest_1@localhost;
DROP USER mysqltest_2@localhost;
DROP DATABASE mysqltest;
disconnect mysqltest_1_con;
disconnect mysqltest_2_con;
connection con1root;
DROP DATABASE IF EXISTS mysqltest;
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;
connection mysqltest_1_con;
USE mysqltest;
CREATE PROCEDURE bug13198_p1()
SELECT 1;
CREATE FUNCTION bug13198_f1() RETURNS INT
RETURN 1;
CALL bug13198_p1();
1
1
SELECT bug13198_f1();
bug13198_f1()
1
connection mysqltest_2_con;
USE mysqltest;
CALL bug13198_p1();
1
1
SELECT bug13198_f1();
bug13198_f1()
1
connection con1root;
disconnect mysqltest_1_con;
DROP USER mysqltest_1@localhost;
connection mysqltest_2_con;
USE mysqltest;
CALL bug13198_p1();
ERROR HY000: The user specified as a definer ('mysqltest_1'@'localhost') does not exist
SELECT bug13198_f1();
ERROR HY000: The user specified as a definer ('mysqltest_1'@'localhost') does not exist
connection con1root;
disconnect mysqltest_2_con;
DROP USER mysqltest_2@localhost;
DROP DATABASE mysqltest;
GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO
user19857@localhost;
SELECT Host,User,Plugin,Authentication_string FROM mysql.user WHERE User='user19857';
Host	User	plugin	authentication_string
localhost	user19857	mysql_native_password	*82DC221D557298F6CE9961037DB1C90604792F5C
connect  mysqltest_2_con,localhost,user19857,meow,test;
connection mysqltest_2_con;
USE test;
CREATE PROCEDURE sp19857() DETERMINISTIC
BEGIN
DECLARE a INT;
SET a=1;
SELECT a;
END //
SHOW CREATE PROCEDURE test.sp19857;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sp19857	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`user19857`@`localhost` PROCEDURE `sp19857`()
    DETERMINISTIC
BEGIN
DECLARE a INT;
SET a=1;
SELECT a;
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
disconnect mysqltest_2_con;
connect  mysqltest_2_con,localhost,user19857,meow,test;
connection mysqltest_2_con;
DROP PROCEDURE IF EXISTS test.sp19857;
connection con1root;
disconnect mysqltest_2_con;
SELECT Host,User,Plugin,Authentication_string FROM mysql.user WHERE User='user19857';
Host	User	plugin	authentication_string
localhost	user19857	mysql_native_password	*82DC221D557298F6CE9961037DB1C90604792F5C
DROP USER user19857@localhost;
disconnect con1root;
connection default;
use test;
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;
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;
GRANT EXECUTE ON test.* TO mysqltest_u1@localhost;
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|
CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil();
connect  conn1, localhost, mysqltest_u1,,;
SELECT COUNT(*) FROM t1;
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table `test`.`t1`
SELECT f_evil();
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table `test`.`t1`
SELECT @a, @b;
@a	@b
mysqltest_u1@localhost	NULL
SELECT f_suid(f_evil());
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table `test`.`t1`
SELECT @a, @b;
@a	@b
mysqltest_u1@localhost	NULL
CALL p_suid(f_evil());
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table `test`.`t1`
SELECT @a, @b;
@a	@b
mysqltest_u1@localhost	NULL
SELECT * FROM v1;
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table `test`.`v1`
SELECT @a, @b;
@a	@b
mysqltest_u1@localhost	NULL
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;
#
# Bug #48872 : Privileges for stored functions ignored if function name 
#  is mixed case
#
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();
f_Test()
123
SELECT * FROM TestTab;
id
1
2
CONNECT  con_tester,localhost,tester,,B48872;
CONNECT  con_tester_denied,localhost,Tester,,B48872;
connection con_tester;
SELECT * FROM TestTab;
id
1
2
SELECT `f_Test`();
`f_Test`()
123
SELECT `F_TEST`();
`F_TEST`()
123
SELECT f_Test();
f_Test()
123
SELECT F_TEST();
F_TEST()
123
connection con_tester_denied;
SELECT * FROM TestTab;
SELECT `f_Test`();
SELECT `F_TEST`();
SELECT f_Test();
SELECT F_TEST();
SELECT `f_Test_denied`();
`f_Test_denied`()
123
SELECT `F_TEST_DENIED`();
`F_TEST_DENIED`()
123
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;
#
# End of 5.0 tests.
#
#
# Test for bug#57061 "User without privilege on routine can discover
# its existence."
#
drop database if exists mysqltest_db;
create database mysqltest_db;
# 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;
connect  conn1, localhost, bug57061_user,,"*NO-ONE*";
# Attempt to drop routine on which user doesn't have privileges
# should result in the same 'access denied' type of error whether
# routine exists or not.
drop function if exists mysqltest_db.f_does_not_exist;
ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist'
drop procedure if exists mysqltest_db.p_does_not_exist;
ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p_does_not_exist'
drop function if exists mysqltest_db.f1;
ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f1'
drop procedure if exists mysqltest_db.p1;
ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p1'
connection default;
disconnect conn1;
drop user bug57061_user@localhost;
drop database mysqltest_db;
#
# Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE
#              DEFINITION OF ANY ROUTINE. 
#
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
CREATE PROCEDURE db1.p1() SELECT 1;
CREATE USER user2@localhost IDENTIFIED BY '';
GRANT SELECT(db) ON mysql.proc TO user2@localhost;
connect  con2, localhost, user2,,"*NO-ONE*";
# The statement below before disclosed info from body_utf8 column.
SHOW CREATE PROCEDURE db1.p1;
ERROR 42000: PROCEDURE p1 does not exist
# Check that SHOW works with SELECT grant on whole table
connection default;
GRANT SELECT ON mysql.proc TO user2@localhost;
connection con2;
# This should work
SHOW CREATE PROCEDURE db1.p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
SELECT 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
connection default;
disconnect con2;
DROP USER user2@localhost;
DROP DATABASE db1;
create user foo@local_ost;
create user foo@`local\_ost` identified via mysql_old_password using '0123456789ABCDEF';
grant select,create routine on test.* to foo@local_ost;
create database foodb;
grant create routine on foodb.* to foo@local_ost;
connect con1,localhost,foo;
select user(), current_user();
user()	current_user()
foo@localhost	foo@local_ost
show grants;
Grants for foo@local_ost
GRANT USAGE ON *.* TO `foo`@`local_ost`
GRANT SELECT, CREATE ROUTINE ON `test`.* TO `foo`@`local_ost`
GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`local_ost`
create procedure fooproc() select 'i am fooproc';
show grants;
Grants for foo@local_ost
GRANT USAGE ON *.* TO `foo`@`local_ost`
GRANT SELECT, CREATE ROUTINE ON `test`.* TO `foo`@`local_ost`
GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`local_ost`
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO `foo`@`local_ost`
disconnect con1;
connection default;
drop user foo@local_ost;
drop user foo@`local\_ost`;
drop procedure fooproc;
drop database foodb;
#
# Test for bug#12602983 - User without privilege on routine can discover
# its existence by executing "select non_existing_func();" or by 
# "call non_existing_proc()";
#
drop database if exists mysqltest_db;
create database mysqltest_db;
create function mysqltest_db.f1() returns int return 0;
create procedure mysqltest_db.p1() begin end;
# Create user with no privileges on mysqltest_db database.
create user bug12602983_user@localhost;
grant create view on test.* to bug12602983_user@localhost;
connect  conn1, localhost, bug12602983_user,,;
# Attempt to execute routine on which user doesn't have privileges
# should result in the same 'access denied' error whether
# routine exists or not.
select mysqltest_db.f_does_not_exist();
ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist'
call mysqltest_db.p_does_not_exist();
ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.p_does_not_exist'
select mysqltest_db.f1();
ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f1'
call mysqltest_db.p1();
ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.p1'
create view bug12602983_v1 as select mysqltest_db.f_does_not_exist();
ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist'
create view bug12602983_v1 as select mysqltest_db.f1();
ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f1'
connection default;
disconnect conn1;
drop user bug12602983_user@localhost;
drop database mysqltest_db;
create user u1@localhost;
grant all privileges on *.* to u1@localhost with grant option;
connect u1, localhost, u1;
set password=password('foobar');
create procedure sp1() select 1;
show grants;
Grants for u1@localhost
GRANT ALL PRIVILEGES ON *.* TO `u1`@`localhost` IDENTIFIED BY PASSWORD '*9B500343BC52E2911172EB52AE5CF4847604C6E5' WITH GRANT OPTION
grant execute on procedure sp1 to current_user() identified by 'barfoo';
show grants;
Grants for u1@localhost
GRANT ALL PRIVILEGES ON *.* TO `u1`@`localhost` IDENTIFIED BY PASSWORD '*343915A8181B5728EADBDC73E1F7E6B0C3998483' WITH GRANT OPTION
GRANT EXECUTE ON PROCEDURE `test`.`sp1` TO `u1`@`localhost`
drop procedure sp1;
disconnect u1;
connection default;
drop user u1@localhost;
#
# MDEV-13396 Unexpected "alter routine comand defined" during CREATE OR REPLACE PROCEDURE
#
CREATE DATABASE u1;
CREATE PROCEDURE u1.p1() BEGIN SELECT 1; END; $$
CREATE FUNCTION u1.f1() RETURNS INT BEGIN RETURN 1; END; $$
CREATE USER u1@localhost;
GRANT CREATE ROUTINE ON u1.* TO u1@localhost;
GRANT ALTER ROUTINE ON FUNCTION u1.f1 TO u1@localhost;
GRANT ALTER ROUTINE ON PROCEDURE u1.p1 TO u1@localhost;
connect  u1, localhost, u1,,u1;
CREATE OR REPLACE FUNCTION f1() RETURNS INT BEGIN RETURN 2; END; $$
CREATE OR REPLACE PROCEDURE p1() BEGIN SELECT 1; END; $$
disconnect u1;
connection default;
DROP DATABASE u1;
DROP USER u1@localhost;
set @@global.character_set_server=@save_character_set_server;
#
# End of 10.2 tests
#
#
# MDEV-20366 Server crashes in get_current_user upon SET PASSWORD via SP
#
CREATE PROCEDURE p1() SET PASSWORD FOR foo@localhost=PASSWORD('x');
CALL p1();
ERROR 28000: Can't find any matching row in the user table
DROP PROCEDURE p1;
CREATE USER foo@localhost;
CREATE  PROCEDURE p1() SET PASSWORD FOR foo@localhost=PASSWORD('x');
CALL p1();
DROP PROCEDURE p1;
DROP USER foo@localhost;
#
# End of 10.5 tests
#
#
# MDEV-29852 SIGSEGV in mysql_create_routine or is_acl_user on 2nd execution, ASAN use-after-poison in get_current_user (sql_acl.cc)
#
set @cmd:="create definer=u function f(i int) returns char binary reads sql data return concat (1,i)";
prepare s from @cmd;
execute s;
Warnings:
Note	1449	The user specified as a definer ('u'@'%') does not exist
execute s;
ERROR 42000: FUNCTION f already exists
drop function f;
#
# End of 10.6 tests
#
#
# MDEV-29167: new db-level SHOW CREATE ROUTINE privilege
#
###
### SHOW-Like commad test
###
SET @save_sql_mode=@@sql_mode;
#
### Prepare functions for the test and SHOW-like by root
#
create database test_db;
use test_db;
create procedure test_db.sp() select 1;
show create procedure test_db.sp;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sp	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `sp`()
select 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	sp	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME	ROUTINE_DEFINITION
sp	select 1
CREATE FUNCTION test_db.fn() RETURNS INT RETURN 1;
show create function test_db.fn;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `fn`() RETURNS int(11)
RETURN 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	fn	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
fn	RETURN 1
SET sql_mode=ORACLE;
CREATE PACKAGE test_db.pk AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END;
$$
CREATE PACKAGE BODY test_db.pk AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END;
$$
SET sql_mode=@save_sql_mode;
show create package test_db.pk;
Package	sql_mode	Create Package	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE "pk" AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create package body test_db.pk;
Package body	sql_mode	Create Package Body	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE BODY "pk" AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	pk	PACKAGE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE	AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END
pk	PACKAGE BODY	AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END
use test;
###
### Pre-"SHOW-CREATE-ROUTINE" behaviour tests
###
#
### Rights on mysql.proc
#
create user user@localhost;
grant all privileges on mysql.* to user@localhost;
grant all privileges on test.* to user@localhost;
connect conn1, localhost, user, , test;
show create procedure test_db.sp;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sp	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `sp`()
select 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	sp	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME
sp
show create function test_db.fn;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `fn`() RETURNS int(11)
RETURN 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	fn	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where
ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
fn	RETURN 1
show create package test_db.pk;
Package	sql_mode	Create Package	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE "pk" AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create package body test_db.pk;
Package body	sql_mode	Create Package Body	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE BODY "pk" AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	pk	PACKAGE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE	AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END
pk	PACKAGE BODY	AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END
connection default;
disconnect conn1;
revoke all privileges on mysql.* from user@localhost;
#
### No privileges
#
connect conn1, localhost, user, , test;
show create procedure test_db.sp;
ERROR 42000: PROCEDURE sp does not exist
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME
show create function test_db.fn;
ERROR 42000: FUNCTION fn does not exist
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where
ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
show create package test_db.pk;
ERROR 42000: PACKAGE pk does not exist
show create package body test_db.pk;
ERROR 42000: PACKAGE BODY pk does not exist
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
connection default;
disconnect conn1;
#
### Execute provilege PROCEDURE/FUNCTION
#
grant execute on procedure test_db.sp to user@localhost;
grant execute on function test_db.fn to user@localhost;
connect conn1, localhost, user, , test;
show create procedure test_db.sp;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sp	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	sp	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME
sp
call test_db.sp();
1
1
show create function test_db.fn;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	fn	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where
ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
fn	NULL
select test_db.fn();
test_db.fn()
1
connection default;
disconnect conn1;
revoke execute on procedure test_db.sp from user@localhost;
revoke execute on function test_db.fn from user@localhost;
#
### Execute provilege PACKAGE+ PACKAGE BODY-
#
SET sql_mode=ORACLE;
grant execute on package test_db.pk to user@localhost;
SET sql_mode=@save_sql_mode;
connect conn1, localhost, user, , test;
show create package test_db.pk;
Package	sql_mode	Create Package	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create package body test_db.pk;
ERROR 42000: PACKAGE BODY pk does not exist
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	pk	PACKAGE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE	NULL
call test_db.pk.pkp();
ERROR 42000: execute command denied to user 'user'@'localhost' for routine 'test_db.pk'
select test_db.pk.pkf();
ERROR 42000: execute command denied to user 'user'@'localhost' for routine 'test_db.pk'
connection default;
disconnect conn1;
SET sql_mode=ORACLE;
revoke execute on package test_db.pk from user@localhost;
SET sql_mode=@save_sql_mode;
#
### Execute provilege PACKAGE- PACKAGE BODY+
#
SET sql_mode=ORACLE;
grant execute on package body test_db.pk to user@localhost;
SET sql_mode=@save_sql_mode;
connect conn1, localhost, user, , test;
show create package test_db.pk;
ERROR 42000: PACKAGE pk does not exist
show create package body test_db.pk;
Package body	sql_mode	Create Package Body	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE BODY	NULL
call test_db.pk.pkp();
pkv
2
select test_db.pk.pkf();
test_db.pk.pkf()
2
connection default;
disconnect conn1;
SET sql_mode=ORACLE;
revoke execute on package body test_db.pk from user@localhost;
SET sql_mode=@save_sql_mode;
#
### Alter routine provilege PROCEDURE/FUNCTION
#
grant alter routine on procedure test_db.sp to user@localhost;
grant alter routine on function test_db.fn to user@localhost;
connect conn1, localhost, user, , test;
show create procedure test_db.sp;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sp	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	sp	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME
sp
show create function test_db.fn;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	fn	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where
ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
fn	NULL
connection default;
disconnect conn1;
revoke alter routine on procedure test_db.sp from user@localhost;
revoke alter routine on function test_db.fn from user@localhost;
#
### Alter routine provilege PACKAGE+ PACKAGE BODY-
#
SET sql_mode=ORACLE;
grant alter routine on package test_db.pk to user@localhost;
SET sql_mode=@save_sql_mode;
connect conn1, localhost, user, , test;
show create package test_db.pk;
Package	sql_mode	Create Package	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create package body test_db.pk;
ERROR 42000: PACKAGE BODY pk does not exist
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	pk	PACKAGE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE	NULL
connection default;
disconnect conn1;
SET sql_mode=ORACLE;
revoke alter routine on package test_db.pk from user@localhost;
SET sql_mode=@save_sql_mode;
#
### Alter routine provilege PACKAGE+ PACKAGE BODY-
#
SET sql_mode=ORACLE;
grant alter routine on package body test_db.pk to user@localhost;
SET sql_mode=@save_sql_mode;
connect conn1, localhost, user, , test;
show create package test_db.pk;
ERROR 42000: PACKAGE pk does not exist
show create package body test_db.pk;
Package body	sql_mode	Create Package Body	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE BODY	NULL
connection default;
disconnect conn1;
SET sql_mode=ORACLE;
revoke alter routine on package body test_db.pk from user@localhost;
SET sql_mode=@save_sql_mode;
###
### SHOW CREATE PROCEDURE tests
###
#
### Global "show create routine" test
#
grant show create routine on *.* to user@localhost;
show grants for user@localhost;
Grants for user@localhost
GRANT SHOW CREATE ROUTINE ON *.* TO `user`@`localhost`
GRANT ALL PRIVILEGES ON `test`.* TO `user`@`localhost`
connect conn1, localhost, user, , test;
show create procedure test_db.sp;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sp	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `sp`()
select 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	sp	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME	ROUTINE_DEFINITION
sp	select 1
show create function test_db.fn;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `fn`() RETURNS int(11)
RETURN 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	fn	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
fn	RETURN 1
show create package test_db.pk;
Package	sql_mode	Create Package	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE "pk" AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create package body test_db.pk;
Package body	sql_mode	Create Package Body	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE BODY "pk" AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	pk	PACKAGE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE	AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END
pk	PACKAGE BODY	AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END
connection default;
disconnect conn1;
revoke show create routine on *.* from user@localhost;
#
### DB-level "show create routine" but other DB test
#
grant show create routine on db_test.* to user@localhost;
show grants for user@localhost;
Grants for user@localhost
GRANT USAGE ON *.* TO `user`@`localhost`
GRANT ALL PRIVILEGES ON `test`.* TO `user`@`localhost`
GRANT SHOW CREATE ROUTINE ON `db_test`.* TO `user`@`localhost`
connect conn1, localhost, user, , test;
show create procedure test_db.sp;
ERROR 42000: PROCEDURE sp does not exist
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME	ROUTINE_DEFINITION
show create function test_db.fn;
ERROR 42000: FUNCTION fn does not exist
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
show create package test_db.pk;
ERROR 42000: PACKAGE pk does not exist
show create package body test_db.pk;
ERROR 42000: PACKAGE BODY pk does not exist
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
connection default;
disconnect conn1;
revoke show create routine on db_test.* from user@localhost;
#
### DB-level "show create routine" test
#
grant show create routine on test_db.* to user@localhost;
show grants for user@localhost;
Grants for user@localhost
GRANT USAGE ON *.* TO `user`@`localhost`
GRANT ALL PRIVILEGES ON `test`.* TO `user`@`localhost`
GRANT SHOW CREATE ROUTINE ON `test_db`.* TO `user`@`localhost`
connect conn1, localhost, user, , test;
show create procedure test_db.sp;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sp	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `sp`()
select 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	sp	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME	ROUTINE_DEFINITION
sp	select 1
show create function test_db.fn;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `fn`() RETURNS int(11)
RETURN 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	fn	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
fn	RETURN 1
show create package test_db.pk;
Package	sql_mode	Create Package	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE "pk" AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create package body test_db.pk;
Package body	sql_mode	Create Package Body	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE BODY "pk" AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	pk	PACKAGE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE	AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END
pk	PACKAGE BODY	AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END
connection default;
disconnect conn1;
revoke show create routine on test_db.* from user@localhost;
#
### Routine-level "show create routine" PROCEDURE and FUNCTION
#
grant show create routine on procedure test_db.sp to user@localhost;
grant show create routine on function test_db.fn to user@localhost;
connect conn1, localhost, user, , test;
show create procedure test_db.sp;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sp	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `sp`()
select 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	sp	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME	ROUTINE_DEFINITION
sp	select 1
call test_db.sp();
ERROR 42000: execute command denied to user 'user'@'localhost' for routine 'test_db.sp'
show create function test_db.fn;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `fn`() RETURNS int(11)
RETURN 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	fn	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where
ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
fn	RETURN 1
select test_db.fn();
ERROR 42000: execute command denied to user 'user'@'localhost' for routine 'test_db.fn'
connection default;
disconnect conn1;
revoke show create routine on procedure test_db.sp from user@localhost;
revoke show create routine on function test_db.fn from user@localhost;
#
### Routine-level "show create routine" PACKAGE+ PACKAGE BODY-
#
SET sql_mode=ORACLE;
grant show create routine on package test_db.pk to user@localhost;
SET sql_mode=@save_sql_mode;
connect conn1, localhost, user, , test;
show create package test_db.pk;
Package	sql_mode	Create Package	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE "pk" AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create package body test_db.pk;
ERROR 42000: PACKAGE BODY pk does not exist
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	pk	PACKAGE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE	AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END
call test_db.pk.pkp();
ERROR 42000: execute command denied to user 'user'@'localhost' for routine 'test_db.pk'
select test_db.pk.pkf();
ERROR 42000: execute command denied to user 'user'@'localhost' for routine 'test_db.pk'
connection default;
disconnect conn1;
SET sql_mode=ORACLE;
revoke show create routine on package test_db.pk from user@localhost;
SET sql_mode=@save_sql_mode;
#
### Routine-level "show create routine" PACKAGE- PACKAGE BODY+
#
SET sql_mode=ORACLE;
grant show create routine on package body test_db.pk to user@localhost;
SET sql_mode=@save_sql_mode;
connect conn1, localhost, user, , test;
show create package test_db.pk;
ERROR 42000: PACKAGE pk does not exist
show create package body test_db.pk;
Package body	sql_mode	Create Package Body	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PACKAGE BODY "pk" AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE BODY	AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END
call test_db.pk.pkp();
ERROR 42000: execute command denied to user 'user'@'localhost' for routine 'test_db.pk'
select test_db.pk.pkf();
ERROR 42000: execute command denied to user 'user'@'localhost' for routine 'test_db.pk'
connection default;
disconnect conn1;
SET sql_mode=ORACLE;
revoke show create routine on package body test_db.pk from user@localhost;
SET sql_mode=@save_sql_mode;
drop user user@localhost;
drop database test_db;
#
### Check owner only rights
#
create user user@localhost;
create database test_db;
use test_db;
create definer=user@localhost procedure test_db.sp() select 1;
CREATE definer=user@localhost FUNCTION test_db.fn() RETURNS INT RETURN 1;
SET sql_mode=ORACLE;
CREATE definer=user@localhost PACKAGE test_db.pk AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END;
$$
CREATE definer=user@localhost PACKAGE BODY test_db.pk AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END;
$$
use test;
connect conn1, localhost, user, , "*NO-ONE*";
show create procedure test_db.sp;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sp	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`user`@`localhost` PROCEDURE `sp`()
select 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PROCEDURE STATUS WHERE name="sp";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	sp	PROCEDURE	user@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp";
ROUTINE_NAME
sp
show create function test_db.fn;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`user`@`localhost` FUNCTION `fn`() RETURNS int(11)
RETURN 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW FUNCTION STATUS WHERE name="fn";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	fn	FUNCTION	user@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where
ROUTINE_NAME="fn";
ROUTINE_NAME	ROUTINE_DEFINITION
fn	RETURN 1
show create package test_db.pk;
Package	sql_mode	Create Package	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="user"@"localhost" PACKAGE "pk" AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create package body test_db.pk;
Package body	sql_mode	Create Package Body	character_set_client	collation_connection	Database Collation
pk	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="user"@"localhost" PACKAGE BODY "pk" AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW PACKAGE STATUS WHERE name="pk";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test_db	pk	PACKAGE	user@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk";
ROUTINE_NAME	ROUTINE_TYPE	ROUTINE_DEFINITION
pk	PACKAGE	AS
FUNCTION pkf() RETURN INT;
PROCEDURE pkp();
END
pk	PACKAGE BODY	AS
pkv INT:=1;
PROCEDURE pkhp() AS
BEGIN
SELECT pkv FROM DUAL;
END;
FUNCTION pkhf() RETURN INT AS
BEGIN
RETURN pkv;
END;
PROCEDURE pkp() AS
BEGIN
CALL pkhp();
END;
FUNCTION pkf() RETURN INT AS
BEGIN
RETURN pkhf();
END;
BEGIN
pkv:=2;
END
connection default;
disconnect conn1;
drop user user@localhost;
drop database test_db;
#
# End of 11.3 tests
#
set @@global.collation_server=@save_collation_server;