diff options
Diffstat (limited to 'mysql-test/r/trigger-grant.result')
-rw-r--r-- | mysql-test/r/trigger-grant.result | 312 |
1 files changed, 312 insertions, 0 deletions
diff --git a/mysql-test/r/trigger-grant.result b/mysql-test/r/trigger-grant.result new file mode 100644 index 00000000000..87e5e11d779 --- /dev/null +++ b/mysql-test/r/trigger-grant.result @@ -0,0 +1,312 @@ +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; +DROP DATABASE IF EXISTS mysqltest_db1; +CREATE DATABASE mysqltest_db1; +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); +CREATE TRIGGER trg1 AFTER INSERT ON t1 +FOR EACH ROW +INSERT INTO t2 VALUES(CURRENT_USER()); + +---> connection: default +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 +TO 'mysqltest_inv'@localhost; +GRANT SELECT ON mysqltest_db1.t2 +TO 'mysqltest_inv'@localhost; + +---> connection: wl2818_definer_con +use mysqltest_db1; +INSERT INTO t1 VALUES(1); +SELECT * FROM t1; +num_value +1 +SELECT * FROM t2; +user_str +mysqltest_dfn@localhost + +---> connection: wl2818_invoker_con +use mysqltest_db1; +INSERT INTO t1 VALUES(2); +SELECT * FROM t1; +num_value +1 +2 +SELECT * FROM t2; +user_str +mysqltest_dfn@localhost +mysqltest_dfn@localhost + +---> connection: default +use mysqltest_db1; +REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; + +---> connection: wl2818_invoker_con +use mysqltest_db1; +INSERT INTO t1 VALUES(3); +ERROR 42000: INSERT command denied to user 'mysqltest_dfn'@'localhost' for table 't2' +SELECT * FROM t1; +num_value +1 +2 +3 +SELECT * FROM t2; +user_str +mysqltest_dfn@localhost +mysqltest_dfn@localhost + +---> connection: wl2818_definer_con +use mysqltest_db1; +DROP TRIGGER trg1; +CREATE DEFINER='mysqltest_inv'@'localhost' + TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @new_sum = 0; +CREATE DEFINER='mysqltest_nonexs'@'localhost' + TRIGGER trg2 AFTER INSERT ON t1 +FOR EACH ROW +SET @new_sum = 0; +Warnings: +Note 1449 There is no 'mysqltest_nonexs'@'localhost' registered +INSERT INTO t1 VALUES(6); +ERROR 42000: Access denied; you need the SUPER privilege for this operation +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 SET @new_sum = 0 BEFORE NULL mysqltest_inv@localhost +trg2 INSERT t1 SET @new_sum = 0 AFTER NULL mysqltest_nonexs@localhost +DROP TRIGGER trg1; +DROP TRIGGER trg2; +CREATE TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @a = 1; +CREATE TRIGGER trg2 AFTER INSERT ON t1 +FOR EACH ROW +SET @a = 2; +CREATE TRIGGER trg3 BEFORE UPDATE ON t1 +FOR EACH ROW +SET @a = 3; +CREATE TRIGGER trg4 AFTER UPDATE ON t1 +FOR EACH ROW +SET @a = 4; +CREATE TRIGGER trg5 BEFORE DELETE ON t1 +FOR EACH ROW +SET @a = 5; + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +trigger_name definer +trg1 +trg2 @ +trg3 @abc@def@@ +trg4 @hostname +trg5 @abcdef@@@hostname +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER +NULL mysqltest_db1 trg1 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW NULL +NULL mysqltest_db1 trg2 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 2 ROW AFTER NULL NULL OLD NEW NULL @ +NULL mysqltest_db1 trg3 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 3 ROW BEFORE NULL NULL OLD NEW NULL @abc@def@@ +NULL mysqltest_db1 trg4 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 4 ROW AFTER NULL NULL OLD NEW NULL @hostname +NULL mysqltest_db1 trg5 DELETE NULL mysqltest_db1 t1 0 NULL SET @a = 5 ROW BEFORE NULL NULL OLD NEW NULL @abcdef@@@hostname + +---> connection: default +DROP USER mysqltest_dfn@localhost; +DROP USER mysqltest_inv@localhost; +DROP DATABASE mysqltest_db1; +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; +DROP DATABASE IF EXISTS mysqltest_db1; +CREATE DATABASE mysqltest_db1; +use mysqltest_db1; +CREATE TABLE t1(col CHAR(20)); +CREATE TABLE t2(col CHAR(20)); +CREATE TABLE t3(col CHAR(20)); +CREATE TABLE t4(col CHAR(20)); +CREATE USER mysqltest_u1@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; +GRANT SUPER ON *.* TO mysqltest_u1@localhost; +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; +SET @mysqltest_var = NULL; + +---> connection: default +use mysqltest_db1; +REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; +GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; +SHOW GRANTS FOR mysqltest_u1@localhost; +Grants for mysqltest_u1@localhost +GRANT SUPER ON *.* TO 'mysqltest_u1'@'localhost' +GRANT DELETE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' + +---> connection: bug15166_u1_con +use mysqltest_db1; +CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1 +FOR EACH ROW +SET @mysqltest_var = 'Hello, world!'; + +---> connection: default +use mysqltest_db1; +GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; +GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost; +GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; +GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; + +---> connection: bug15166_u1_con +use mysqltest_db1; +CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @mysqltest_var = NEW.col; +DROP TRIGGER t1_trg_err_1; +CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1 +FOR EACH ROW +SET @mysqltest_var = OLD.col; +DROP TRIGGER t1_trg_err_2; +CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2 +FOR EACH ROW +SET NEW.col = 't2_trg_before_insert'; +CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3 +FOR EACH ROW +SET @mysqltest_var = NEW.col; +DROP TRIGGER t3_trg_err_1; +CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3 +FOR EACH ROW +SET @mysqltest_var = OLD.col; +DROP TRIGGER t3_trg_err_2; +CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4 +FOR EACH ROW +SET NEW.col = 't4_trg_before_insert'; + +---> connection: default +use mysqltest_db1; +REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; +REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; +GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost; +REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; +REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; +GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost; +GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost; + +---> connection: bug15166_u1_con +use mysqltest_db1; +CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1 +FOR EACH ROW +SET @mysqltest_var = NEW.col; +CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1 +FOR EACH ROW +SET @mysqltest_var = OLD.col; +CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2 +FOR EACH ROW +SET NEW.col = 't2_trg_err_1'; +DROP TRIGGER t2_trg_err_1; +CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2 +FOR EACH ROW +SET NEW.col = CONCAT(OLD.col, '(updated)'); +DROP TRIGGER t2_trg_err_2; +CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3 +FOR EACH ROW +SET @mysqltest_var = NEW.col; +CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3 +FOR EACH ROW +SET @mysqltest_var = OLD.col; +CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4 +FOR EACH ROW +SET NEW.col = 't4_trg_err_1'; +DROP TRIGGER t4_trg_err_1; +CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4 +FOR EACH ROW +SET NEW.col = CONCAT(OLD.col, '(updated)'); +DROP TRIGGER t4_trg_err_2; + +---> connection: default +use mysqltest_db1; +REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; +REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; +GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; +GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost; +REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; +REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; +GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; +GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; +INSERT INTO t1 VALUES('line1'); +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't1' +SELECT * FROM t1; +col +line1 +SELECT @mysqltest_var; +@mysqltest_var +NULL +INSERT INTO t2 VALUES('line2'); +SELECT * FROM t2; +col +t2_trg_before_insert +INSERT INTO t3 VALUES('t3_line1'); +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't3' +SELECT * FROM t3; +col +t3_line1 +SELECT @mysqltest_var; +@mysqltest_var +NULL +INSERT INTO t4 VALUES('t4_line2'); +SELECT * FROM t4; +col +t4_trg_before_insert + +---> connection: default +use mysqltest_db1; +REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; +REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; +GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost; +REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; +REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; +GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; +GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; +INSERT INTO t1 VALUES('line3'); +SELECT * FROM t1; +col +line1 +line3 +SELECT @mysqltest_var; +@mysqltest_var +line3 +INSERT INTO t2 VALUES('line4'); +ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't2' +SELECT * FROM t2; +col +t2_trg_before_insert +INSERT INTO t3 VALUES('t3_line2'); +SELECT * FROM t3; +col +t3_line1 +t3_line2 +SELECT @mysqltest_var; +@mysqltest_var +t3_line2 +INSERT INTO t4 VALUES('t4_line2'); +ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't4' +SELECT * FROM t4; +col +t4_trg_before_insert +DELETE FROM t1; +SELECT @mysqltest_var; +@mysqltest_var +Hello, world! +DROP USER mysqltest_u1@localhost; +DROP DATABASE mysqltest_db1; |