Edit File: memory_trig_03e.result
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; USE test; Testcase for db level: ---------------------- drop database if exists priv_db; drop database if exists no_priv_db; create database priv_db; create database no_priv_db; use priv_db; create table t1 (f1 char(20)) engine= memory; create User test_yesprivs@localhost; set password for test_yesprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; grant select on priv_db.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost' create User test_noprivs@localhost; set password for test_noprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; grant select,insert on priv_db.* to test_noprivs@localhost; show grants for test_noprivs@localhost; Grants for test_noprivs@localhost GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT ON `priv_db`.* TO 'test_noprivs'@'localhost' no trigger privilege on db level for create: -------------------------------------------- use priv_db; create trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig 1_1-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' use priv_db; insert into t1 (f1) values ('insert-yes'); select f1 from t1 order by f1; f1 insert-yes select current_user; current_user root@localhost grant TRIGGER on priv_db.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' trigger privilege on db level for create: ----------------------------------------- select current_user; current_user test_yesprivs@localhost use priv_db; create trigger trg1_2 before INSERT on t1 for each row set new.f1 = 'trig 1_2-yes'; select current_user; current_user test_noprivs@localhost use priv_db; insert into t1 (f1) values ('insert-yes'); ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1' select f1 from t1 order by f1; f1 insert-yes select current_user; current_user root@localhost grant UPDATE on priv_db.* to test_yesprivs@localhost; use priv_db; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 insert-yes trig 1_2-yes select current_user; current_user test_noprivs@localhost use priv_db; insert into t1 (f1) values ('insert-yes'); select f1 from t1 order by f1; f1 insert-yes trig 1_2-yes trig 1_2-yes select current_user; current_user root@localhost revoke TRIGGER on priv_db.* from test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost' no trigger privilege on db level for drop: ------------------------------------------ select current_user; current_user test_yesprivs@localhost use priv_db; drop trigger trg1_2; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select current_user; current_user test_noprivs@localhost use priv_db; no trigger privilege at activation time: ---------------------------------------- insert into t1 (f1) values ('insert-yes'); ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 insert-yes trig 1_2-yes trig 1_2-yes trigger privilege at activation time: ------------------------------------- select current_user; current_user root@localhost grant TRIGGER on priv_db.* to test_yesprivs@localhost; select current_user; current_user test_noprivs@localhost use priv_db; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 insert-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes trigger privilege on db level for drop: --------------------------------------- select current_user; current_user test_yesprivs@localhost show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD <secret> GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' drop trigger trg1_2; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' takes effect after use priv_db: ------------------------------- use priv_db; drop trigger trg1_2; select current_user; current_user root@localhost use priv_db; insert into t1 (f1) values ('insert-yes'); select f1 from t1 order by f1; f1 insert-yes insert-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes switch to db without having trigger priv for it: ------------------------------------------------ use no_priv_db; create table t1 (f1 char(20)) engine= memory; grant SELECT,UPDATE on no_priv_db.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' GRANT SELECT, UPDATE ON `no_priv_db`.* TO 'test_yesprivs'@'localhost' use db with trigger privilege on db level and without...: --------------------------------------------------------- select current_user; current_user test_yesprivs@localhost use no_priv_db; create trigger trg1_3 before INSERT on t1 for each row set new.f1 = 'trig 1_3-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' use priv_db; create trigger trg1_3 before INSERT on t1 for each row set new.f1 = 'trig 1_3-yes'; use no_priv_db; create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' use priv_db; create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-yes'; select current_user; current_user test_noprivs@localhost use no_priv_db; ERROR 42000: Access denied for user 'test_noprivs'@'localhost' to database 'no_priv_db' insert into t1 (f1) values ('insert-yes'); select f1 from t1 order by f1; f1 insert-yes insert-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes trig 1_3-yes use priv_db; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 insert-yes insert-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes trig 1_3-yes trig 1_3-yes select current_user; current_user test_yesprivs@localhost use no_priv_db; drop trigger trg1_3; ERROR HY000: Trigger does not exist use priv_db; drop trigger trg1_3; use no_priv_db; drop trigger trg1_4; ERROR HY000: Trigger does not exist use priv_db; drop trigger trg1_4; select current_user; current_user root@localhost drop table priv_db.t1; drop table no_priv_db.t1; drop database if exists priv_db; drop database if exists no_priv_db; drop user test_yesprivs@localhost; drop user test_noprivs@localhost; ######### Testcase for table level: ######## --------------------------------------------------- drop database if exists priv_db; create database priv_db; use priv_db; create table t1 (f1 char(20)) engine= memory; create User test_yesprivs@localhost; set password for test_yesprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; create User test_noprivs@localhost; set password for test_noprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; no trigger privilege on table level for create: ----------------------------------------------- select current_user; current_user root@localhost show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation grant select, insert, update on priv_db.t1 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' grant select, update, insert on priv_db.t1 to test_noprivs@localhost; show grants for test_noprivs@localhost; Grants for test_noprivs@localhost GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_noprivs'@'localhost' select current_user; current_user test_yesprivs@localhost use priv_db; show tables; Tables_in_priv_db t1 create trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig 1_1-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select current_user; current_user test_noprivs@localhost use priv_db; insert into t1 (f1) values ('insert1-yes'); select f1 from t1 order by f1; f1 insert1-yes select current_user; current_user root@localhost show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation show tables; Tables_in_priv_db t1 insert into t1 (f1) values ('insert2-yes'); select f1 from t1 order by f1; f1 insert1-yes insert2-yes grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' trigger privilege on table level for create: -------------------------------------------- select current_user; current_user test_yesprivs@localhost show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation create trigger trg1_2 before INSERT on t1 for each row set new.f1 = 'trig 1_2-yes'; select current_user; current_user test_noprivs@localhost insert into t1 (f1) values ('insert3-no'); select f1 from t1 order by f1; f1 insert1-yes insert2-yes trig 1_2-yes select current_user; current_user root@localhost insert into t1 (f1) values ('insert4-no'); select f1 from t1 order by f1; f1 insert1-yes insert2-yes trig 1_2-yes trig 1_2-yes revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' no trigger privilege on table level for drop: --------------------------------------------- select current_user; current_user test_yesprivs@localhost drop trigger trg1_2; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' no trigger privilege at activation time: ---------------------------------------- select current_user; current_user test_noprivs@localhost insert into t1 (f1) values ('insert5-no'); ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 insert1-yes insert2-yes trig 1_2-yes trig 1_2-yes select current_user; current_user root@localhost grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; trigger privilege at activation time: ------------------------------------- select current_user; current_user test_noprivs@localhost insert into t1 (f1) values ('insert6-no'); select f1 from t1 order by f1; f1 insert1-yes insert2-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes trigger privilege on table level for drop: ------------------------------------------ select current_user; current_user test_yesprivs@localhost show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD <secret> GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' drop trigger trg1_2; select current_user; current_user test_noprivs@localhost insert into t1 (f1) values ('insert7-yes'); select f1 from t1 order by f1; f1 insert1-yes insert2-yes insert7-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes select current_user; current_user root@localhost insert into t1 (f1) values ('insert8-yes'); select f1 from t1 order by f1; f1 insert1-yes insert2-yes insert7-yes insert8-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes switch to table without having trigger priv for it: --------------------------------------------------- create table t2 (f1 char(20)) engine= memory; grant SELECT, INSERT, UPDATE on priv_db.t2 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost' GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' grant SELECT, INSERT, UPDATE on priv_db.t2 to test_noprivs@localhost; show grants for test_noprivs@localhost; Grants for test_noprivs@localhost GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_noprivs'@'localhost' GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO 'test_noprivs'@'localhost' use table with trigger privilege and without...: ------------------------------------------------ select current_user; current_user test_yesprivs@localhost create trigger trg2_1 before INSERT on t2 for each row set new.f1 = 'trig 2_1-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2' create trigger trg1_3 before INSERT on t1 for each row set new.f1 = 'trig 1_3-yes'; create trigger trg2_2 before UPDATE on t2 for each row set new.f1 = 'trig 2_2-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2' create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-yes'; show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci select current_user; current_user test_noprivs@localhost insert into t2 (f1) values ('insert9-yes'); select f1 from t2 order by f1; f1 insert9-yes insert into t1 (f1) values ('insert10-no'); select f1 from t1 order by f1; f1 insert1-yes insert2-yes insert7-yes insert8-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes trig 1_3-yes select current_user; current_user test_yesprivs@localhost drop trigger trg2_1; ERROR HY000: Trigger does not exist drop trigger trg1_3; drop trigger trg2_2; ERROR HY000: Trigger does not exist drop trigger trg1_4; select current_user; current_user root@localhost drop database if exists priv_db; drop user test_yesprivs@localhost; drop user test_noprivs@localhost; #### Testcase for mix of user(global) and db level: #### -------------------------------------------------------- drop database if exists priv_db; drop database if exists no_priv_db; create database priv_db; create database no_priv_db; use priv_db; create table t1 (f1 char(20)) engine= memory; use no_priv_db; create table t1 (f1 char(20)) engine= memory; create User test_yesprivs@localhost; set password for test_yesprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; grant ALL on *.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT ALL PRIVILEGES ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' create User test_noprivs@localhost; set password for test_noprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; grant SELECT,INSERT on *.* to test_noprivs@localhost; show grants for test_noprivs@localhost; Grants for test_noprivs@localhost GRANT SELECT, INSERT ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' select current_user; current_user test_yesprivs@localhost trigger privilege on user level for create: ------------------------------------------- use priv_db; create trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig 1_1-yes'; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 trig 1_1-yes use no_priv_db; create trigger priv_db.trg1_5 before UPDATE on priv_db.t1 for each row set new.f1 = 'trig 1_5-yes'; insert into priv_db.t1 (f1) values ('insert-no'); select f1 from priv_db.t1 order by f1; f1 trig 1_1-yes trig 1_1-yes drop trigger priv_db.trg1_5; select current_user; current_user test_noprivs@localhost use priv_db; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes select current_user; current_user root@localhost use priv_db; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes revoke TRIGGER on *.* from test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, CREATE TABLESPACE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' select current_user; current_user test_yesprivs@localhost use priv_db; show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation select * from information_schema.triggers; 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 drop trigger trg1_1; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select current_user; current_user root@localhost show grants; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION drop trigger trg1_1; use priv_db; no trigger privilege on db level for create: -------------------------------------------- select current_user; current_user test_yesprivs@localhost create trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig 1_1-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select current_user; current_user test_noprivs@localhost use priv_db; insert into t1 (f1) values ('insert-yes'); select f1 from t1 order by f1; f1 insert-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes select current_user; current_user root@localhost grant TRIGGER on priv_db.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, CREATE TABLESPACE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' trigger privilege on db level for create: ----------------------------------------- select current_user; current_user test_yesprivs@localhost use priv_db; create trigger trg1_2 before INSERT on t1 for each row set new.f1 = 'trig 1_2-yes'; create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1 for each row set new.f1 = 'trig 1_9-yes'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' use no_priv_db; create trigger trg1_2 before INSERT on t1 for each row set new.f1 = 'trig 1_2-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' create trigger priv_db.trg1_9 before UPDATE on priv_db.t1 for each row set new.f1 = 'trig 1_9-yes'; select current_user; current_user test_noprivs@localhost use priv_db; insert into t1 (f1) values ('insert-yes'); select f1 from t1 order by f1; f1 insert-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_2-yes use no_priv_db; insert into t1 (f1) values ('insert-yes'); select f1 from t1 order by f1; f1 insert-yes drop trigger priv_db.trg1_9; ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1' select current_user; current_user root@localhost drop trigger priv_db.trg1_9; revoke TRIGGER on priv_db.* from test_yesprivs@localhost; use priv_db; insert into t1 (f1) values ('insert-yes'); ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 insert-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_2-yes grant TRIGGER on *.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT ALL PRIVILEGES ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' select current_user; current_user test_yesprivs@localhost use no_priv_db; create trigger trg1_2 before INSERT on t1 for each row set new.f1 = 'trig 1_2-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select current_user; current_user test_noprivs@localhost use priv_db; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 insert-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_2-yes trig 1_2-yes use no_priv_db; insert into t1 (f1) values ('insert-yes'); select f1 from t1 order by f1; f1 insert-yes insert-yes select current_user; current_user test_yesprivs@localhost use no_priv_db; create trigger trg1_2 before INSERT on t1 for each row set new.f1 = 'trig 1_2-yes'; select current_user; current_user test_noprivs@localhost use priv_db; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 insert-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes use no_priv_db; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 insert-yes insert-yes trig 1_2-yes select current_user; current_user root@localhost drop database if exists priv_db; drop database if exists no_priv_db; drop database if exists h1; drop user test_yesprivs@localhost; drop user test_noprivs@localhost; ####### Testcase for mix of db and table level: ####### ------------------------------------------------------- drop database if exists priv1_db; drop database if exists priv2_db; create database priv1_db; create database priv2_db; use priv1_db; create table t1 (f1 char(20)) engine= memory; create table t2 (f1 char(20)) engine= memory; use priv2_db; create table t1 (f1 char(20)) engine= memory; create User test_yesprivs@localhost; set password for test_yesprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; grant ALL on priv1_db.* to test_yesprivs@localhost; grant SELECT,UPDATE on priv2_db.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost' GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost' create User test_noprivs@localhost; set password for test_noprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; grant SELECT,INSERT,UPDATE on priv1_db.* to test_noprivs@localhost; grant SELECT,INSERT on priv2_db.* to test_noprivs@localhost; show grants for test_noprivs@localhost; Grants for test_noprivs@localhost GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE ON `priv1_db`.* TO 'test_noprivs'@'localhost' GRANT SELECT, INSERT ON `priv2_db`.* TO 'test_noprivs'@'localhost' use priv1_db; use priv1_db; trigger privilege on one db1 db level, not on db2 ------------------------------------------------- select current_user; current_user test_yesprivs@localhost use priv1_db; create trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig 1_1-yes'; create trigger trg2_1 before INSERT on t2 for each row set new.f1 = 'trig 2_1-yes'; use priv2_db; create trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig1_1-yes'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select current_user; current_user test_noprivs@localhost insert into t1 (f1) values ('insert1_no'); select f1 from t1 order by f1; f1 trig 1_1-yes insert into t2 (f1) values ('insert1_no'); select f1 from t2 order by f1; f1 trig 2_1-yes insert into priv2_db.t1 (f1) values ('insert21-yes'); select f1 from priv2_db.t1 order by f1; f1 insert21-yes use priv2_db; insert into t1 (f1) values ('insert1_yes'); select f1 from t1 order by f1; f1 insert1_yes insert21-yes insert into priv1_db.t1 (f1) values ('insert11-no'); select f1 from priv1_db.t1 order by f1; f1 trig 1_1-yes trig 1_1-yes insert into priv1_db.t2 (f1) values ('insert22-no'); select f1 from priv1_db.t2 order by f1; f1 trig 2_1-yes trig 2_1-yes revoke trigger privilege on table level (not existing) ------------------------------------------------------ select current_user; current_user root@localhost use priv1_db; revoke TRIGGER on priv1_db.t1 from test_yesprivs@localhost; ERROR 42000: There is no such grant defined for user 'test_yesprivs' on host 'localhost' on table 't1' show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost' GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost' select current_user; current_user test_yesprivs@localhost drop trigger trg1_1; ERROR HY000: Trigger does not exist drop trigger trg2_1; ERROR HY000: Trigger does not exist use priv1_db; drop trigger trg1_1; drop trigger trg2_1; select current_user; current_user root@localhost use priv1_db; revoke TRIGGER on priv1_db.* from test_yesprivs@localhost; no trigger privilege on table level for create: ----------------------------------------------- select current_user; current_user test_yesprivs@localhost use priv1_db; create trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig 1_1-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select current_user; current_user root@localhost show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation grant TRIGGER on priv1_db.t1 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `priv1_db`.* TO 'test_yesprivs'@'localhost' GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost' GRANT TRIGGER ON `priv1_db`.`t1` TO 'test_yesprivs'@'localhost' trigger privilege on table level for create: -------------------------------------------- select current_user; current_user test_yesprivs@localhost show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation create trigger trg1_2 before INSERT on t1 for each row set new.f1 = 'trig 1_2-yes'; create trigger trg2_1 before INSERT on t2 for each row set new.f1 = 'trig 2_1-no'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2' select current_user; current_user test_noprivs@localhost use priv1_db; insert into t1 (f1) values ('insert2-no'); select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_2-yes insert into t2 (f1) values ('insert2-yes'); select f1 from t2 order by f1; f1 insert2-yes trig 2_1-yes trig 2_1-yes insert into priv2_db.t1 (f1) values ('insert22-yes'); select f1 from priv2_db.t1 order by f1; f1 insert1_yes insert21-yes insert22-yes select current_user; current_user root@localhost grant TRIGGER on priv1_db.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost' GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost' GRANT TRIGGER ON `priv1_db`.`t1` TO 'test_yesprivs'@'localhost' select current_user; current_user test_yesprivs@localhost create trigger trg2_1 before INSERT on t2 for each row set new.f1 = 'trig 2_1-yes'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2' use priv1_db; create trigger trg2_1 before INSERT on t2 for each row set new.f1 = 'trig 2_1-yes'; select current_user; current_user test_noprivs@localhost use priv1_db; insert into t1 (f1) values ('insert3-no'); select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_2-yes trig 1_2-yes insert into t2 (f1) values ('insert3-no'); select f1 from t2 order by f1; f1 insert2-yes trig 2_1-yes trig 2_1-yes trig 2_1-yes use priv2_db; insert into priv1_db.t1 (f1) values ('insert12-no'); select f1 from priv1_db.t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes insert into priv1_db.t2 (f1) values ('insert23-no'); select f1 from priv1_db.t2 order by f1; f1 insert2-yes trig 2_1-yes trig 2_1-yes trig 2_1-yes trig 2_1-yes select current_user; current_user test_yesprivs@localhost drop trigger trg1_2; drop trigger trg2_1; select current_user; current_user root@localhost drop database if exists priv1_db; drop database if exists priv2_db; drop user test_yesprivs@localhost; drop user test_noprivs@localhost; #### Testcase for trigger privilege on execution time ######## -------------------------------------------------------------- drop database if exists priv_db; create database priv_db; use priv_db; create table t1 (f1 char(20)) engine= memory; create User test_yesprivs@localhost; set password for test_yesprivs@localhost = password('PWD'); create User test_useprivs@localhost; set password for test_useprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; revoke ALL PRIVILEGES, GRANT OPTION FROM test_useprivs@localhost; select current_user; current_user root@localhost show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation grant select, insert, update ,trigger on priv_db.t1 to test_yesprivs@localhost with grant option; grant select on priv_db.t1 to test_useprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION select current_user; current_user test_yesprivs@localhost use priv_db; create trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig 1_1-yes'; grant insert on t1 to test_useprivs@localhost; prepare ins1 from 'insert into t1 (f1) values (''insert1-no'')'; execute ins1; select f1 from t1 order by f1; f1 trig 1_1-yes prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')'; select current_user; current_user test_useprivs@localhost use priv_db; prepare ins1 from 'insert into t1 (f1) values (''insert3-no'')'; execute ins1; select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes select current_user; current_user root@localhost revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION select current_user; current_user test_yesprivs@localhost execute ins1; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')'; select current_user; current_user test_useprivs@localhost prepare ins1 from 'insert into t1 (f1) values (''insert5-no'')'; execute ins1; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes select current_user; current_user root@localhost grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION select current_user; current_user test_yesprivs@localhost execute ins1; select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')'; select current_user; current_user test_useprivs@localhost execute ins1; select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')'; select current_user; current_user root@localhost revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION select current_user; current_user test_yesprivs@localhost execute ins1; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes select current_user; current_user test_useprivs@localhost execute ins1; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes select current_user; current_user root@localhost grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION select current_user; current_user test_yesprivs@localhost execute ins1; select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes select current_user; current_user test_useprivs@localhost execute ins1; select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes select current_user; current_user root@localhost revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION select current_user; current_user test_yesprivs@localhost execute ins1; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes deallocate prepare ins1; select current_user; current_user test_useprivs@localhost execute ins1; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes deallocate prepare ins1; select current_user; current_user root@localhost grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION select current_user; current_user test_yesprivs@localhost drop trigger trg1_1; select current_user; current_user root@localhost select current_user; current_user root@localhost drop database if exists priv_db; drop user test_yesprivs@localhost; drop user test_useprivs@localhost; ######### Testcase for definer: ######## ----------------------------------------------- drop database if exists priv_db; create database priv_db; use priv_db; create table t1 (f1 char(20)) engine= memory; create User test_yesprivs@localhost; set password for test_yesprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; select current_user; current_user root@localhost create definer=not_ex_user@localhost trigger trg1_0 before INSERT on t1 for each row set new.f1 = 'trig 1_0-yes'; Warnings: Note 1449 The user specified as a definer ('not_ex_user'@'localhost') does not exist drop trigger trg1_0; create definer=test_yesprivs@localhost trigger trg1_0 before INSERT on t1 for each row set new.f1 = 'trig 1_0-yes'; grant select, insert, update on priv_db.t1 to test_yesprivs@localhost; select current_user; current_user test_yesprivs@localhost use priv_db; insert into t1 (f1) values ('insert-no'); ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f1 from t1 order by f1; f1 drop trigger trg1_0; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select current_user; current_user root@localhost grant select, insert, update ,trigger on priv_db.t1 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' select current_user; current_user test_yesprivs@localhost insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 trig 1_0-yes drop trigger trg1_0; create definer=not_ex_user@localhost trigger trg1_0 before INSERT on t1 for each row set new.f1 = 'trig 1_0-yes'; ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation create definer=current_user trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig 1_1-yes'; insert into t1 (f1) values ('insert-no'); select f1 from t1 order by f1; f1 trig 1_0-yes trig 1_1-yes create definer=test_yesprivs@localhost trigger trg1_2 before UPDATE on t1 for each row set new.f1 = 'trig 1_2-yes'; update t1 set f1 = 'update-yes' where f1 like '%trig%'; select f1 from t1 order by f1; f1 trig 1_2-yes trig 1_2-yes select current_user; current_user root@localhost grant trigger on priv_db.* to test_yesprivs@localhost with grant option; select current_user; current_user test_yesprivs@localhost show grants; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD <secret> GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' WITH GRANT OPTION GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' create definer=not_ex_user@localhost trigger trg1_3 after UPDATE on t1 for each row set @var1 = 'trig 1_3-yes'; ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation select current_user; current_user root@localhost select current_user; current_user root@localhost drop database if exists priv_db; drop user test_yesprivs@localhost; ####### Testcase for column privileges of triggers: ####### ----------------------------------------------------------- drop database if exists priv_db; drop database if exists no_priv_db; create database priv_db; use priv_db; create table t1 (f1 char(20)) engine= memory; create table t2 (f1 char(20)) engine= memory; create User test_yesprivs@localhost; set password for test_yesprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; grant TRIGGER on priv_db.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' create User test_noprivs@localhost; set password for test_noprivs@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; grant SELECT,UPDATE on priv_db.* to test_noprivs@localhost; show grants for test_noprivs@localhost; Grants for test_noprivs@localhost GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT, UPDATE ON `priv_db`.* TO 'test_noprivs'@'localhost' update only on column: ---------------------- select current_user; current_user root@localhost grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t1 to test_yesprivs@localhost; grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t2 to test_yesprivs@localhost; select current_user; current_user test_yesprivs@localhost use priv_db; insert into t1 (f1) values ('insert1-yes'); insert into t2 (f1) values ('insert1-yes'); create trigger trg1_1 before UPDATE on t1 for each row set new.f1 = 'trig 1_1-yes'; create trigger trg2_1 before UPDATE on t2 for each row set new.f1 = 'trig 2_1-yes'; select current_user; current_user test_noprivs@localhost use priv_db; select f1 from t1 order by f1; f1 insert1-yes update t1 set f1 = 'update1_no' where f1 like '%insert%'; select f1 from t1 order by f1; f1 trig 1_1-yes select f1 from t2 order by f1; f1 insert1-yes update t2 set f1 = 'update1_no' where f1 like '%insert%'; select f1 from t2 order by f1; f1 trig 2_1-yes select current_user; current_user root@localhost revoke UPDATE on priv_db.* from test_yesprivs@localhost; revoke UPDATE(f1) on priv_db.t2 from test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' GRANT SELECT (f1), INSERT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost' GRANT SELECT (f1), INSERT, UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' select current_user; current_user test_yesprivs@localhost use priv_db; insert into t1 (f1) values ('insert2-yes'); insert into t2 (f1) values ('insert2-yes'); select current_user; current_user test_noprivs@localhost use priv_db; update t1 set f1 = 'update2_no' where f1 like '%insert%'; update t2 set f1 = 'update2_no' where f1 like '%insert%'; ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2' update t1 set f1 = 'update3_no' where f1 like '%insert%'; update t2 set f1 = 'update3_no' where f1 like '%insert%'; ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2' select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes select f1 from t2 order by f1; f1 insert2-yes trig 2_1-yes check if access only on one of three columns -------------------------------------------- select current_user; current_user root@localhost alter table priv_db.t1 add f2 char(20), add f3 int; revoke TRIGGER on priv_db.* from test_yesprivs@localhost; grant TRIGGER,SELECT on priv_db.t1 to test_yesprivs@localhost; grant UPDATE on priv_db.t2 to test_yesprivs@localhost; select current_user; current_user test_yesprivs@localhost use priv_db; insert into t1 values ('insert2-yes','insert2-yes',1); insert into t1 values ('insert3-yes','insert3-yes',2); select * from t1 order by f1; f1 f2 f3 insert2-yes insert2-yes 1 insert3-yes insert3-yes 2 trig 1_1-yes NULL NULL trig 1_1-yes NULL NULL select current_user; current_user test_noprivs@localhost use priv_db; update t1 set f1 = 'update4-no', f2 = 'update4-yes', f3 = f3*10 where f2 like '%yes'; select * from t1 order by f1,f2,f3; f1 f2 f3 trig 1_1-yes NULL NULL trig 1_1-yes NULL NULL trig 1_1-yes update4-yes 10 trig 1_1-yes update4-yes 20 select current_user; current_user test_yesprivs@localhost create trigger trg1_2 after UPDATE on t1 for each row set @f2 = 'trig 1_2-yes'; select current_user; current_user test_noprivs@localhost update t1 set f1 = 'update5-yes', f2 = 'update5-yes' where f2 like '%yes'; select * from t1 order by f1,f2,f3; f1 f2 f3 trig 1_1-yes NULL NULL trig 1_1-yes NULL NULL trig 1_1-yes update5-yes 10 trig 1_1-yes update5-yes 20 select @f2; @f2 trig 1_2-yes update t1 set f1 = 'update6_no' where f1 like '%insert%'; update t2 set f1 = 'update6_no' where f1 like '%insert%'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2' update t1 set f1 = 'update7_no' where f1 like '%insert%'; update t2 set f1 = 'update7_no' where f1 like '%insert%'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2' select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes select f1 from t2 order by f1; f1 insert2-yes trig 2_1-yes check if rejected without trigger privilege: -------------------------------------------- select current_user; current_user root@localhost revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; select current_user; current_user test_noprivs@localhost update t1 set f1 = 'update8-no', f2 = 'update8-no' where f2 like '%yes'; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select * from t1 order by f1,f2,f3; f1 f2 f3 trig 1_1-yes NULL NULL trig 1_1-yes NULL NULL trig 1_1-yes update5-yes 10 trig 1_1-yes update5-yes 20 select @f2; @f2 trig 1_2-yes check trigger, but not update privilege on column: -------------------------------------------------- select current_user; current_user root@localhost revoke UPDATE(f1) on priv_db.t1 from test_yesprivs@localhost; grant TRIGGER,UPDATE(f2),UPDATE(f3) on priv_db.t1 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost' GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2), TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' select current_user; current_user test_yesprivs@localhost use priv_db; drop trigger trg1_1; create trigger trg1_3 before UPDATE on t1 for each row set new.f1 = 'trig 1_3-yes'; select current_user; current_user test_noprivs@localhost use priv_db; update t1 set f1 = 'update9-no', f2 = 'update9-no' where f2 like '%yes'; ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1' select * from t1 order by f1,f2,f3; f1 f2 f3 trig 1_1-yes NULL NULL trig 1_1-yes NULL NULL trig 1_1-yes update5-yes 10 trig 1_1-yes update5-yes 20 update t1 set f3= f3+1; ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1' select f3 from t1 order by f3; f3 NULL NULL 10 20 select current_user; current_user root@localhost revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; grant UPDATE(f1),UPDATE(f2),UPDATE(f3) on priv_db.t1 to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost' GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2, f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' select current_user; current_user test_noprivs@localhost use priv_db; update t1 set f3= f3+1; ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1' select f3 from t1 order by f3; f3 NULL NULL 10 20 ##### trigger privilege on column level? ####### ------------------------------------------------ grant TRIGGER(f1) on priv_db.t1 to test_yesprivs@localhost; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(f1) on priv_db.t1 to test_yesprivs@localhost' at line 1 select current_user; current_user root@localhost drop database if exists priv_db; drop user test_yesprivs@localhost; drop user test_noprivs@localhost;
Back to File Manager