diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/r/memory_trig_03.result')
| -rw-r--r-- | mysql-test/suite/funcs_1/r/memory_trig_03.result | 92 |
1 files changed, 92 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/memory_trig_03.result b/mysql-test/suite/funcs_1/r/memory_trig_03.result index 31de6d2759e..8fd3e034735 100644 --- a/mysql-test/suite/funcs_1/r/memory_trig_03.result +++ b/mysql-test/suite/funcs_1/r/memory_trig_03.result @@ -86,9 +86,13 @@ show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' +connect no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; Testcase 3.5.3.2: ----------------- +connection no_privs; select current_user; current_user test_noprivs@localhost @@ -96,17 +100,20 @@ use priv_db; create trigger trg1_1 before INSERT on t1 for each row set new.f1 = 'trig 3.5.3.2_1-no'; ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1' +connection default; use priv_db; insert into t1 (f1) values ('insert 3.5.3.2-no'); select f1 from t1 order by f1; f1 insert 3.5.3.2-no +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 3.5.3.2_2-yes'; +connection default; select current_user; current_user root@localhost @@ -125,9 +132,11 @@ trig 3.5.3.2_2-yes Testcase 3.5.3.6: ----------------- +connection no_privs; use priv_db; drop trigger trg1_2; ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1' +connection default; use priv_db; insert into t1 (f1) values ('insert 3.5.3.6-yes'); select f1 from t1 order by f1; @@ -135,8 +144,10 @@ f1 insert 3.5.3.2-no trig 3.5.3.2_2-yes trig 3.5.3.2_2-yes +connection yes_privs; use priv_db; drop trigger trg1_2; +connection default; use priv_db; insert into t1 (f1) values ('insert 3.5.3.6-no'); select f1 from t1 order by f1; @@ -145,7 +156,10 @@ insert 3.5.3.2-no insert 3.5.3.6-no trig 3.5.3.2_2-yes trig 3.5.3.2_2-yes +connection default; drop trigger trg1_2; +disconnect no_privs; +disconnect yes_privs; Testcase 3.5.3.7a: ------------------ @@ -160,6 +174,9 @@ grant TRIGGER, UPDATE on *.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' +connect no_privs_424a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect yes_privs_424a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection no_privs_424a; select current_user; current_user test_noprivs@localhost @@ -175,6 +192,7 @@ trig 3.5.3.2_2-yes trig 3.5.3.2_2-yes create trigger trg4a_1 before INSERT on t1 for each row set new.f1 = 'trig 3.5.3.7-1a'; +connection default; insert into t1 (f1) values ('insert 3.5.3.7-1a'); ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1' select f1 from t1 order by f1; @@ -184,6 +202,7 @@ insert 3.5.3.6-no trig 3.5.3.2_2-yes trig 3.5.3.2_2-yes drop trigger trg4a_1; +connection yes_privs_424a; use priv_db; select current_user; current_user @@ -193,6 +212,7 @@ Grants for test_yesprivs@localhost GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' create trigger trg4a_2 before INSERT on t1 for each row set new.f1 = 'trig 3.5.3.7-2a'; +connection default; insert into t1 (f1) values ('insert 3.5.3.7-2b'); select f1 from t1 order by f1; f1 @@ -202,6 +222,8 @@ trig 3.5.3.2_2-yes trig 3.5.3.2_2-yes trig 3.5.3.7-2a drop trigger trg4a_2; +disconnect no_privs_424a; +disconnect yes_privs_424a; Testcase 3.5.3.7b: ------------------ @@ -219,6 +241,10 @@ show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost' +connect no_privs_424b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect yes_privs_424b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; +connection no_privs_424b; show grants; Grants for test_noprivs@localhost GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -226,6 +252,7 @@ GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEM use priv_db; create trigger trg4b_1 before UPDATE on t1 for each row set new.f1 = 'trig 3.5.3.7-1b'; +connection default; insert into t1 (f1) values ('insert 3.5.3.7-1b'); select f1 from t1 order by f1; f1 @@ -246,6 +273,7 @@ trig 3.5.3.2_2-yes trig 3.5.3.2_2-yes trig 3.5.3.7-2a drop trigger trg4b_1; +connection yes_privs_424b; show grants; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -253,6 +281,7 @@ GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost' use priv_db; create trigger trg4b_2 before UPDATE on t1 for each row set new.f1 = 'trig 3.5.3.7-2b'; +connection default; insert into t1 (f1) values ('insert 3.5.3.7-2b'); select f1 from t1 order by f1; f1 @@ -274,6 +303,8 @@ trig 3.5.3.2_2-yes trig 3.5.3.7-2a trig 3.5.3.7-2b drop trigger trg4b_2; +disconnect no_privs_424b; +disconnect yes_privs_424b; Testcase 3.5.3.7c ----------------- @@ -291,6 +322,10 @@ show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' +connect no_privs_424c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect yes_privs_424c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; +connection no_privs_424c; show grants; Grants for test_noprivs@localhost GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -298,6 +333,7 @@ GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIE use priv_db; create trigger trg4c_1 before INSERT on t1 for each row set new.f1 = 'trig 3.5.3.7-1c'; +connection default; insert into t1 (f1) values ('insert 3.5.3.7-1c'); ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1' select f1 from t1 order by f1; @@ -310,6 +346,7 @@ trig 3.5.3.2_2-yes trig 3.5.3.7-2a trig 3.5.3.7-2b drop trigger trg4c_1; +connection yes_privs_424c; show grants; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -317,6 +354,7 @@ GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' use priv_db; create trigger trg4c_2 before INSERT on t1 for each row set new.f1 = 'trig 3.5.3.7-2c'; +connection default; insert into t1 (f1) values ('insert 3.5.3.7-2c'); select f1 from t1 order by f1; f1 @@ -329,6 +367,8 @@ trig 3.5.3.7-2a trig 3.5.3.7-2b trig 3.5.3.7-2c drop trigger trg4c_2; +disconnect no_privs_424c; +disconnect yes_privs_424c; Testcase 3.5.3.7d: ------------------ @@ -344,6 +384,10 @@ grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost; show grants for test_noprivs; Grants for test_noprivs@% GRANT TRIGGER ON *.* TO 'test_noprivs'@'%' +connect no_privs_424d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect yes_privs_424d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; +connection no_privs_424d; show grants; Grants for test_noprivs@localhost GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -351,6 +395,7 @@ GRANT SELECT (f1), INSERT (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost' use priv_db; create trigger trg4d_1 before INSERT on t1 for each row set new.f1 = 'trig 3.5.3.7-1d'; +connection default; insert into t1 (f1) values ('insert 3.5.3.7-1d'); ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1' select f1 from t1 order by f1; @@ -364,6 +409,7 @@ trig 3.5.3.7-2a trig 3.5.3.7-2b trig 3.5.3.7-2c drop trigger trg4d_1; +connection yes_privs_424d; show grants; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -371,6 +417,7 @@ GRANT UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' use priv_db; create trigger trg4d_2 before INSERT on t1 for each row set new.f1 = 'trig 3.5.3.7-2d'; +connection default; insert into t1 (f1) values ('insert 3.5.3.7-2d'); select f1 from t1 order by f1; f1 @@ -384,6 +431,8 @@ trig 3.5.3.7-2b trig 3.5.3.7-2c trig 3.5.3.7-2d drop trigger trg4d_2; +disconnect no_privs_424d; +disconnect yes_privs_424d; Testcase 3.5.3.8a: ------------------ @@ -398,6 +447,10 @@ grant TRIGGER, SELECT on *.* to test_yesprivs@localhost; show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' +connect no_privs_425a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect yes_privs_425a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; +connection no_privs_425a; select current_user; current_user test_noprivs@localhost @@ -407,6 +460,7 @@ Grants for test_noprivs@localhost GRANT 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, TRIGGER, CREATE TABLESPACE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' create trigger trg5a_1 before INSERT on t1 for each row set @test_var = new.f1; +connection default; set @test_var = 'before trig 3.5.3.8-1a'; select @test_var; @test_var @@ -417,6 +471,7 @@ select @test_var; @test_var before trig 3.5.3.8-1a drop trigger trg5a_1; +connection yes_privs_425a; use priv_db; select current_user; current_user @@ -426,6 +481,7 @@ Grants for test_yesprivs@localhost GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' create trigger trg5a_2 before INSERT on t1 for each row set @test_var= new.f1; +connection default; set @test_var= 'before trig 3.5.3.8-2a'; select @test_var; @test_var @@ -435,6 +491,8 @@ select @test_var; @test_var insert 3.5.3.8-2a drop trigger trg5a_2; +disconnect no_privs_425a; +disconnect yes_privs_425a; Testcase: 3.5.3.8b ------------------ @@ -453,6 +511,10 @@ show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost' +connect no_privs_425b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect yes_privs_425b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; +connection no_privs_425b; show grants; Grants for test_noprivs@localhost GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -460,6 +522,7 @@ GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEM use priv_db; create trigger trg5b_1 before UPDATE on t1 for each row set @test_var= new.f1; +connection default; set @test_var= 'before trig 3.5.3.8-1b'; insert into t1 (f1) values ('insert 3.5.3.8-1b'); select @test_var; @@ -471,6 +534,7 @@ select @test_var; @test_var before trig 3.5.3.8-1b drop trigger trg5b_1; +connection yes_privs_425b; show grants; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -478,6 +542,7 @@ GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost' use priv_db; create trigger trg5b_2 before UPDATE on t1 for each row set @test_var= new.f1; +connection default; set @test_var= 'before trig 3.5.3.8-2b'; insert into t1 (f1) values ('insert 3.5.3.8-2b'); select @test_var; @@ -488,6 +553,8 @@ select @test_var; @test_var update 3.5.3.8-2b drop trigger trg5b_2; +disconnect no_privs_425b; +disconnect yes_privs_425b; Testcase 3.5.3.8c: ------------------ @@ -506,6 +573,10 @@ show grants for test_yesprivs@localhost; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' +connect no_privs_425c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect yes_privs_425c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; +connection no_privs_425c; show grants; Grants for test_noprivs@localhost GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -513,6 +584,7 @@ GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIE use priv_db; create trigger trg5c_1 before INSERT on t1 for each row set @test_var= new.f1; +connection default; set @test_var= 'before trig 3.5.3.8-1c'; insert into t1 (f1) values ('insert 3.5.3.8-1c'); ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1' @@ -520,6 +592,7 @@ select @test_var; @test_var before trig 3.5.3.8-1c drop trigger trg5c_1; +connection yes_privs_425c; show grants; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -527,12 +600,15 @@ GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' use priv_db; create trigger trg5c_2 before INSERT on t1 for each row set @test_var= new.f1; +connection default; set @test_var='before trig 3.5.3.8-2c'; insert into t1 (f1) values ('insert 3.5.3.8-2c'); select @test_var; @test_var insert 3.5.3.8-2c drop trigger trg5c_2; +disconnect no_privs_425c; +disconnect yes_privs_425c; Testcase: 3.5.3.8d: ------------------- @@ -550,6 +626,10 @@ show grants for test_noprivs@localhost; Grants for test_noprivs@localhost GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost' +connect no_privs_425d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect yes_privs_425d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; +connection no_privs_425d; show grants; Grants for test_noprivs@localhost GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -557,6 +637,7 @@ GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost' use priv_db; create trigger trg5d_1 before INSERT on t1 for each row set @test_var= new.f1; +connection default; set @test_var='before trig 3.5.3.8-1d'; insert into t1 (f1) values ('insert 3.5.3.8-1d'); ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1' @@ -564,6 +645,7 @@ select @test_var; @test_var before trig 3.5.3.8-1d drop trigger trg5d_1; +connection yes_privs_425d; show grants; Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' @@ -571,6 +653,7 @@ GRANT SELECT (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' use priv_db; create trigger trg5d_2 before INSERT on t1 for each row set @test_var= new.f1; +connection default; set @test_var='before trig 3.5.3.8-2d'; insert into t1 (f1) values ('insert 3.5.3.8-2d'); select @test_var; @@ -594,12 +677,15 @@ Grants for test_yesprivs@localhost GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576' GRANT SELECT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost' GRANT SELECT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' +connect yes_353x,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection yes_353x; select current_user; current_user test_yesprivs@localhost use priv_db; create trigger trg1 before insert on t1 for each row insert into t2 values (new.f1); +connection default; use priv_db; insert into t1 (f1) values (4); ERROR 42000: INSERT command denied to user 'test_yesprivs'@'localhost' for table 't2' @@ -612,10 +698,12 @@ f1 select f2 from t2 order by f2; f2 4 +connection yes_353x; use priv_db; drop trigger trg1; create trigger trg2 before insert on t1 for each row update t2 set f2=new.f1-1; +connection default; use priv_db; insert into t1 (f1) values (2); ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for table 't2' @@ -629,10 +717,12 @@ f1 select f2 from t2 order by f2; f2 1 +connection yes_353x; use priv_db; drop trigger trg2; create trigger trg3 before insert on t1 for each row select f2 into @aaa from t2 where f2=new.f1; +connection default; use priv_db; insert into t1 (f1) values (1); ERROR 42000: SELECT command denied to user 'test_yesprivs'@'localhost' for table 't2' @@ -650,10 +740,12 @@ f2 select @aaa; @aaa 1 +connection yes_353x; use priv_db; drop trigger trg3; create trigger trg4 before insert on t1 for each row delete from t2; +connection default; use priv_db; insert into t1 (f1) values (1); ERROR 42000: DELETE command denied to user 'test_yesprivs'@'localhost' for table 't2' |