oracle+禁用ddl,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