1# 2# MDEV-12461 TYPE OF and ROW TYPE OF anchored data types 3# 4# 5# Initiation: 6# - creating database db1 7# - creating user user1 with access rights to db1 8# 9CREATE DATABASE db1; 10CREATE TABLE db1.t1 (a INT, b VARCHAR(10)); 11CREATE USER user1; 12GRANT ALL PRIVILEGES ON test.* TO user1; 13connect conn1,localhost,user1,,test; 14SELECT database(); 15database() 16test 17SELECT user(); 18user() 19user1@localhost 20# 21# Making sure that user1 does not have privileges to db1.t1 22# 23SHOW CREATE TABLE db1.t1; 24ERROR 42000: SHOW command denied to user 'user1'@'localhost' for table 't1' 25SHOW FIELDS IN db1.t1; 26ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 27# 28# Trigger: using TYPE OF with a table we don't have access to 29# 30CREATE TABLE test.t1 (a INT, b INT); 31INSERT INTO test.t1 (a,b) VALUES (10,20); 32SELECT * FROM t1; 33a b 3410 20 35CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW 36BEGIN 37DECLARE b TYPE OF db1.t1.b DEFAULT 20; 38SET NEW.b = 10; 39END 40$$ 41INSERT INTO t1 (a) VALUES (10); 42ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 43SELECT * FROM t1; 44a b 4510 20 46DROP TRIGGER tr1; 47DROP TABLE t1; 48# 49# Stored procedure: Using TYPE OF for with a table that we don't have access to 50# DEFINER user1, SQL SECURITY DEFAULT 51# 52CREATE PROCEDURE p1() 53BEGIN 54DECLARE a TYPE OF db1.t1.a DEFAULT 10; 55SELECT a; 56END; 57$$ 58CALL p1; 59ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 60DROP PROCEDURE p1; 61# 62# Stored procedure: Using TYPE OF for with a table that we don't have access to 63# DEFINER root, SQL SECURITY INVOKER 64# 65connection default; 66CREATE PROCEDURE p1() 67SQL SECURITY INVOKER 68BEGIN 69DECLARE a TYPE OF db1.t1.a DEFAULT 10; 70SELECT a; 71END; 72$$ 73connection conn1; 74CALL p1; 75ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 76DROP PROCEDURE p1; 77connection default; 78CREATE PROCEDURE p1() 79SQL SECURITY INVOKER 80BEGIN 81DECLARE a ROW TYPE OF db1.t1; 82SELECT a.a; 83END; 84$$ 85connection conn1; 86CALL p1; 87ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 88DROP PROCEDURE p1; 89# 90# Stored procedure: Using TYPE OF for with a table that we don't have access to 91# DEFINER root, SQL SECURITY DEFINER 92# 93connection default; 94CREATE PROCEDURE p1() 95SQL SECURITY DEFINER 96BEGIN 97DECLARE a TYPE OF db1.t1.a DEFAULT 10; 98SELECT a; 99END; 100$$ 101connection conn1; 102CALL p1; 103a 10410 105DROP PROCEDURE p1; 106connection default; 107CREATE PROCEDURE p1() 108SQL SECURITY DEFINER 109BEGIN 110DECLARE a ROW TYPE OF db1.t1; 111SET a.a= 10; 112SELECT a.a; 113END; 114$$ 115connection conn1; 116CALL p1; 117a.a 11810 119DROP PROCEDURE p1; 120# 121# Stored function: Using TYPE OF for with a table that we don't have access to 122# DEFINER user1, SQL SECURITY DEFAULT 123# 124CREATE TABLE t1 (a INT); 125CREATE FUNCTION f1() RETURNS INT 126BEGIN 127DECLARE a TYPE OF db1.t1.a DEFAULT 0; 128RETURN OCTET_LENGTH(a); 129END; 130$$ 131SELECT f1(); 132ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 133DROP FUNCTION f1; 134DROP TABLE t1; 135# 136# Stored function: Using TYPE OF for with a table that we don't have access to 137# DEFINER root, SQL SECURITY INVOKER 138# 139connection default; 140CREATE TABLE t1 (a INT); 141CREATE FUNCTION f1() RETURNS INT 142SQL SECURITY INVOKER 143BEGIN 144DECLARE a TYPE OF db1.t1.a DEFAULT 0; 145RETURN OCTET_LENGTH(a); 146END; 147$$ 148connection conn1; 149SELECT f1(); 150ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 151DROP FUNCTION f1; 152DROP TABLE t1; 153# 154# Stored function: Using TYPE OF for with a table that we don't have access to 155# DEFINER root, SQL SECURITY DEFINER 156# 157connection default; 158CREATE TABLE t1 (a INT); 159CREATE FUNCTION f1() RETURNS INT 160SQL SECURITY DEFINER 161BEGIN 162DECLARE a TYPE OF db1.t1.a DEFAULT 0; 163RETURN OCTET_LENGTH(a); 164END; 165$$ 166connection conn1; 167SELECT f1(); 168f1() 1691 170DROP FUNCTION f1; 171DROP TABLE t1; 172connection default; 173GRANT SELECT (a) ON db1.t1 TO user1; 174connection conn1; 175# 176# Making sure that user1 has access to db1.t1.a, but not to db1.t1.b 177# 178SHOW CREATE TABLE db1.t1; 179ERROR 42000: SHOW command denied to user 'user1'@'localhost' for table 't1' 180SHOW FIELDS IN db1.t1; 181Field Type Null Key Default Extra 182a int(11) YES NULL 183# 184# Trigger: Per-column privileges 185# 186CREATE TABLE test.t1 (a INT, b INT); 187INSERT INTO test.t1 (a,b) VALUES (10,20); 188SELECT * FROM t1; 189a b 19010 20 191CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW 192BEGIN 193DECLARE a TYPE OF db1.t1.a DEFAULT 20; 194BEGIN 195SET NEW.b := 10; 196END; 197END 198$$ 199INSERT INTO t1 (a) VALUES (10); 200SELECT * FROM t1; 201a b 20210 20 20310 10 204DROP TRIGGER tr1; 205CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW 206BEGIN 207DECLARE b TYPE OF db1.t1.b DEFAULT 20; 208BEGIN 209SET NEW.b = 10; 210END; 211END 212$$ 213INSERT INTO t1 (a) VALUES (10); 214ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1' 215SELECT * FROM t1; 216a b 21710 20 21810 10 219DROP TRIGGER tr1; 220DROP TABLE t1; 221# 222# Stored procedure: Per-column privileges 223# DEFINER user1, SQL SECURITY DEFAULT 224# 225CREATE PROCEDURE p1() 226BEGIN 227DECLARE a TYPE OF db1.t1.a DEFAULT 10; 228SELECT a; 229END; 230$$ 231CALL p1; 232a 23310 234DROP PROCEDURE p1; 235CREATE PROCEDURE p1() 236BEGIN 237DECLARE b TYPE OF db1.t1.b DEFAULT 10; 238SELECT b; 239END; 240$$ 241CALL p1; 242ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1' 243DROP PROCEDURE p1; 244CREATE PROCEDURE p1() 245BEGIN 246DECLARE b ROW TYPE OF db1.t1; 247SET b.b=10; 248SELECT b.b; 249END; 250$$ 251CALL p1; 252ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1' 253DROP PROCEDURE p1; 254# 255# Clean up 256# 257disconnect conn1; 258connection default; 259DROP USER user1; 260DROP DATABASE db1; 261# 262# End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types 263# 264