mariadb/mysql-test/suite/compat/oracle/t/sp-security.test

--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 #