triggers_03.inc

来自「这个文件是windows mysql源码」· INC 代码 · 共 672 行 · 第 1/2 页

		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;

# Cleanup
	--disable_warnings
	drop trigger trg4d_2;
	disconnect no_privs_424d;
	disconnect yes_privs_424d;
	--enable_warnings

#Section 3.5.3.8
# Test case: Ensure that use of the construct "SET <target> = NEW. <Column name>" fails
#            at CREATE TRIGGER time, if the current user does not have the SELECT privilege
#            on the column specified.

# --- 3.5.3.8a - Privs set on a global level
let $message= Testcase 3.5.3.8a:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant ALL  on *.* to test_noprivs@localhost;
	revoke SELECT  on *.* from test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER, SELECT on *.* to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_425a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_425a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_425a;
	select current_user;
	use priv_db;
	show grants;

	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;
	insert into t1 (f1) values ('insert 3.5.3.8-1a');
	select @test_var;
	drop trigger trg5a_1;

	connection yes_privs_425a;
	use priv_db;
	select current_user;
	show grants;
	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;

	insert into t1 (f1) values ('insert 3.5.3.8-2a');
	select @test_var;

# Cleanup
	--disable_warnings
	drop trigger trg5a_2;
	disconnect no_privs_425a;
	disconnect yes_privs_425a;
	--enable_warnings

# --- 3.5.3.8b - Privs set on a database level
let $message= Testcase: 3.5.3.8b;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant SUPER on *.* to test_noprivs@localhost;
	grant ALL  on priv_db.* to test_noprivs@localhost;
	revoke SELECT  on priv_db.* from test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant SELECT on priv_db.* to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_425b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_425b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_425b;
	show grants;
	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;
	update t1 set  f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
	select @test_var;
	drop trigger trg5b_1;

	connection yes_privs_425b;
	show grants;
	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;

	update t1 set  f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
	select @test_var;
# Cleanup
	--disable_warnings
	drop trigger trg5b_2;
	disconnect no_privs_425b;
	disconnect yes_privs_425b;
	--enable_warnings

# --- 3.5.3.8c - Privs set on a table level
let $message= Testcase 3.5.3.8c:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant SUPER on *.* to test_noprivs@localhost;
	grant ALL  on priv_db.t1 to test_noprivs@localhost;
	revoke SELECT  on priv_db.t1 from test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant SELECT on priv_db.t1 to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_425c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_425c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_425c;
	show grants;
	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');
	select @test_var;
	drop trigger trg5c_1;

	connection yes_privs_425c;
	show grants;
	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;
# Cleanup
	--disable_warnings
	drop trigger trg5c_2;
	disconnect no_privs_425c;
	disconnect yes_privs_425c;
	--enable_warnings

# --- 3.5.3.8d - Privs set on a column level
let $message=Testcase: 3.5.3.8d:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant SUPER on *.* to test_noprivs@localhost;
# There is no ALL prov on the column level
	grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
	show grants for test_noprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_425d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_425d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_425d;
	show grants;
	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');
	select @test_var;
	drop trigger trg5d_1;

	connection yes_privs_425d;
	show grants;
	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;

# Cleanup 3.5.3.8
	--disable_warnings
	drop trigger trg5d_2;
	--enable_warnings

# --- 3.5.3.x   to test for trigger definer privs in the case of trigger
#               actions (insert/update/delete/select) performed on other
#               tables.
let $message=Testcase: 3.5.3.x:;
--source include/show_msg.inc

	use priv_db;
        --disable_warnings
        drop table if exists t1;
        drop table if exists t2;
        --enable_warnings

        --replace_result $engine_type <engine_to_be_used>
        eval create table t1 (f1 int) engine= $engine_type;
        --replace_result $engine_type <engine_to_be_used>
        eval create table t2 (f2 int) engine= $engine_type;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost;
	grant SELECT on priv_db.t2 to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

       --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
       connect (yes_353x,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);

	connection yes_353x;
        select current_user;
	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);
	revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
	grant INSERT on priv_db.t2 to test_yesprivs@localhost;
	insert into t1 (f1) values (4);
	select f1 from t1 order by f1;
	select f2 from t2 order by f2;

	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);
	revoke INSERT on priv_db.t2 from test_yesprivs@localhost;
	grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
	insert into t1 (f1) values (2);
	select f1 from t1 order by f1;
	select f2 from t2 order by f2;

	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);
	revoke UPDATE on priv_db.t2 from test_yesprivs@localhost;
	grant SELECT on priv_db.t2 to test_yesprivs@localhost;
	insert into t1 (f1) values (1);
	select f1 from t1 order by f1;
	select f2 from t2 order by f2;
        select @aaa;

	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);
	revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
	grant DELETE on priv_db.t2 to test_yesprivs@localhost;
	insert into t1 (f1) values (1);
	select f1 from t1 order by f1;
	select f2 from t2 order by f2;



# Cleanup 3.5.3
	--disable_warnings
	drop database if exists priv_db;
	drop user test_yesprivs@localhost;
	drop user test_noprivs@localhost;
	drop user test_noprivs;
	--enable_warnings

use test;
drop table tb3;