rem this script can be used to monitor a objectrem deny any drop operation on it.create or replace trigger trg_dropdeny before drop on databasebegin if lower (ora_dict_obj_name ()) = 'test' then raise_application_error (num => -20000, msg => '你疯了,想删除表 ' || ora_dict_obj_name () || ' ?!!!!!' || '你完了，警察已在途中.....' ); end if;end;/
测试效果:
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
create or replace trigger ddl_denybefore create or alter or drop or truncate on databasedeclare 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;/
我们看一下效果:
[[email protected] 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
