如何使用触发器实现数据库级守护,防止DDL操作

Last Updated: Sunday, 2004-10-31 12:06 Eygle

不管是有意还是无意的,你可能会遇到数据库中重要的数据表等对象被drop掉的情况,这可能会给我们带来巨大的损失.

通过触发器，我们可以实现对于表等对象的数据库级守护，禁止用户drop操作.

以下是一个简单的范例，供参考:

测试效果:

SQL> connect scott/tigerConnected.SQL> create table test as select * from dba_users;Table created.SQL> connect / as sysdbaConnected.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 /Trigger created.SQL> connect scott/tigerConnected.SQL> drop table test;drop table test*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-20000: 你疯了,想删除表 TEST ?!!!!!你完了，警察已在途中.....ORA-06512: at line 4

Oracle从Oracle8i开始,允许实施DDL事件trigger，可是实现对于DDL的监视及控制,以下是一个进一步的例子:

我们看一下效果:

[oracle@jumper tools]$ sqlplus "/ as sysdba"

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production

SQL> @ddlt

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 || '.' || ora_dict_obj_name || ' ' || l_errmsg);

8 elsif ora_sysevent = 'ALTER' then

9 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);

10 elsif ora_sysevent = 'DROP' then

11 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);

12 elsif ora_sysevent = 'TRUNCATE' then

13 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);

14 end if;

16 exception

17 when no_data_found then

18 null;

19 end;

20 /

SQL>

SQL> connect scott/tiger

Connected.

SQL> create table t as select * from test;

create table t as select * from test

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: SCOTT.T You have no permission to this operation

ORA-06512: at line 5

alter table test add (id number)

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: SCOTT.TEST You have no permission to this operation

ORA-06512: at line 7

drop table test

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: SCOTT.TEST You have no permission to this operation

ORA-06512: at line 9

truncate table test

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: SCOTT.TEST You have no permission to this operation

ORA-06512: at line 11

我们可以看到,ddl语句都被禁止了,如果你不是禁止,可以选择把执行这些操作的用户及时间记录到另外的临时表中.以备查询.

本文作者:

eygle,Oracle技术关注者,来自中国最大的Oracle技术论坛itpub.

www.eygle.com是作者的个人站点.你可通过Guoqiang.Gai@gmail.com来联系作者.欢迎技术探讨交流以及链接交换.

原文出处:

http://www.eygle.com/faq/Use.Trigger.To.implement.ddl.deny.htm

回首页