【数据安全】针对单表的删除审计（删除记录追踪）

最近发现一个基础表的记录居然有被删除了，很是奇怪，不应该被删除呀。为了监控删除的情况，记录日志，并做出告警，用于把握时机，定位并查询应用日志当时情况，查找干这事的来源

当然，要查询这事，其实也可以通过挖掘日志，但是由于在哪个时间点删除的不确定，日志挖掘会比较耗时耗力

1. 数据库审计功能

查询数据库是否开启了审计

SQL> show parameter aut
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_undo_autotune                       boolean     FALSE
hs_autoregister                      boolean     TRUE
ldap_directory_sysauth               string      no
os_authent_prefix                    string      ops$
parallel_automatic_tuning            boolean     FALSE
remote_os_authent                    boolean     FALSE

复制

发现并无，而要开启审计，需要进行数据库重启

alter system set audit_sys_operations=TRUE scope=spfile;
alter system set audit_trail=db_extended scope=spfile;
复制

记录t表的操作（2种方式）

1）记录对表t的每个操作

AUDIT DELETETABLE t BY ACCESS;
复制

2）记录每个会话中是否进行了操作（不关心操作了多少次）,此命令默认使用BY SESSION的选项

AUDIT DELETE ON t;
复制

由于生产系统，不是想重启就能重启的，故另想了办法，使用触发器去实现

2. 【重点】触发器实现delete表记录日志

1）创建日志存放表，表根据实际情况调整记录的字段

CREATE TABLE t_delete_log (
  id VARCHAR2(90),
  data VARCHAR2(2000),
  delete_date timestamp(6)
);
复制

2) 创建触发器

CREATE OR REPLACE TRIGGER t_delete_trigger
AFTER DELETE ON t
FOR EACH ROW
BEGIN
  INSERT INTO t_delete_log(id, data, delete_date)
  VALUES (:OLD.id, :OLD.barcode, SYSDATE);
END;
/
复制

3） 验证操作

-- 查询日志表，发现无数据
select * from t_delete_log；

-- 执行删除表t的操作

select * from t  where id ='6563f39861cbb3477c6feae2' -- 查询有一行记录
delete from t  where id ='6563f39861cbb3477c6feae2'  -- 删除一行记录

select * from t_delete_log； --再次查询，发现记录里添加了一行记录
SQL> select * from t_delete_log;

ID                           DATA             DELETE_DATE
-------------------------- ------------------------- -----------
6563f39861cbb3477c6feae2    202311271200       2024-01-12 15:26:44

SQL>
复制

查询发现日志表中已经记录了一行记录。

监控脚本

思路：获取表t的变动，然后通过即时通讯或者邮件通知到相关人

#!/bin/bash
. /home/oracle/.bash_profile
now_date=`date "+%Y-%m-%d_%H:%M:%S"`
echo $now_date
host=`/usr/sbin/ifconfig |sed -n "2p"|awk '{print $2}'`
 
t_log_num(){
nums=`sqlplus -s / as sysdba <<EOF
start /mnt/mon/select_t_log.sql
exit
EOF`
if [ $nums -ge 1 ] ;then
        source /mnt/mon/mail.sh  " 【 $now_date 】 主机addr:${host} ：发现t表数据大于1，当前记录数 ${nums}"
fi
}

复制

– mail.sh 脚本可以是调用邮件或者钉钉发送通知出来
– select_t_log.sql内容

select count(1) as nums from t_delete_log;
复制

4. 【常见问题调试】触发器调试过程

若触发器创建完成后，可能会遇到各种报错“ ORA-04098: 触发器 ‘USER1.T_DELETE_TRIGGER’ 无效且未通过重新验证”，可以通过以下方法查看详细报错

-- 重新编译触发器
SQL> ALTER TRIGGER T_DELETE_TRIGGER COMPILE;

Warning: Trigger altered with compilation errors


SQL> SHOW ERRORS TRIGGER t_delete_trigger;
Errors for TRIGGER USER1.T_DELETE_TRIGGER:
LINE/COL ERROR
-------- -----------------------------
2/15     PL/SQL: ORA-00942: 表或视图不存在
2/3      PL/SQL: SQL Statement ignored

-- 也可能是报错
SQL> SHOW ERRORS TRIGGER t_delete_trigger;
Errors for TRIGGER USER1.T_DELETE_TRIGGER:
LINE/COL ERROR
-------- --------------------------------------------
2/72     PL/SQL: ORA-00904: "DELETE_DATE": 标识符无效
2/3      PL/SQL: SQL Statement ignored
复制

5. 扩展，当然若是想知道增删改（insert 、delete 、 update 操作，按实际情况灵活配置即可）

-- 新增表
CREATE TABLE t_log  (
  id VARCHAR2(90),
  data VARCHAR2(2000),
  operation VARCHAR2(20)
  delete_date timestamp(6)
);

-- 创建触发器
CREATE OR REPLACE TRIGGER t_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON t
FOR EACH ROW
DECLARE
  v_operation VARCHAR2(20);
BEGIN
  IF INSERTING THEN
    v_operation := 'INSERT';
  ELSIF UPDATING THEN
    v_operation := 'UPDATE';
  ELSIF DELETING THEN
    v_operation := 'DELETE';
  END IF;

  INSERT INTO t_log (id, data, operation, op_date)
  VALUES (
    NVL(:NEW.id, :OLD.id),
    NVL(:NEW.data, :OLD.data),
    v_operation,
    SYSDATE
  );
END;
/

复制

PS：类似的，也可以记录更多的东西，比如：来源用户，来源的机器等等