本实验参考Oracle By Example中的实验：Audit Top-Level User Activities。

准备测试环境

安装Oracle Database Sample Schema（如果没有）。我用的19c版。

示例表为employees，来自HR schema。操作前先备份到employees_bak。

SQL> select count(*) from employees;

  COUNT(*)
----------
       107
       
SQL> create table employees_bak as select * from employees;

Table created.

创建测试存储过程，源代码在这里。

我对过程raise_salary稍作了改动，将获取薪资上下限的逻辑从employees表改为了jobs表。完整的代码如下。

SET ECHO ON 
CREATE OR REPLACE PACKAGE hr.emp_admin AUTHID DEFINER AS
  TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER);
  CURSOR desc_salary RETURN EmpRecTyp;
  invalid_salary EXCEPTION;

  PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);
END emp_admin;
/

CREATE OR REPLACE PACKAGE BODY hr.emp_admin AS
  number_hired  NUMBER;  

  CURSOR desc_salary RETURN EmpRecTyp IS
    SELECT employee_id, salary
    FROM employees
    ORDER BY salary DESC;

	FUNCTION sal_ok (
    jobid VARCHAR2,
    sal NUMBER
  ) RETURN BOOLEAN
  IS
    min_sal NUMBER;
    max_sal NUMBER;
  BEGIN
    SELECT min_salary, max_salary
    INTO min_sal, max_sal
    FROM jobs
    WHERE job_id = jobid;

    RETURN (sal >= min_sal) AND (sal <= max_sal);
  END sal_ok;
  
  PROCEDURE raise_salary (
    emp_id NUMBER,
    amount NUMBER
  )
  IS
    sal NUMBER(8,2);
    jobid VARCHAR2(10);
  BEGIN
    SELECT job_id, salary INTO jobid, sal
    FROM employees
    WHERE employee_id = emp_id;

    IF sal_ok(jobid, sal + amount) THEN  -- Invoke private function
      UPDATE employees
      SET salary = salary + amount
      WHERE employee_id = emp_id;
	  COMMIT;
    ELSE
      RAISE invalid_salary;
    END IF;
  EXCEPTION
    WHEN invalid_salary THEN
      DBMS_OUTPUT.PUT_LINE ('The salary is out of the specified range.');
  END raise_salary;

END emp_admin;
/

raise_salary是一个涨/减薪操作，其中emp_id是雇员ID，amount是增加的薪资。涨/减薪后的薪资不能超过了雇员对应工资的范围。
例如，雇员106对应的工资是程序员，其薪资范围为4000-10000：

SQL> select salary, job_id from employees where employee_id = 106;

    SALARY JOB_ID
---------- ----------
    5820.1 IT_PROG

SQL> select * from jobs where job_id='IT_PROG';

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
IT_PROG    Programmer                                4000      10000

SQL> set serveroutput on
SQL> exec emp_admin.raise_salary(106, 99999);
The salary is out of the specified range.

PL/SQL procedure successfully completed.

审计实验1：基本操作

创建并启用审计策略：

create user auditor_admin identified by Welcome1;
grant create session, audit_admin to auditor_admin;
connect auditor_admin/Welcome1@orclpdb1
create audit policy pol_sal_increase actions update on hr.employees;
AUDIT POLICY pol_sal_increase WHENEVER SUCCESSFUL;

修改表，以触发审计操作：

connect hr@orclpdb1
-- 修改前，雇员106的工资是4800
-- 其工种为IT_PROG，工资范围为4000-10000
exec emp_admin.raise_salary(106, 10);
-- 涨薪后，工资为4810
update employees set salary=salary*1.1 where employee_id = 106;
commit;
-- 手工修改后，工资为5291

查看审计记录：

-- 执行刷新操作，以防内存中的审计数据没有同步到表
EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

SQL> 
col action_name for a30
col object_name for a30
SELECT action_name, object_name, sql_text FROM   unified_audit_trail
WHERE  unified_audit_policies = 'POL_SAL_INCREASE';

ACTION_NAME                    OBJECT_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
UPDATE                         EMPLOYEES
UPDATE EMPLOYEES SET SALARY = SALARY + :B2 WHERE EMPLOYEE_ID = :B1

UPDATE                         EMPLOYEES
update employees set salary=salary*1.1 where employee_id = 106

禁止并删除审计策略。

NOAUDIT POLICY pol_sal_increase;
DROP AUDIT POLICY pol_sal_increase;

审计实验2：记录不成功的操作

上一个实验中，审计策略使用了WHENEVER SUCCESSFUL。

根据https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/AUDIT-Unified-Auditing.html，WHENEVER [NOT] SUCCESSFUL的含义为：

1. 指定 WHENEVER SUCCESSFUL 仅审计成功的 SQL 语句和操作。

2. 指定 WHENEVER NOT SUCCESSFUL 仅审计失败或导致错误的 SQL 语句和操作。

3. 如果省略此子句，则无论成功与否，Oracle 数据库都会执行审计。

3即我们想要的，我们将测试他会记录哪些不成功的语句。

策略定义如下，此时我们省略了WHENEVER SUCCESSFUL子句：

create audit policy pol_sal_increase actions update,delete,insert on hr.employees;

测试过程略，我们直接看结果：

SQL>
SELECT action_name, object_name, sql_text FROM   unified_audit_trail
WHERE  unified_audit_policies = 'POL_SAL_INCREASE' and event_timestamp > '18-SEP-25';

USERHOST             DBUSERNAME   CLIENT_PROGRAM_NAME                      ACTION_NAME     RETURN_CODE OBJECT_NAME   SQL_TEXT
oracle-19c-vagrant   HR           sqlplus@oracle-19c-vagrant (TNS V1-V3)   DELETE                    0 EMPLOYEES     delete from employees where employee_id = 206
LAPTOP-ECV73U39      HR           SQL Developer                            DELETE                  904 EMPLOYEES     delete from employees where emp_no=100
LAPTOP-ECV73U39      HR           SQL Developer                            DELETE                  904 EMPLOYEES     delete from employees where emp_no=100
LAPTOP-ECV73U39      HR           SQL Developer                            DELETE                 2292 EMPLOYEES     delete from employees where employee_id=100
LAPTOP-ECV73U39      HR           SQL Developer                            DELETE                 2292 EMPLOYEES     delete from employees where employee_id=101
oracle-19c-vagrant   HR           sqlplus@oracle-19c-vagrant (TNS V1-V3)   DELETE                12081 EMPLOYEES     delete from employees where employee_id = 206


6 rows selected.

在以上审计信息中，查看RETURN_CODE对应不同的测试语句：

-- RETURN_CODE = 0，执行成功
delete from employees where employee_id = 206

-- RETURN_CODE = 904，语法错误。表没有emp_no列
delete from employees where emp_no=100

-- RETURN_CODE = 2292，参照一致性错误错误。job_history表中有关联数据
delete from employees where employee_id=100

-- RETURN_CODE = 12801，表被设为只读，不允许写。
-- delete from employees where employee_id = 206

所以，这个不成功设计了语法错误（类似于compile time错误）以及runtime错误。

注意，审计不成功的语句，要防止恶意用户不断错误尝试，从而导致生成过多的日志。此时可以考虑EVALUATE PER STATEMENT|SESSION|INSTANCE子句。详见文档。

清理环境

SQL> connect auditor_admin/Welcome1@orclpdb1
Connected.

SQL> NOAUDIT POLICY pol_sal_increase;

Noaudit succeeded.

SQL> DROP AUDIT POLICY pol_sal_increase;

Audit dropped.

SQL> drop user auditor_admin;

User dropped.