Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03e_definer.inc

raw
1 #======================================================================
2 #
3 # Trigger Tests
4 # test cases for TRIGGER privilege on db, table and column level
5 #======================================================================
6
7 --disable_abort_on_error
8
9 ###########################################
10 ################ Section 3.5.3 ############
11 # Check for the definer of Triggers #
12 ###########################################
13
14 # General setup to be used in all testcases
15 let $message= ######### Testcase for definer: ########;
16 --source include/show_msg.inc
17
18 --disable_warnings
19 drop database if exists priv_db;
20 --enable_warnings
21 create database priv_db;
22 use priv_db;
23 eval create table t1 (f1 char(20)) engine= $engine_type;
24
25 create User test_yesprivs@localhost;
26 set password for test_yesprivs@localhost = 'PWD';
27
28 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
29
30 connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
31
32 # create trigger with not existing definer shall deliver a warning:
33 connection default;
34 select current_user;
35 # --warning 1449
36 create definer=not_ex_user@localhost trigger trg1_0
37 before INSERT on t1 for each row
38 set new.f1 = 'trig 1_0-yes';
39 drop trigger trg1_0;
40 # create trigger with definer test_yesprivs@localhost->succeed:
41 create definer=test_yesprivs@localhost trigger trg1_0
42 before INSERT on t1 for each row
43 set new.f1 = 'trig 1_0-yes';
44 grant select, insert, update
45 on priv_db.t1 to test_yesprivs@localhost;
46
47 connection yes_privs;
48 select current_user;
49 use priv_db;
50 # user hasn't trigger privilege->fail:
51 --error ER_TABLEACCESS_DENIED_ERROR
52 insert into t1 (f1) values ('insert-no');
53 select f1 from t1 order by f1;
54 --error ER_TABLEACCESS_DENIED_ERROR
55 drop trigger trg1_0;
56
57 connection default;
58 select current_user;
59 grant select, insert, update ,trigger
60 on priv_db.t1 to test_yesprivs@localhost;
61 show grants for test_yesprivs@localhost;
62
63 connection yes_privs;
64 select current_user;
65 # user now has trigger privilege->succeed:
66 insert into t1 (f1) values ('insert-no');
67 select f1 from t1 order by f1;
68 drop trigger trg1_0;
69 # user has not super privilege->fail:
70 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
71 create definer=not_ex_user@localhost trigger trg1_0
72 before INSERT on t1 for each row
73 set new.f1 = 'trig 1_0-yes';
74 connection default;
75 create user definer_user@localhost identified by 'boo';
76 grant set_user_id,system_user, trigger on *.* to definer_user@localhost;
77 connect (con1,localhost,definer_user,boo,test,$MASTER_MYPORT,$MASTER_MYSOCK);
78 create table test.t1 (f1 varchar(20));
79 create definer=root@localhost trigger test.trg1_0
80 before INSERT on test.t1 for each row
81 set new.f1 = 'yes';
82 connection default;
83 drop trigger test.trg1_0;
84 drop user definer_user@localhost;
85 drop table test.t1;
86 connection yes_privs;
87
88 # shall always succeed:
89 create definer=current_user trigger trg1_1
90 before INSERT on t1 for each row
91 set new.f1 = 'trig 1_1-yes';
92 insert into t1 (f1) values ('insert-no');
93 select f1 from t1 order by f1;
94 # shall always succeed:
95 create definer=test_yesprivs@localhost trigger trg1_2
96 before UPDATE on t1 for each row
97 set new.f1 = 'trig 1_2-yes';
98 update t1 set f1 = 'update-yes' where f1 like '%trig%';
99 select f1 from t1 order by f1;
100
101 connection default;
102 select current_user;
103 grant trigger on priv_db.* to test_yesprivs@localhost
104 with grant option;
105
106 # user has not super privilege->fail:
107 connection yes_privs;
108 select current_user;
109 show grants;
110 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
111 create definer=not_ex_user@localhost trigger trg1_3
112 after UPDATE on t1 for each row
113 set @var1 = 'trig 1_3-yes';
114
115 connection default;
116 select current_user;
117
118 # Cleanup prepare
119 --disable_warnings
120 disconnect yes_privs;
121
122 connection default;
123 select current_user;
124 --enable_warnings
125
126
127 # general Cleanup
128 --disable_warnings
129 drop database if exists priv_db;
130 drop user test_yesprivs@localhost;
131 --enable_warnings
132