Thursday 4 July 2013

12C Auditing Goodies


As we all know that in most cases auditing is required for reporting because of security and compliance regulations and talking about  auditing here once again relative to 12C is not something new, so here comes a new 12C Database goodie known as unified auditing. Good thing is it comes up with no changes required to the database initialization parameters since previous versions. Not only this it enables the security administrators to configure audit policies and enable them in the database with no production database downtime, providing so much ease of administration and flexibility for the database. Thats not the end, as we know that security and compliance regulations require accurate monitoring and reporting of Oracle database activity so in this case we have a new insert-only tablespace which provides guarantee that audit records are not modified or deleted after they have been written to the audit trail. Remember the maintenance of the audit trail is only limited to the database users who have been given this AUDIT_ADMIN role. So the users with the AUDIT_ADMIN role can manage the retention policy of the audit data.

As auditing is suppose to be done because regulations such as the Sarbanes-Oxley Act have common auditing-related requirements like monitoring a suspicious user or notify the auditor the actions of an unauthorised user and other requirements too, so it became more important for oracle to provide more features and options in security as always with a new version . Now in relation to the auditing database we have a new initialization parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE introduced which configures the size of SGA queue for unified auditing. Actually this parameter is very useful for the systems where there is lot of audit records are generated very frequently depending on type of auditing enabled and the amount of auditing options enabled like for standard auditing options for DB there would be huge flushing of audit records to system tablespace in table aud$ in 11G version or when using VAULT the table aud$ would be in system schema with the tablespace being the same. Interestingly in 12C there are 2 modes for this one, the important one is queued-write mode, in this case audit records are first written to the SGA queue and when the SGA queue reaches the threshold, the audit records will be flushed to the disk inside AUDSYS schema table. This queue size can definately be setup and tuned accordingly depending on the amount audit records getting generated. So if there is frequent flushing it makes sense to increase queue size.

In 12C database there are 2 roles which have been introduced which are AUDIT_ADMIN and AUDIT_VIEWER, so they are required to administer unified auditing. So make sure the user who is doing these tasks has these roles.

So now let us take a look at how unified auditing policy is created and how we can manually flush the changes to disk. Dont forget this data is stored in the AUDSYS schema/SYSAUX tablespace.

SQL>    CREATE AUDIT POLICY ORA_KRISHNA
     PRIVILEGES
                ALTER DATABASE, ALTER SYSTEM,
                CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM
    ACTIONS     CREATE DATABASE LINK, ALTER DATABASE LINK, DROP DATABASE LINK,
               LOGON, LOGOFF, CREATE DIRECTORY, DROP DIRECTORY
   /

Audit policy created.

SQL>  audit policy  ORA_KRISHNA ;

Audit succeeded.
##### Remember if the audit trail mode is set to QUEUED, then audit records are not written to disk till the time memory queues become full. The following command explicitly flushes the queues to disk, and then you can see the audit trail records in UNIFIED_AUDIT_TRAIL view.

SQL>  EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed.

SQL> SELECT  SCN, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL where DBUSERNAME='SYS' and sql_text like 'alter system%';

no rows selected

SQL>  alter system set audit_trail=XML scope=spfile;

System altered.

SQL>  EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed.


SQL> SELECT  SCN, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL where DBUSERNAME='SYS' and sql_text like 'alter system%';

       SCN SQL_TEXT
---------- --------------------------------------------------------------------------------
   1732822 alter system set audit_trail=XML scope=spfile           
                                 
Disabling Unified Auditing


If you must disable unified auditing, then follow this procedure:

    Log into the database instance as user SYS with the SYSOPER privilege.

    sqlplus sys as sysoper
    Enter password: password

    In a multitenant environment, connect to the appropriate PDB.

    For example:

    CONNECT SYS@appdb AS SYSOPER
    Enter password: password

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

    Shut down the database.

    For example:

    SHUTDOWN IMMEDIATE

    From the command prompt, run the following commands:

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

    In SQL*Plus, restart the database.

    STARTUP


Just to add up to the goodies of 12C security there is an increased security in 12C when using SELECT ANY DICTIONARY privilege so SELECT ANY DICTIONARY privilege no longer allows access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS.

With this security is increased of the database as subset of data dictionary tables is not allowed to be seen even though you have SELECT ANY DICTIONARY privilege.

From the Oracle Documentation please see how Mixed Mode Auditing is suppose to behave from the following link

http://docs.oracle.com/cd/E16655_01/network.121/e17607/auditing.htm#CHDBDCHI

Below is what have been mentioned in oracle doc as it is

About Mixed Mode Auditing

When you create a new database, the database uses mixed mode auditing, which enables both traditional (that is, from releases earlier than Release 12c) and new audit facilities (unified auditing).

As the term refers, mixed mode auditing enables both traditional and unified audit facilities together. Needless to say, the traditional audit facility is driven by the AUDIT_TRAIL initialization parameter. Only for mixed mode auditing, you should set this parameter to the appropriate traditional audit trail. This traditional audit trail will then be populated with audit records, along with the unified audit trail.

When you upgrade your database to the current release, you must manually migrate to unified auditing if you want to use unified auditing. Afterward, traditional auditing is disabled, and the new audit records are written to the unified audit trail. After you complete the migration, the audit records from the previous release are still available in those audit trails. You can then archive and purge these older audit trails by using the DBMS_AUDIT_MGMT PL/SQL procedures, based on your enterprise retention policies.

There is still a lot more in 12C relative to auditing but that shall be next time.

No comments:

Post a Comment