#
# Testing SQL SECURITY of stored procedures (DEBUGG binaries)
#

--source include/have_debug.inc
# Can't test with embedded server that doesn't support grants
--source include/not_embedded.inc
--source include/default_charset.inc
set @@global.character_set_server=@@session.character_set_server;


--echo #
--echo # MDEV-29167: new db-level SHOW CREATE ROUTINE privilege
--echo #

--echo ###
--echo ### SHOW-Like commad test
--echo ###

SET @save_sql_mode=@@sql_mode;

--echo #
--echo ### Prepare functions for the test and SHOW-like by owner
--echo #

create database test_db;
use test_db;
create procedure test_db.sp() select 1;
show procedure code test_db.sp;
CREATE FUNCTION test_db.fn() RETURNS INT RETURN 1;
show function code test_db.fn;

SET sql_mode=ORACLE;
DELIMITER $$;
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;
$$
DELIMITER ;$$

SET sql_mode=@save_sql_mode;
--error ER_PARSE_ERROR
show package code test_db.pk;
show package body code test_db.pk;

use test;


--echo ###
--echo ### Pre-"SHOW-CREATE-ROUTINE" behaviour tests
--echo ###


--echo #
--echo ### Rights on mysql.proc
--echo #

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 procedure code test_db.sp;
show function code test_db.fn;
show package body code test_db.pk;

connection default;
disconnect conn1;

revoke all privileges on mysql.* from user@localhost;


--echo #
--echo ### No privileges
--echo #

connect conn1, localhost, user, , test;

--error ER_SP_DOES_NOT_EXIST
show procedure code test_db.sp;
--error ER_SP_DOES_NOT_EXIST
show function code test_db.fn;
--error ER_SP_DOES_NOT_EXIST
show package body code test_db.pk;

connection default;
disconnect conn1;

--echo #
--echo ### Execute provilege PROCEDURE/FUNCTION
--echo #

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;

--error ER_SP_DOES_NOT_EXIST
show procedure code test_db.sp;
--error ER_SP_DOES_NOT_EXIST
show function code test_db.fn;

connection default;
disconnect conn1;

revoke execute on procedure test_db.sp from user@localhost;
revoke execute on function test_db.fn from user@localhost;

--echo #
--echo ### Execute provilege PACKAGE+ PACKAGE BODY-
--echo #

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;

--error ER_SP_DOES_NOT_EXIST
show package body code 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;


--echo #
--echo ### Execute provilege PACKAGE- PACKAGE BODY+
--echo #

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;

--error ER_SP_DOES_NOT_EXIST
show package body code test_db.pk;

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;

--echo #
--echo ### Alter routine provilege PROCEDURE/FUNCTION
--echo #

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;

--error ER_SP_DOES_NOT_EXIST
show procedure code test_db.sp;
--error ER_SP_DOES_NOT_EXIST
show function code test_db.fn;

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;

--echo #
--echo ### Alter routine provilege PACKAGE+ PACKAGE BODY-
--echo #

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;

--error ER_SP_DOES_NOT_EXIST
show package body code test_db.pk;

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;


--echo #
--echo ### Alter routine provilege PACKAGE+ PACKAGE BODY-
--echo #

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;

--error ER_SP_DOES_NOT_EXIST
show package body code test_db.pk;
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;


--echo ###
--echo ### SHOW CREATE PROCEDURE tests
--echo ###


--echo #
--echo ### Global "show create routine" test
--echo #

grant show create routine on *.* to user@localhost;
show grants for user@localhost;

connect conn1, localhost, user, , test;

show procedure code test_db.sp;
show function code test_db.fn;
show package body code test_db.pk;

connection default;
disconnect conn1;

revoke show create routine on *.* from user@localhost;

--echo #
--echo ### DB-level "show create routine" but other DB test
--echo #

grant show create routine on db_test.* to user@localhost;
show grants for user@localhost;

connect conn1, localhost, user, , test;

--error ER_SP_DOES_NOT_EXIST
show procedure code test_db.sp;
--error ER_SP_DOES_NOT_EXIST
show function code test_db.fn;
--error ER_SP_DOES_NOT_EXIST
show package body code test_db.pk;

connection default;
disconnect conn1;

revoke show create routine on db_test.* from user@localhost;

--echo #
--echo ### DB-level "show create routine" test
--echo #

grant show create routine on test_db.* to user@localhost;
show grants for user@localhost;

connect conn1, localhost, user, , test;

show procedure code test_db.sp;
show function code test_db.fn;
show package body code test_db.pk;

connection default;
disconnect conn1;

revoke show create routine on test_db.* from user@localhost;


--echo #
--echo ### Routine-level "show create routine" PROCEDURE and FUNCTION
--echo #

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 procedure code test_db.sp;
show function code 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;


--echo #
--echo ### Routine-level "show create routine" PACKAGE+ PACKAGE BODY-
--echo #

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;

--error ER_SP_DOES_NOT_EXIST
show package body code 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;


--echo #
--echo ### Routine-level "show create routine" PACKAGE- PACKAGE BODY+
--echo #

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 package body code 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 database test_db;
drop user user@localhost;

--echo #
--echo # End of 11.3 tests
--echo #