关于19C的审计日志

           最近在用19C做测试时，发现了个问题，即在audit_trail  为DB的情况下，模拟用户登录登出过程，但是 aud$ 表里没记录。

 查阅资料得知:

12.1.0.1 开始情况使用的 统一审计（Unified Auditing），默认开启的ORA_SECURECONFIG审计策略不包含对所有用户的 LOGON 和 LOGOFF 审计，而是通过ORA_LOGON_FAILURES审计策略仅审计logon失败的情况。

 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

如果我们希望回到11g时默认对非sys用户的所有logon和logoff都审计应该如何设置？

有MOS文章【How To Audit Of User Login & Logoff of Database by Unified Auditing (Doc ID 2509211.1)】介绍如下：

1.可以针对某个特定用户TEST01进行logon logoff审计

create audit policy connection_policy actions logon,logoff WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''TEST01''' evaluate per session;

audit policy connection_policy;

2.或针对所有应用用户进行logon logoff审计

create audit policy  connection_policy actions logon,logoff  WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') not in (''SYS'',''ORAMON'',''PATROL'')   ' evaluate per session;

audit policy  connection_policy;

3.或针对所有用户进行logon logoff审计

 create audit policy all_connection_policy actions logon,logoff  ;

 audit policy  all_connection_policy;

-- 查询logon logoff记录

select to_char(event_timestamp, 'YYYYMMDD HH24:MI:SS.FF3') time, action_name,dbusername

from unified_audit_trail where dbusername='TEST01' order by 1;

-- 取消审计策略

noaudit policy connection_policy;

drop audit policy connection_policy;

--查询当前生效的统一审计策略

 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

ALL_CONNECTION_POLICY          BY USER         ALL USERS                      USER    YES YES

开启统一审计后历史日志怎么清理：

现在19C工艺中未配置CONNECTION_POLICY的情况下，现在的backup_aud.sh 脚本对aud$的清理也就也就无效了。

12C开始的统一审计（ Unified Auditing ）的审计数据都是存放在 AUDSYS schema  下

  set pagesize 200

  set linesize 200

  col OWNER format a10

  col SEGMENT_NAME format a25

  col SEGMENT_TYPE format a20

  col PARTITION_NAME format a20

  select OWNER,SEGMENT_NAME,SEGMENT_TYPE,PARTITION_NAME,bytes/1024/1024 "sizeMB"

   from DBA_SEGMENTS

   where OWNER='AUDSYS';

OWNER      SEGMENT_NAME              SEGMENT_TYPE         PARTITION_NAME             sizeMB

---------- ------------------------- -------------------- -------------------- ------------

AUDSYS     AUD$UNIFIED               TABLE PARTITION      SYS_P281                    .0625

AUDSYS     AUD$UNIFIED               TABLE PARTITION      SYS_P181                     .125

AUDSYS     SYS_IL0000018570C00097$$  INDEX PARTITION      SYS_IL_P287                 .0625

AUDSYS     SYS_IL0000018570C00031$$  INDEX PARTITION      SYS_IL_P285                 .0625

AUDSYS     SYS_IL0000018570C00030$$  INDEX PARTITION      SYS_IL_P283                 .0625

AUDSYS     SYS_IL0000018570C00097$$  INDEX PARTITION      SYS_IL_P187                 .0625

AUDSYS     SYS_IL0000018570C00031$$  INDEX PARTITION      SYS_IL_P185                 .0625

AUDSYS     SYS_IL0000018570C00030$$  INDEX PARTITION      SYS_IL_P183                 .0625

AUDSYS     SYS_LOB0000018570C00030$$ LOB PARTITION        SYS_LOB_P282                 .125

AUDSYS     SYS_LOB0000018570C00030$$ LOB PARTITION        SYS_LOB_P182                 .125

AUDSYS     SYS_LOB0000018570C00031$$ LOB PARTITION        SYS_LOB_P184                 .125

AUDSYS     SYS_LOB0000018570C00097$$ LOB PARTITION        SYS_LOB_P186                 .125

AUDSYS     SYS_LOB0000018570C00031$$ LOB PARTITION        SYS_LOB_P284                 .125

AUDSYS     SYS_LOB0000018570C00097$$ LOB PARTITION        SYS_LOB_P286                 .125

如果后面启用自定义的CONNECTION_POLICY后，则backup_aud.sh 脚本需要调整定期备份清理 AUDSYS.AUD$UNIFIED 表了。