Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 288 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp-security.result b/mysql-test/suite/compat/oracle/r/sp-security.result
new file mode 100644
index 00000000..db29a17a
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/sp-security.result
@@ -0,0 +1,288 @@
+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
+#