triggers_master.test

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· TEST 代码 · 共 2,059 行 · 第 1/5 页

#Cleanup
	--disable_warnings 
	drop database trig_db1;
	drop database trig_db2;
	drop database trig_db3;
	--enable_warnings

###########################################
################ Section 3.5.2 ############
# Check for the global nature of Triggers #
###########################################

#Section 3.5.2.1 
# Test case: Ensure that if a trigger created without a qualifying database 
#            name belongs to the database in use at creation time.
#Section 3.5.2.2 
# Test case: Ensure that if a trigger created with a qualifying database name 
#            belongs to the database specified.
#Section 3.5.2.3 
# Test case: Ensure that if a trigger created with a qualifying database name 
#            does not belong to the database in use at creation time unless 
#            the qualifying database name identifies the database that is 
#            also in use at creation time.
let $message= Testcase 3.5.2.1/2/3:;
--source include/show_msg.inc


	--disable_warnings
        drop database if exists trig_db1;
        drop database if exists trig_db2;
	--enable_warnings
	create database trig_db1;
	create database trig_db2;
	use trig_db1;
	eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
	eval create table trig_db2.t1 (f1 char(50), f2 integer) engine = $engine_type;
	create trigger trig1_b before insert on t1 
		for each row set @test_var1='trig1_b';
	create trigger trig_db1.trig1_a after insert on t1 
		for each row set @test_var2='trig1_a';
	create trigger trig_db2.trig2 before insert on trig_db2.t1 
		for each row set @test_var3='trig2';
	select trigger_schema, trigger_name, event_object_table
		from information_schema.triggers;

	set @test_var1= '', @test_var2= '', @test_var3= '';
	insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
	insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
	select @test_var1, @test_var2, @test_var3;

#Cleanup
	--disable_warnings 
	drop database trig_db1;
	drop database trig_db2;

###########################################
################ Section 3.5.3 ############
# Check for the global nature of Triggers #
###########################################

# General setup to be used in all testcases of 3.5.3
let $message= Testcase 3.5.3:;
--source include/show_msg.inc

        --disable_warnings
	drop database if exists priv_db;
	--enable_warnings
	create database priv_db;
	use priv_db;
	create table t1 (f1 char(20));

	create User test_noprivs@localhost;
	set password for test_noprivs@localhost = password('PWD');

	create User test_yesprivs@localhost;
	set password for test_yesprivs@localhost = password('PWD');

#Section 3.5.3.1 / 3.5.3.2 
# Test case: Ensure SUPER privilege is required to create a trigger
#Section 3.5.3.3 / 3.5.3.4
# Test case: Ensure that root always has the SUPER privilege.
# OMR - No need to test this since SUPER priv is an existing one and not related
#       or added for triggers (TP 2005-06-06) 
#Section 3.5.3.5 / 3.5.3.6 
# Test case: Ensure that the SUPER privilege is required to drop a trigger.
let $message= Testcase 3.5.3.2/6:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant ALL  on *.* to test_noprivs@localhost;
	revoke SUPER  on *.* 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;
# Adding the minimal priv to be able to set to the db
	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,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

let $message= Testcase 3.5.3.2:;
--source include/show_msg.inc
	
	connection no_privs;
        select current_user;
	use priv_db;
	
	--error 1227
	create trigger trg1_1 before INSERT on t1 for each row 
		set new.f1 = 'trig 3.5.3.2_1-no';

	connection default;
	use priv_db;
	insert into t1 (f1) values ('insert 3.5.3.2-no');
	select f1 from t1;

	connection yes_privs;
        select current_user;
	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;
	use priv_db;
	insert into t1 (f1) values ('insert 3.5.3.2-yes');
	select f1 from t1;
	
let $message= Testcase 3.5.3.6:;
--source include/show_msg.inc

	connection no_privs;
	use priv_db;
	
	--error 1227
	drop trigger trg1_2;  

	connection default;
	use priv_db;
	insert into t1 (f1) values ('insert 3.5.3.6-yes');
	select f1 from t1;

	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;

# Cleanup	
	--disable_warnings 
	connection default;
	--error 0, 1360
	drop trigger trg1_2;
	disconnect no_privs;
	disconnect yes_privs;
	--enable_warnings


#Section 3.5.3.7
# Test case: Ensure that use of the construct "SET NEW. <column name> = <value>" 
#            fails at CREATE TRIGGER time, if the current user does not have the
#            UPDATE privilege on the column specified
# Note: As a result of bug 8884 the triggers are actually created. 
#       Disabled because of bug 8884

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

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

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

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_424a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_424a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);

	connection no_privs_424a;
	select current_user;
	use priv_db;
	show grants;
	select f1 from t1;

let $message= Trigger create disabled - should fail - Bug 8884;
--source include/show_msg.inc
#	--error 1227
#	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');
	select f1 from t1;
	--error 0, 1360
	drop trigger trg4a_1;

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

# Cleanup
	--disable_warnings 
	drop trigger trg4a_2;
	disconnect no_privs_424a;
	disconnect yes_privs_424a;
	--enable_warnings

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

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

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant UPDATE 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_424b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_424b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_424b;
	show grants;
	use priv_db;

let $message= Trigger create disabled - should fail - Bug 8884;
--source include/show_msg.inc
#	--error 1227
#	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;
	update t1 set  f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
	select f1 from t1;
	--error 0, 1360
	drop trigger trg4b_1;

	connection yes_privs_424b;
	show grants;
	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;
	update t1 set  f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
	select f1 from t1;
# Cleanup
	--disable_warnings 
	drop trigger trg4b_2;
	disconnect no_privs_424b;
	disconnect yes_privs_424b;
	--enable_warnings

# --- 3.5.3.7c - Privs set on a table level
let $message= Testcase 3.5.3.7c;
--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 UPDATE  on priv_db.t1 from test_noprivs@localhost;
	show grants for test_noprivs;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant UPDATE 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_424c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_424c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_424c;
	show grants;
	use priv_db;

let $message= Trigger create disabled - should fail - Bug 8884;
--source include/show_msg.inc
#	--error 1227
#	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');
	select f1 from t1;
	--error 0, 1360
	drop trigger trg4c_1;

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

# Cleanup
	--disable_warnings 
	drop trigger trg4c_2;
	disconnect no_privs_424c;
	disconnect yes_privs_424c;
	--enable_warnings

# --- 3.5.3.7d - Privs set on a column level
--disable_query_log
let $message= Testcase 3.5.3.7d:;
--enable_query_log
--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 privs on the column level
	grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
	show grants for test_noprivs;

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

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

	connection no_privs_424d;
	show grants;
	use priv_db;
let $message= Trigger create disabled - should fail - Bug 8884;
--source include/show_msg.inc
#	--error 1227
#	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');
	select f1 from t1;
	--error 0, 1360
	drop trigger trg4d_1;

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

# 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;