diff options
Diffstat (limited to 'mysql-test/suite/compat/oracle/r/sp-security.result')
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-security.result | 288 |
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 +# |