Oracle 12c has implemented the unified auditing which is based off of system and user level policies.   This article describes the creation and enabling audit policies.

 

1. Logon to oracle server and logon to sqlplus as sysdba.

Verify that unified auditing is enabled with command: select value from v$option where parameter=’Unified Auditing’;

 

SQL> select value from v$option where parameter=’Unified Auditing’;

 

VALUE

—————————————————————-

TRUE

 

SQL>

 

2. Create audit policy test_user, which will audit session creation and all RDBMS actions.

 

create audit policy test_user privileges create session

ACTIONS all;

 

3. Enable the audit policy for the user test with the command: This enabling will only be for actions performed by user test.

 

audit policy test_user by test;

 

SQL> audit policy test_user by test;

 

Audit succeeded.

 

SQL>

 

4. View the active policies.

 

SQL> select policy_name from audit_unified_enabled_policies;

 

POLICY_NAME

——————————————————————————–

TEST_USER

 

SQL>

 

5. This completes the creation and enablement of Unified Audit policies.

 

Larry Catt

OCP

Auditing monitors and records selected actions by database users and processes.  It can be based on specific SQL, object, system or object privilege and monitor both successful and/or failed attempts.  Auditing must be enabled to perform auditing.  Audit records are either stored in data dictionary or OS file.  Auditing provides internal controls and typical implementations are:

1. Enable accountability for actions.
2. Deter user or intruders form inappropriate actions.
3. Investigate suspicious activity.
4. Notify an auditor of unauthorized actions.
5. Monitor and gather data about specific database activities.
6. Detect problems with an authorization or access control.
7. Address auditing requirements for regulatory compliance.

 

The system initialization parameter AUDIT_TRAIL controls database auditing process and has the following options.

• DB – audit trail is sent to data dictionary. All mandatory and SYS audit trails are always sent to OS audit trail.   DB is the default setting form AUDIT_TRAIL.
• DB, EXTENDED – Same as DB, but gives SQL, Bind variables, and CLOB columns to SYS.AUD$
• OS – Puts all audit trails on OS directory defined in AUDIT_FILE_DEST init parameter.
• XML – Puts all audit trails in XML file on OS directory defined in XML_AUDIT_TRAIL value. Does not affect syslog.audit file, which will always be in text.
• XML, EXTENDED – same as XML but includes SQL, Bind variables, etc.
• NONE – Disables auditing.

The SYS.AUD$ table holds standard audit records within the database and the SYS.FGA_LOG$ table holds records for fine grain auditing.  Audit records can only be deleted by administrator.  IF parameter 07_DIRCTIONARY_ACCESSIBILITY is set to FALSE the default value, only SYSDBA can perform DML on SYS.AUD$ or SYS.FGA_LOG$.   Oracle Database Vault and Oracle Label Security can add additional protection to audit trail but requires additional licensing.

 

Some actions by SYSDBA and SYSOPER are always audited and place in OS audit trail, this are referred to as mandatory auditing and happens even if audit trail is disabled.    These audit records are located in $ORACLE_BASE/admin/$ORACLE_SID/adump directory by default.   Mandatory actions that are audited:

1. Database Startup
2. SYSDBA and SYSOPER logins
3. Database shutdown.

FGA – Fine-grained auditing extends the capabilities of standard auditing and allows for conditions to be set to trigger auditing to occur.

 

Larry Catt

OCP

Oracle 12c has implemented the unified auditing which is based off of system and user level policies.   This article describes the use of audit policies.

 

 

Audit Policy General Aspects.

1. A unified audit policy is a named group of audit setting that track a particular aspect of user behavior in DB.
2. The CREATE AUDIT POLICY is used to create unified audit policy.
3. Multiple audit policy can be active at any given time.
4. AUDIT and NOAUDIT statements enable and disable.
5. The AUDIT statement can include or exclude specific users.
6. AUDIT and NOAUDIT can enable audit application context values.
7. Activities that can be audited:
   1. USER ACCOUNTS, ROLES, and PRIVILEGEs
   2. OBJECT ACTION: drop table; running a procedure.
   3. APPLICATION CONTEXT VALUES.

 

8. You should group auditing into related policies and attempt to minimize the number of policies defined.
9. Many policies increase overhead in the UGA.
10. The CREATE AUDIT POLICY statement creates a first class object in the SYS schema not in the schema of the creator.
11. Once policy is created, it must be enabled with the AUDIT statement with POLICY clause.
12. CREATE AUDIT POLICY and AUDIT statement do not affect current sessions, user must disconnect and reconnect for auditing to start.
13. CLAUSES of the AUDIT statement.

BY — Used to apply the unified audit policy to one or more users.

AUDIT POLICY change_table_pol BY ocpuser;

 

EXCEPT — Used to exclude users from the unified audit policy.

AUDIT POLICY change_table_pol EXCEPT jtkirk, jlpicard;

 

WHENEVER SUCCESSFUL — Records only successful executions of the audited activity.

AUDIT change_table_pol WHENEVER SUCCESSFUL;

 

WHENEVER NOT SUCCESSFUL — Records only failed executions of the audited activity.

AUDIT change_table_pol WHENEVER NOT SUCCESSFUL;

 

Note the following:

WHENEVER — If the WHENEVER clause is omitted, then both failed and successful user activities are written to the audit trail.

 

BY/ EXCEPT — A unified audit policy can be enabled with either the BY clause or the EXCEPT clause, but not both simultaneously.

 

AUDIT… BY — If multiple AUDIT statements are executed on the same unified audit policy with different BY users, all of the specified users are audited.

 

AUDIT… EXCEPT — If multiple AUDIT statements are executed on the same unified audit policy with different EXCEPT users, only the last exception user list is used.

 

COMMON policies — Common unified audit policies can only be enabled from the root and only for common users.

 

LOCAL policies — Local audit policies can only be enabled from the PDB to which it applies.

 

14. No audit policy is necessary to record RMAN activities. RMAN activities are recorded automatically.

RMAN_SESSION_RECID — Recovery Manager session identifier. Together with the RMAN_SESSION_STAMP column, this column uniquely identifies the Recovery Manager job. RMAN_SESSION_STAMP — Timestamp for the session.

RMAN_OPERATION — The Recovery Manager operation executed by the job.

RMAN_OBJECT_TYPE — Type of objects involved in a Recovery Manager session.

RMAN_DEVICE_TYPE — Device associated with a Recovery Manager session.

 

 

 

Larry Catt

OCP

When upgrading to 12c the unified auditing is not enabled.  The audit processes of the upgraded database will be used.   Now newly installed databases will use the mixed use unified auditing by default.  In upgrade, you must migrate the database to unified auditing to disable traditional auditing.   The following procedure does this.

 

1. Logon as SYSDBA
2. Determine if database is already in unified auditing with the following statement. TRUE indicates no migration is necessary.

select VALUE from V$OPTION where PARAMETER = ‘Unified Auditing’;

3. Shutdown the database
4. Stop the listener.
5. Change directory to $ORACLE_HOME/rdbms/lib
6. Enable unified auditing executable with unix command:

make –f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORALE_HOME

In Windows:

Rename file %ORACLE_HJOME%/bin/oauniaud12dll.dbl to %ORACLE_HJOME%/bin/oauniaud12dll

7. Restart listener.
8. Restart the database.

 

Larry Catt

OCP

In 12c the Unified Audit Trail consolidates audit information from multiple sources and makes this information available in a standard format in the UNIFIED_AUDIT_TRAIL dictionary view.  This article describes enabling the Unified Audit Data Trail.

 

1. Logon to your oracle server as the oracle software owner and logon to sqlplus with sysdba privileges.
2. To determine if Unified Auditing is enabled, execute the query: select value from v$option where parameter=’Unified Auditing’;

 

SQL>  select value from v$option where parameter=’Unified Auditing’;

 

VALUE

—————————————————————-

FALSE

 

SQL>

 

3. NOTE: that Unified Auditing is not currently enabled.
4. Shutdown the database instance with command: shutdown immediate.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

5. Exit out of SQLPLUS and shutdown the listener utility with the OS command: lsnrctl stop

 

SQL> exit

Disconnected

[oracle@linux2 ~]$ lsnrctl stop

 

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-APR-2016 10:13:21

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.1.101)(PORT=1521)))

The command completed successfully

[oracle@linux2 ~]$

 

6. Change directory to $ORACLE_HOME/rdbms/lib and execute the make command to relink oracle with ‘uniaud_on’ option.

 

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_on ioracle

 

 

7. Restart Oracle listener with command: lsnrctl start

 

[oracle@linux2 lib]$ lsnrctl start

 

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-APR-2016 10:17:53

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

 

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.1.101)(PORT=1521)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.1.101)(PORT=1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date                15-APR-2016 10:17:53

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File         /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.1.101)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

  Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 lib]$

 

8. Logon to Oracle SQLPLUS with sysdba privileges and startup the database with command: startup.

[oracle@linux2 lib]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Thu APR 15 10:18:25 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  549453824 bytes

Fixed Size                  2926616 bytes

Variable Size             268437480 bytes

Database Buffers          272629760 bytes

Redo Buffers                5459968 bytes

Database mounted.

Database opened.

SQL>

 

 

 

9. Check the status of Unified Auditing with command: select value from v$option where parameter=’Unified Auditing’;

 

SQL> select value from v$option where parameter=’Unified Auditing’;

 

VALUE

—————————————————————-

TRUE

 

SQL>

 

 

10. This completes the enablement of Unified Audit Data in 12c

 

Larry Catt

OCP

 

1. In 12c the Unified Audit Trail consolidates audit information form multiple sources and makes this information available in a standard format in the UNIFIED_AUDIT_TRAIL dictionary view.
2. The Unified Audit Trail is stored in a read-only table in the AUDSYS schema in SYSAUX tablespace
3. Data is available to SYS and users with AUDIT_ADMIN and AUDIT_VIEWER roles.
4. AUDIT_ADMIN can view data and create audit policies.
5. AUDIT_VIEWER can only view data.
6. The Unified Audit Data Trail combines the following:
7. Unified audit policies and AUDIT settings

• Fine-grained audit records from DBMS_FGA
• Oracle Database Real Application Security
• Oracle Recovery Manager
• Oracle Database Vault
• Oracle Label Security
• Oracle Data Mining
• Oracle Data Pump
• Oracle SQL* Loader Direct Load

8. Once configured the unified auditing is always enabled and does not depend on initialization parameters.
9. If the database is open in READ-ONLY mode, audit records are written to new OS files in $ORACLE_BASE/audit/$ORACLE_SID directory.
10. The V$OPTION view can be queried to determine if DB is using unified auditing.

Select value from v$option where parameter=’Unified Auditing’;

Parameter                          Value

—————–                    —————–

Unified Auditing              TRUE

 

11. New 12c database mixed mode auditing is enabled by default by predefined policy
12. Mixed-mode auditing enables both Traditional (pre-12c auditing and unified auditing.
13. Traditional auditing is controlled by AUDIT_TRAIL init parameters.
14. When AUDIT_TRAIL init parameter is parameter other than NONE, the traditional audit trail will be populated as well as the Unified Audit trail.
15. Auditing can be set to individual PDBs or CDB as a whole.
16. In Multi-tenant DB each PDB and root CDB has its own audit trail.
17. When upgrading to 12c, you have to manually migrate to unified auditing to enable.
18. Once unified auditing is enabled, traditional is disabled.
19. To start using Unified, at least one unified policy must be enabled.
20. To stop using Unified, disable all unified policies.
21. Predefined policy ORA_SECURECONFIG is initially enabled on all new 12c databases.

 

Larry Catt

OCP

Oracle 12c does permit for you to disable unified auditing and use traditional auditing features.   This article covers the steps to disabling unified auditing.

 

1. Logon to oracle database server and logon to sqlplus with sysdba.

 

[oracle@linux2 lib]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 10:35:07 2015

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL>

 

2. Disable any enabled unified audit policies, preventing database from going into mixed mode auditing. You can use the statement:  select ‘noaudit policy ‘||policy_name||’;’ from audit_unified_enabled_policies;

 

SQL> select ‘noaudit policy ‘||policy_name||’;’ from audit_unified_enabled_policies;

 

‘NOAUDITPOLICY’||POLICY_NAME||’;’

——————————————————————————–

noaudit policy ORA_SECURECONFIG;

noaudit policy ORA_LOGON_FAILURES;

 

SQL> noaudit policy ORA_SECURECONFIG;

 

Noaudit succeeded.

 

SQL>

SQL> noaudit policy ORA_LOGON_FAILURES;

 

Noaudit succeeded.

 

SQL>

 

 

3. Verify that no unified audit policies are now enabled by querying the audit_unified_enabled_policy table.

 

SQL> select count(*) from audit_unified_enabled_policies;

 

  COUNT(*)

———-

         0

 

SQL>

 

4. Shutdown the database with immediate option.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

5. Exit out of SQLPLUS and shutdown the oracle listener with command: lsnrctl stop

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 lib]$ lsnrctl stop

 

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-DEC-2015 10:38:36

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.98)(PORT=1521)))

The command completed successfully

[oracle@linux2 lib]$

 

 

 

6. From the command prompt go to directory $ORACLE_HOME/rdbms/lib and use make to execute ins_rdbms.mk uniaud_off ioracle command

 

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_off ioracle

 

7. Startup the Oracle listener with command: lsnrctl stop

[oracle@linux2 lib]$ lsnrctl start

 

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-DEC-2015 10:41:06

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

 

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.98)(PORT=1521)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.98)(PORT=1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date                15-DEC-2015 10:41:06

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File         /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.98)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

  Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 lib]$

 

8. Logon to SQL*PLUS start the database.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 10:41:32 2015

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup open

ORACLE instance started.

 

Total System Global Area  549453824 bytes

Fixed Size                  2926616 bytes

Variable Size             268437480 bytes

Database Buffers          272629760 bytes

Redo Buffers                5459968 bytes

Database mounted.

Database opened.

SQL>

 

9. Verify that the unified Auditing is off by following statement: select value from v$option where parameter=’Unified Auditing’;

 

SQL> select value from v$option where parameter=’Unified Auditing’;

 

VALUE

—————————————————————-

FALSE

 

SQL>

 

10. This completes disabling the Unified Auditing in Oracle 12c.

 

Larry Catt

OCP