path: root/mysql-test/r/trigger-grant.result

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;
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 (i1 INT);
CREATE TABLE t2 (i1 INT);
CREATE USER mysqltest_dfn@localhost;
CREATE USER mysqltest_inv@localhost;
GRANT EXECUTE, CREATE ROUTINE, SUPER ON *.* TO mysqltest_dfn@localhost;
GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
INSERT INTO t1 VALUES (7);
ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
INSERT INTO t2 VALUES (11);
ERROR 42000: SELECT,UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
INSERT INTO t1 VALUES (13);
ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
INSERT INTO t2 VALUES (17);
ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
INSERT INTO t1 VALUES (19);
INSERT INTO t2 VALUES (23);
ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
INSERT INTO t1 VALUES (29);
INSERT INTO t2 VALUES (31);
REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
DROP PROCEDURE p2;
DROP PROCEDURE p1;
GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
INSERT INTO t1 VALUES (41);
DROP PROCEDURE p1;
CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
INSERT INTO t1 VALUES (47);
ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
DROP PROCEDURE p1;
CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
INSERT INTO t1 VALUES (53);
ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
DROP PROCEDURE p1;
REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
DROP TRIGGER t1_bi;
DROP USER mysqltest_inv@localhost;
DROP USER mysqltest_dfn@localhost;
DROP TABLE t2;
DROP TABLE t1;
DROP DATABASE mysqltest_db1;
USE test;
End of 5.0 tests.