系统级触发器

 使用这些触发器，可以对数据库发生的一些重要事件进行审计
可触发的事件包括:
      数据库启动/关闭触发器
      DDL触发器 schema
      最终用户登陆/注销触发器
      系统错误触发器

当触发事件发生的时候，可以启动触发器。

在触发器中，可以通过DML操作将审计操作记录在日志表中，
或者通过抛出一个EXCEPTION来制止某种操作。
在触发器中，可以通过使用系统事件函数（Event Attribute Functions）来获取一些信息。
以下是系统事件函数的详细情况：

系统事件函数:

函数名称   类型    描述 
ora_client_ip_address   VARCHAR2   客户端的IP地址
ora_database_name    VARCHAR2(50)  数据库名称
ora_dict_obj_name    VARCHAR2(30)  DDL发生的对象名称
ora_dict_obj_owner    VARCHAR2(30)  DDL发生对象的宿主
ora_dict_obj_type    VARCHAR2(20)  对象类别
ora_is_alter_column(
column_name IN VARCHAR2)  BOOLEAN   当某列被修改的时候返回真，否则返回假  
ora_is_drop_column(
column_name IN VARCHAR2)  BOOLEAN   当某列被删除的时候返回真，否则返回假  
ora_login_user     VARCHAR2(30)  登录的用户名  
ora_sysevent     VARCHAR2(20)  系统事件的名称  
is_servererror(
error_num in integer)  BOLEAN   返回系统是否产生某个错误  

 

ORACLE 8I开始，提供了一个新的函数“SYS_CONTEXT”。

通过使用SYS_CONTEXT函数可以获得一些和用户相关的信息，比如：
SELECT sys_context('USERENV','TERMINAL') FROM DUAL;
用户环境的取值包括：
         TERMINAL：  客户端操作系统终端的名称     
         LANGUAGE：  NLS_LANG的值  
         LANG  ：  ISO字符集的名称.  
         SESSIONID：  SESSION的ID
         INSTANCE：  实例的ID
         ISDBA：   是否具有DBA权限
         CLIENT_INFO： 64字节的用户信息，可以用DBMS_APPLICATION_INFO设置的值：
         NLS_TERRITORY：当前SESSION的 territory
         NLS_CURRENCY： 当前SESSION的货币符
         NLS_CALENDAR： 当前SESSION的历法
         NLS_DATE_FORMAT：当前SESSION的日期格式
         NLS_DATE_LANGUAGE ：显示日期的语言
         NLS_SORT：排序方式（BINARY 或者linguistic）
         CURRENT_USER：当前SESSION拥有权限的用户的名称（比如说当前SESSION是SYS,但是正在执行system.myproc，那么current_user就是system）
         CURRENT_USERID ：当前SESSION拥有的权限的用户的ID
         SESSION_USER：session所属的用户名
         SESSION_USERID：当前SESSION所属的用户id
         CURRENT_SCHEMA：当前SESSION缺省的SCHEMA名称，可以用SESSION SET CURRENT_SCHEMA 语句修改.  
         CURRENT_SCHEMAID ：当前SESSION缺省的SCHEMA的ID
         PROXY_USER：打开当前SESSION的用户的名称
         PROXY_USERID：打开当前SESSION的用户的ID
         DB_DOMAIN：当前数据库的DOMAIN
         DB_NAME：当前数据库的名称
         HOST：客户端的主机名称
         OS_USER：客户端的操作系统用户名
         EXTERNAL_NAME：用户的外部名称。对于SSL用户，使用v.503协议，返回的值是证书中的DN
         IP_ADDRESS：客户端的IP地址
         NETWORK_PROTOCOL：连接串中的PROTOCOL=protocol指明的网络协议
         BG_JOB_ID ：如果当前的SESSION是由ORACLE后台进程启动的，那么返回JOB_ID，否则返回空值
         FG_JOB_ID：如果当前SESSION是由ORACLE客户端进程启动的一个JOB,那么返回JOB_ID，否则返回空值  
         AUTHENTICATION_TYPE：返回数据库鉴权的方法，返回值包括：
         DATABASE: 使用数据库的用户名口令
         OS:使用操作系统外部用户鉴权
         NETWORK:网络鉴权
         PROXY:OCI的代理连接鉴权  
         AUTHENTICATION_DATA：使用X.503证书鉴权的时候，返回HEX2的证书

系统级触发器 触发事件表:

触发器事件 触发时间  触发条件

Logon  After  用户登录成功后
Logoff  Before  用户退出登录前
Startup  After  数据库启动后
Shutdown Before  数据库关闭前
Servererror After  系统发生故障后

Logon/Logoff触发器可以用来记录用户登入和退出的时间。

数据库启动和关闭触发器可以用来进行一些数据库启动后和关闭前的前处理和后处理。
Servererror触发器可以用于记录某些重要的错误信息，以便于跟踪系统，发现故障。

创建登录和注销的审计触发器

创建记录日志的表

CREATE TABLE log_audit (
login_date DATE, 
logoff_date date,
username VARCHAR2(20),
user_ip  varchar2(20),
error_code varchar2(15)
); 

create or replace TRIGGER logon_audit 
AFTER LOGON 
ON DATABASE
BEGIN
 if user not in ('SYS','SYSMAN')
 then
    insert into log_audit(login_date,username,user_ip) 
   values(sysdate,user,ora_client_ip_address);
 end if;
END;
/
create or replace TRIGGER logoff_audit 
BEFORE LOGOFF 
ON DATABASE
BEGIN
 if user not in ('SYS','SYSMAN')
 THEN
    insert into log_audit (logoff_date,username,user_ip) 
          values(sysdate,user,ora_client_ip_address);
 end if;
END;
/

SQL> select * from log_audit;

no rows selected

SQL> conn 

Connected.
SQL> select sysdate from dual;

SYSDATE

-------------------
2011-03-11 07:00:42

SQL> conn / as sysdba

Connected.
SQL> select * from log_audit;

LOGIN_DATE     LOGOFF_DATE  USERNAME      USER_IP    ERROR_CODE

------------------- ------------------- -------------------- -------------------- ---------------
2011-03-11 07:00:35   SCOTT       10.10.10.10
      2011-03-11 07:00:49 SCOTT

SQL> 

create table log_audit(

l_user varchar2(20),l_on_date date,l_off_date date,user_ip varchar2(17),error_code varchar2(20));
create or replace trigger tri_logon
after logon
on database
begin
    if sys_context('userenv','session_user') not in ('SYS','SYSMAN','SYSTEM') THEN
       INSERT INTO LOG_AUDIT 
       VALUES(sys_context('userenv','session_user'),sysdate,null,sys_context('userenv','ip_address'),null);
    end if;
end;

create or replace trigger tri_logoff

before logoff
on database
begin
    if sys_context('userenv','session_user') not in('SYS','SYSTEM','SYSMAN') THEN
        INSERT INTO log_audiT
        VALUES( sys_context('userenv','session_user'),null,sysdate,sys_context('userenv','ip_address'),null);
    end if;
end;

错误跟踪的触发器

CREATE OR REPLACE TRIGGER log_errors 

AFTER SERVERERROR 
ON DATABASE
BEGIN
 IF (IS_SERVERERROR (1017)) THEN
  insert into log_audit (login_date,USERNAME,USER_IP,error_code) 
 values(sysdate,ora_login_user,ora_client_ip_address,'ORA-1017');
 ELSIF (IS_SERVERERROR (2449)) THEN
  insert into log_audit (login_date,USERNAME,USER_IP,error_code) 
 values(sysdate,ora_login_user,ora_client_ip_address,'ORA-2449');
 END IF;
END;
/

SQL> truncate table log_audit;

Table truncated.

SQL> conn 

ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn 
Connected.
SQL> drop table dept;
drop table dept
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> conn / as sysdba

Connected.
SQL> select * from log_audit;

LOGIN_DATE       LOGOFF_DATE    USERNAME      USER_IP      ERROR_CODE

------------------- ------------------- -------------------- -------------------- ---------------
2011-03-11 07:12:10                   ORA-1017
2011-03-11 07:12:13      SCOTT        10.10.10.10
2011-03-11 07:12:17      SCOTT           ORA-2449
        2011-03-11 07:12:27 SCOTT

SQL> 

create or replace trigger tri_errcode

after servererror
on database
begin
    if (is_servererror(1017)) then
        insert into log_audit
        values(sys_context('userenv','os_user'),sysdate,null,sys_context('userenv','ip_address'),'ora-01017');
    elsif (is_servererror(2449)) then
        insert into log_audit
        values(sys_context('userenv','current_user'),sysdate,null,sys_context('userenv','ip_address'),'ora-01017');
    end if;
end;

练习:

创建个触发器限制scott用户从指定IP登录:
CREATE OR REPLACE TRIGGER TRIGGER_RESTRICT_LOGON
AFTER LOGON ON DATABASE
DECLARE
 RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
 ALLOWED_IP      VARCHAR2(16) := '10.10.10.10';
 LOGON_USER      VARCHAR2(32);
 CLIENT_IP       VARCHAR2(16);
BEGIN
 LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');
 CLIENT_IP  := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
  IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
   RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
 END IF;
END;
/
create or replace
trigger tri_for_user
after logon
on database
declare
    r_user VARCHAR2(30) := 'HR';
    r_ip VARCHAR2(17) := '192.168.8.2';
begin
    if sys_context('userenv','session_user') = r_user and sys_context('userenv','ip_address') <> r_ip then
       raise_application_error(-20001,'do not permit to logon with this ip');
    end if;
end;

sqlplus 

DDL触发器
 禁止用户做DDL操作
 不能做在sys上 只能拿普通用做
 使用raise_application_error函数来禁止操作
CREATE OR REPLACE TRIGGER DDL_FB 
before  ALTER OR DROP OR RENAME 
on schema 
BEGIN
  Raise_application_error(-20030,'此系统已经运行，不允许对表进行DDL维护');
end;

create table log_audit(

l_user varchar2(20),l_on_date date,l_off_date date,user_ip varchar2(17),error_code varchar2(20));
create or replace trigger tri_logon
after logon
on database
begin
    if sys_context('userenv','session_user') not in ('SYS','SYSMAN','SYSTEM') THEN
       INSERT INTO LOG_AUDIT 
       VALUES(sys_context('userenv','session_user'),sysdate,null,sys_context('userenv','ip_address'),null);
    end if;
end;