Oracle中的System Triggers(DDL触发器)

作者:renfengjun 和数据库相关  


 

这算是比较偏门的知识了,但是有很多实际意义,可以帮助DBA完成一些日常的管理工作,比如可以禁用一些用户的功能(禁止用户修改或者删除某一个表,限制IP登陆等)。

 

概述

Oracle数据库中的System Trigger可以建立在Schema(在某些DDL语句执行的时候触发)或者Database(主要在系统层面触发,比如说登陆或者发生某些系统级别的报错)。

 

SCHEMA trigger:

创建于某一个schema上面,并且经由某些DDL语句触发执行。具体看下文链接更多的DDL event。

 

 

Database trigger:

数据库触发器基于数据库之上,由某些数据库级别的事件触发。具体看下文链接更多的Database event。

 

触发模式

与DML触发器一样,有两种触发模式

before:在语句执行前触发

after:在语句执行后触发

 

这个就不用细说了。

 

简单示例

 

 

限制IP登陆

 

组织IP为192.168.100.71的机器发出的连接请求。

 


create or replace trigger limit_ip

  AFTERLOGON on database

begin

  ifSYS_CONTEXT('USERENV', 'ip_address') = '192.168.100.71' then

   raise_application_error(-20999, 'Cann''t logon this database.');

  end if;

end;


C:\Users\fengjun>sqlplusscott/tiger@192.168.100.221:1521/repo

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 25 09:49:182014

 

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

 

ERROR:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-20999: Cann't logon this database.

ORA-06512: 在 line 2

 

注:如果用户拥有DBA权限,则不受此触发器影响。

 

 

 

 

 

禁止用户删除对象

 

CREATE OR REPLACE TRIGGER dexter_drop_trigger

  BEFOREDROP ON dexter.SCHEMA

BEGIN

 RAISE_APPLICATION_ERROR(-20999, 'Cannot drop this object.');

END;

/

dexter@REPO>drop table ttt ;

drop table ttt

*

第 1 行出现错误:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-20999: Cannot drop this object.

ORA-06512: 在 line 2

 

 

 

根据IP判断是否可以删除对象

 

CREATE OR REPLACE TRIGGER DDL_TRIGGER_IOT

  BEFORE ALTER OR DROP ON IOTPLATFORM.SCHEMA

BEGIN

  IF SYS_CONTEXT( 'USERENV', 'ip_address' )= '192.168.0.39' AND

     SYS_CONTEXT( 'USERENV', 'OS_USER' )= 'dex' AND

     SYS_CONTEXT( 'USERENV', 'HOST' )= 'WORKGROUP\DEX-PC' THEN

    NULL;

  ELSE

    RAISE_APPLICATION_ERROR(-20999, 'Connectto ur dba .' );

  END IF;

END;

 

 

注意

 

如果没有使用Listener,而是使用本地IPC方式登陆数据库,限制IP的功能会有缺失,因为sys_context无法获得相关的IP信息。

 

 

拓展阅读

其实重点在这里

更多的DDL event

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#CIHGCJHC

 

 

更多的Database event

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS2064

 

得到更多的用户连接信息

通过sys_context 获取更多连接信息。

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#sthref1594

 

 

 

相关资料:

Oracle中的System Triggers(DDL触发器)来源网络,如有侵权请告知,即处理!

编程Tags: