Oracle 11g/12c中PLSQL存储过程提交时记录审计日志的方案咨询

下面提供三种可行的实现方案，覆盖不同场景需求：

方案一：显式追踪DML影响行数（适用于可控的存储过程） #

这种方式需要在存储过程中手动捕获每个DML操作的影响行数，提交前统一写入审计日志表。

1. 创建审计日志表 #

CREATE TABLE audit_dml_log (
    log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name VARCHAR2(128) NOT NULL,
    operation_type VARCHAR2(10) NOT NULL, -- 取值：INSERT/UPDATE/DELETE
    affected_rows NUMBER NOT NULL,
    commit_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP,
    procedure_name VARCHAR2(128) NOT NULL,
    user_name VARCHAR2(30) DEFAULT USER
);

2. 改造存储过程示例 #

CREATE OR REPLACE PROCEDURE update_employee_salary (p_dept_id NUMBER, p_percent NUMBER)
IS
    v_update_rows NUMBER := 0;
    v_insert_rows NUMBER := 0;
BEGIN
    -- 执行UPDATE操作并捕获影响行数
    UPDATE employees
    SET salary = salary * (1 + p_percent/100)
    WHERE department_id = p_dept_id;
    v_update_rows := SQL%ROWCOUNT;

    -- 执行INSERT操作并捕获影响行数
    INSERT INTO emp_salary_history
    SELECT employee_id, salary, SYSTIMESTAMP
    FROM employees
    WHERE department_id = p_dept_id;
    v_insert_rows := SQL%ROWCOUNT;

    -- 将操作记录写入审计表
    IF v_update_rows > 0 THEN
        INSERT INTO audit_dml_log (table_name, operation_type, affected_rows, procedure_name)
        VALUES ('EMPLOYEES', 'UPDATE', v_update_rows, 'UPDATE_EMPLOYEE_SALARY');
    END IF;
    IF v_insert_rows > 0 THEN
        INSERT INTO audit_dml_log (table_name, operation_type, affected_rows, procedure_name)
        VALUES ('EMP_SALARY_HISTORY', 'INSERT', v_insert_rows, 'UPDATE_EMPLOYEE_SALARY');
    END IF;

    -- 提交事务（包含业务操作和审计日志）
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

优缺点：

• 优点：完全可控，仅追踪目标存储过程的操作，性能开销低。
• 缺点：需要修改现有存储过程代码，需手动覆盖所有DML操作，易遗漏。

方案二：表级触发器全局追踪（适用于全表审计） #

通过创建AFTER语句级触发器，自动捕获对指定表的所有DML操作，无需修改业务存储过程。

1. 复用方案一的audit_dml_log表 #

2. 创建触发器示例（以EMPLOYEES表为例） #

CREATE OR REPLACE TRIGGER trg_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH STATEMENT
DECLARE
    v_op_type VARCHAR2(10);
BEGIN
    -- 判断操作类型
    IF INSERTING THEN
        v_op_type := 'INSERT';
    ELSIF UPDATING THEN
        v_op_type := 'UPDATE';
    ELSIF DELETING THEN
        v_op_type := 'DELETE';
    END IF;

    -- 写入审计日志，通过SYS_CONTEXT获取当前执行的存储过程名
    INSERT INTO audit_dml_log (table_name, operation_type, affected_rows, procedure_name)
    VALUES ('EMPLOYEES', v_op_type, SQL%ROWCOUNT, SYS_CONTEXT('USERENV', 'MODULE'));
END;
/

3. 存储过程中设置模块名（可选，保证procedure_name字段准确） #

在存储过程开头添加以下代码，确保触发器能捕获到正确的存储过程名称：

DBMS_APPLICATION_INFO.SET_MODULE('UPDATE_EMPLOYEE_SALARY', 'Updating employee salaries');

优缺点：

• 优点：无需修改业务代码，全局追踪指定表的所有DML操作。
• 缺点：对高并发场景有一定性能开销，需避免递归触发（审计表本身不要创建触发器）。

方案三：Oracle内置审计功能（适用于全局审计需求） #

利用Oracle内置的审计机制，可快速实现全库或指定对象的审计，但默认不记录影响行数，需结合其他方式补充。

11g传统审计配置 #

-- 开启EMPLOYEES表的DML审计
AUDIT INSERT, UPDATE, DELETE ON employees BY ACCESS;

-- 查询审计记录
SELECT username, obj_name, action_name, timestamp
FROM dba_audit_trail
WHERE obj_name = 'EMPLOYEES';

12c统一审计配置 #

-- 创建审计策略
CREATE AUDIT POLICY emp_dml_policy
ACTIONS INSERT, UPDATE, DELETE ON employees;

-- 启用审计策略
AUDIT POLICY emp_dml_policy;

-- 查询统一审计记录
SELECT username, object_name, action_name, event_timestamp
FROM unified_audit_trail
WHERE object_name = 'EMPLOYEES';

注意：内置审计默认不返回影响行数，若必须获取行数，建议结合方案一或方案二使用。

通用注意事项 #

• 定期清理审计日志表，避免数据量过大影响数据库性能。
• 确保执行存储过程/触发器的用户拥有audit_dml_log表的INSERT权限。
• 生产环境中测试触发器和审计逻辑，避免影响业务流程。

内容的提问来源于stack exchange，提问作者Prathamesh Pitale