概述
统一审计在默认情况下是在12c数据库中启用的，称为混合模式审计(它同时启用了传统审计和统一审计)。
混合模式旨在引入统一审计，这样就可以了解它是如何工作的以及它的细微差别和好处是什么。混合模式使您能够迁移现有的应用程序和脚本，以使用统一的审计。
一旦您决定使用纯统一审计，您就可以在打开统一审计选项的情况下重新链接oracle二进制文件，从而使其成为oracle数据库运行的唯一审计工具。
混合模式（传统+统一）
需要audit_trail参数配置为DB或DB,EXTEDNED
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL> select parameter,value from gv$option where parameter='Unified Auditing';
PARAMETER VALUE
------------------------------ ----------
Unified Auditing FALSE
Unified Auditing FALSE
SQL> select ENTITY_NAME,POLICY_NAME,ENTITY_TYPE,ENABLED_OPTION,SUCCESS,FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES order by ENTITY_NAME,POLICY_NAME;
ENTITY_NAME POLICY_NAME ENTITY_ ENABLED_OPTION SUC FAI
------------------------ ------------------------------ ------- --------------- --- ---
ALL USERS ORA_LOGON_FAILURES USER BY USER NO YES
ALL USERS ORA_SECURECONFIG USER BY USER YES YES
复制纯统一审计
需要关闭传统审计,rac每个节点都执行relink
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL>
$ srvctl stop database -d testdba
[oracle@rac01 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@rac01 lib]$ make -f ins_rdbms.mk uniaud_on ioracle
/usr/bin/ar d /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/kzaiang.o
chmod 755 /u01/app/oracle/product/19.0.0/db_1/bin
- Linking Oracle
rm -f /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/oracle
/u01/app/oracle/product/19.0.0/db_1/bin/orald -o /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/19.0.0/db_1/rdbms/lib/ -L/u01/app/oracle/product/19.0.0/db_1/lib/ -L/u01/app/oracle/product/19.0.0/db_1/lib/stubs/ -Wl,-E /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/opimai.o /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv19 -Wl,--no-whole-archive /u01/app/oracle/product/19.0.0/db_1/lib/nautab.o /u01/app/oracle/product/19.0.0/db_1/lib/naeet.o /u01/app/oracle/product/19.0.0/db_1/lib/naect.o /u01/app/oracle/product/19.0.0/db_1/lib/naedhs.o /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/config.o -ldmext -lserver19 -lodm19 -lofs -lcell19 -lnnet19 -lskgxp19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lclient19 -lvsnst19 -lcommon19 -lgeneric19 -lknlopt -loraolap19 -lskjcx19 -lslax19 -lpls19 -lrt -lplp19 -ldmext -lserver19 -lclient19 -lvsnst19 -lcommon19 -lgeneric19 `if [ -f /u01/app/oracle/product/19.0.0/db_1/lib/libavserver19.a ] ; then echo "-lavserver19" ; else echo "-lavstub19"; fi` `if [ -f /u01/app/oracle/product/19.0.0/db_1/lib/libavclient19.a ] ; then echo "-lavclient19" ; fi` -lknlopt -lslax19 -lpls19 -lrt -lplp19 -ljavavm19 -lserver19 -lwwg `cat /u01/app/oracle/product/19.0.0/db_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/19.0.0/db_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lmm -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lztkg19 `cat /u01/app/oracle/product/19.0.0/db_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/19.0.0/db_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 `if /usr/bin/ar tv /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo19 -lserver19"; fi` -L/u01/app/oracle/product/19.0.0/db_1/ctx/lib/ -lctxc19 -lctx19 -lzx19 -lgx19 -lctx19 -lzx19 -lgx19 -lclscest19 -loevm -lclsra19 -ldbcfg19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -locr19 -locrb19 -locrutl19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -lgeneric19 -lorazip -loraz -llzopro5 -lorabz2 -lorazstd -loralz4 -lipp_z -lipp_bz2 -lippdc -lipps -lippcore -lippcp -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lsnls19 -lunls19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lasmclnt19 -lcommon19 -lcore19 -ledtn19 -laio -lons -lmql1 -lipc1 -lfthread19 `cat /u01/app/oracle/product/19.0.0/db_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/19.0.0/db_1/lib -lm `cat /u01/app/oracle/product/19.0.0/db_1/lib/sysliblist` -ldl -lm -L/u01/app/oracle/product/19.0.0/db_1/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
rm -f /u01/app/oracle/product/19.0.0/db_1/bin/oracle
mv /u01/app/oracle/product/19.0.0/db_1/rdbms/lib/oracle /u01/app/oracle/product/19.0.0/db_1/bin/oracle
chmod 6751 /u01/app/oracle/product/19.0.0/db_1/bin/oracle
(if [ ! -f /u01/app/oracle/product/19.0.0/db_1/bin/crsd.bin ]; then \
getcrshome="/u01/app/oracle/product/19.0.0/db_1/srvm/admin/getcrshome" ; \
if [ -f "$getcrshome" ]; then \
crshome="`$getcrshome`"; \
if [ -n "$crshome" ]; then \
if [ $crshome != /u01/app/oracle/product/19.0.0/db_1 ]; then \
oracle="/u01/app/oracle/product/19.0.0/db_1/bin/oracle"; \
$crshome/bin/setasmgidwrap oracle_binary_path=$oracle; \
fi \
fi \
fi \
fi\
);
[oracle@rac01 lib]$
$ srvctl start database -d testdba
SQL> select * from gv$option where parameter like 'Unified Auditing';
INST_ID PARAMETER VALUE CON_ID
---------- ------------------------------ ------------------------------ ----------
1 Unified Auditing TRUE 0
2 Unified Auditing TRUE 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBOD1A READ WRITE NO
SQL> alter session set container=PDBOD1A;
Session altered.
SQL> select * from gv$option where parameter like 'Unified Auditing';
INST_ID PARAMETER VALUE CON_ID
---------- ------------------------------ ------------------------------ ----------
1 Unified Auditing TRUE 0
2 Unified Auditing TRUE 0
SQL>
--默认的策略
SQL> SELECT * FROM AUDIT_UNIFIED_POLICY_COMMENTS;
POLICY_NAME COMMENTS
------------------------------ ----------------------------------------------------------------------------------------------------
ORA_LOGON_FAILURES Audit policy containing audit options to capture logon failures
ORA_SECURECONFIG Audit policy containing audit options as per database security best practices
ORA_CIS_RECOMMENDATIONS Audit policy containing audit options as per CIS recommendations
ORA_ACCOUNT_MGMT Audit policy containing audit options for auditing account management actions
ORA_DATABASE_PARAMETER Audit policy containing audit options to audit changes in database parameters
--默认启用的策略
SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES;
POLICY_NAME ENABLED_OPTION ENTITY_NAME ENTITY_ SUC FAI
------------------------------ --------------- ------------------------------ ------- --- ---
ORA_SECURECONFIG BY USER ALL USERS USER YES YES
ORA_LOGON_FAILURES BY USER ALL USERS USER NO YES
复制其他sql
--默认策略的审计选项查看
select * from AUDIT_UNIFIED_POLICIES where policy_name in ('ORA_LOGON_FAILURES','ORA_SECURECONFIG') ORDER BY policy_name;
--默认审计的配置
SQL> select * from dba_audit_mgmt_config_params ;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
---------------------------------------- ---------------------------------------- ----------------------------
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL
14 rows selected.
https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/administering-the-audit-trail.html#GUID-1DD625ED-AC75-47E7-ADF6-1C7C93656F22
在Oracle Database 12c release 1(12.1)中，您可以选择在内存中对审计记录进行排队(queued-write mode)，并定期将其写入到AUDSYS模式审计表中。
然而，从Oracle Database 12c release 2(12.2)开始，立即写入模式和排队写入模式已被弃用。
控制它们的参数(UNIFIED_AUDIT_SGA_QUEUE_SIZE, DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE, and DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE)，虽然仍然可见，但不再具有任何功能。
将审计记录始终写入AUDSYS模式中的关系表的新功能可以防止在实例崩溃或SHUTDOWN ABORT操作期间审计记录丢失的风险。新功能还提高了审计跟踪和整个数据库的性能。
--查看策略创建DDL
SQL> select dbms_metadata.get_ddl('AUDIT_POLICY','ORA_LOGON_FAILURES') from dual;
DBMS_METADATA.GET_DDL('AUDIT_POLICY','ORA_LOGON_FAILURES')
--------------------------------------------------------------------------------
CREATE AUDIT POLICY "ORA_LOGON_FAILURES" ACTIONS LOGON
-- 查看支持的审计动作
SELECT * FROM auditable_system_actions
复制审计登录登出（不论是否成功）
SQL> CREATE AUDIT POLICY ORA_LOGIN_LOGOUT ACTIONS LOGON,LOGOFF;
Audit policy created.
SQL>
SQL> AUDIT POLICY ORA_LOGIN_LOGOUT by dhw;
Audit succeeded.
SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES;
POLICY_NAME ENABLED_OPTION ENTITY_NAME ENTITY_ SUC FAI
------------------------------ --------------- ------------------------------ ------- --- ---
ORA_LOGIN_LOGOUT BY USER DHW USER YES YES
ORA_SECURECONFIG BY USER ALL USERS USER YES YES
ORA_LOGON_FAILURES BY USER ALL USERS USER NO YES
SQL>
--测试
SQL> conn dhw/dhw@192.168.31.206/PDBOD1A
Connected.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@rac02 lib]$
--检查sql
select instance_id,
SESSIONID,
OS_USERNAME,
USERHOST,
TERMINAL,
DBUSERNAME,
CLIENT_PROGRAM_NAME,
ACTION_NAME,
OBJECT_SCHEMA,
OBJECT_NAME,
SQL_TEXT,
--SQL_BINDS,
unified_audit_policies,
return_code,
to_char(EVENT_TIMESTAMP, 'yyyymmdd hh24:mi:ss') EVENT_TIMESTAMP
from UNIFIED_AUDIT_TRAIL where EVENT_TIMESTAMP >sysdate-10/24 order by EVENT_TIMESTAMP desc
复制
可根据条件进行审计
参考文档如下：
https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-audit-policies.html#GUID-A107C42E-94EF-4E11-AC65-D7334FC153AB
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-AUDIT-POLICY-Unified-Auditing.html#GUID-8D6961FB-2E50-46F5-81F7-9AEA314FC693
CREATE AUDIT POLICY logon_pol
ACTIONS LOGON
WHEN 'INSTR(UPPER(SYS_CONTEXT(''USERENV'', ''CLIENT_PROGRAM_NAME'')), ''SQLPLUS'') > 0'
EVALUATE PER SESSION;
AUDIT POLICY logon_pol BY USERS WITH GRANTED ROLES emp_admin, sales_admin;
复制审计DML操作
SQL> create audit policy test_audit actions insert,delete,update;
Audit policy created.
SQL> audit policy test_audit by test;
Audit succeeded.
SQL> select * from audit_unified_enabled_policies;
POLICY_NAME ENABLED_OPTION ENTITY_NAME ENTITY_TYPE SUCCES FAILUR
-------------------- ------------------------------ -------------------- -------------- ------ ------
TEST_AUDIT BY USER TEST USER YES YES
SQL>
复制最后修改时间：2024-01-10 14:12:05
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
