Hi,
I am trying to track user/admin activities via either legacy logging or unified logging. I followed official documentations, read bunch of posts. But it seems I am missing something that you guys might see.
I use Oracledb 23 AI version which is free. So far I activated almost any useful audit logging policy but I only get mandatory logging activities saved to an XML file on my path. I am using dockerized Oracle solution as it is much easier to build.
Here is some output from my db;
audit config(I tried both legacy off + unified on / legacy on options. Result is the same, mandatory log dumps on the audit_file_dest)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/oracle/admin/FREE/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string XML
unified_audit_common_systemlog string
unified_audit_systemlog string USER.DEBUG
unified_audit_trail_exclude_columns string NONE
above right now I am dumping XML.
I also added/enabled some policies, forexample:
CREATE AUDIT POLICY ORA_SECURECONFIG4
PRIVILEGES ALTER ANY TABLE, CREATE ANY TABLE, DROP ANY TABLE,
CREATE ANY PROCEDURE, DROP ANY PROCEDURE, ALTER ANY PROCEDURE,
GRANT ANY PRIVILEGE, GRANT ANY OBJECT PRIVILEGE, GRANT ANY ROLE,
AUDIT SYSTEM, CREATE EXTERNAL JOB, CREATE ANY JOB,
CREATE ANY LIBRARY,
EXEMPT ACCESS POLICY,
CREATE USER, DROP USER,
ALTER DATABASE, ALTER SYSTEM,
CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM,
CREATE SQL TRANSLATION PROFILE, CREATE ANY SQL TRANSLATION PROFILE,
DROP ANY SQL TRANSLATION PROFILE, ALTER ANY SQL TRANSLATION PROFILE,
TRANSLATE ANY SQL,
EXEMPT REDACTION POLICY,
PURGE DBA_RECYCLEBIN, LOGMINING,
ADMINISTER KEY MANAGEMENT, BECOME USER
ACTIONS ALTER USER, CREATE ROLE, ALTER ROLE, DROP ROLE,
SET ROLE, CREATE PROFILE, ALTER PROFILE,
DROP PROFILE, CREATE DATABASE LINK,
ALTER DATABASE LINK, DROP DATABASE LINK,
CREATE DIRECTORY, DROP DIRECTORY,
CREATE PLUGGABLE DATABASE,
DROP PLUGGABLE DATABASE,
ALTER PLUGGABLE DATABASE,
EXECUTE ON DBMS_RLS,
ALTER DATABASE DICTIONARY;
However all I get is mandatory logs like this and some internal logging.
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>9</StatementId><EntryId>7</EntryId><Extended_Timestamp>2024-12-09T06:48:23.496361Z</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>oracle</Ext_Name><OS_User>oracle</OS_User><Userhost>e17efe527fc2</Userhost><OS_Process>163</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><Scn>0</Scn><OSPrivilege>SYSDBA</OSPrivilege><DBID>1458252082</DBID><Current_User>SYS</Current_User>
<Sql_Text>ALTER DATABASE OPEN</Sql_Text>
</AuditRecord>
Oh, I also enabled these policies and many more that comes with the oracle. Then restarted the db, so these policies are in effect.
I am by no means a db engineer, I am just interested with logging and auditing. I can not figure out why I cant see more enriched logging, I would appreciate some help. Thanks !
PS: I can provide more information.
Link for some policies: https://docs.oracle.com/en/database/oracle/audit-vault-database-firewall/20/sigau/audit_policies.html#GUID-71378A3F-8C2C-4390-9167-F38415A099E1
https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-audit-policies.html#GUID-B1E2C21B-8D41-427B-98BF-B5DEEE44AAA1
My logging directory(aud is from unified logging)
bash-4.4$ ls
27F11574D36209A9E063020011ACFACB FREE_m004_418_20241128111416773195476655.aud FREE_ora_158_20241128123034496128191925.aud FREE_ora_159_20241204114308523832722079.xml FREE_ora_163_20241209064819735343730062.xml FREE_ora_1953_20241128121039584175130902.aud FREE_ora_457_20241204114354398445928431.xml
FREE_j001_426_20241204111739653318352877.aud FREE_ora_1103_20241204123006820102613407.xml FREE_ora_159_20241128122624381440807650.aud FREE_ora_159_20241204121513721404348090.xml FREE_ora_165_20241128110918095073367304.aud FREE_ora_3296_20241128121512587942565323.aud FREE_ora_534_20241128111703752955106295.aud
FREE_m004_402_20241204114910077752304064.xml FREE_ora_157_20241204111241420282930432.aud FREE_ora_159_20241204114035034505500417.aud FREE_ora_159_20241204123028777064134581.xml FREE_ora_1860_20241204121510106362730486.xml FREE_ora_444_20241204123039173898732935.xml FREE_ora_588_20241204121902503310399164.xml