Unified auditing enables you to capture audit records from a variety of sources.
Unified Auditing is a new auditing facility in Oracle Database 12c Release 1 (12.1).
The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in an uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view.
When the database is writable, audit records are written to the unified audit trail. If the database is not writable, then audit records are written to new format operating system files in the POSIX file system in the $ORACLE_BASE/audit/$ORACLE_SID directory.
Enabling Unified Auditing
The Unified Auditing option is not enabled after you intall Oracle Database. You can find if your database has been migrated to unified auditing by querying the V$OPTION dynamic view. Query the VALUE column of the V$OPTION dynamic view as follows with SQL*Plus:
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
PARAMETER VALUE
---------------- ----------
Unified Auditing FALSE
If the output for the VALUE column is TRUE, then pure unified auditing is already enabled in your database. If unified auditing has not been enabled, then the output is FALSE.
To enable the Unified Auditing option, relink the ORAKNL binary in the ORALOAD library $ORACINST.ORALOAD.LIB. Relinking is done by calling a BS2000 command procedure.
After shutting down all databases and stopping all listeners, log in to the installation user ID $ORACINST. Enter the following BS2000 command to enable Unified Auditing:
/CALL-PROCEDURE INSTALL.P.UNIAUD-ON
Restart the databases and listeners in your DBA user IDs. After restarting, all the databases run with Unified Auditing.
Steps:
1. Stop all Oracle processes: databases and listener.
Logon to oracle database server and logon to sqlplus with sysdba.
[oracle@Oradb1 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>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@Oradb1 lib]$ps -ef | grep pmon
Exit out of SQLPLUS and shutdown the oracle listener with command: lsnrctl stop
[oracle@Oradb1 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@Oradb1 lib]$
2. Relink Oracle with the uniaud_on option.
[oracle@Oradb1 lib]$cd $ORACLE_HOME/rdbms/lib
[oracle@Oradb1]make -f ins_rdbms.mk uniaud_on ioracle
3. Restart all Oracle processes: listener, databases.For the purpose of the demonstration, only the orcl database instance needs to be started and the listener.
Startup the Oracle listener with command: lsnrctl start
[oracle@Oradb1 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/Oradb1/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/Oradb1/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@Oradb1 lib]$
Logon to SQL*PLUS start the database.
[oracle@Oradb1 ~]$ 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>
4. Verify that unified auditing is now enabled.
SQL> sqlplus / as sysdba
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
PARAMETER VALUE
---------------- ----------
Unified Auditing TRUE
Disabling Unified Auditing
To disable the Unified Auditing option, relink the ORAKNL binary in the ORALOAD library $ORACINST.ORALOAD.LIB. Relinking is done by calling a BS2000 command procedure.
After shutting down all databases and stopping all listeners, log in to the installation user ID $ORACINST. Enter the following BS2000 command to disable Unified Auditing:
/CALL-PROCEDURE INSTALL.P.UNIAUD-OFF
1. Restart the databases and listeners in your DBA user IDs. After restarting, all databases run without the Unified Auditing option.
Logon to oracle database server and logon to sqlplus with sysdba.
[oracle@Oradb1 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>
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>
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
2. Shutdown the database with immediate option.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
3. 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@Oradb1 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@Oradb1 lib]$
4. 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
5. Startup the Oracle listener with command: lsnrctl start
[oracle@Oradb1 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/Oradb1/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/Oradb1/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@Oradb1 lib]$
6. Logon to SQL*PLUS start the database.
[oracle@Oradb1 ~]$ 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>
7. 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
Unified Auditing is a new auditing facility in Oracle Database 12c Release 1 (12.1).
The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in an uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view.
When the database is writable, audit records are written to the unified audit trail. If the database is not writable, then audit records are written to new format operating system files in the POSIX file system in the $ORACLE_BASE/audit/$ORACLE_SID directory.
Enabling Unified Auditing
The Unified Auditing option is not enabled after you intall Oracle Database. You can find if your database has been migrated to unified auditing by querying the V$OPTION dynamic view. Query the VALUE column of the V$OPTION dynamic view as follows with SQL*Plus:
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
PARAMETER VALUE
---------------- ----------
Unified Auditing FALSE
If the output for the VALUE column is TRUE, then pure unified auditing is already enabled in your database. If unified auditing has not been enabled, then the output is FALSE.
To enable the Unified Auditing option, relink the ORAKNL binary in the ORALOAD library $ORACINST.ORALOAD.LIB. Relinking is done by calling a BS2000 command procedure.
After shutting down all databases and stopping all listeners, log in to the installation user ID $ORACINST. Enter the following BS2000 command to enable Unified Auditing:
/CALL-PROCEDURE INSTALL.P.UNIAUD-ON
Restart the databases and listeners in your DBA user IDs. After restarting, all the databases run with Unified Auditing.
Steps:
1. Stop all Oracle processes: databases and listener.
Logon to oracle database server and logon to sqlplus with sysdba.
[oracle@Oradb1 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>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@Oradb1 lib]$ps -ef | grep pmon
Exit out of SQLPLUS and shutdown the oracle listener with command: lsnrctl stop
[oracle@Oradb1 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@Oradb1 lib]$
2. Relink Oracle with the uniaud_on option.
[oracle@Oradb1 lib]$cd $ORACLE_HOME/rdbms/lib
[oracle@Oradb1]make -f ins_rdbms.mk uniaud_on ioracle
3. Restart all Oracle processes: listener, databases.For the purpose of the demonstration, only the orcl database instance needs to be started and the listener.
Startup the Oracle listener with command: lsnrctl start
[oracle@Oradb1 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/Oradb1/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/Oradb1/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@Oradb1 lib]$
Logon to SQL*PLUS start the database.
[oracle@Oradb1 ~]$ 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>
4. Verify that unified auditing is now enabled.
SQL> sqlplus / as sysdba
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
PARAMETER VALUE
---------------- ----------
Unified Auditing TRUE
Disabling Unified Auditing
To disable the Unified Auditing option, relink the ORAKNL binary in the ORALOAD library $ORACINST.ORALOAD.LIB. Relinking is done by calling a BS2000 command procedure.
After shutting down all databases and stopping all listeners, log in to the installation user ID $ORACINST. Enter the following BS2000 command to disable Unified Auditing:
/CALL-PROCEDURE INSTALL.P.UNIAUD-OFF
1. Restart the databases and listeners in your DBA user IDs. After restarting, all databases run without the Unified Auditing option.
Logon to oracle database server and logon to sqlplus with sysdba.
[oracle@Oradb1 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>
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>
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
2. Shutdown the database with immediate option.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
3. 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@Oradb1 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@Oradb1 lib]$
4. 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
5. Startup the Oracle listener with command: lsnrctl start
[oracle@Oradb1 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/Oradb1/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/Oradb1/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@Oradb1 lib]$
6. Logon to SQL*PLUS start the database.
[oracle@Oradb1 ~]$ 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>
7. 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
No comments:
Post a Comment