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.