 |  |
|---|---|
 | |
| --- | --- |
如何使用触发器实现数据库级守护,防止DDL操作
--对于重要对象,实施DDL拒绝,防止create,drop,truncate,alter等重要操作
Last Updated: Sunday, 2004-10-31 12:06 Eygle
|
不管是有意还是无意的,你可能会遇到数据库中重要的数据表等对象被drop掉的情况,这可能会给我们带来巨大的损失.
通过触发器，我们可以实现对于表等对象的数据库级守护，禁止用户drop操作.
以下是一个简单的范例，供参考:
|
>
> REM this script can be used to monitor a object
> REM deny any drop operation on it.
> CREATE OR REPLACE TRIGGER trg_dropdeny
> BEFORE DROP ON DATABASE
> BEGIN
> IF LOWER (ora_dict_obj_name ()) = 'test'
> THEN
> raise_application_error (num => -20000,
> msg => '你疯了,想删除表 '
> || ora_dict_obj_name ()
> || ' ?!!!!!'
> || '你完了，警察已在途中.....'
> );
> END IF;
> END;
> /
>
测试效果:
>
> SQL> connect scott/tiger
> Connected.
> SQL> create table test as select * from dba_users;
>
> Table created.
>
> SQL> connect / as sysdba
> Connected.
> 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/tiger
> Connected.
> SQL> drop table test;
> drop table test
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-20000: 你疯了,想删除表 TEST ?!!!!!你完了，警察已在途中.....
> ORA-06512: at line 4
>
Oracle从Oracle8i开始,允许实施DDL事件trigger，可是实现对于DDL的监视及控制,以下是一个进一步的例子:
>
> create or replace trigger ddl_deny
> before create or alter or drop or truncate on database
> declare
> l_errmsg varchar2(100):= 'You have no permission to this operation';
> begin
> if ora_sysevent = 'CREATE' then
> raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
> elsif ora_sysevent = 'ALTER' then
> raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
> elsif ora_sysevent = 'DROP' then
> raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
> elsif ora_sysevent = 'TRUNCATE' then
> raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
> end if;
>
> exception
> when no_data_found then
> null;
> end;
> /
>
>
我们看一下效果:
>
> [oracle@jumper tools]$ sqlplus "/ as sysdba"
>
> SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 31 11:38:25 2004
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
> With the Partitioning option
> JServer Release 9.2.0.4.0 - Production
>
> SQL> set echo on
> 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;
> 15
> 16 exception
> 17 when no_data_found then
> 18 null;
> 19 end;
> 20 /
>
> Trigger created.
>
> SQL>
> 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
>
>
> SQL> alter table test add (id number);
> 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
>
>
> SQL> drop table test;
> 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
>
>
> SQL> truncate table test;
> 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 是作者的个人站点.你可通过 [email protected] 来联系作者.欢迎技术探讨交流以及链接交换.
原文出处:
http://www.eygle.com/faq/Use.Trigger.To.implement.ddl.deny.htm
如欲转载,请注明作者与出处.并请保留本文的连接.
回首页
| |