diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/r/memory_trig_03e.result')
| -rw-r--r-- | mysql-test/suite/funcs_1/r/memory_trig_03e.result | 139 |
1 files changed, 139 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/memory_trig_03e.result b/mysql-test/suite/funcs_1/r/memory_trig_03e.result index cfa839b382c..768e1577177 100644 --- a/mysql-test/suite/funcs_1/r/memory_trig_03e.result +++ b/mysql-test/suite/funcs_1/r/memory_trig_03e.result @@ -25,6 +25,7 @@ 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' +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; no trigger privilege on db level for create: -------------------------------------------- @@ -32,11 +33,13 @@ 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' +connect no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; use priv_db; insert into t1 (f1) values ('insert-yes'); select f1 from t1 order by f1; f1 insert-yes +connection default; select current_user; current_user root@localhost @@ -48,12 +51,14 @@ GRANT SELECT, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' trigger privilege on db level for create: ----------------------------------------- +connection yes_privs; 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'; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -63,6 +68,7 @@ ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for colum select f1 from t1 order by f1; f1 insert-yes +connection default; select current_user; current_user root@localhost @@ -73,6 +79,7 @@ select f1 from t1 order by f1; f1 insert-yes trig 1_2-yes +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -83,6 +90,7 @@ f1 insert-yes trig 1_2-yes trig 1_2-yes +connection default; select current_user; current_user root@localhost @@ -94,12 +102,14 @@ GRANT SELECT, UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost' no trigger privilege on db level for drop: ------------------------------------------ +connection yes_privs; 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' +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -117,10 +127,12 @@ trig 1_2-yes trigger privilege at activation time: ------------------------------------- +connection default; select current_user; current_user root@localhost grant TRIGGER on priv_db.* to test_yesprivs@localhost; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -135,6 +147,7 @@ trig 1_2-yes trigger privilege on db level for drop: --------------------------------------- +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -149,6 +162,7 @@ takes effect after use priv_db: ------------------------------- use priv_db; drop trigger trg1_2; +connection default; select current_user; current_user root@localhost @@ -175,6 +189,7 @@ GRANT SELECT, UPDATE ON `no_priv_db`.* TO 'test_yesprivs'@'localhost' use db with trigger privilege on db level and without...: --------------------------------------------------------- +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -192,6 +207,7 @@ ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for tabl use priv_db; create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-yes'; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -217,6 +233,8 @@ trig 1_2-yes trig 1_2-yes trig 1_3-yes trig 1_3-yes +disconnect no_privs; +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -230,6 +248,8 @@ drop trigger trg1_4; ERROR HY000: Trigger does not exist use priv_db; drop trigger trg1_4; +disconnect yes_privs; +connection default; select current_user; current_user root@localhost @@ -252,9 +272,12 @@ 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; +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; no trigger privilege on table level for create: ----------------------------------------------- +connection default; select current_user; current_user root@localhost @@ -270,6 +293,7 @@ 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' +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -280,6 +304,7 @@ 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' +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -288,6 +313,7 @@ insert into t1 (f1) values ('insert1-yes'); select f1 from t1 order by f1; f1 insert1-yes +connection default; select current_user; current_user root@localhost @@ -309,6 +335,7 @@ GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'loca trigger privilege on table level for create: -------------------------------------------- +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -316,6 +343,7 @@ 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'; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -325,6 +353,7 @@ f1 insert1-yes insert2-yes trig 1_2-yes +connection default; select current_user; current_user root@localhost @@ -343,6 +372,7 @@ GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' no trigger privilege on table level for drop: --------------------------------------------- +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -351,6 +381,7 @@ ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for tabl no trigger privilege at activation time: ---------------------------------------- +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -362,6 +393,7 @@ insert1-yes insert2-yes trig 1_2-yes trig 1_2-yes +connection default; select current_user; current_user root@localhost @@ -369,6 +401,7 @@ grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; trigger privilege at activation time: ------------------------------------- +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -383,6 +416,7 @@ trig 1_2-yes trigger privilege on table level for drop: ------------------------------------------ +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -391,6 +425,7 @@ 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' drop trigger trg1_2; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -403,6 +438,7 @@ insert7-yes trig 1_2-yes trig 1_2-yes trig 1_2-yes +connection default; select current_user; current_user root@localhost @@ -435,6 +471,7 @@ GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO 'test_noprivs'@'localhost' use table with trigger privilege and without...: ------------------------------------------------ +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -452,6 +489,7 @@ 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 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -470,6 +508,8 @@ trig 1_2-yes trig 1_2-yes trig 1_2-yes trig 1_3-yes +disconnect no_privs; +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -479,6 +519,8 @@ drop trigger trg1_3; drop trigger trg2_2; ERROR HY000: Trigger does not exist drop trigger trg1_4; +disconnect yes_privs; +connection default; select current_user; current_user root@localhost @@ -510,6 +552,9 @@ 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' +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -533,6 +578,7 @@ f1 trig 1_1-yes trig 1_1-yes drop trigger priv_db.trg1_5; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -543,6 +589,7 @@ f1 trig 1_1-yes trig 1_1-yes trig 1_1-yes +connection default; select current_user; current_user root@localhost @@ -558,6 +605,8 @@ 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' +disconnect yes_privs; +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; select current_user; current_user test_yesprivs@localhost @@ -568,6 +617,7 @@ 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' +connection default; select current_user; current_user root@localhost @@ -580,12 +630,14 @@ use priv_db; no trigger privilege on db level for create: -------------------------------------------- +connection yes_privs; 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' +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -598,6 +650,7 @@ trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes +connection default; select current_user; current_user root@localhost @@ -609,6 +662,7 @@ GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' trigger privilege on db level for create: ----------------------------------------- +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -626,6 +680,7 @@ ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for tabl create trigger priv_db.trg1_9 before UPDATE on priv_db.t1 for each row set new.f1 = 'trig 1_9-yes'; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -646,6 +701,7 @@ f1 insert-yes drop trigger priv_db.trg1_9; ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1' +connection default; select current_user; current_user root@localhost @@ -666,6 +722,7 @@ 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' +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -673,6 +730,7 @@ 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' +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -693,12 +751,16 @@ select f1 from t1 order by f1; f1 insert-yes insert-yes +disconnect yes_privs; +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; 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'; +disconnect yes_privs; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -721,6 +783,8 @@ f1 insert-yes insert-yes trig 1_2-yes +disconnect no_privs; +connection default; select current_user; current_user root@localhost @@ -761,11 +825,14 @@ 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' +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; use priv1_db; +connect no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; use priv1_db; trigger privilege on one db1 db level, not on db2 ------------------------------------------------- +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -778,6 +845,7 @@ 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' +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -812,6 +880,7 @@ trig 2_1-yes revoke trigger privilege on table level (not existing) ------------------------------------------------------ +connection default; select current_user; current_user root@localhost @@ -823,6 +892,7 @@ 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' +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -833,6 +903,7 @@ ERROR HY000: Trigger does not exist use priv1_db; drop trigger trg1_1; drop trigger trg2_1; +connection default; select current_user; current_user root@localhost @@ -841,6 +912,7 @@ revoke TRIGGER on priv1_db.* from test_yesprivs@localhost; no trigger privilege on table level for create: ----------------------------------------------- +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -848,6 +920,7 @@ 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' +connection default; select current_user; current_user root@localhost @@ -863,6 +936,7 @@ GRANT TRIGGER ON `priv1_db`.`t1` TO 'test_yesprivs'@'localhost' trigger privilege on table level for create: -------------------------------------------- +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -873,6 +947,7 @@ 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' +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -895,6 +970,7 @@ f1 insert1_yes insert21-yes insert22-yes +connection default; select current_user; current_user root@localhost @@ -905,6 +981,7 @@ GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C4973 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' +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -914,6 +991,7 @@ ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for tabl use priv1_db; create trigger trg2_1 before INSERT on t2 for each row set new.f1 = 'trig 2_1-yes'; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -949,11 +1027,15 @@ trig 2_1-yes trig 2_1-yes trig 2_1-yes trig 2_1-yes +disconnect no_privs; +connection yes_privs; select current_user; current_user test_yesprivs@localhost drop trigger trg1_2; drop trigger trg2_1; +disconnect yes_privs; +connection default; select current_user; current_user root@localhost @@ -974,6 +1056,8 @@ 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; +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; select current_user; current_user root@localhost @@ -988,6 +1072,7 @@ 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 +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1001,6 +1086,7 @@ select f1 from t1 order by f1; f1 trig 1_1-yes prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')'; +connect use_privs,localhost,test_useprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; select current_user; current_user test_useprivs@localhost @@ -1011,6 +1097,7 @@ select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes +connection default; select current_user; current_user root@localhost @@ -1019,6 +1106,7 @@ 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 +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1029,6 +1117,7 @@ f1 trig 1_1-yes trig 1_1-yes prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')'; +connection use_privs; select current_user; current_user test_useprivs@localhost @@ -1039,6 +1128,7 @@ select f1 from t1 order by f1; f1 trig 1_1-yes trig 1_1-yes +connection default; select current_user; current_user root@localhost @@ -1047,6 +1137,7 @@ 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 +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1057,6 +1148,7 @@ trig 1_1-yes trig 1_1-yes trig 1_1-yes prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')'; +connection use_privs; select current_user; current_user test_useprivs@localhost @@ -1068,6 +1160,7 @@ trig 1_1-yes trig 1_1-yes trig 1_1-yes prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')'; +connection default; select current_user; current_user root@localhost @@ -1076,6 +1169,7 @@ 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 +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1087,6 +1181,7 @@ trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes +connection use_privs; select current_user; current_user test_useprivs@localhost @@ -1098,6 +1193,7 @@ trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes +connection default; select current_user; current_user root@localhost @@ -1106,6 +1202,7 @@ 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 +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1117,6 +1214,7 @@ trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes +connection use_privs; select current_user; current_user test_useprivs@localhost @@ -1129,6 +1227,7 @@ trig 1_1-yes trig 1_1-yes trig 1_1-yes trig 1_1-yes +connection default; select current_user; current_user root@localhost @@ -1137,6 +1236,7 @@ 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 +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1151,6 +1251,7 @@ trig 1_1-yes trig 1_1-yes trig 1_1-yes deallocate prepare ins1; +connection use_privs; select current_user; current_user test_useprivs@localhost @@ -1165,6 +1266,7 @@ trig 1_1-yes trig 1_1-yes trig 1_1-yes deallocate prepare ins1; +connection default; select current_user; current_user root@localhost @@ -1173,13 +1275,17 @@ 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 +connection yes_privs; select current_user; current_user test_yesprivs@localhost drop trigger trg1_1; +connection default; select current_user; current_user root@localhost +disconnect yes_privs; +connection default; select current_user; current_user root@localhost @@ -1196,6 +1302,8 @@ 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; +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; select current_user; current_user root@localhost @@ -1210,6 +1318,7 @@ 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; +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1220,6 +1329,7 @@ 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' +connection default; select current_user; current_user root@localhost @@ -1229,6 +1339,7 @@ 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' +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1257,11 +1368,13 @@ select f1 from t1 order by f1; f1 trig 1_2-yes trig 1_2-yes +connection default; select current_user; current_user root@localhost grant trigger on priv_db.* to test_yesprivs@localhost with grant option; +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1274,9 +1387,12 @@ 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 +connection default; select current_user; current_user root@localhost +disconnect yes_privs; +connection default; select current_user; current_user root@localhost @@ -1307,9 +1423,12 @@ 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' +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; update only on column: ---------------------- +connection default; select current_user; current_user root@localhost @@ -1317,6 +1436,7 @@ 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; +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1327,6 +1447,7 @@ 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'; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -1347,6 +1468,7 @@ update t2 set f1 = 'update1_no' select f1 from t2 order by f1; f1 trig 2_1-yes +connection default; select current_user; current_user root@localhost @@ -1360,12 +1482,14 @@ GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C4973 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' +connection yes_privs; 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'); +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -1391,6 +1515,7 @@ trig 2_1-yes check if access only on one of three columns -------------------------------------------- +connection default; select current_user; current_user root@localhost @@ -1398,6 +1523,7 @@ 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; +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1410,6 +1536,7 @@ insert2-yes insert2-yes 1 insert3-yes insert3-yes 2 trig 1_1-yes NULL NULL trig 1_1-yes NULL NULL +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -1424,11 +1551,13 @@ 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 +connection yes_privs; 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'; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -1467,10 +1596,12 @@ trig 2_1-yes check if rejected without trigger privilege: -------------------------------------------- +connection default; select current_user; current_user root@localhost revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -1490,6 +1621,7 @@ trig 1_2-yes check trigger, but not update privilege on column: -------------------------------------------------- +connection default; select current_user; current_user root@localhost @@ -1501,6 +1633,7 @@ 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' +connection yes_privs; select current_user; current_user test_yesprivs@localhost @@ -1508,6 +1641,7 @@ 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'; +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -1530,6 +1664,7 @@ NULL NULL 10 20 +connection default; select current_user; current_user root@localhost @@ -1541,6 +1676,7 @@ 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' +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -1558,6 +1694,9 @@ NULL ------------------------------------------------ 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 MariaDB server version for the right syntax to use near '(f1) on priv_db.t1 to test_yesprivs@localhost' at line 1 +disconnect yes_privs; +disconnect no_privs; +connection default; select current_user; current_user root@localhost |