{"id":5752,"date":"2024-10-07T07:00:00","date_gmt":"2024-10-07T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=5752"},"modified":"2024-10-06T22:27:59","modified_gmt":"2024-10-06T19:27:59","slug":"pos-energopoioyme-to-unified-auditing-se-oracle-database","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-energopoioyme-to-unified-auditing-se-oracle-database\/","title":{"rendered":"How we enable Unified Auditing in Oracle Database"},"content":{"rendered":"

In previous article<\/a> we had seen how to activate it Auditing<\/strong> in Oracle Database, in this article we will see how to enable Unified Auditing<\/strong> which allows us to put filters and so that we can record.<\/p>\n\n\n\n

Unlike classic Auditing, we can, if we want, exclude machines that we do not want to be recorded, either SESSION<\/code> either way STATEMENT <\/code>and by activating it, we can choose whether to only show those that have succeeded in their execution or not (WHENEVER SUCCESSFUL<\/code>). Accordingly, we can choose to record only specific users or only specific machines for this policy. We can also choose whether to record all statements or ONLY TOPLEVEL<\/code> like a procedure. <\/p>\n\n\n\n

<\/p>\n\n\n\n

How is it activated?<\/h2>\n\n\n\n

First you will need to stop the database and activate it with the following command Unified Auditing<\/strong>:<\/p>\n\n\n\n

sqlplus \/ as sysdba;\nshutdown immediate;\nexit;\n\ncd $ORACLE_HOME\/rdbms\/lib\nmake -f ins_rdbms.mk uniaud_on ioracle\n\nsqlplus \/ as sysdba;\nstartup;<\/pre>\n\n\n\n
In case we want to record actions with the administrator user as well SYS<\/code> we need to change the following parameter and restart:<\/p>\n\n\n\n
sqlplus \/ as sysdba;\nALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;\nshutdown immediate;\nstartup;<\/pre>\n\n\n\n
Then we clear the Unified Audit Trail with the following command:<\/p>\n\n\n\n
begin\n  dbms_audit_mgmt.flush_unified_audit_trail(\n    flush_type => dbms_audit_mgmt.flush_current_instance,\n    container  => dbms_audit_mgmt.container_all);\nend;\n\/<\/pre>\n\n\n\n
How do I store the audit trail files in a separate tablespace<\/h4>\n\n\n\n
If we want to use a different tablespace with other datafiles, we run the following command:<\/p>\n\n\n\n
create tablespace AUDIT_TS datafile '\/oracle\/oradata\/orcl\/audit_ts_001.dbf' size 10m autoextend on next 64m maxsize 32767m;\n\nBEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, audit_trail_location_value => 'AUDIT_TS');\nEND;\n\/<\/pre>\n\n\n\n
How we maintain Unified Auditing<\/h4>\n\n\n\n
If we want to create a job that will delete records older than 30 days automatically, we run the following:<\/p>\n\n\n\n
BEGIN\nDBMS_SCHEDULER.create_job (\njob_name => 'JOB_PURGE_AUDIT_RECORDS',\njob_type => 'PLSQL_BLOCK',\njob_action => 'BEGIN \nDBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TRUNC(SYSTIMESTAMP)-30); \nDBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(\naudit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,\nuse_last_arch_timestamp  =>  TRUE); \nEND;',\nstart_date => SYSTIMESTAMP,\nrepeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',\nend_date => NULL,\nenabled => TRUE,\ncomments => 'Update last_archive_timestamp');\nEND;\n\/<\/pre>\n\n\n\n
<\/p>\n\n\n\n
How we create Unified Audit Policies<\/h2>\n\n\n\n
<\/p>\n\n\n\n
Create Select Policy<\/h4>\n\n\n\n
With the following code we can create a policy that will record successful select statements in two tables that do not come from two specific machines:<\/p>\n\n\n\n
CREATE AUDIT POLICY stratos_selects\nACTIONS\nSELECT ON stratos.customers,\nSELECT ON stratos.products\nWHEN 'SYS_CONTEXT(''USERENV'', ''HOST'') NOT IN (''oracledev1'', ''oracledev2'')'\nEVALUATE PER SESSION;\n\naudit policy stratos_selects WHENEVER SUCCESSFUL;\n--noaudit policy stratos_selects ;\n--drop audit policy stratos_selects ;<\/pre>\n\n\n\n
Create a Login Monitoring Policy<\/h4>\n\n\n\n
With the following code we can create a policy that will record successful connections with two users who do not come from two specific machines:<\/p>\n\n\n\n
CREATE AUDIT POLICY monitor_connects\nACTIONS LOGON\nWHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') IN (''STRATOS'', ''HR'') and SYS_CONTEXT(''USERENV'', ''HOST'') NOT IN (''oracledev1'', ''oracledev2'')'\nEVALUATE PER SESSION;\n\naudit policy monitor_connects WHENEVER SUCCESSFUL;\n--noaudit policy monitor_connects;\n--drop audit policy monitor_connects;<\/pre>\n\n\n\n
Create DDL\/DML Policy<\/h4>\n\n\n\n
With the following code we can create a policy that will record successful DDL and DML actions that do not come from two specific machines:<\/p>\n\n\n\n
CREATE AUDIT POLICY stratos_changes\nACTIONS\nTRUNCATE TABLE,\nDROP TABLE,\nALTER TABLE,\nDROP VIEW,\nALTER VIEW,\nINSERT ON stratos.customers,\nUPDATE ON stratos.customers,\nDELETE ON stratos.customers,\nINSERT ON stratos.products,\nUPDATE ON stratos.products,\nDELETE ON stratos.products\nWHEN 'SYS_CONTEXT(''USERENV'', ''HOST'') NOT IN (''oracledev1'', ''oracledev2'')'\nWHENEVER SUCCESSFUL;\nEVALUATE PER SESSION;\n\naudit policy stratos_changes WHENEVER SUCCESSFUL;\n--noaudit policy stratos_changes;\n--drop audit policy stratos_changes;<\/pre>\n\n\n\n
If we want to add or remove an option from the recording, we can do so with the following:<\/p>\n\n\n\n
ALTER AUDIT POLICY stratos_changes\n  ADD ACTIONS DELETE ON stratos.logging\n  DROP ACTIONS DELETE ON stratos.housekeeping;<\/pre>\n\n\n\n
How do we view the Unified Audit Policies that exist?<\/h2>\n\n\n\n
With the following query we can see the Unified Audit Policies<\/strong> which we have created with the parameters we have set:<\/p>\n\n\n\n
SELECT * FROM AUDIT_UNIFIED_POLICIES\n--where policy_name='STRATOS_CHANGES'\n;<\/pre>\n\n\n\n
\"\"
1<\/figcaption><\/figure>\n\n\n\n
To see which policies are active, we run the following query:<\/p>\n\n\n\n
SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;<\/pre>\n\n\n\n
\"\"
2<\/figcaption><\/figure>\n\n\n\n
By default, the two standard policies are enabled, which we can disable with the following:<\/p>\n\n\n\n
noaudit policy ORA_SECURECONFIG;\nnoaudit policy ORA_LOGON_FAILURES;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
How do we view information from Unified Audit Policies?<\/h2>\n\n\n\n
To see the information that Auditing has recorded with the name of the machine, the OS user, the database user, the program and the exact statement that was run, run the following:<\/p>\n\n\n\n
SELECT \nevent_timestamp\n,OS_USERNAME\n,USERHOST\n,DBUSERNAME\n,client_program_name\n,action_name\n,object_schema\n,object_name\n,sql_text\n,sql_binds\n,unified_audit_policies \nFROM UNIFIED_AUDIT_TRAIL \nWHERE  unified_audit_policies is not null and UNIFIED_AUDIT_POLICIES not in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')\nORDER BY EVENT_TIMESTAMP DESC;<\/pre>\n\n\n\n
\"\"
3<\/figcaption><\/figure>\n\n\n\n
By running the following query we can see how many records have been recorded for each policy and each action:<\/p>\n\n\n\n
select audit_type,unified_audit_policies,action_name,return_code,count(*) \n   from unified_audit_trail where event_timestamp>sysdate-1\n   group by audit_type,unified_audit_policies,action_name,return_code\n   order by count(*);<\/pre>\n\n\n\n
\"\"
4<\/figcaption><\/figure>\n\n\n\n
To see the Unified Audit Trail configuration, run the following:<\/p>\n\n\n\n
select * from DBA_AUDIT_MGMT_CONFIG_PARAMS where AUDIT_TRAIL='UNIFIED AUDIT TRAIL';<\/pre>\n\n\n\n
\"\"
5<\/figcaption><\/figure>\n\n\n\n
Finally, with the following query we can see the space occupied by Auditing:<\/p>\n\n\n\n
select occupant_name,schema_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like 'AUD%';\n<\/pre>\n\n\n\n
\"\"
6<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n