Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted March 13, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Unified Audit Trail in Oracle 12c

By David Fitzjarrell

In Oracle's most recent release, the audit options have been expanded and modified to create the Unified Audit Trail, a 'one-stop shop' for recording and reporting audit information. A new view, UNIFIED_AUDIT_TRAIL, provides access to the audit details:


 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- ----------------------------
 AUDIT_TYPE                                                                                                     VARCHAR2(64)
 SESSIONID                                                                                                      NUMBER
 PROXY_SESSIONID                                                                                                NUMBER
 OS_USERNAME                                                                                                    VARCHAR2(30)
 USERHOST                                                                                                       VARCHAR2(128)
 TERMINAL                                                                                                       VARCHAR2(30)
 INSTANCE_ID                                                                                                    NUMBER
 DBID                                                                                                           NUMBER
 AUTHENTICATION_TYPE                                                                                            VARCHAR2(1024)
 DBUSERNAME                                                                                                     VARCHAR2(30)
 DBPROXY_USERNAME                                                                                               VARCHAR2(30)
 EXTERNAL_USERID                                                                                                VARCHAR2(1024)
 GLOBAL_USERID                                                                                                  VARCHAR2(32)
 CLIENT_PROGRAM_NAME                                                                                            VARCHAR2(48)
 DBLINK_INFO                                                                                                    VARCHAR2(4000)
 XS_USER_NAME                                                                                                   VARCHAR2(128)
 XS_SESSIONID                                                                                                   RAW(33)
 ENTRY_ID                                                                                                       NUMBER
 STATEMENT_ID                                                                                                   NUMBER
 EVENT_TIMESTAMP                                                                                                TIMESTAMP(6) WITH LOCAL TIME ZONE
 ACTION_NAME                                                                                                    VARCHAR2(64)
 RETURN_CODE                                                                                                    NUMBER
 OS_PROCESS                                                                                                     VARCHAR2(16)
 TRANSACTION_ID                                                                                                 RAW(8)
 SCN                                                                                                            NUMBER
 EXECUTION_ID                                                                                                   VARCHAR2(64)
 OBJECT_SCHEMA                                                                                                  VARCHAR2(30)
 OBJECT_NAME                                                                                                    VARCHAR2(128)
 SQL_TEXT                                                                                                       CLOB
 SQL_BINDS                                                                                                      CLOB
 APPLICATION_CONTEXTS                                                                                           VARCHAR2(4000)
 CLIENT_IDENTIFIER                                                                                              VARCHAR2(64)
 NEW_SCHEMA                                                                                                     VARCHAR2(30)
 NEW_NAME                                                                                                       VARCHAR2(128)
 OBJECT_EDITION                                                                                                 VARCHAR2(30)
 SYSTEM_PRIVILEGE_USED                                                                                          VARCHAR2(1024)
 SYSTEM_PRIVILEGE                                                                                               VARCHAR2(40)
 AUDIT_OPTION                                                                                                   VARCHAR2(40)
 OBJECT_PRIVILEGES                                                                                              VARCHAR2(19)
 ROLE                                                                                                           VARCHAR2(30)
 TARGET_USER                                                                                                    VARCHAR2(30)
 EXCLUDED_USER                                                                                                  VARCHAR2(30)
 EXCLUDED_SCHEMA                                                                                                VARCHAR2(30)
 EXCLUDED_OBJECT                                                                                                VARCHAR2(128)
 ADDITIONAL_INFO                                                                                                VARCHAR2(4000)
 UNIFIED_AUDIT_POLICIES                                                                                         VARCHAR2(4000)
 FGA_POLICY_NAME                                                                                                VARCHAR2(30)
 XS_INACTIVITY_TIMEOUT                                                                                          NUMBER
 XS_ENTITY_TYPE                                                                                                 VARCHAR2(32)
 XS_TARGET_PRINCIPAL_NAME                                                                                       VARCHAR2(30)
 XS_PROXY_USER_NAME                                                                                             VARCHAR2(30)
 XS_DATASEC_POLICY_NAME                                                                                         VARCHAR2(30)
 XS_SCHEMA_NAME                                                                                                 VARCHAR2(30)
 XS_CALLBACK_EVENT_TYPE                                                                                         VARCHAR2(32)
 XS_PACKAGE_NAME                                                                                                VARCHAR2(30)
 XS_PROCEDURE_NAME                                                                                              VARCHAR2(30)
 XS_ENABLED_ROLE                                                                                                VARCHAR2(30)
 XS_COOKIE                                                                                                      VARCHAR2(1024)
 XS_NS_NAME                                                                                                     VARCHAR2(30)
 XS_NS_ATTRIBUTE                                                                                                VARCHAR2(4000)
 XS_NS_ATTRIBUTE_OLD_VAL                                                                                        VARCHAR2(4000)
 XS_NS_ATTRIBUTE_NEW_VAL                                                                                        VARCHAR2(4000)
 DV_ACTION_CODE                                                                                                 NUMBER
 DV_ACTION_NAME                                                                                                 VARCHAR2(30)
 DV_EXTENDED_ACTION_CODE                                                                                        NUMBER
 DV_GRANTEE                                                                                                     VARCHAR2(30)
 DV_RETURN_CODE                                                                                                 NUMBER
 DV_ACTION_OBJECT_NAME                                                                                          VARCHAR2(128)
 DV_RULE_SET_NAME                                                                                               VARCHAR2(90)
 DV_COMMENT                                                                                                     VARCHAR2(4000)
 DV_FACTOR_CONTEXT                                                                                              VARCHAR2(4000)
 DV_OBJECT_STATUS                                                                                               VARCHAR2(1)
 OLS_POLICY_NAME                                                                                                VARCHAR2(30)
 OLS_GRANTEE                                                                                                    VARCHAR2(30)
 OLS_MAX_READ_LABEL                                                                                             VARCHAR2(4000)
 OLS_MAX_WRITE_LABEL                                                                                            VARCHAR2(4000)
 OLS_MIN_WRITE_LABEL                                                                                            VARCHAR2(4000)
 OLS_PRIVILEGES_GRANTED                                                                                         VARCHAR2(30)
 OLS_PROGRAM_UNIT_NAME                                                                                          VARCHAR2(30)
 OLS_PRIVILEGES_USED                                                                                            VARCHAR2(128)
 OLS_STRING_LABEL                                                                                               VARCHAR2(4000)
 OLS_LABEL_COMPONENT_TYPE                                                                                       VARCHAR2(12)
 OLS_LABEL_COMPONENT_NAME                                                                                       VARCHAR2(30)
 OLS_PARENT_GROUP_NAME                                                                                          VARCHAR2(30)
 OLS_OLD_VALUE                                                                                                  VARCHAR2(4000)
 OLS_NEW_VALUE                                                                                                  VARCHAR2(4000)
 RMAN_SESSION_RECID                                                                                             NUMBER
 RMAN_SESSION_STAMP                                                                                             NUMBER
 RMAN_OPERATION                                                                                                 VARCHAR2(20)
 RMAN_OBJECT_TYPE                                                                                               VARCHAR2(20)
 RMAN_DEVICE_TYPE                                                                                               VARCHAR2(5)
 DP_TEXT_PARAMETERS1                                                                                            VARCHAR2(512)
 DP_BOOLEAN_PARAMETERS1                                                                                         VARCHAR2(512)
 DIRECT_PATH_NUM_COLUMNS_LOADED                                                                                 NUMBER

A wealth of information is supplied, at the database and O/S level, which makes auditing tasks much easier as only one view needs to be accessed to generate an audit report. To get started the 'Unified Auditing' option must be enabled:


SQL> --
SQL> -- Verify unified auditing option is enabled
SQL> --
SQL> select value from v$option where parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

SQL>

If that value is FALSE some basic features of Unified Auditing will work but the full functionality will be disabled. To enable Unified Auditing the Oracle kernel will need to be re-linked to include Unified Auditing:


cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

Unified Auditing requires that an auditing policy be created against the table or tables that need to be audited. In the example below one table is created and selected for audit:


SQL>
SQL> --
SQL> -- Create a table to test the unified audit trail
SQL> --
SQL> create table audit_tst_tbl
  2  as select * from dba_objects
  3  where rownum < 2;

Table created.

SQL>
SQL> --
SQL> -- Create an audit policy for the table
SQL> --
SQL> CREATE AUDIT POLICY my_audit_policy
  2  	ACTIONS INSERT ON audit_tst_tbl,
  3  	       UPDATE ON  audit_tst_tbl;

Audit policy created.

SQL>

The policy, once created, needs to be enabled using the AUDIT statement:


SQL>
SQL> --
SQL> -- Enable the policy
SQL> --
SQL> AUDIT POLICY my_audit_policy;

Audit succeeded.

SQL>

It's a good idea to verify that the policy is enabled and contains the desired actions:


SQL>
SQL> --
SQL> -- Now verify that the policy is enabled
SQL> --
SQL> set line 180
SQL> col user_name format a20
SQL> col policy_name format a20
SQL> select * from audit_unified_enabled_policies
  2  where policy_name = 'MY_AUDIT_POLICY';

USER_NAME            POLICY_NAME          ENABLED_ SUC FAI
-------------------- -------------------- -------- --- ---
ALL USERS            MY_AUDIT_POLICY      BY       YES YES

SQL>
SQL> --
SQL> -- And verify that the policy has update and insert
SQL> --
SQL> set line 180
SQL> col audit_option format a20
SQL> col object_name format a20
SQL> select policy_name, audit_option, object_name, object_type
  2  from   AUDIT_UNIFIED_POLICIES
  3  where    policy_name in ('MY_AUDIT_POLICY');

POLICY_NAME          AUDIT_OPTION         OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------- -------------------- -----------------------
MY_AUDIT_POLICY      INSERT               AUDIT_TST_TBL        TABLE
MY_AUDIT_POLICY      UPDATE               AUDIT_TST_TBL        TABLE

SQL>

The first act, after configuring and enabling the policy, is to test it. This, however, can provide some confusing results when run from the session that created and enabled the policy; in this case inserts and updates can be performed with absolutely no record generated in the Unified Audit Trail:


SQL>
SQL> --
SQL> -- Insert some data
SQL> --
SQL> insert into audit_tst_tbl
  2  (select * from dba_objects where rownum < 10);

9 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Update data
SQL> --
SQL> update audit_tst_tbl
  2  set object_id = object_id+1000;

10 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Flush the trail to get any data out
SQL> --
SQL> EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Check the trail for entries
SQL> --
SQL> -- This fails from the session that created the policy
SQL> --
SQL> -- It succeeds when executed from a new session
SQL> --
SQL> col system_privilege_used format a20
SQL> col object_name format a20
SQL> col action_name format a20
SQL> col event_timestamp format a30
SQL>
SQL> select event_timestamp, action_name, system_privilege_used, object_name
  2  from unified_audit_trail
  3  where (object_name = 'MY_AUDIT_POLICY'
  4  or object_name = 'AUDIT_TST_TBL'  )
  5  and action_name in ('INSERT','UPDATE')
  6  order by event_timestamp;

no rows selected

SQL>

Once the policy is created and enabled every new session that connects to the database will generate audit records if inserts, updates, or both are performed on the table configured for auditing:


SQL>
SQL> --
SQL> -- Create a new session
SQL> --
SQL>
SQL> connect bing/##############
Connected.
SQL>
SQL> --
SQL> -- Insert some data
SQL> --
SQL> insert into audit_tst_tbl
  2  (select * from dba_objects where rownum < 10);

9 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Update data
SQL> --
SQL> update audit_tst_tbl
  2  set object_id = object_id+1000;

19 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Flush the trail to get any data out
SQL> --
SQL> EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Check the trail for entries
SQL> --
SQL> col system_privilege_used format a20
SQL> col object_name format a20
SQL> col action_name format a20
SQL> col event_timestamp format a30
SQL>
SQL> select event_timestamp, action_name, system_privilege_used, object_name
  2  from unified_audit_trail
  3  where (object_name = 'MY_AUDIT_POLICY'
  4  or object_name = 'AUDIT_TST_TBL'  )
  5  and action_name in ('INSERT','UPDATE')
  6  order by event_timestamp;

EVENT_TIMESTAMP                ACTION_NAME          SYSTEM_PRIVILEGE_USE OBJECT_NAME
------------------------------ -------------------- -------------------- --------------------
02-MAR-17 10.48.25.687388 AM   INSERT                                    AUDIT_TST_TBL
02-MAR-17 10.48.25.689887 AM   UPDATE                                    AUDIT_TST_TBL

SQL>

Other records will be generated, including records for enabling the policy, but the session that creates the audit won't be audited for insert and update operations.

The audit trail can grow large and Oracle has provided a procedure to purge the Unified Audit Trail that can be run manually or scripted to run through DBMS_SCHEDULER or through the operating system scheduler. An example of running it manually in a non-container 12c database is shown below:


SQL> BEGIN
  2    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
  3     AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  4     USE_LAST_ARCH_TIMESTAMP    =>  FALSE
  5    );
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

It can also process the Unified Audit Trail in container databases by using the CONTAINER parameter; that parameter accepts the following values:


DBMS_AUDIT_MGMT.CONTAINER_CURRENT
DBMS_AUDIT_MGMT.CONTAINER_ALL

Using the first processes the trail in the current container; the second processes all available containers in the CDB.

The USE_LAST_ARCH_TIMESTAMP parameter controls which records get purged, in a rather basic fashion. If the value passed is TRUE then all audit records written before the last archive timestamp are purged, leaving current records in the trail. Changing this to FALSE purges the entire trail.

It's nice to see that Oracle has somewhat simplified the audit trail and has made it easier to audit specific actions on selected tables as well as auditing multiple tables in a single policy. You may not need this functionality now, but it's good to know how it is configured so you're ready when the requirement is made.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM