ContractOracle

Subscribe to ContractOracle feed
Contract Oracle Limited is not affiliated to Oracle Corporation.
Updated: 2 hours 18 min ago

Oracle 12c New Features - Unified Auditing

Thu, 2013-06-27 02:07
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
      Categories: DBA Blogs

      Oracle 12c - need to start container AND pluggable databases.

      Thu, 2013-06-27 01:56
      In Oracle 12c we start the Container Database (CDB) the same as we started previous database versions.

      [oracle@rac1 lib]$ sqlplus / as sysdba

      SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 14:47:35 2013

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

      Connected to an idle instance.

      SQL> startup
      ORACLE instance started.

      Total System Global Area 1653518336 bytes
      Fixed Size                  2289016 bytes
      Variable Size             989856392 bytes
      Database Buffers          654311424 bytes
      Redo Buffers                7061504 bytes
      Database mounted.
      Database opened.

      But then we find that the Pluggable Databases (PDBs) are still in "MOUNTED" state, so we will need to open them before we can login.

      SQL> select name, open_mode from v$pdbs;

      NAME                           OPEN_MODE
      ------------------------------ ----------
      PDB$SEED                       READ ONLY
      PDB1                           MOUNTED
      PDB2                           MOUNTED
      PDB3                           MOUNTED

      From CDB$ROOT we can manage any PDB.  

      SQL> show con_name

      CON_NAME
      ------------------------------
      CDB$ROOT

      To open one PDB :-

      SQL> alter pluggable database PDB1 open;

      Pluggable database altered.

      To open ALL PDBS :-

      SQL> alter pluggable database all open;

      Pluggable database altered.

      SQL> select name, open_mode from v$pdbs;

      NAME                           OPEN_MODE
      ------------------------------ ----------
      PDB$SEED                       READ ONLY
      PDB1                           READ WRITE
      PDB2                           READ WRITE
      PDB3                           READ WRITE

      Or we can move down to a PDB container to stop and start them individually.

      SQL> alter session set container=PDB1;

      Session altered.

      SQL> show con_name

      CON_NAME
      ------------------------------
      PDB1

      SQL> shutdown;
      Pluggable Database closed.
      SQL> startup;
      Pluggable Database opened.


      Startup of PDBs can be automated using a startup trigger.

      SQL> create or replace trigger Sys.After_Startup
                                after startup on database
      begin
         execute immediate 'alter pluggable database all open';
      end;
      /  

      Trigger created.

      SQL> shutdown immediate;
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      SQL> startup
      ORACLE instance started.

      Total System Global Area 1636814848 bytes
      Fixed Size                  2288968 bytes
      Variable Size             973079224 bytes
      Database Buffers          654311424 bytes
      Redo Buffers                7135232 bytes
      Database mounted.
      Database opened.

      SQL> select name, open_mode from v$pdbs;

      NAME                           OPEN_MODE
      ------------------------------ ----------
      PDB$SEED                       READ ONLY
      PDB1                           READ WRITE
      PDB2                           READ WRITE
      PDB3                           READ WRITE

      Categories: DBA Blogs

      Oracle 12c Limitations to RESOURCE, SELECT ANY DICTIONARY

      Thu, 2013-06-27 01:38
      Oracle 12c has implemented a few improvements to the existing system privileges.  

      Dictionary tables containing password hashes (DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$XS$VERIFIERSare no longer included in the SELECT ANY DICTIONARY system privilege.  This makes it safer to give developers access to dictionary tables for tuning and debugging, without giving them the chance to run brute force attacks ...

      Unlimited Tablespace is no longer included in the RESOURCE role. This should reduce the number of times developers create segments in SYSTEM tablespace ....

      SQL> grant select any dictionary to god;

      Grant succeeded.

      SQL> grant resource to god;

      Grant succeeded.

      SQL> connect god/god@T12P1
      Connected.
      SQL> select password from user$;
      select password from user$
                           *
      ERROR at line 1:
      ORA-00942: table or view does not exist

      SQL> select default_tablespace from dba_users where username = 'GOD';

      DEFAULT_TABLESPACE
      ------------------------------
      USERS

      SQL> create table test(id integer) tablespace system;

      Table created.

      SQL> insert into test values (1);
      insert into test values (1)
                  *
      ERROR at line 1:
      ORA-01950: no privileges on tablespace 'SYSTEM'

      Categories: DBA Blogs

      Oracle 12c New Features - Data Redaction via DBMS_REDACT

      Wed, 2013-06-26 21:55
      Oracle 12c has added functionality to redact data in specific columns depending on a policy configured by the DBMS_REDACT package.  

      First create table EMPLOYEE in schema TEST and grant select to GOD and NOBODY.

      SQL> connect test/test@T12P1
      Connected.
      SQL> create table employee (emp_id integer primary key, emp_name varchar2(10), salary number);

      Table created.

      SQL> grant select on employee to god;

      Grant succeeded.

      SQL> grant select on employee to nobody;

      Grant succeeded.

      SQL> insert into employee (emp_id, emp_name, salary) values (1,'John',100000);

      1 row created.

      SQL> insert into employee (emp_id, emp_name, salary) values (2,'Ben',80000);

      1 row created.

      SQL> commit;

      Commit complete.

      Now we create the data redaction policy using DBMS_REDACT.ADD_POLICY.  Only someone logged in as GOD should be allowed to see the values stored in TEST.EMPLOYEE.SALARY.  In this case we are using function type FULL which obscures all data, but other options include PARTIAL, RANDOM, REGEXP.

      SQL> connect system/password@T12P1
      Connected.
      SQL> BEGIN
        2  DBMS_REDACT.add_policy(object_schema => 'TEST'
        3  ,object_name => 'EMPLOYEE'
        4  ,policy_name => 'Salary Redaction'
        5  ,expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''GOD'' OR SYS_CONTEXT(''USERENV'',''SESSION_USER'') IS NULL'
        6  ,column_name => 'SALARY'
        7  ,function_type => dbms_redact.FULL
        8  );
        9  END;
       10  /

      PL/SQL procedure successfully completed.

      When we are logged in as nobody the salary column is redacted.

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

                    EMP_ID EMP_NAME                 SALARY
      -------------------- ---------- --------------------
                         1 John                          0
                         2 Ben                           0

      But when we are logged in as god we can see the data.

      SQL> connect god/god@T12P1
      Connected.
      SQL> select * from test.employee;

                    EMP_ID EMP_NAME                 SALARY
      -------------------- ---------- --------------------
                         1 John                     100000
                         2 Ben                       80000

      Note that redaction policies do NOT apply to users with the EXEMPT REDACTION POLICY system privilege, which by default is granted via EXP_FULL_DATABASE and DBA roles.  This means that by default DBAs will still have access to redacted data.

      SQL> connect system/password@T12P1
      Connected.
      SQL> select * from test.employee;

                    EMP_ID EMP_NAME                 SALARY
      -------------------- ---------- --------------------
                         1 John                     100000
                         2 Ben                       80000

      SQL> select grantee, privilege from dba_sys_privs where privilege = 'EXEMPT REDACTION POLICY';

      GRANTEE
      -----------------------------------------------------------------
      EXP_FULL_DATABASE

      SQL> select grantee from dba_role_privs where granted_role = 'EXP_FULL_DATABASE';

      GRANTEE
      -----------------------------------------------------------------
      SYS
      DATAPUMP_EXP_FULL_DATABASE
      DBA
      DATAPUMP_IMP_FULL_DATABASE







      Categories: DBA Blogs

      Oracle 12c New Features - In Database Row Archiving

      Wed, 2013-06-26 20:57
      Oracle 12c has a new feature called In Database Row Archiving.  Instead of deleting old records they can now be marked as archived, be invisible to running applications, but remain in the original table in case they need to be restored at a later date.  This should reduce the need for DBAs to restore old backups, and should keep auditors happy.

      First create a test table.

      SQL> create table employee (emp_id integer primary key, emp_name varchar2(10), archive_date date);

      Table created.

      SQL> insert into employee (emp_id, emp_name) values (1,'John');

      1 row created.

      SQL> insert into employee (emp_id, emp_name) values (2,'Ben');

      1 row created.

      SQL> commit;

      Commit complete.

      Now enable archival for the table.

      SQL> alter table employee row archival;

      Table altered.

      We can see from hidden attribute ORA_ARCHIVE_STATE=0 that the records are not archived.

      SQL> select * from employee;

      EMP_ID EMP_NAME   ARCHIVE_D
      ------ ---------- ---------
           1 John
           2 Ben

      SQL> select emp_id, emp_name, ora_archive_state from employee;

      EMP_ID EMP_NAME   ORA_ARCHIVE_STATE
      ------ ---------- -----------------------------------------------
           1 John       0
           2 Ben        0

      Now we want to archive the record for employee Ben (he resigned).

      SQL> update employee
      set ora_archive_state=dbms_ilm.archivestatename(1), archive_date=sysdate
      where emp_id=2; 

      1 row updated.

      SQL> commit;

      Commit complete.

      The record record for Ben is no longer visible to normal select operations.

      SQL> select * from employee;

      EMP_ID EMP_NAME   ARCHIVE_D
      ------ ---------- ---------
           1 John

      But if we set row archive visibility=all then we can see that the record still exists in the table.  

      SQL> alter session set row archival visibility = all;

      Session altered.

      SQL> select emp_id, emp_name, ora_archive_state from employee;

      EMP_ID EMP_NAME   ORA_ARCHIVE_STATE
      ------ ---------- -----------------------------------------------
           1 John       0
           2 Ben        1


      SQL> alter session set row archival visibility = active;

      Session altered.

      And while the record is invisible to the application, it is still considered by constraints.

      SQL> select * from employee;

      EMP_ID EMP_NAME   ARCHIVE_D
      ------ ---------- ---------
           1 John

      SQL> insert into employee (emp_id, emp_name) values (2,'Ben');
      insert into employee (emp_id, emp_name) values (2,'Ben')
      *
      ERROR at line 1:
      ORA-00001: unique constraint (TEST.SYS_C009859) violated

      And if we re-hire Ben, the record can be brought back from archive.

      SQL> alter session set row archival visibility = all;

      Session altered.

      SQL> update employee
      set ora_archive_state=dbms_ilm.archivestatename(0), archive_date=null
      where emp_id=2; 

      1 row updated.

      SQL> commit;

      Commit complete.

      SQL> alter session set row archival visibility = active;

      Session altered.

      SQL> select * from employee;

      EMP_ID EMP_NAME   ARCHIVE_D
      ------ ---------- ---------
           1 John
           2 Ben


      Categories: DBA Blogs

      Oracle 12c New Features - Partial Indexing on Partitioned Tables

      Wed, 2013-06-26 03:42
      Oracle 12c allows DBAs to set specific table partitions "INDEXING OFF" and create indexes with "INDEXING PARTIAL".  This means that index partitions won't be created for the specified table partitions.  This allows DBAs to have multiple indexing strategies for different partitions, or to rapidly create indexes on specific partitions.

      Example :- create a partitioned table :-

      SQL> CREATE TABLE test_range
      (id  NUMBER(5),
      att1 char(1),
      att2 char(1),
      att3 char(1))
      PARTITION BY RANGE(id)
      (
      PARTITION id_10 VALUES LESS THAN(10),
      PARTITION id_20 VALUES LESS THAN(20)
      );

      Table created.

      Create a local index.  By default it will create index partitions for every table partition.

      SQL> create index test_index1 on test_range(att1) local;

      Index created.

      Now set indexing off for one partition, and create a second index with partial indexing enabled.

      SQL> alter table test_range modify partition id_20 indexing off;

      Table altered.

      SQL> create index test_index2 on test_range(att2) local indexing partial;

      Index created.

      Insert to create the segments.

      SQL> insert into test_range values (1,'a','a','a');

      1 row created.

      SQL> insert into test_range values (11,'b','b','b');

      1 row created.

      SQL> commit;

      Commit complete.

      And we can see that for index TEST_INDEX2 with PARTIAL INDEXING set, no segment was created for table partition ID_20 with INDEXING OFF.

      SQL> select index_name, partition_name, segment_created from dba_ind_partitions where index_owner = 'TEST';

      INDEX_NAME
      --------------------------------------------------------------------------------
      PARTITION_NAME
      --------------------------------------------------------------------------------
      SEGMENT_CREATED
      -------------------------
      TEST_INDEX1
      ID_10
      YES

      TEST_INDEX1
      ID_20
      YES

      TEST_INDEX2
      ID_10
      YES

      TEST_INDEX2
      ID_20
      NO

      SQL> select segment_name, segment_type, count(*) from dba_segments where owner = 'TEST' group by segment_name, segment_type;

      SEGMENT_NAME
      --------------------------------------------------------------------------------
      SEGMENT_TYPE         COUNT(*)
      ------------------ ----------
      TEST_RANGE
      TABLE PARTITION             2

      TEST_INDEX1
      INDEX PARTITION             2

      TEST_INDEX2
      INDEX PARTITION             1


      Categories: DBA Blogs

      Pages