使用触发器实现数据库级守护防止DDL操作的方法

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;
/ 
           

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