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.
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.