Oracle使用触发器禁止远程DDL操作

SQL> grant select on v_$session to zhongwc;

Grant succeeded.

SQL> grant execute on dbms_system to zhongwc;

Grant succeeded.

SQL> @ddl_trigger

Trigger created.

SQL> !cat ddl_trigger.sql
create or replace trigger ddl_trigger
  before ddl on zhongwc.schema
declare
  n           number;
  str_stmt    varchar2(4000);
  sql_text    ora_name_list_t;
  l_trace     number;
  str_session v$session%rowtype;
begin
  select count(*)
    into l_trace
    from dual
   where utl_inaddr.get_host_address is not null
     and sys_context('userenv', 'ip_address') is not null
     and sys_context('userenv', 'ip_address') <>
         utl_inaddr.get_host_address;

  if l_trace > 0 then
    n := ora_sql_txt(sql_text);
    for i in 1 .. n loop
      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
    end loop;
  
    select *
      into str_session
      from v$session
     where audsid = userenv('sessionid');
  
    sys.dbms_system.ksdwrt(2,
                           to_char(sysdate, 'yyyymmdd hh24:mi:ss') ||
                           'ORA-20001 user: ' || user || ' program: ' ||
                           str_session.program || ' IP: ' ||
                           sys_context('userenv', 'ip_address') ||
                           ' object: ' || ora_dict_obj_name || ' DDL: ' ||
                           str_stmt);
    raise_application_error(-20001, 'DDL is deny from remote connection.');
  end if;
end;
/

remote

C:\>sqlplus zhongwc@single

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 1月 15 16:54:01 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

输入口令:

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> truncate table t_test;
truncate table t_test
               *
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20001: DDL is deny from remote connection.
ORA-06512: 在 line 34


SQL> drop table t_test;
drop table t_test
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20001: DDL is deny from remote connection.
ORA-06512: 在 line 34


SQL> create table t_test002 as select * from t_test;
create table t_test002 as select * from t_test
                                        *
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20001: DDL is deny from remote connection.
ORA-06512: 在 line 34

local

[oracle@zhongwc ~]$ sqlplus zhongwc@zwc

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 15 16:58:49 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select tname from tab;

TNAME
------------------------------
T_TEST

SQL> create table t_test002 as select * from t_test;

Table created.

SQL> select tname from tab;

TNAME
------------------------------
T_TEST
T_TEST002

收录 eygle DBA 手记4