19C中用户行为审计功能

审计是对行为的跟踪记录，对于核心表，我们希望记录该表的DML操作信息，这样再出现问题比如误删除数据，更新失误以及性能问题时回溯历史
SQL。下面我们测试和模拟19C中的用户行为审计，这些SQL可以是用户直接发出的，也可以是是通过调用存储过程或者函数实现，具体审计
哪个行为用户可以自己定义。

1 执行脚本创建测试表和存储过程。
cat create_proc.sql
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 employees
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;
/

查询创建的对象
SQL> select object_name from user_objects where object_type='PACKAGE';

OBJECT_NAME
--------------------------------------------------------------------------------
EMP_ADMIN
SQL> desc EMP_ADMIN;
PROCEDURE RAISE_SALARY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
EMP_ID NUMBER IN
AMOUNT NUMBER IN


下面创建审计策略，来审计表中工资字段的变化，比如增长
SQL> CREATE AUDIT POLICY pol_sal_increase ACTIONS UPDATE ON hr.employees;

Audit policy created.
开启审计
SQL> AUDIT POLICY pol_sal_increase WHENEVER SUCCESSFUL;

Audit succeeded.

下面跟新表的工资字段值，增加10元。这里
SQL> select employee_id ,salary from employees where employee_id=106;

EMPLOYEE_ID SALARY
----------- ----------
106 4800

SQL> EXEC emp_admin.raise_salary(106,10)

PL/SQL procedure successfully completed.

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

EMPLOYEE_ID SALARY
----------- ----------
106 4810

这里是通过存储过程实现，下面我们通过用户直接发出的SQL实现工资字段的变化
[oracle@rac2 trace]$ sqlplus hr/oracle@rac1:1521/prod

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 24 10:03:26 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Last Successful login time: Fri Sep 24 2021 09:58:06 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user;
USER is "HR"
SQL> UPDATE hr.employees SET salary=salary*0.1
WHERE employee_id = 106;

1 row updated.

SQL> commit;

Commit complete.

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

EMPLOYEE_ID SALARY
----------- ----------
106 481
这次再之前增加10元的基础上，变成之前的0.1倍。（4810元--->481元）

下面查询审计结果
SQL> col action_name for a30
SQL> col object_name for a20
SQL> col sql_text for a60
SQL> set line 200
SQL> col terminal for a10
SQL> col sql_binds for a10
SQL> select terminal,action_name, object_name, sql_text,sql_binds FROM unified_audit_trail WHERE unified_audit_policies = 'POL_SAL_INCREASE'

TERMINAL ACTION_NAME OBJECT_NAME SQL_TEXT SQL_BINDS
---------- ------------------------------ -------------------- ----------------------------------------------------------------------------------------- --------------
pts/1 UPDATE EMPLOYEES UPDATE hr.employees SET salary=salary*0.1
WHERE employee_id = 106
pts/0 UPDATE EMPLOYEES UPDATE EMPLOYEES SET SALARY = SALARY + :B2 WHERE EMPLOYEE_ID #1(2):10
= :B1 #2(3):106


disable审计
NOAUDIT POLICY pol_sal_increase;
删除审计策略
DROP AUDIT POLICY pol_sal_increase;

下面分析对象
SQL> col owner for a20
SQL> col object_type for a20
select owner, object_type,object_name from dba_objects where object_name=upper('unified_audit_trail')

OWNER OBJECT_TYPE OBJECT_NAME
-------------------- -------------------- --------------------
PUBLIC SYNONYM UNIFIED_AUDIT_TRAIL
AUDSYS VIEW UNIFIED_AUDIT_TRAIL


CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "AUDSYS"."UNIFIED_AUDIT_TRAIL" (
...)
as select ...
from sys.gv_$unified_audit_trail uview, sys.all_unified_audit_actions act,
sys.system_privilege_map spx, sys.stmt_audit_option_map aom
where ...
union all
select ...
from audsys.aud$unified auduni, sys.all_unified_audit_actions act1,
sys.system_privilege_map spx1, sys.stmt_audit_option_map aom1
where...

SQL> l
1* select object_type ,object_name from dba_objects where object_name in (upper('all_unified_audit_actions'), upper('system_privilege_map'),upper('stmt_audit_option_map'))
SQL> /

OBJECT_TYPE OBJECT_NAME
-------------------- ------------------------------
TABLE SYSTEM_PRIVILEGE_MAP
TABLE STMT_AUDIT_OPTION_MAP
TABLE ALL_UNIFIED_AUDIT_ACTIONS
SYNONYM SYSTEM_PRIVILEGE_MAP
SYNONYM STMT_AUDIT_OPTION_MAP

SQL> select tablespace_name from dba_tables where table_name='SYSTEM_PRIVILEGE_MAP';

TABLESPACE_NAME
------------------------------------------------------------
SYSTEM

SQL> select tablespace_name from dba_tables where table_name='STMT_AUDIT_OPTION_MAP';

TABLESPACE_NAME
------------------------------------------------------------
SYSTEM

SQL> select tablespace_name from dba_tables where table_name='STMT_AUDIT_OPTION_MAP';

TABLESPACE_NAME
------------------------------------------------------------
SYSTEM

经过查询发现与审计相关的数据会放在系统表空间，所以一旦开启审计就要注意监控System表空间的使用率，及时扩容
或者根据业务需求及时关闭审计。