diff options
Diffstat (limited to 'mysql-test/main/sp-security-anchor-type.result')
| -rw-r--r-- | mysql-test/main/sp-security-anchor-type.result | 263 |
1 files changed, 263 insertions, 0 deletions
diff --git a/mysql-test/main/sp-security-anchor-type.result b/mysql-test/main/sp-security-anchor-type.result new file mode 100644 index 00000000000..4ee846f9f2e --- /dev/null +++ b/mysql-test/main/sp-security-anchor-type.result @@ -0,0 +1,263 @@ +# +# MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# +# +# 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; +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 't1' +SHOW FIELDS IN db1.t1; +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +# +# Trigger: using TYPE OF 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 TYPE OF db1.t1.b DEFAULT 20; +SET NEW.b = 10; +END +$$ +INSERT INTO t1 (a) VALUES (10); +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +SELECT * FROM t1; +a b +10 20 +DROP TRIGGER tr1; +DROP TABLE t1; +# +# Stored procedure: Using TYPE OF for with a table that we don't have access to +# DEFINER user1, SQL SECURITY DEFAULT +# +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 10; +SELECT a; +END; +$$ +CALL p1; +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +DROP PROCEDURE p1; +# +# Stored procedure: Using TYPE OF for with a table that we don't have access to +# DEFINER root, SQL SECURITY INVOKER +# +connection default; +CREATE PROCEDURE p1() +SQL SECURITY INVOKER +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 10; +SELECT a; +END; +$$ +connection conn1; +CALL p1; +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +DROP PROCEDURE p1; +connection default; +CREATE PROCEDURE p1() +SQL SECURITY INVOKER +BEGIN +DECLARE a ROW TYPE OF db1.t1; +SELECT a.a; +END; +$$ +connection conn1; +CALL p1; +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +DROP PROCEDURE p1; +# +# Stored procedure: Using TYPE OF for with a table that we don't have access to +# DEFINER root, SQL SECURITY DEFINER +# +connection default; +CREATE PROCEDURE p1() +SQL SECURITY DEFINER +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 10; +SELECT a; +END; +$$ +connection conn1; +CALL p1; +a +10 +DROP PROCEDURE p1; +connection default; +CREATE PROCEDURE p1() +SQL SECURITY DEFINER +BEGIN +DECLARE a ROW TYPE OF db1.t1; +SET a.a= 10; +SELECT a.a; +END; +$$ +connection conn1; +CALL p1; +a.a +10 +DROP PROCEDURE p1; +# +# Stored function: Using TYPE OF for with a table that we don't have access to +# DEFINER user1, SQL SECURITY DEFAULT +# +CREATE TABLE t1 (a INT); +CREATE FUNCTION f1() RETURNS INT +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 0; +RETURN OCTET_LENGTH(a); +END; +$$ +SELECT f1(); +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +DROP FUNCTION f1; +DROP TABLE t1; +# +# Stored function: Using TYPE OF 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() RETURNS INT +SQL SECURITY INVOKER +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 0; +RETURN OCTET_LENGTH(a); +END; +$$ +connection conn1; +SELECT f1(); +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +DROP FUNCTION f1; +DROP TABLE t1; +# +# Stored function: Using TYPE OF 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() RETURNS INT +SQL SECURITY DEFINER +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 0; +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 '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 TYPE OF db1.t1.a DEFAULT 20; +BEGIN +SET 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 TYPE OF db1.t1.b DEFAULT 20; +BEGIN +SET 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() +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 10; +SELECT a; +END; +$$ +CALL p1; +a +10 +DROP PROCEDURE p1; +CREATE PROCEDURE p1() +BEGIN +DECLARE b TYPE OF db1.t1.b DEFAULT 10; +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() +BEGIN +DECLARE b ROW TYPE OF db1.t1; +SET 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-12461 TYPE OF and ROW TYPE OF anchored data types +# |