General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx .
Purpose
Support user applications' schema evolution during application upgrade and other table maintenance tasks beyond DDL statements supported by Flashback Data Archive.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
NODROP
BINARY_INTEGER
1
NOCOMMIT
BINARY_INTEGER
2
NODELETE
BINARY_INTEGER
4
Dependencies
DBMS_FDA_LIB
SYS_FBA_CONTEXT_AUD
SYS_FBA_TSFA
SQLOBJ$AUXDATA
SYS_FBA_CONTEXT_LIST
SYS_FBA_USERS
SYS_FBA_APP
SYS_FBA_DL
SYS_MFBA_NCHANGE
SYS_FBA_APP_TABLES
SYS_FBA_FA
SYS_MFBA_NROW
SYS_FBA_BARRIERSCN
SYS_FBA_PARTITIONS
SYS_MFBA_NTCRV
SYS_FBA_COLS
SYS_FBA_PERIOD
SYS_MFBA_STAGE_RID
SYS_FBA_CONTEXT
SYS_FBA_TRACKEDTABLES
SYS_MFBA_TRACKED_TXN
Documented
Yes: Packages and Types Reference
Exceptions
Error Code
Reason
ORA-06512
Unable to disassociate or re-associate Flashback Data Archive table <schema_name>.<table_name>
ORA-55602
The table "<schema_name"."<table_name>" is not enabled for Flashback Archive
ORA-55603
Invalid flashback archive or valid time period command
ORA-55634
Flashback Data Archive enabled table "string"."string" has different definition from its history table
ORA-55636
Flashback Data Archive enabled table "string"."string" has different definition from its history table
ORA-55637
Flashback Data Archive enabled table <schema_name>.<table_name> is not in the correct compliance
First Available
11.2
Related System Privileges
FLASHBACK ARCHIVE ADMINISTER is required to import user generated history, set context level, and tamper-proof tables.
Security Model
Owned by SYS with EXECUTE granted to the DBA role
Source
{ORACLE_HOME}/rdbms/admin/dbmstran.sql
Subprograms
ADD_TABLE_TO_APPLICATION
Takes an application name and marks a table in it as a security table
dbms_flashback_archive.add_table_to_application(
application_name IN VARCHAR2,
table_name IN VARCHAR2,
schema_name IN VARCHAR2 DEFAULT '');
See REGISTER_APPLICATION Demo Below
CREATE_TEMP_HISTORY_TABLE
Creates a table called TEMP_HISTORY with the correct definition in schema
dbms_flashback_archive.create_temp_history_table(
owner_name1 IN VARCHAR2,
table_name1 IN VARCHAR2);
conn sys@pdbdev as sysdba
CREATE FLASHBACK ARCHIVE uw_archive
TABLESPACE uwdata
QUOTA 15G
RETENTION 30 DAY;
CREATE FLASHBACK ARCHIVE uw_archive
*
ERROR at line 1:
ORA-65131: The feature Flashback Data Archive is not supported in a pluggable database.
DISABLE_APPLICATION
Takes an application name and marks a table in it as a security table
dbms_flashback_archive.disable_application(application_name IN VARCHAR2);
See REGISTER_APPLICATION Demo Below
DISABLE_ASOF_VALID_TIME
Disables session level valid-time flashback
dbms_flashback_archive.disable_asof_valid_time;
exec dbms_flashback_archive.disable_asof_valid_time ;
DISASSOCIATE_FBA
Disassociates the given table from the flashback data archive
dbms_flashback_archive.open_fba(
owner_name IN VARCHAR2,
table_name IN VARCHAR2);
See REASSOCIATE_FBA Demo Below
DROP_APPLICATION
emoves an application from the applications list
dbms_flashback_archive.drop_application(application_name IN VARCHAR2);
See REGISTER_APPLICATION Demo Below
ENABLE_APPLICATION
Takes an application name and enables flashback archive on all the security tables for this application
dbms_flashback_archive.enable_application(
application_name IN VARCHAR2,
flashback_archive_name IN VARCHAR2 DEFAULT '');
See REGISTER_APPLICATION Demo Below
ENABLE_AT_VALID_TIME
This procedure enables session level valid time flashback
dbms_flashback_archive.enable_at_valid_time(
level IN VARCHAR2,
query_time IN TIMESTAMP DEFAULT SYSTIMESTAMP);
exec dbms_flashback_archive.enable_at_valid_time ('CURRENT');
EXTEND_MAPPINGS
Extends time mappings to times in the past
dbms_flashback_archive.extend_mappings;
exec dbms_flashback_archive.extend_mappings ;
GET_SYS_CONTEXT
Gets the context previously selected by the SET_CONTEXT_LEVEL Procedure
dbms_flashback_archive.get_sys_context(
xid IN RAW,
namespace IN VARCHAR2,
parameter IN VARCHAR2)
RETURN VARCHAR2;
TBD
IMPORT_HISTORY
Imports history from a table called TEMP_HISTORY in the given schema
dbms_flashback_archive.import_history(
owner_name1 IN VARCHAR2,
table_name1 IN VARCHAR2,
temp_history_name IN VARCHAR2 DEFAULT 'TEMP_HISTORY',
options IN BINARY_INTEGER DEFAULT 0);
TBD
LOCK_DOWN_APPLICATION
Takes an application name and makes all the security tables read-only. The group called SYSTEM cannot be locked
dbms_flashback_archive.lock_down_application(application_name IN VARCHAR2);
See REGISTER_APPLICATION Demo Below
PURGE_CONTEXT
Purges the context to be saved selected by SET_CONTEXT_LEVEL
dbms_flashback_archive.purge_context;
See SET_CONTEXT_LEVEL Demo Below
REASSOCIATE_FBA
Reassociates the history table with the base table which will fail if the user has not performed corresponding structural modifications (through DDL statements) to the history table.
dbms_flashback_archive.reassociate_fba(
owner_name IN VARCHAR2,
table_name IN VARCHAR2);
ALTER SYSTEM SET undo_retention = 1 SCOPE=MEMORY;
CREATE FLASHBACK ARCHIVE uw_archive
TABLESPACE uwdata
QUOTA 10M
RETENTION 30 DAY;
ALTER TABLE servers FLASHBACK ARCHIVE uw_archive;
-- perform DML on the servers table until flashback archive tables are created
SELECT table_name
FROM user_all_tables
WHERE table_name LIKE 'SYS_FBA%';
DELETE FROM SYS_FBA_HIST_73776
WHERE srvr_id = 515;
exec dbms_flashback_archive.disassociate_fba ('UWCLASS', 'SERVERS');
DELETE FROM SYS_FBA_HIST_73776
WHERE srvr_id = 515;
exec dbms_flashback_archive.reassociate_fba ('UWCLASS', 'SERVERS');
-- make the corresponding change to the fba table
exec dbms_flashback_archive.reassociate_fba ('UWCLASS', 'SERVERS');
REGISTER_APPLICATION
Takes an application name and optionally a flashback archive, and registers an application for database hardening
dbms_flashback_archive.register_application(
application_name IN VARCHAR2,
flashback_archive_name IN VARCHAR2 DEFAULT '');
conn / as sysdba
ALTER SESSION SET CONTAINER = PDBDEV;
exec dbms_flashback_archive.register_application ('UWAPP', 'UW_ARCHIVE');
SELECT *
FROM sys_fba_app;
SELECT *
FROM sys_fba_app_tables;
exec dbms_flashback_archive.add_table_to_application ('UWAPP', 'SERVERS', 'UWCLASS');
SELECT *
FROM sys_fba_app_tables;
exec dbms_flashback_archive.lock_down_application ('UWAPP');
exec dbms_flashback_archive.unlock_application ('UWAPP');
exec dbms_flashback_archive.disable_application ('UWAPP');
exec dbms_flashback_archive.enable_application ('UWAPP', 'SERVERS', 'UWCLASS');
exec dbms_flashback_archive.remove_table_from_application ('UWAPP', 'SERVERS', 'UWCLASS');
SELECT *
FROM sys_fba_app_tables;
REMOVE_TABLE_FROM_APPLICATION
Takes an application name and marks a table in it as no longer being a security table
dbms_flashback_archive.remove_table_from_application (
application_name IN VARCHAR2,
table_name IN VARCHAR2,
schema_name IN VARCHAR2 DEFAULT '');
See REGISTER_APPLICATION Demo Above
SET_CONTEXT_LEVEL
Defines how much of the user context is to be saved
dbms_flashback_archive.set_context_level(level IN VARCHAR2);
-- valid values are ALL, TYPICAL, and NONE
exec dbms_flashback_archive.set_context_level ('ALL');
exec dbms_flashback_archive.purge_context ;
-- I have not yet found where any of this is saved in the data dictionary ... it is not in the SYS_FBA_CONTEXT tables
UNLOCK_APPLICATION
Reverses a lock down performed with LOCK_DOWN_APPLICATION
dbms_flashback_archive.unlock_application(application_name IN VARCHAR2);
See REGISTER_APPLICATION Demo Above