本文作者eygle
-----------------------------------
|-对于重要对象,实施DDL拒绝,防止create,drop,truncate,alter等重要操作.|
-----------------------------------
SQL> create or replace trigger trg_dropdeny
2 before drop on database
3 begin
4 if lower(ora_dict_obj_name())='test'
5 then
6 raise_application_error(
7 num=>-20000,
8 msg=>'你疯了,想删除表'||ora_dict_obj_name()||'?!!!'||'你完了,警察来抓
你了...');
9 end if;
10 end;
11 /
触发器已创建
SQL> create table test(a number);
表已创建。
SQL> drop table test;
drop table test
*
ERROR 位于第 1 行:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-20000: 你疯了,想删除表TEST?!!!你完了,警察来抓你了...
ORA-06512: 在line 4
-----------------------------------
|--DDL监控及控制 |
-----------------------------------
SQL> create or replace trigger ddl_deny
2 before create or alter or drop or truncate on database
3 declare
4 l_errmsg varchar2(100):= 'You have no permission to this operation';
5 begin
6 if ora_sysevent = 'CREATE' then
7 raise_application_error(-20001, ora_dict_obj_owner || '.' ||
8 ora_dict_obj_name || ' ' || l_errmsg);
9 elsif ora_sysevent = 'ALTER' then
10 raise_application_error(-20001, ora_dict_obj_owner || '.' ||
11 ora_dict_obj_name || ' ' || l_errmsg);
12 elsif ora_sysevent = 'DROP' then
13 raise_application_error(-20001, ora_dict_obj_owner || '.' ||
14 ora_dict_obj_name || ' ' || l_errmsg);
15 elsif ora_sysevent = 'TRUNCATE' then
16 raise_application_error(-20001, ora_dict_obj_owner || '.' ||
17 ora_dict_obj_name || ' ' || l_errmsg);
18 end if;
19 exception
20 when no_data_found then
21 null;
22 end;
23 /
触发器已创建
已用时间: 00: 00: 00.00
SQL> create table b(b number);
create table b(b number)
*
ERROR 位于第 1 行:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-20001: SYS.B You have no permission to this operation
ORA-06512: 在line 5
SQL> drop trigger ddl_deny;
触发器已丢弃
-----------------------------------
|-对于重要表,实施DDL拒绝,防止delete、update、insert等重要操作.|
-----------------------------------
SQL> conn scott/tiger@oemrep
已连接。
SQL> @c:/trigger_del.txt
SQL> rem this script can be used to monitor a object
SQL> rem deny an delete operation on it
SQL> rem this trigger can't run with sys
SQL> rem this trigger can't create on database
SQL> create or replace trigger trg_del
2 before insert or delete or update on zj1
3 for each row
4 begin
5 raise_application_error(
6 num=>-20000,
7 msg=>'你疯了,想删除记录?!!!'||'你完了,警察来抓你了...');
8 end;
9 /
触发器已创建
已用时间: 00: 00: 00.00
SQL> delete zj1 where xh=18;
delete zj1 where xh=18
*
ERROR 位于第 1 行:
ORA-20000: 你疯了,想删除记录?!!!你完了,警察来抓你了...
ORA-06512: 在"SCOTT.TRG_DEL", line 2
ORA-04088: 触发器 'SCOTT.TRG_DEL' 执行过程中出错
已用时间: 00: 00: 00.00
SQL> delete zj1;
delete zj1
*
ERROR 位于第 1 行:
ORA-20000: 你疯了,想删除记录?!!!你完了,警察来抓你了...
ORA-06512: 在"SCOTT.TRG_DEL", line 2
ORA-04088: 触发器 'SCOTT.TRG_DEL' 执行过程中出错
已用时间: 00: 00: 00.00
SQL> update zj1 set name='ll' where xh=18;
update zj1 set name='ll' where xh=18
*
ERROR 位于第 1 行:
ORA-20000: 你疯了,想删除记录?!!!你完了,警察来抓你了...
ORA-06512: 在"SCOTT.TRG_DEL", line 2
ORA-04088: 触发器 'SCOTT.TRG_DEL' 执行过程中出错
已用时间: 00: 00: 00.00
SQL>