Wednesday 17 September 2014

ILM in 12c - database hardening

The new database hardening feature of Oracle 12c Flashback Data Archive enables an application designer to group a set of tables together as an ‘application’ and administer them together with a single command. These tables may hold sensitive data that need to be protected and tracked for changes. This application can have a user-defined name and additional tables can be added to it.

All of the application’s tables will all share a designated (or the database default) flashback archive.

Example:

To let the administrator create a security-hardened application, the dbms_flashback_archive package supports a procedure call named register_application. For example…

SQL> ed

Wrote file afiedt.buf

1 begin

2 dbms_flashback_archive.register_application(

3 application_name => 'FDBA_APP1',

4 flashback_archive_name => 'FDBA1');

5* end;

SQL> /

PL/SQL procedure successfully completed.

The above pl/sql block creates a security application within the database named FDBA_APP1. Note that the application name should be in upper case.

A table named T1 is being added to the application (using dbms_flashback_archive.add_table_to_application) as shown:

begin

sys.dbms_flashback_archive.add_table_to_application(

Application_name => 'FDBA_APP1',

Table_name => 'T1',

Schema_name => 'FDB_USER');

end;

/SQL> 2 3 4 5 6 7

PL/SQL procedure successfully completed.

And below, adding a second table (T2) to the application named FDBA_APP1.

SQL> begin

sys.dbms_flashback_archive.add_table_to_application(

Application_name => 'FDBA_APP1',

Schema_name => 'FDB_USER',

Table_name => 'T2');

end;

2 3 4 5 6 7 /

PL/SQL procedure successfully completed.

Enabling flashback archiving for all the tables in this application (currently there are 2 tables)

SQL> begin

sys.dbms_flashback_archive.enable_application(Application_name => 'FDBA_APP1',

flashback_archive_name => 'FDBA1');

end;

/ 2 3 4 5

After executing this procedure both the tables should show up in the dba_flashback_archive_table.

SQL> SELECT TABLE_NAME, FLASHBACK_ARCHIVE_NAME, STATUS FROM DBA_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME FLASHBACK_ARCHIVE_NA STATUS

-------------------- -------------------- ----------

T1 FDBA1 ENABLED

T2 FDBA1 ENABLED

SQL>

Locking down the application

All the tables in a flashback archive application can be locked and protected against any modifications by locking them down. The dbms_flashback_archive.lock_down_application locks all the tables contained within the application.

For example we have set up an application called FDBA_APP1 with 2 tables, T1 and T2.

Demonstrating lock-down of the application FDBA_APP1

1 begin

2 sys.dbms_flashback_archive.lock_down_application(Application_name => 'FDBA_APP1');

3* end;

SQL> /

PL/SQL procedure successfully completed.

Notice below that the tables cannot be modified when we try to delete its contents.

SQL> delete from fdb_user.t1;

delete from fdb_user.t1

ERROR at line 1:

ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on

table "FDB_USER"."T1"

Following up on Karin's post:

I agree that the second parameter for the dbms_flashback.enable_application procedure is misplaced, because, the archive to use for logging would have already been identified at the time of application registration.

Incidentally, both dbms_flashback_archive.register_application and dbms_flashback_archive.enable_application accept a second parameter which is the flashback archive name (release 12.1.0.2).

But only dbms_flashback_archive.register_application's 2nd parameter is documented.

It could be a bug that dbms_flashback_archive.enable_application still has a second parameter :)

Doing a dbms_flashback_archive describe we get:

SQL> desc dbms_flashback_archive;
PROCEDURE ADD_TABLE_TO_APPLICATION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
APPLICATION_NAME VARCHAR2 IN
TABLE_NAME VARCHAR2 IN
SCHEMA_NAME VARCHAR2 IN DEFAULT
PROCEDURE CREATE_TEMP_HISTORY_TABLE

...
...

PROCEDURE ENABLE_APPLICATION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
APPLICATION_NAME VARCHAR2 IN
FLASHBACK_ARCHIVE_NAME VARCHAR2 IN DEFAULT

...
...
PROCEDURE REGISTER_APPLICATION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
APPLICATION_NAME VARCHAR2 IN
FLASHBACK_ARCHIVE_NAME VARCHAR2 IN DEFAULT

Thank you, Karin, for spotting that.

ILM in 12c - Flashback Data Archive (importing history)

Importing flashback history from user-maintained history table

If the evolution of the data in a table is being tracked in user-defined tables by means of a user-defined mechanism – such as triggers, then its possible in 12c to import that history into a Oracle-provided flashback archive.

Using a flashback archive enabled table called emp, the following example shows how the history information may be imported from user-defined source.

Steps:

Starting off with a table called EMPLOYEES having employee number and salary.

SQL> select * from fdb_user.emp;

EMPNO SAL

---------- ----------

1 1000

2 2000

3 3000

Assign a flashback archive for the the table.

SQL> alter table fdb_user.emp flashback archive fdba1;

Table altered.

Create the temp history table using dbms_flashback_archive.create_temp_history_table.

SQL> begin

sys.dbms_flashback_archive.create_temp_history_table(

owner_name1 => 'FDB_USER',

table_name1 => 'EMP');

end;

2 3 4 5 6 /

PL/SQL procedure successfully completed.

Populate the history table with a few trial records, specifying the scn, and dml operation.

Using INSERT statements, we create 2 update records for empno=1 in the temporary table.

insert into fdb_user.temp_history
(rid, startscn, endscn, xid, operation, empno, sal)
values
(
null,timestamp_to_scn(to_date('17-09-2014 14:30:00','dd-mm-yyyy hh24:mi:ss')),
timestamp_to_scn(to_date('17-09-2014 14:30:00','dd-mm-yyyy hh24:mi:ss')),
null,'U',1,800);

insert into fdb_user.temp_history
(rid, startscn, endscn, xid, operation, empno, sal
)
values
(
null,timestamp_to_scn(to_date('17-09-2014 14:40:00','dd-mm-yyyy hh24:mi:ss')),
timestamp_to_scn(to_date('17-09-2014 14:50:40','dd-mm-yyyy hh24:mi:ss')),
null,'U',1,900);

Querying the data we created.

SQL> select * from fdb_user.temp_history;

RID STARTSCN ENDSCN XID O EMPNO SAL

--- ---------- ---------- ---------------- - ---------- ----------

560873 560873 U 1 800

561396 562013 U 1 900

Import the history using dbms_flashback_archive.import_history.

This procedure call accepts 3 arguments

1. Schema name of the table being tracked

2. Name of the table being tracked.

3. Name of the history table which contains the modification history.

1 begin

2 dbms_flashback_archive.import_history (

3 owner_name1 => 'FDB_USER',

4 table_name1 => 'EMP',

5 temp_history_name => 'TEMP_HISTORY'

6 );

7* end;

SQL> /

PL/SQL procedure successfully completed.

Query the table with the flashback versions query. Below, the first 2 records are being reported from the imported history. The current value of SAL is 1000 for EMPNO=1.

SQL> SELECT versions_endscn, versions_operation, emp.*

FROM FDB_USER.EMP versions between scn minvalue and maxvalue

where empno =1 order by versions_endscn nulls last

2 3 4 /

VERSIONS_ENDSCN V EMPNO SAL

--------------- - ---------- ----------

560873 U 1 800

562013 U 1 900

1 1000

Tuesday 16 September 2014

ILM features in 12c -- an overview

Exploring the ILM features in Oracle Database 12c.

Organizations need to retain and store ever larger volumes transaction data for longer intervals, and account for evolution of the data.

These needs present information lifecycle management challenges, such as.

Storing large volumes data efficiently for very long times.
Tracking and auditing of changes done to data, and preventing theft/tampering.

Oracle Database 12c has introduced some approaches and solutions.

A couple of the new ILM features are discussed in the links below.

Feature	Description/Link to example	Availability
Feature	Description/Link to example	NONCDB	CDB
In-database archiving	Allows setting of specific rows (old, inactive data) in a table as archived (inactive) and invisible to applications.	Yes	Yes
Flashback Data Archive	Enhanced in 12c, this feature enables tracking of all versions of a record and its evolution over time. Capturing the session context to track a flashback archive enabled table Database hardening. The ability to import change tracking history from a user-provided table	Yes	Yes
Temporal Validity	Defines a time-based validity attribute to a record, hides from applications, those records which do not satisfy validity.	Yes	No
ILM ADO policies	Manage data as it ages, specify how data is stored, where it is stored, and what actions are needed when data meets specific criteria.	Yes	No

ILM in 12c - in-database archiving

About In-database archiving

Older, rarely accessed data may not be used or processed by applications. However there may be regulatory requirements that specify that the data must be retrievable. For such a need, Oracle Database 12c introduces the ability to archive older records within the table itself.

This is known as in-database archiving. When a table is enabled for such archiving, each of its record has a archival state property. By default, a record archived in this manner is not seen by applications. But archived data can be queried by enabling a session parameter ROW_ARCHIVAL_VISIBILITY.

Examples:

Enabling archival for a table

Consider the following employees table, which is contained in a pluggable database named hr.

It has a 10 records.

SQL> select * from employees;

ID NAME START_DAT EMPNO

---------- -------------------- --------- ----------

1 EMP_1 02-JAN-00 12346

2 EMP_2 03-JAN-00 12347

3 EMP_3 04-JAN-00 12348

4 EMP_4 05-JAN-00 12349

5 EMP_5 06-JAN-00 12350

6 EMP_6 07-JAN-00 12351

7 EMP_7 08-JAN-00 12352

8 EMP_8 09-JAN-00 12353

9 EMP_9 10-JAN-00 12354

10 EMP_10 11-JAN-00 12355

10 rows selected.

To enable row level archiving on this table, issue the ALTER TABLE … ROW ARCHIVAL statement as shown.

SQL> alter table emp row archival;

Table altered.

The above alter table statement changes the table structure to add a hidden column to track the archive state of each row. This column can be selected from sql explicitly:

SQL> select id, name, ora_archive_state from employees;

ID NAME ORA_ARCHIVE_STATE

---------- -------------------- --------------------

1 EMP_1 0

2 EMP_2 0

3 EMP_3 0

4 EMP_4 0

5 EMP_5 0

6 EMP_6 0

7 EMP_7 0

8 EMP_8 0

9 EMP_9 0

10 EMP_10 0

10 rows selected.

The value is 0 by default which denotes active status. All rows are returned at this time.

Setting the archival state for a record to archived

An update can be issued to set the ora_archive_state column to a non-zero (archived) status.

A non-zero archival state means that the record is in archived state, and it wont be visible to SQL. The records with an archived status are hidden from sql as seen below. Only 8 out of the 10 total records are listed. The 8 are the records which stil have an active status, following the update. Even a count(*) would only count 8 records.

SQL> update employees set ora_archive_state=1 where id in (1,2);

2 rows updated.

SQL> select id, name, ora_archive_state from employees;

ID NAME ORA_ARCHIVE_STATE

---------- -------------------- --------------------

3 EMP_3 0

4 EMP_4 0

5 EMP_5 0

6 EMP_6 0

7 EMP_7 0

8 EMP_8 0

9 EMP_9 0

10 EMP_10 0

8 rows selected.

Examining the explain plan for the above sql, its seen that a WHERE (WHERE ora_archivestate=’0’) predicate filter was automatically added to the SQL.

Over-riding the default invisibility of archived state records.

If its desired to view archived records , issue an ALTER SESSION statement to alter the ROW ARCHIVAL VISIBILITY property to ALL (the default is ACTIVE) and query the table as before. For example, see below how the select issued after the ALTER SESSION returns inactive as well as active rows.

SQL> alter session set ROW ARCHIVAL VISIBILITY=all;

Session altered.

SQL> select id, name, ora_archive_state from employees;

ID NAME ORA_ARCHIVE_STATE

---------- -------------------- --------------------

1 EMP_1 1

2 EMP_2 1

3 EMP_3 0

4 EMP_4 0

5 EMP_5 0

6 EMP_6 0

7 EMP_7 0

8 EMP_8 0

9 EMP_9 0

10 EMP_10 0

10 rows selected.

Its seen that the plan does not have the WHERE filter now.

Disabling archival for a table

To disable issue the ALTER TABLE <TABLE> NO ROW ARCHIVAL command. The column created to track archival status will be dropped.

SQL> alter table employees no row archival;

Table altered.

ILM in 12c - Flashback Data Archive (session context capture)

Capturing context information in flashback archive records and retrieving it

Discussed below is an example of how Oracle 12c can capture (and retrieve) session context information when a flashback-archive enabled table is modified.

The example has these steps:

Step 1 : Sets up a schema and a table within that schema, having flashback archiving enabled.

Step 2 : After inserting a few rows, they are modified by connecting as a different database user.

Step 3 : The captured context and flashback data are then queried.

Step # 1 Set up the schema

Setting up the tablespace for use by the test schema and flashback archive…

[oracle@laboms ~]$ sqlplus system/oracle@localhost:1521/noncdb3

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 5 13:32:33 2014

Last Successful login time: Fri Sep 05 2014 11:58:06 +08:00

Connected to:

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

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

SQL> create tablespace users

datafile '/u01/app/oracle/oradata/noncdb3/users.ora'

size 10m autoextend on ; 2 3

Tablespace created.

SQL> create tablespace ts_arch

datafile '/u01/app/oracle/oradata/noncdb3/ts_arch.ora'

size 10m ; 2 3

Tablespace created.

Setting up the schema (FDB_USER) and table which will be enabled for flashback archiving…

SQL> create user fdb_user identified by oracle

profile default default tablespace users;

User created.

SQL> alter user fdb_user quota unlimited on ts_arch;

User altered.

Granting the necessary privileges to the user…

SQL> grant create session, create table, unlimited tablespace to fdb_user;

Grant succeeded.

SQL> grant flashback archive administer to fdb_user;

Grant succeeded.

Creating the flashback archive in the database…

SQL> create flashback archive fdba1

tablespace ts_arch retention 1 year; 2

Flashback archive created.

SQL> alter table fdb_user.flashback_trial

2 flashback archive fdba1;

Table altered.

Setting the level of context detail captured in flashback archive… (setting to ALL)

begin

dbms_flashback_archive.set_context_level(level => 'ALL');

end;

2 3 4

PL/SQL procedure successfully completed.

Step # 2 – Creating the archive-enabled table and populating it…

Creation of the actual table whose evolution will be tracked in the flashback archive and populate it with some data.

[oracle@laboms ~]$ sqlplus fdb_user/oracle@localhost:1521/noncdb3

...

SQL> create table flashback_trial as

select object_id, object_name from all_objects where 2=1; 2

Table created.

SQL> insert into flashback_trial select object_id,object_name from all_objects where rownum<=5;

5 rows created.

SQL> commit;

Commit complete.

Viewing the data…

SQL> select * from flashback_trial;

OBJECT_ID OBJECT_NAME

--------- --------------------

133 ORA$BASE

142 DUAL

143 DUAL

356 MAP_OBJECT

453 SYSTEM_PRIVILEGE_MAP

Connecting as the system user and modifying the data…

[oracle@laboms ~]$ sqlplus system/oracle@localhost:1521/noncdb3

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 5 14:20:07 2014

Last Successful login time: Fri Sep 05 2014 13:32:33 +08:00

Connected to:

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

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

SQL> alter session set current_schema=fdb_user;

Session altered.

Now we will delete the record with object_id=143;

SQL> select * from fdb_user.flashback_trial where object_id=143;

OBJECT_ID OBJECT_NAME

---------- ----------------------------------------

142 DUAL

SQL> delete fdb_user.flashback_trial where object_id=143;

1 row deleted.

SQL> commit;

Commit complete.

Querying the USERENV context for some attributes from the session which deleted the record.

SQL> select sys_context('USERENV','SESSIONID') sessionid,

2 sys_context('USERENV','SID') sid,

3 sys_context('USERENV','CURRENT_SCHEMA') schema,

4 sys_context('USERENV','IP_ADDRESS') ip_addr from dual;

SESSIONID SID SCHEMA IP_ADDR

-------------------- -------------------- -------------------- ------------------------------

120085 24 FDB_USER 192.168.1.108

Step # 3 – Querying the information stored in the flashback archive.

Retrieving the transaction id of the delete operation using flashback versions query…

SQL > select versions_xid, versions_operation, flashback_trial.*

from fdb_user.flashback_trial versions between scn minvalue and maxvalue;

VERSIONS_XID V OBJECT_ID OBJECT_NAME

-------------------- - ---------- --------------------

06000A009C010000 D 143 DUAL

143 DUAL

356 MAP_OBJECT

453 SYSTEM_PRIVILEGE_MAP

The way to retrieve the context is by using the transaction id which marked the data change. To get the context, use the DBMS_FLASHBACK_ARCHIVE.get_sys_context() procedure as shown

Parameter	Purpose	Example value
Xid	Transaction identifier. Use the versions_xid returned from the flashback versions query	06000A009C010000
Namespace	Name of the context	‘USERENV’ typically
Parameter	The attribute to retrieve	Any of the attributes that of the USERENV context

In the example below, the context information is retrieved using the dbms_flashback_archive.get_sys_context procedure call (output reformatted):

SQL > select sys.dbms_flashback_archive.get_sys_context(xid       => hextoraw('06000A009C010000'),
                  namespace => 'USERENV',parameter => 'SESSION_USER') sess_user,
       sys.dbms_flashback_archive.get_sys_context(xid       => hextoraw('06000A009C010000'),
                 namespace => 'USERENV',parameter => 'SESSIONID') sess_id,
       sys.dbms_flashback_archive.get_sys_context(xid       => hextoraw('06000A009C010000'),
                 namespace => 'USERENV',parameter => 'IP_ADDRESS') ip_addr
FROM DUAL;

SESS_USER SESS_ID IP_ADDR

-------------------- -------------------- ------------------------------

SYSTEM 120085 192.168.1.108

The context details captured provide additional information about the environment in which the change took place.