oracle 常用审计相关脚本整理

   随着这些年勒索病毒的爆发，各个企业对数据安全的要求越来越高，常见的办法有开启数据库审计，加数据库防火墙，网络限制等等；但是细粒度审计会消耗大量系统资源，第三方数据库防火墙一般是需要收费的；这里介绍我个人常用的四个db级别trigger，用于记录部分关键信息可以应对部分审计需要。

• logon 成功信息

session logon后触发 记录登陆session的IP，machine，program，username，logontime等关键信息
PS:如果负载很高的DB 这个trigger请谨慎使用，数据量可能会很大，也可能会对登陆的时效产生部分影响

-- Create table
create table SYS.A_DB_SUCCESSFUL_LOGINS
(
  inst_id    NUMBER,
  username   VARCHAR2(30),
  osuser     VARCHAR2(30),
  machine    VARCHAR2(64),
  terminal   VARCHAR2(30),
  ipaddr     VARCHAR2(30),
  program    VARCHAR2(48),
  module     VARCHAR2(48),
  isdba      VARCHAR2(10),
  logon_time DATE
)


---CREATE TRIGGER
CREATE OR REPLACE TRIGGER SYSTEM.a_db_successful_login_trg

AFTER logon ON DATABASE

DECLARE

   v_inst_id    a_db_successful_logins.inst_id%TYPE;
   v_ipaddr     a_db_successful_logins.ipaddr%TYPE;
   v_logon_user a_db_successful_logins.username%TYPE;
   v_machine    a_db_successful_logins.machine%TYPE;
   v_terminal   a_db_successful_logins.terminal%TYPE;
   v_osuser     a_db_successful_logins.osuser%TYPE;
   v_program    a_db_successful_logins.program%TYPE;
   v_module     a_db_successful_logins.module%TYPE;
   v_isdba      a_db_successful_logins.isdba%TYPE;
/************************************************************************
   name:  a_db_successful_login_trg
   purpose: log and check the database LOGON action.
   revisions:
   ver        DATE         author           description
   1.0        2012         xiaoxiangqin      record logon info
   ---------  ----------  ---------------  ---------------------------------
   
   ************************************************************************/

 BEGIN
   -- read the context

    v_inst_id    := sys_context('USERENV','INSTANCE');
    v_osuser     := sys_context('USERENV','OS_USER');
    v_machine    := sys_context('USERENV','HOST');
    v_terminal   := sys_context('USERENV','TERMINAL');
    v_ipaddr     := sys_context('USERENV','IP_ADDRESS');
    v_logon_user := sys_context('USERENV','SESSION_USER');
    v_isdba      := sys_context('USERENV','ISDBA');
--cut strange char for windows server.
    v_machine := REPLACE(v_machine,chr(0),'');

	IF v_machine <> sys_context('USERENV','SERVER_HOST') --List the local ap or database server.

     THEN
  --fetch additional session info.
    SELECT program,module
    INTO v_program,v_module
    FROM v$session
    WHERE sid IN (SELECT m.sid FROM v$mystat m WHERE rownum < 2);
    --record the logon record.

    INSERT INTO sys.a_db_successful_logins
     (inst_id,username,osuser,
	 machine,terminal,ipaddr,
	 program,module,isdba,
	 logon_time)

     VALUES(v_inst_id
           ,v_logon_user
           ,v_osuser
           ,v_machine
           ,v_terminal
           ,v_ipaddr
           ,v_program
           ,v_module
           ,v_isdba
           ,SYSDATE);
   END IF;

END a_db_successful_login_trg;

• logon 失败信息
  session logon失败后触发 记录登陆session的IP，machine，program，username，logontime等关键信息
  和上面的登陆成功信息类似，不过一般这个表数据量不会大。
  

  -- Create table
  create table SYS.A_DB_UNSUCCESSFUL_LOGINS
  (
    username   VARCHAR2(30),
    osuser     VARCHAR2(30),
    machine    VARCHAR2(64),
    terminal   VARCHAR2(30),
    ipaddr     VARCHAR2(30),
    program    VARCHAR2(48),
    module     VARCHAR2(48),
    isdba      VARCHAR2(10),
    errormsg   VARCHAR2(60),
    logon_time DATE default SYSDATE
  )
  
  ---create trigger
  
  CREATE OR REPLACE TRIGGER SYS.a_db_unsuccessful_login_trg
  
  AFTER servererror ON DATABASE
  
  DECLARE
  
      v_ipaddr     a_db_unsuccessful_logins.ipaddr%TYPE;
      v_logon_user a_db_unsuccessful_logins.username%TYPE;
      v_machine    a_db_unsuccessful_logins.machine%TYPE;
      v_terminal   a_db_unsuccessful_logins.terminal%TYPE;
      v_osuser     a_db_unsuccessful_logins.osuser%TYPE;
      v_program    a_db_unsuccessful_logins.program%TYPE;
      v_module     a_db_unsuccessful_logins.module%TYPE;
      v_errormsg   a_db_unsuccessful_logins.errormsg%TYPE;
      v_isdba      a_db_unsuccessful_logins.isdba%TYPE;
      v_log_error  BOOLEAN DEFAULT FALSE;
  
      /************************************************************************
  
      name:  a_db_unsuccessful_login_trg
  
      purpose: log unsuccessful LOGON action.
  
  	revisions:
  
      ver        DATE         author           description
  
      ---------  ----------  ---------------  ---------------------------------
  
      1.0        2023/11/01    norton.fan   create this TRIGGER.
  
      ************************************************************************/
  
  BEGIN
  
      -- read the context
  
      v_osuser     := sys_context('USERENV','OS_USER');
      v_machine    := sys_context('USERENV','HOST');
      v_terminal   := sys_context('USERENV','TERMINAL');
      v_ipaddr     := sys_context('USERENV','IP_ADDRESS');
      v_logon_user := sys_context('USERENV','SESSION_USER');
      v_isdba      := sys_context('USERENV','ISDBA');
      --cut strange char for windows server.
  
      v_machine := REPLACE(v_machine,chr(0),'');
  
  --fetch additional session info.
  
      SELECT program ,module
        INTO v_program ,v_module
        FROM v$session
       WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);
  
      IF is_servererror(1017)
      THEN
         v_log_error := TRUE;
         v_errormsg  := 'ORA-01017: invalid username/password; logon denied';
      END IF;
  
      IF is_servererror(20017)
      THEN
         v_log_error := TRUE;
         v_errormsg  := 'ORA-20017: invalid access privilege; logon denied';
      END IF;
  
      IF v_log_error
      THEN
         --record the unsuccessful logon
         INSERT INTO system.a_db_unsuccessful_logins
            (username
            ,osuser
            ,machine
            ,terminal
            ,ipaddr
            ,program
            ,module
            ,isdba
            ,errormsg
            ,logon_time)
         VALUES
            (v_logon_user
            ,v_osuser
            ,v_machine
            ,v_terminal
            ,v_ipaddr
            ,v_program
            ,v_module
            ,v_isdba
            ,v_errormsg
            ,SYSDATE);
  
      END IF;
   END a_db_unsuccessful_login_trg;
  /

• DDL记录
  触发条件为发生DDL，会记录ddl相关的关键信息 包括执行会话的IP，machine，program，object name，sql text，exec time等等
  这个信息我个人认为是非常重要的，比如发生drop table，truncate等危险操作时有log可以追溯
  

  -- Create table
  create table SYSTEM.A_SYSTEM_DDL_LOG
  (
    db_name    VARCHAR2(50),
    inst_num   NUMBER,
    event_name VARCHAR2(20),
    login_user VARCHAR2(30),
    obj_type   VARCHAR2(20),
    obj_owner  VARCHAR2(30),
    obj_name   VARCHAR2(30),
    osuser     VARCHAR2(30),
    machine    VARCHAR2(64),
    client_ip  VARCHAR2(15),
    program    VARCHAR2(64),
    module     VARCHAR2(48),
    event_date DATE default SYSDATE,
    sql_text   VARCHAR2(4000)
  )
  
  ----
  CREATE OR REPLACE TRIGGER SYSTEM.a_system_ddl_trigger
     AFTER CREATE OR ALTER OR DROP OR TRUNCATE ON DATABASE
  DECLARE
     v_event      a_system_ddl_log.event_name%TYPE;
     v_inst_num   a_system_ddl_log.inst_num%TYPE;
     v_db_name    a_system_ddl_log.db_name%TYPE;
     v_type       a_system_ddl_log.obj_type%TYPE;
     v_name       a_system_ddl_log.obj_name%TYPE;
     v_owner      a_system_ddl_log.obj_owner%TYPE;
     v_client_ip  a_system_ddl_log.client_ip%TYPE;
     v_login_user a_system_ddl_log.login_user%TYPE;
     v_machine    a_system_ddl_log.machine%TYPE;
     v_osuser     a_system_ddl_log.osuser%TYPE;
     v_program    a_system_ddl_log.program%TYPE;
     v_module     a_system_ddl_log.module%TYPE;
     v_sqltext    a_system_ddl_log.sql_text%TYPE; --varchar2(4000)
     sql_text     ora_name_list_t;
     n            NUMBER;
     i            NUMBER;
     /************************************************************************
     name:  a_system_ddl_trigger
     purpose: monitor the database DDL statement
  
     revisions:
     ver        DATE         author           description
     ---------  ----------  ---------------  ---------------------------------
     1.0        2012    xiaoxiangqin       created this TRIGGER.
     ************************************************************************/
  BEGIN
     -- read the event first
     v_event      := ora_sysevent;
     v_inst_num   := ora_instance_num;
     v_db_name    := ora_database_name;
     v_type       := ora_dict_obj_type;
     v_name       := ora_dict_obj_name;
     v_owner      := ora_dict_obj_owner;
     v_client_ip  := sys_context('USERENV' ,'IP_ADDRESS');
     v_login_user := ora_login_user;
     n            := ora_sql_txt(sql_text);
  
     --get the sql text
     i := 1;
     LOOP
        v_sqltext := v_sqltext || sql_text(i);
        i         := i + 1;
        EXIT WHEN (i > n OR (length(v_sqltext || sql_text(i)) > 2000));
     END LOOP;
  
     SELECT osuser
           ,machine
           ,program
           ,module
       INTO v_osuser
           ,v_machine
           ,v_program
           ,v_module
       FROM v$session
      WHERE sid IN (SELECT m.sid
                      FROM v$mystat m
                     WHERE rownum < 2);
  
     --record the event to the log
     INSERT INTO system.a_system_ddl_log
        (event_name
        ,inst_num
        ,db_name
        ,obj_type
        ,obj_name
        ,obj_owner
        ,client_ip
        ,login_user
        ,osuser
        ,machine
        ,program
        ,module
        ,sql_text)
     VALUES
        (v_event
        ,v_inst_num
        ,v_db_name
        ,v_type
        ,v_name
        ,v_owner
        ,v_client_ip
        ,v_login_user
        ,v_osuser
        ,v_machine
        ,v_program
        ,v_module
        ,v_sqltext);
  END a_system_ddl_trigger;
  

  
  
• 访问控制
  这个trigger的作用是只允许指定的IP和OSUSER使用开发工具（plsql，toad，sqlplus）登陆到数据库，一般用于敏感数据库的访问控制，如HR系统，资金系统等等；
  目前的开发工具很多并不能完全拦截，个人认为加上主流的开发工具限制应该可以了； 也可以根据业务逻辑抓取特定的客户端信息来做定制化的限制，这里只是提供了一个范例。
  

  -- Create table 存储授权登陆的osuser和ip
  create table mtr.PERMIT_USER
  (
    user_ad VARCHAR2(20),
    ip      VARCHAR2(30)
  )
  
  
  create or replace trigger sys.check_user after logon on database      ----logon trigger
  DECLARE
     v_osuser  VARCHAR2(16);
     v_program VARCHAR2(50);
     v_cnt     NUMBER;
     v_user    VARCHAR2(10);
     v_mac     VARCHAR2(30);
     v_ipaddr  VARCHAR2(30);
  BEGIN
     v_osuser := sys_context('USERENV'
                            ,'OS_USER');
     v_user   := sys_context('USERENV'
                            ,'SESSION_USER');
     v_mac    := sys_context('USERENV'
                            ,'TERMINAL');  
     v_ipaddr     := sys_context('USERENV'
                                ,'IP_ADDRESS');                                                 ----logon terminal info
     SELECT upper(module)
       INTO v_program
       FROM v$session
      WHERE sid = (SELECT sid
                     FROM v$mystat
                    WHERE rownum = 1);
     SELECT COUNT(*)
       INTO v_cnt
       FROM mtr.permit_user          ----table storge
      WHERE upper(osuser) = upper(v_osuser) and ip=v_ipaddr;
     IF v_cnt = 0
     THEN
        IF (substr(v_program,1,4) = 'PLSQ' OR
            substr(v_program,1,4) = 'TOAD' OR
            substr(v_program,1,7) = 'SQLPLUS') AND
           v_user in ('MTR')   ---被审计用户
        THEN
           raise_application_error(-20001
                                  ,'you are not permitted.');
        END IF;
  
     END IF;
  
  END;
  

  后记，对于越来越严峻的信息安全威胁，这些可能是不足够，只是提供了一个无需外部支持，仅仅依赖于oracle本身的trigger来实现的一些基础的审计记录；

具体代码请根据需求 审核测试后再应用！