Thursday, June 27, 2013

Oracle 12c New Features - Unified Auditing

Oracle 12c introduces Unified Auditing, which consolidates database audit records including :-

  • DDL, DML, DCL
  • Fine Grained Auditing (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
The data is stored in the AUDSYS schema / SYSAUX tablespace.

    By default Unified Auditing is not enabled.  To enable it, shutdown the database and listener and relink :-

    make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
    
    
    Then start the listener and database and confirm it is enabled.

    SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'; 

    VALUE
    ---------------------------------------------------------------TRUE
    
    
    Unified Auditing can be configured to queue writes of audit data in SGA to improve performance, or immediately write to disk to reduce data loss in case of crash.
    
    

    To configure immediate write :-

    BEGIN 
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(  
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,  
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,  
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
    END;
    /


    To configure queued writes :-

    BEGIN   
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(   
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,      
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,   
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE); 
    END; 
    /
    
    
    12C has a new parameter unified_audit_sga_queue_size. I did not change this.

    SQL> show parameter unified

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ----------------
    unified_audit_sga_queue_size         integer     1048576

    New roles AUDIT_ADMIN and AUDIT_VIEWER are required to administer unified auditing.

    SQL> create user auditor identified by auditor;

    User created.

    SQL> grant create session to auditor;

    Grant succeeded.

    SQL> grant AUDIT_ADMIN to auditor;

    Grant succeeded.

    Create an audit policy with an action to capture SELECT on table TEST.SALARY.  It would also be possible to capture events DDL, RMAN, FGA, Data Pump etc.

    SQL> connect auditor/auditor@T12P1
    Connected.
    SQL> CREATE AUDIT POLICY audit_salary
    ACTIONS SELECT ON TEST.SALARY;


    Audit policy created.


    Enable the policy for user "nobody"

    SQL> AUDIT POLICY audit_salary by nobody;

    Audit succeeded.


    Login as "nobody" and select from table TEST.SALARY.

    SQL> connect nobody/nobody@T12P1
    Connected.
    SQL> select * from test.salary;

    no rows selected

    To flush the audit data to table execute DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL :-

    SQL> connect / as sysdba
    Connected.
    SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; PL/SQL procedure successfully completed.

    Audit data can be extracted from table UNIFIED_AUDIT_TRAIL. You can see below there are audit records for the LOGON and the SELECT.

    SQL> connect auditor/auditor@T12P1 Connected.
    SQL> SELECT ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME, EVENT_TIMESTAMP FROM UNIFIED_AUDIT_TRAIL WHERE DBUSERNAME = 'NOBODY'; ACTION_NAME ---------------------------------------------------------------- OBJECT_SCHEMA ------------------------------ OBJECT_NAME ---------------------------------------------------------------- EVENT_TIMESTAMP ---------------------------------------------------------------- SELECT TEST SALARY 27-JUN-13 03.24.07.677753 PM LOGON 27-JUN-13 03.24.02.215469 PM
    To upload OS audit files to the DB :-
    EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;
    SQL> desc unified_audit_trail
     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
    

    More details can be found in the documentation :- http://docs.oracle.com/cd/E16655_01/network.121/e17607/audit_config.htm