Default page

Edit File: trigger_notembedded.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 CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
CREATE TABLE t1(num_value INT);
CREATE TABLE t2(user_str TEXT);

---> connection: default
GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;

---> connection: default
GRANT SUPER ON *.* TO mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
CREATE TRIGGER trg1 AFTER INSERT ON t1
FOR EACH ROW
INSERT INTO t2 VALUES(CURRENT_USER());
ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'

---> connection: default
GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
CREATE TRIGGER trg1 AFTER INSERT ON t1
FOR EACH ROW
INSERT INTO t2 VALUES(CURRENT_USER());

---> connection: default
REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
DROP TRIGGER trg1;
ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'

---> connection: wl2818_definer_con
INSERT INTO t1 VALUES(0);
ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'

---> connection: default
GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
INSERT INTO t1 VALUES(0);
DROP TRIGGER trg1;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;

---> connection: default
REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
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;
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

---> connection: default
use mysqltest_db1;
GRANT SUPER ON *.* TO mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
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	The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist
INSERT INTO t1 VALUES(6);
ERROR HY000: The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist
SHOW TRIGGERS;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
trg1	INSERT	t1	SET @new_sum = 0	BEFORE	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	mysqltest_inv@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
trg2	INSERT	t1	SET @new_sum = 0	AFTER	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	mysqltest_nonexs@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
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

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	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
def	mysqltest_db1	trg1	INSERT	def	mysqltest_db1	t1	1	NULL	SET @a = 1	ROW	BEFORE	NULL	NULL	OLD	NEW	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		latin1	latin1_swedish_ci	latin1_swedish_ci
def	mysqltest_db1	trg2	INSERT	def	mysqltest_db1	t1	1	NULL	SET @a = 2	ROW	AFTER	NULL	NULL	OLD	NEW	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	@	latin1	latin1_swedish_ci	latin1_swedish_ci
def	mysqltest_db1	trg3	UPDATE	def	mysqltest_db1	t1	1	NULL	SET @a = 3	ROW	BEFORE	NULL	NULL	OLD	NEW	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	@abc@def@@	latin1	latin1_swedish_ci	latin1_swedish_ci
def	mysqltest_db1	trg4	UPDATE	def	mysqltest_db1	t1	1	NULL	SET @a = 4	ROW	AFTER	NULL	NULL	OLD	NEW	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci
def	mysqltest_db1	trg5	DELETE	def	mysqltest_db1	t1	1	NULL	SET @a = 5	ROW	BEFORE	NULL	NULL	OLD	NEW	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	@abcdef@@@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci

---> connection: default
DROP USER mysqltest_dfn@localhost;
DROP USER mysqltest_inv@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(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 TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;
SET @mysqltest_var = NULL;

---> connection: default
use mysqltest_db1;
GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
SHOW GRANTS FOR mysqltest_u1@localhost;
Grants for mysqltest_u1@localhost
GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
GRANT DELETE, TRIGGER 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: 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, TRIGGER 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.
drop table if exists t1;
create table t1 (i int);
connection: default
lock tables t1 write;
connection: flush
flush tables with read lock;;
connection: default
create trigger t1_bi before insert on t1 for each row begin end;
unlock tables;
connection: flush
unlock tables;
select * from t1;
i
drop table t1;
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a char(30)) ENGINE=MEMORY;
CREATE TRIGGER db1.trg AFTER INSERT ON db1.t1 FOR EACH ROW
INSERT INTO db1.t1 VALUES('Some very sensitive data goes here');
CREATE USER 'no_rights'@'localhost';
REVOKE ALL ON *.* FROM 'no_rights'@'localhost';
FLUSH PRIVILEGES;
SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS
WHERE trigger_schema = 'db1';
trigger_name
SHOW CREATE TRIGGER db1.trg;
ERROR 42000: Access denied; you need (at least one of) the TRIGGER privilege(s) for this operation
DROP USER 'no_rights'@'localhost';
DROP DATABASE db1;
DROP DATABASE IF EXISTS mysqltest_db1;
CREATE DATABASE mysqltest_db1;
USE mysqltest_db1;
set @orig_sql_mode= @@sql_mode;
set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost;
Warnings:
Warning	1287	Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.
set sql_mode= @orig_sql_mode;
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
CREATE TABLE t1 (
a1 int,
a2 int
);
INSERT INTO t1 VALUES (1, 20);
CREATE TRIGGER mysqltest_db1.upd_t1
BEFORE UPDATE ON t1 FOR EACH ROW SET new.a2 = 200;
CREATE TABLE t2 (
a1 int
);
INSERT INTO t2 VALUES (2);
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
UPDATE IGNORE t1, t2 SET t1.a1 = 2, t2.a1 = 3 WHERE t1.a1 = 1 AND t2.a1 = 2;
ERROR 42000: TRIGGER command denied to user 'mysqltest_u1'@'localhost' for table 't1'
DROP DATABASE mysqltest_db1;
DROP USER mysqltest_u1@localhost;
USE test;
End of 5.1 tests.
#
# WL#2284: Increase the length of a user name
#
CREATE DATABASE test1;
CREATE TABLE test1.t1 (
int_field INTEGER UNSIGNED NOT NULL,
char_field CHAR(10),
INDEX(`int_field`)
);
use test;
CREATE USER user_name_len_22_01234@localhost;
CREATE USER user_name_len_32_012345678901234@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name_len_22_01234@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name_len_32_012345678901234@localhost;
# Check that user_name_len_22_01234 has no SELECT permission ON t1
SELECT * FROM test1.t1;
ERROR 42000: SELECT command denied to user 'user_name_len_22_01234'@'localhost' for table 't1'
# Check that user_name_len_32_012345678901234 has no SELECT permission ON t1
SELECT * FROM test1.t1;
ERROR 42000: SELECT command denied to user 'user_name_len_32_012345678901234'@'localhost' for table 't1'
GRANT ALL ON test1.* TO user_name_len_32_012345678901234@localhost;
CREATE DEFINER=user_name_len_32_012345678901234@localhost
TRIGGER test1.t1_ai AFTER INSERT ON test1.t1 FOR EACH ROW SET @a = (SELECT COUNT(*) FROM test1.t1);
GRANT INSERT ON test1.t1 TO user_name_len_22_01234@localhost;
# Now user_name_len_22_01234 should be able to "SELECT" COUNT(*) FROM
# test1.t1 by using the trigger
set @a:=0;
INSERT INTO test1.t1 VALUES (1,'haha');
SELECT @a;
@a
1
CREATE DEFINER=user_name_len_33_0123456789012345@localhost
TRIGGER test1.t1_bi BEFORE INSERT ON test1.t1 FOR EACH ROW SET @a = (SELECT COUNT(*) FROM test1.t1);
ERROR HY000: String 'user_name_len_33_0123456789012345' is too long for user name (should be no longer than 32)
DROP DATABASE test1;
DROP USER user_name_len_22_01234@localhost;
DROP USER user_name_len_32_012345678901234@localhost;