1 files changed, 345 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/t/sp-security.test b/mysql-test/suite/compat/oracle/t/sp-security.test
new file mode 100644
index 00000000..1732c0b8
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/sp-security.test
@@ -0,0 +1,345 @@
+--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 #