innodb_triggers.result

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

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;
trigger_schema	trigger_name	event_object_table
trig_db1	trig1_b	t1
trig_db1	trig1_a	t1
trig_db2	trig2	t1
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;
@test_var1	@test_var2	@test_var3
trig1_b	trig1_a	trig2
drop database trig_db1;
drop database trig_db2;

Testcase 3.5.3:
---------------
drop database if exists priv_db;
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');

Testcase 3.5.3.2/6:
-------------------
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;
Grants for test_noprivs@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
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;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);

Testcase 3.5.3.2:
-----------------
select current_user;
current_user
test_noprivs@localhost
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: Access denied; you need the SUPER privilege for this operation
use priv_db;
insert into t1 (f1) values ('insert 3.5.3.2-no');
select f1 from t1;
f1
insert 3.5.3.2-no
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';
use priv_db;
insert into t1 (f1) values ('insert 3.5.3.2-yes');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes

Testcase 3.5.3.6:
-----------------
use priv_db;
drop trigger trg1_2;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
use priv_db;
insert into t1 (f1) values ('insert 3.5.3.6-yes');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
use priv_db;
drop trigger trg1_2;
use priv_db;
insert into t1 (f1) values ('insert 3.5.3.6-no');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
drop trigger trg1_2;

Testcase 3.5.3.7a:
------------------
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;
Grants for test_noprivs@localhost
GRANT SELECT, INSERT, 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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER, UPDATE on *.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT UPDATE, SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
select current_user;
current_user
test_noprivs@localhost
use priv_db;
show grants;
Grants for test_noprivs@localhost
GRANT SELECT, INSERT, 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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no

Trigger create disabled - should fail - Bug 8884
------------------------------------------------
insert into t1 (f1) values ('insert 3.5.3.7-1a');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
drop trigger trg4a_1;
use priv_db;
select current_user;
current_user
test_yesprivs@localhost
show grants;
Grants for test_yesprivs@localhost
GRANT UPDATE, SUPER 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';
insert into t1 (f1) values ('insert 3.5.3.7-2b');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
drop trigger trg4a_2;

Testcase 3.5.3.7b:
------------------
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;
Grants for test_noprivs@%
GRANT SUPER ON *.* TO '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;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `priv_db`.* TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8884
------------------------------------------------
insert into t1 (f1) values ('insert 3.5.3.7-1b');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
insert 3.5.3.7-1b
update t1 set  f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
drop trigger trg4b_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
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';
insert into t1 (f1) values ('insert 3.5.3.7-2b');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
insert 3.5.3.7-2b
update t1 set  f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b
drop trigger trg4b_2;

Testcase 3.5.3.7c
-----------------
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;
Grants for test_noprivs@%
GRANT SUPER ON *.* TO '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;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8884
------------------------------------------------
insert into t1 (f1) values ('insert 3.5.3.7-1c');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b
insert 3.5.3.7-1c
drop trigger trg4c_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
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';
insert into t1 (f1) values ('insert 3.5.3.7-2c');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b
insert 3.5.3.7-1c
trig 3.5.3.7-2c
drop trigger trg4c_2;

Testcase 3.5.3.7d:
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs@localhost;
grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
show grants for test_noprivs;
Grants for test_noprivs@%
GRANT SUPER ON *.* TO '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;
Grants for test_noprivs@%
GRANT SUPER ON *.* TO 'test_noprivs'@'%'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT (f1), INSERT (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8884
------------------------------------------------
insert into t1 (f1) values ('insert 3.5.3.7-1d');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b
insert 3.5.3.7-1c
trig 3.5.3.7-2c
insert 3.5.3.7-1d
drop trigger trg4d_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
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';
insert into t1 (f1) values ('insert 3.5.3.7-2d');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b