mirror of
https://github.com/MariaDB/server.git
synced 2026-01-29 14:59:08 +01:00
28f5322a44
MariaDB 11.8 and above now supports `DEFAULT` values for parameters in stored procedures and functions. This commit introduces the `INFORMATION_SCHEMA.PARAMETERS.PARAMETER_DEFAULT` column to display the default value of parameters. The value is only shown if the current user or any of it's enabled roles is the owner of parameter's `SPECIFIC_SCHEMA`, otherwise `NULL`.
393 lines
7.3 KiB
Text
393 lines
7.3 KiB
Text
--source include/not_embedded.inc
|
|
|
|
SET sql_mode=ORACLE;
|
|
|
|
--echo #
|
|
--echo # MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
|
|
--echo #
|
|
|
|
|
|
--echo #
|
|
--echo # Initiation:
|
|
--echo # - creating database db1
|
|
--echo # - creating user user1 with access rights to db1
|
|
--echo #
|
|
|
|
CREATE DATABASE db1;
|
|
CREATE TABLE db1.t1 (a INT, b VARCHAR(10));
|
|
|
|
CREATE USER user1;
|
|
|
|
GRANT ALL PRIVILEGES ON test.* TO user1;
|
|
|
|
connect (conn1,localhost,user1,,test);
|
|
SET sql_mode=ORACLE;
|
|
|
|
SELECT database();
|
|
SELECT user();
|
|
|
|
--echo #
|
|
--echo # Making sure that user1 does not have privileges to db1.t1
|
|
--echo #
|
|
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
SHOW CREATE TABLE db1.t1;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
SHOW FIELDS IN db1.t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Trigger: using %TYPE with a table we don't have access to
|
|
--echo #
|
|
CREATE TABLE test.t1 (a INT, b INT);
|
|
INSERT INTO test.t1 (a,b) VALUES (10,20);
|
|
SELECT * FROM t1;
|
|
DELIMITER $$;
|
|
CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW
|
|
BEGIN
|
|
DECLARE b db1.t1.b%TYPE := 20;
|
|
BEGIN
|
|
:NEW.b := 10;
|
|
END;
|
|
END
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
INSERT INTO t1 (a) VALUES (10);
|
|
SELECT * FROM t1;
|
|
DROP TRIGGER tr1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Stored procedure: Using %TYPE for with a table that we don't have access to
|
|
--echo # DEFINER user1, SQL SECURITY DEFAULT
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
a db1.t1.a%TYPE := 10;
|
|
BEGIN
|
|
SELECT a;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
a db1.t1%ROWTYPE;
|
|
BEGIN
|
|
SELECT a.a;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Stored procedure: Using %TYPE for with a table that we don't have access to
|
|
--echo # DEFINER root, SQL SECURITY INVOKER
|
|
--echo #
|
|
|
|
connection default;
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
SQL SECURITY INVOKER
|
|
AS
|
|
a db1.t1.a%TYPE := 10;
|
|
BEGIN
|
|
SELECT a;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
connection conn1;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
connection default;
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
SQL SECURITY INVOKER
|
|
AS
|
|
a db1.t1%ROWTYPE;
|
|
BEGIN
|
|
SELECT a.a;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
connection conn1;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Stored procedure: Using %TYPE for with a table that we don't have access to
|
|
--echo # DEFINER root, SQL SECURITY DEFINER
|
|
--echo #
|
|
|
|
connection default;
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
SQL SECURITY DEFINER
|
|
AS
|
|
a db1.t1.a%TYPE := 10;
|
|
BEGIN
|
|
SELECT a;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
connection conn1;
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
connection default;
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
SQL SECURITY DEFINER
|
|
AS
|
|
a db1.t1%ROWTYPE;
|
|
BEGIN
|
|
a.a:= 10;
|
|
SELECT a.a;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
connection conn1;
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Stored function: Using %TYPE for with a table that we don't have access to
|
|
--echo # DEFINER user1, SQL SECURITY DEFAULT
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
DELIMITER $$;
|
|
CREATE FUNCTION f1() RETURN INT
|
|
AS
|
|
a db1.t1.a%TYPE:=0;
|
|
BEGIN
|
|
RETURN OCTET_LENGTH(a);
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
SELECT f1();
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Stored function: Using %TYPE for with a table that we don't have access to
|
|
--echo # DEFINER root, SQL SECURITY INVOKER
|
|
--echo #
|
|
|
|
connection default;
|
|
CREATE TABLE t1 (a INT);
|
|
DELIMITER $$;
|
|
CREATE FUNCTION f1() RETURN INT
|
|
SQL SECURITY INVOKER
|
|
AS
|
|
a db1.t1.a%TYPE:=0;
|
|
BEGIN
|
|
RETURN OCTET_LENGTH(a);
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
connection conn1;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
SELECT f1();
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Stored function: Using %TYPE for with a table that we don't have access to
|
|
--echo # DEFINER root, SQL SECURITY DEFINER
|
|
--echo #
|
|
|
|
connection default;
|
|
CREATE TABLE t1 (a INT);
|
|
DELIMITER $$;
|
|
CREATE FUNCTION f1() RETURN INT
|
|
SQL SECURITY DEFINER
|
|
AS
|
|
a db1.t1.a%TYPE:=0;
|
|
BEGIN
|
|
RETURN OCTET_LENGTH(a);
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
connection conn1;
|
|
SELECT f1();
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
connection default;
|
|
GRANT SELECT (a) ON db1.t1 TO user1;
|
|
connection conn1;
|
|
|
|
--echo #
|
|
--echo # Making sure that user1 has access to db1.t1.a, but not to db1.t1.b
|
|
--echo #
|
|
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
SHOW CREATE TABLE db1.t1;
|
|
SHOW FIELDS IN db1.t1;
|
|
|
|
--echo #
|
|
--echo # Trigger: Per-column privileges
|
|
--echo #
|
|
CREATE TABLE test.t1 (a INT, b INT);
|
|
INSERT INTO test.t1 (a,b) VALUES (10,20);
|
|
SELECT * FROM t1;
|
|
# %TYPE reference using a column we have access to
|
|
DELIMITER $$;
|
|
CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW
|
|
BEGIN
|
|
DECLARE a db1.t1.a%TYPE := 20;
|
|
BEGIN
|
|
:NEW.b := 10;
|
|
END;
|
|
END
|
|
$$
|
|
DELIMITER ;$$
|
|
INSERT INTO t1 (a) VALUES (10);
|
|
SELECT * FROM t1;
|
|
DROP TRIGGER tr1;
|
|
# %TYPE reference using a column that we don't have access to
|
|
DELIMITER $$;
|
|
CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW
|
|
BEGIN
|
|
DECLARE b db1.t1.b%TYPE := 20;
|
|
BEGIN
|
|
:NEW.b := 10;
|
|
END;
|
|
END
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_COLUMNACCESS_DENIED_ERROR
|
|
INSERT INTO t1 (a) VALUES (10);
|
|
SELECT * FROM t1;
|
|
DROP TRIGGER tr1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
|
|
--echo #
|
|
--echo # Stored procedure: Per-column privileges
|
|
--echo # DEFINER user1, SQL SECURITY DEFAULT
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
a db1.t1.a%TYPE := 10;
|
|
BEGIN
|
|
SELECT a;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
b db1.t1.b%TYPE := 10;
|
|
BEGIN
|
|
SELECT b;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_COLUMNACCESS_DENIED_ERROR
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
b db1.t1%ROWTYPE;
|
|
BEGIN
|
|
b.b:=10;
|
|
SELECT b.b;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_COLUMNACCESS_DENIED_ERROR
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Clean up
|
|
--echo #
|
|
disconnect conn1;
|
|
connection default;
|
|
|
|
DROP USER user1;
|
|
DROP DATABASE db1;
|
|
|
|
--echo #
|
|
--echo # End of MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # MDEV-37054: Implement INFORMATION_SCHEMA.PARAMETERS.PARAMETER_DEFAULT column
|
|
--echo #
|
|
|
|
DELIMITER //;
|
|
CREATE PROCEDURE proc(a INT DEFAULT 1, b INT DEFAULT (a+1), c INT DEFAULT POW(2,b)) AS
|
|
BEGIN
|
|
SELECT a, b, c FROM DUAL;
|
|
END;
|
|
//
|
|
|
|
CREATE FUNCTION func(a INT DEFAULT 8, b INT DEFAULT LOG2(a)) RETURN INT AS
|
|
BEGIN
|
|
RETURN b;
|
|
END;
|
|
//
|
|
DELIMITER ;//
|
|
|
|
# Tests to verify access control for procedures
|
|
CREATE USER user@localhost;
|
|
GRANT EXECUTE ON PROCEDURE test.proc TO user@localhost;
|
|
GRANT EXECUTE ON FUNCTION test.func TO user@localhost;
|
|
CONNECT conn1, localhost, user, , test;
|
|
|
|
# should not display default parameter values
|
|
SELECT specific_schema, specific_name, parameter_name, parameter_default FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'proc';
|
|
SELECT specific_schema, specific_name, parameter_name, parameter_default FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'func';
|
|
|
|
CONNECTION default;
|
|
DISCONNECT conn1;
|
|
|
|
GRANT SHOW CREATE ROUTINE ON PROCEDURE test.proc TO user@localhost;
|
|
GRANT SHOW CREATE ROUTINE ON FUNCTION test.func TO user@localhost;
|
|
CONNECT conn1, localhost, user, , test;
|
|
|
|
# should display default parameter values
|
|
SELECT specific_schema, specific_name, parameter_name, parameter_default FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'proc';
|
|
SELECT specific_schema, specific_name, parameter_name, parameter_default FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'func';
|
|
|
|
CONNECTION default;
|
|
DISCONNECT conn1;
|
|
|
|
DROP USER user@localhost;
|
|
DROP PROCEDURE proc;
|
|
DROP FUNCTION func;
|
|
|
|
--echo # End of 12.2 tests
|