基于系统级触发器的审计功能

引自：http://www.oraclefans.cn/forum/showblog.jsp?rootid=4679

从Oracle8i开始，Oracle引入了特殊的触发器，这些触发器并不是和特殊的DML事件相关联的（DML事件，如，INSERT,UPDATE和DELETE）。这些系统级别的触发器包括：

         数据库启动/关闭触发器

         DDL触发器

         最终用户登陆/注销触发器

         系统错误触发器

         DDL触发器

使用这些触发器，可以对数据库发生的一些重要事件进行审计。

当触发事件发生的时候，可以启动触发器。在触发器中，可以通过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的证书

1.5.1  oracle 8i新增的数据库事件触发器
从ORALCE 8i开始，用户可以通过常见系统级触发器来追踪一些系统事件。通过这些触发器，用户可以进行一些操作，包括审计、把一些对象PIN入共享池、进行一些维护操作等。以下是一些系统级触发器的类型：

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

Logon/Logoff触发器可以用来记录用户等入和退出的时间。数据库启动和关闭触发器可以用来进行一些数据库启动后和关闭前的前处理和后处理。比如在数据库启动的时候把一些PL/SQL对象固定倒内存中。Servererror触发器可以用于记录某些重要的错误信息，以便于跟踪系统，发现故障。在使用系统级触发器前，首先要确认系统参数_system_trig_enabled是否设置为TRUE。

1.5.2 DDL触发器概述
通过DDL触发器可以记录特定的DDL操作。DDL触发器是从ORACLE 8i开始具有的触发器类型。在ORACLE 9i中，DDL触发器得到了加强。DDL触发器的触发事件???括：

l         BEFORE / AFTER ALTER

l         BEFORE / AFTER CREATE

l         BEFORE / AFTER DROP

l         BEFORE / AFTER RENAME

l         BEFORE / AFTER ANALYZE

l         BEFORE / AFTER ASSOCIATE STATISTICS

l         BEFORE / AFTER DISASSOCIATE STATISTICS

l         BEFORE / AFTER AUDIT

l         BEFORE / AFTER NOAUDIT

l         BEFORE / AFTER COMMENT

l         BEFORE / AFTER DDL

l         BEFORE / AFTER GRANT

l         BEFORE / AFTER REVOKE

l         BEFORE / AFTER TRUNCATE

l         AFTER SUSPEND

要创建DDL触发器，需要一定的系统权限，这些权限包括：

l         create trigger

l         create any trigger

l         administer database trigger

l         alter any trigger

l         drop any trigger

DDL触发器可以用于细化的DDL操作审计。一般的做法是设计一张日志表，当DDL触发器激活后，通过系统相关的系统事件函数获取锁需要的信息，然后把数据插入日志表中。这样，就可以记录系统中需要审计的DDL操作。DDL触发器可以针对Database级或者SCHEMA级???

要注意的是，DDL触发器仅对以下的对象有效：cluster, function, index, package, procedure, role, sequence,  synonym, table, tablespace, trigger, type, view,  user。

1.5.3 使用系统级触发器审计用户登录情况
    本节的例子介绍了使用系统的LOGON/LOGOFF触发器来审计用户登录的情况。对于需要掌握用户在线时间的系统中十分有用。首先要创建一张表，该表保存用户登录的信息。然后创建三个触发器，分别对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 log_errors AFTER SERVERERROR ON DATABASE

BEGIN

 IF (IS_SERVERERROR (1017)) THEN

  insert into system.log_audit (login_date,error_code) values(sysdate,'ORA-1017');

 END IF;

END;

创建登录审计触发器：

CREATE OR REPLACE TRIGGER logon_audit AFTER LOGON ON DATABASE

BEGIN

 insert into system.log_audit(login_date,username,user_ip) values(sysdate,user,ora_client_ip_address);

END;

创建退出审计触发器：

CREATE OR REPLACE TRIGGER logoff_audit BEFORE LOGOFF ON DATABASE

BEGIN

 insert into system.log_audit (logoff_date,username,user_ip) values(sysdate,user,ora_client_ip_address);

END;

/

    本例子可以对数据库的登录和退出记录日志。当触发器发生故障的时候（比如log_audit表所在的表空间满的时候，会导致用户登录失败。此时，需要使用具有ADMINISTER DATABASE TRIGGER权限的帐号登录，然后将审计所使用的触发器disabale，或者修正触发器的错误。因此在使用系统事件触发器的时候要十分谨慎。

1.5.4 使用系统级触发器禁用DDL语句
对于一个上线运行的系统，使用DDL操作是十分谨慎的。为了防止不必要的误操作，可以通过DDL触发器对某些表禁用DDL操作。可以在需要禁止的操作类型上使用raise_application_error函数来禁止操作，比如：

CREATE OR REPLACE TRIGGER DDL_FB before  ALTER OR DROP  OR RENAME

on schema 

BEGIN

  Raise_application_error(-20030,’此系统已经运行，不允许对表进行DDL维护’);

end;

对于设置了这类触发器的用户，如果需要修改表结构，那么需要首先把这个触发器关掉。

1.5.5 使用系统级触发器审计重要帐号的DDL语句
如果要审计数据库中的DDL操作，那么可以通过DDL触发器来实现，本节介绍一个例子，把数据库中的所有DDL操作都记录下来。本例子可以在oracle 8i或更高的版本中使用。

第一步，创建表空间和相关的日志表：

create tablespace statlog datafile '/oradata/statlog.dbf' size 200m;

create table stat$log_ddl

(

 ddl_date date,

 user_name varchar2(30),

 ip_addr VARCHAR2(30),

obj_name VARCHAR2(50),

 ddl_type      VARCHAR2(30),

 object_type   VARCHAR2(18),

 owner         VARCHAR2(30),

 SQL_TEXT      VARCHAR2(1000)

) TABLESPACE STATLOG;

第二步，创建数据库级的DDL触发器，把所有的DDL操作都记录下来

CREATE OR REPLACE TRIGGER DDL_audit AFTER CREATE OR ALTER OR DROP OR TRUNCATE OR

GRANT OR REVOKE OR RENAME

on DATABASE 

declare

 ipaddr varchar2(20);

 STEXT VARCHAR2(1000);

BEGIN

 begin

  select sys_context('USERENV', 'IP_ADDRESS') into ipaddr FROM dual;

 exception when others then

  ipaddr:='-';

 end;

 begin

  select SQL_TEXT INTO STEXT FROM v$open_cursor      WHERE UPPER(sql_text) LIKE 'ALTER%';

 exception when others then

  STEXT:='-';

 end;

 insert into sys.stat$log_DDL values

 (sysdate,

 user,

 nvl (ipaddr,'-'),

 NVL(ora_dict_obj_name,'-'),

 NVL(ORA_SYSEVENT,'-'),

 NVL(ora_dict_obj_type,'-'),

 NVL(ora_dict_obj_owner,'-'),

 STEXT

  );

exception when others then

 null;

end;

/