ORACLE审计之谁动了我的报表

1.开发背景

众所周知，ORACLE自带审计功能，但是开启审计会造成系统性能下降，
审计和性能经常是对立面，完全没有审计，违规的修改可能就会钻空子。如果此时权限也没有得到合规的管理，系统将会经常面临莫名奇妙的风险，并且难以溯源。
曾经遇到过一个事情，报表的SQL逻辑被错误修改，原SQL逻辑还没有做备份，领导非常着急，由于没有审计也找不出到底是谁乱改的。为了恢复报表正常使用，最后我通过SQL历史视图DBA_HIST_SQLTEXT找到了以前的SQL文本，按照经验进行了还原。领导希望再出现这种事情可以快速恢复，并且能可以对操作记录进行审计，但又不想开启审计改造系统应用。要求我在不开启数据库审计的前提下，做好审计管控。
无奈之下，只好退而求其次做个性化开发，针对重要的业务设计触发器。触发器还必须考虑不能用于DML操作过于频繁的功能。

2.设计思路

（1）创建日志表

创建一个表用于记录修改日志

-- 审计日志表
create table T_AUDIT_SHEETS_LOG
(
  operate_date       TIMESTAMP(6) not null,
  event_name         VARCHAR2(32),
  user_name          VARCHAR2(32),
  ip_address         VARCHAR2(32),
  machine            VARCHAR2(80),
  module             VARCHAR2(4000),
  os_user            VARCHAR2(80),
  terminal           VARCHAR2(50),
  session_id         NUMBER(10),
  new_sheetsql       CLOB,
  old_sheetsql       CLOB)
);

（2）设计目标表的DML触发器

假定需要审计的报表对象表为T_SHEETS并需要管控这张表相关的INSERT, UPDATE, DELETE语句，需要记录下来操作IP，终端名称，连接ORACLE的客户端名

--触发器设计
CREATE OR REPLACE TRIGGER TRI_DML_SHEETS_LOG
  BEFORE UPDATE OR DELETE OR INSERT ON T_SHEETS
  FOR EACH ROW

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  V_IP       VARCHAR2(100);
  V_JQM      VARCHAR2(80);
  V_MD       VARCHAR2(4000);
  V_OSUSER   VARCHAR2(80);
  V_TERMINAL VARCHAR2(50);
  V_SID      NUMBER(10);
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO V_IP FROM DUAL;
  SELECT SYS_CONTEXT('USERENV', 'HOST') INTO V_JQM FROM DUAL;
  SELECT SYS_CONTEXT('USERENV', 'MODULE') INTO V_MD FROM DUAL;
  SELECT SYS_CONTEXT('USERENV', 'OS_USER') INTO V_OSUSER FROM DUAL;
  SELECT SYS_CONTEXT('USERENV', 'TERMINAL') INTO V_TERMINAL FROM DUAL;
  SELECT SYS_CONTEXT('USERENV', 'SID') INTO V_SID FROM DUAL;

  IF INSERTING
  THEN
    INSERT INTO T_AUDIT_SHEETS_LOG
      (OPERATE_DATE,
       EVENT_NAME,
       USER_NAME,
       IP_ADDRESS,
       MACHINE,
       MODULE,
       OS_USER,
       TERMINAL,
       SESSION_ID,
       new_sheetsql,
       old_sheetsql)
    VALUES
      (SYSTIMESTAMP,
       'INSERT',
       ORA_LOGIN_USER,
       V_IP,
       V_JQM,
       V_MD,
       V_OSUSER,
       V_TERMINAL,
       V_SID,
       :NEW.sheetsql,
       :OLD.sheetsql);
  
  ELSIF UPDATING
  THEN
    INSERT INTO T_AUDIT_SHEETS_LOG
      (OPERATE_DATE,
       EVENT_NAME,
       USER_NAME,
       IP_ADDRESS,
       MACHINE,
       MODULE,
       OS_USER,
       TERMINAL,
       SESSION_ID,
       new_sheetsql,
       old_sheetsql)
    VALUES
      (SYSTIMESTAMP,
       'UPDATE',
       ORA_LOGIN_USER,
       V_IP,
       V_JQM,
       V_MD,
       V_OSUSER,
       V_TERMINAL,
       V_SID,
       :NEW.sheetsql,
       :OLD.sheetsql);
  ELSIF DELETING
  THEN
    INSERT INTO T_AUDIT_SHEETS_LOG
      (OPERATE_DATE,
       EVENT_NAME,
       USER_NAME,
       IP_ADDRESS,
       MACHINE,
       MODULE,
       OS_USER,
       TERMINAL,
       SESSION_ID,
       new_sheetsql,
       old_sheetsql)
    VALUES
      (SYSTIMESTAMP,
       'DELETE',
       ORA_LOGIN_USER,
       V_IP,
       V_JQM,
       V_MD,
       V_OSUSER,
       V_TERMINAL,
       V_SID,
       :NEW.sheetsql,
       :OLD.sheetsql);
  
  END IF;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
  
   -- 记录异常到独立日志表
    INSERT INTO audit_error_log VALUES
    (SYSTIMESTAMP,SQLERRM);
  
END;
/

（3）验证测试

对目标表做各种DML操作，观察是否记录了操作日志

--测试DML语句
UPDATE T_AUDIT_SHEETS_LOG
   SET SHEETSQL = NULL
 WHERE SHEETSQL = 'NONAME';

INSERT INTO T_AUDIT_SHEETS_LOG
   VALUES(1,SHEETSQL)

DELETE T_AUDIT_SHEETS_LOG
 WHERE SHEETSQL = 'NONAME';

--检查GG_CXMK审计日志
SELECT OPERATE_DATE,
       EVENT_NAME,
       USER_NAME,
       IP_ADDRESS,
       MACHINE,
       MODULE,
       OS_USER,
       TERMINAL,
       SESSION_ID,
       new_sheetsql,
       old_sheetsql FROM T_AUDIT_SHEETS_LOG;

恢复直接用OLD_SHEETSQL的内容还原即可，并且可以看到操作人的信息便于追查线索。单纯恢复数据如果修改时间在UNDO_RETENTION参数范围之内，可以直接考虑闪回查询。不嫌麻烦还可以考虑LOGMINR，这里卖个关子，后面详细谈谈这个，一张表的问题就不费劲了。

3. 后记：

有意思的是，触发器建立之后好像就形成了一把悬在天上达摩之剑，再没有人未经允许擅自更改报表逻辑了。触发器审计设计的很简单，如果想追踪特定的对象比如存储过程，也可以考虑此方法。但触发器明显有个缺点就是每次只能监控一个对象，除非条件有限，还是更建议增加运维审计系统或者严格发布流程限制权限来进行把控。触发器太多一样面临着性能问题。