SET sql_mode=ORACLE;
#
# MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
#
#
# Initiation:
# - creating database db1
# - creating user user1 with access rights to db1
#
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();
database()
test
SELECT user();
user()
user1@localhost
#
# Making sure that user1 does not have privileges to db1.t1
#
SHOW CREATE TABLE db1.t1;
ERROR 42000: SHOW command denied to user 'user1'@'localhost' for table `db1`.`t1`
SHOW FIELDS IN db1.t1;
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1`.`t1`
#
# Trigger: using %TYPE with a table we don't have access to
#
CREATE TABLE test.t1 (a INT, b INT);
INSERT INTO test.t1 (a,b) VALUES (10,20);
SELECT * FROM t1;
a b
10 20
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
$$
INSERT INTO t1 (a) VALUES (10);
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1`.`t1`
SELECT * FROM t1;
a b
10 20
DROP TRIGGER tr1;
DROP TABLE t1;
#
# Stored procedure: Using %TYPE for with a table that we don't have access to
# DEFINER user1, SQL SECURITY DEFAULT
#
CREATE PROCEDURE p1()
AS
a db1.t1.a%TYPE := 10;
BEGIN
SELECT a;
END;
$$
CALL p1;
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1`.`t1`
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
AS
a db1.t1%ROWTYPE;
BEGIN
SELECT a.a;
END;
$$
CALL p1;
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1`.`t1`
DROP PROCEDURE p1;
#
# Stored procedure: Using %TYPE for with a table that we don't have access to
# DEFINER root, SQL SECURITY INVOKER
#
connection default;
CREATE PROCEDURE p1()
SQL SECURITY INVOKER
AS
a db1.t1.a%TYPE := 10;
BEGIN
SELECT a;
END;
$$
connection conn1;
CALL p1;
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1`.`t1`
DROP PROCEDURE p1;
connection default;
CREATE PROCEDURE p1()
SQL SECURITY INVOKER
AS
a db1.t1%ROWTYPE;
BEGIN
SELECT a.a;
END;
$$
connection conn1;
CALL p1;
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1`.`t1`
DROP PROCEDURE p1;
#
# Stored procedure: Using %TYPE for with a table that we don't have access to
# DEFINER root, SQL SECURITY DEFINER
#
connection default;
CREATE PROCEDURE p1()
SQL SECURITY DEFINER
AS
a db1.t1.a%TYPE := 10;
BEGIN
SELECT a;
END;
$$
connection conn1;
CALL p1;
a
10
DROP PROCEDURE p1;
connection default;
CREATE PROCEDURE p1()
SQL SECURITY DEFINER
AS
a db1.t1%ROWTYPE;
BEGIN
a.a:= 10;
SELECT a.a;
END;
$$
connection conn1;
CALL p1;
a.a
10
DROP PROCEDURE p1;
#
# Stored function: Using %TYPE for with a table that we don't have access to
# DEFINER user1, SQL SECURITY DEFAULT
#
CREATE TABLE t1 (a INT);
CREATE FUNCTION f1() RETURN INT
AS
a db1.t1.a%TYPE:=0;
BEGIN
RETURN OCTET_LENGTH(a);
END;
$$
SELECT f1();
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1`.`t1`
DROP FUNCTION f1;
DROP TABLE t1;
#
# Stored function: Using %TYPE for with a table that we don't have access to
# DEFINER root, SQL SECURITY INVOKER
#
connection default;
CREATE TABLE t1 (a INT);
CREATE FUNCTION f1() RETURN INT
SQL SECURITY INVOKER
AS
a db1.t1.a%TYPE:=0;
BEGIN
RETURN OCTET_LENGTH(a);
END;
$$
connection conn1;
SELECT f1();
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table `db1`.`t1`
DROP FUNCTION f1;
DROP TABLE t1;
#
# Stored function: Using %TYPE for with a table that we don't have access to
# DEFINER root, SQL SECURITY DEFINER
#
connection default;
CREATE TABLE t1 (a INT);
CREATE FUNCTION f1() RETURN INT
SQL SECURITY DEFINER
AS
a db1.t1.a%TYPE:=0;
BEGIN
RETURN OCTET_LENGTH(a);
END;
$$
connection conn1;
SELECT f1();
f1()
1
DROP FUNCTION f1;
DROP TABLE t1;
connection default;
GRANT SELECT (a) ON db1.t1 TO user1;
connection conn1;
#
# Making sure that user1 has access to db1.t1.a, but not to db1.t1.b
#
SHOW CREATE TABLE db1.t1;
ERROR 42000: SHOW command denied to user 'user1'@'localhost' for table `db1`.`t1`
SHOW FIELDS IN db1.t1;
Field Type Null Key Default Extra
a int(11) YES  NULL 
#
# Trigger: Per-column privileges
#
CREATE TABLE test.t1 (a INT, b INT);
INSERT INTO test.t1 (a,b) VALUES (10,20);
SELECT * FROM t1;
a b
10 20
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
$$
INSERT INTO t1 (a) VALUES (10);
SELECT * FROM t1;
a b
10 20
10 10
DROP TRIGGER tr1;
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
$$
INSERT INTO t1 (a) VALUES (10);
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1'
SELECT * FROM t1;
a b
10 20
10 10
DROP TRIGGER tr1;
DROP TABLE t1;
#
# Stored procedure: Per-column privileges
# DEFINER user1, SQL SECURITY DEFAULT
#
CREATE PROCEDURE p1()
AS
a db1.t1.a%TYPE := 10;
BEGIN
SELECT a;
END;
$$
CALL p1;
a
10
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
AS
b db1.t1.b%TYPE := 10;
BEGIN
SELECT b;
END;
$$
CALL p1;
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1'
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
AS
b db1.t1%ROWTYPE;
BEGIN
b.b:=10;
SELECT b.b;
END;
$$
CALL p1;
ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1'
DROP PROCEDURE p1;
#
# Clean up
#
disconnect conn1;
connection default;
DROP USER user1;
DROP DATABASE db1;
#
# End of MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
#
#
# MDEV-37054: Implement INFORMATION_SCHEMA.PARAMETERS.PARAMETER_DEFAULT column
#
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;
//
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;
SELECT specific_schema, specific_name, parameter_name, parameter_default FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'proc';
specific_schema specific_name parameter_name parameter_default
test proc a NULL
test proc b NULL
test proc c NULL
SELECT specific_schema, specific_name, parameter_name, parameter_default FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'func';
specific_schema specific_name parameter_name parameter_default
test func NULL NULL
test func a NULL
test func b NULL
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;
SELECT specific_schema, specific_name, parameter_name, parameter_default FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'proc';
specific_schema specific_name parameter_name parameter_default
test proc a 1
test proc b (`a` + 1)
test proc c pow(2,`b`)
SELECT specific_schema, specific_name, parameter_name, parameter_default FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'func';
specific_schema specific_name parameter_name parameter_default
test func NULL NULL
test func a 8
test func b log2(`a`)
connection default;
disconnect conn1;
DROP USER user@localhost;
DROP PROCEDURE proc;
DROP FUNCTION func;
# End of 12.2 tests