·
It is able to record access
of specific columns, for a specific user.
·
It can audit for exact
statement, collect the complete SQL text and bind values.
·
Because of being able audit
at a granular level, auditing specific columns, in most cases it will thus store less rows and result in
lesser resource usage on the database server.
·
Unlike the standard
auditing, no parameter setting or a database restart is required.
·
It can easily be turned
on-off with a database restart.
The Fine Grained Audit policies can be set making it
possible to audit objects specifying access based on the following.
·
Auditing on views or
tables.
- · Objects being audited must already exist.
- · SELECT, INSERT, UPDATE, and DELETE are the four types of statements that can be audited.
- · Can audit specific columns.
- · Can audit based on specific column values.
Restrictions
- · The maximum FGA policies on any object (table/view) is 256
- · FGA should not be applied to LOB columns.
- · For a table with FGA policy define, if Fast Path insert operation is run, it will internally convert it to a conventional insert.
Configuring Oracle FGA
FGA is implemented via PL/SQL package named DBMS_FGA. It
uses the concept of policies which is basically an audit rule. When you define
a policy, it will have store information on the rows and columns that are
required to be monitored. While defining a policy you will use a predicate,
much like the WHERE clause in SQL.
The DBMS_FGA package has four procedures. Below is the
description of each of them.
·
ADD_POLICY Creates an
audit policy using the supplied predicate as the audit condition.
·
DROP_POLICY Drops an
audit policy.
·
DISABLE_POLICY Disables an audit
policy.
·
ENABLE_POLICY Enables an audit policy which is
disabled
Oracle Database Security controls the level of auditing and
the location where the audit data is stored using the parameters below. The
type of data collected is either just the statement or the bind values as well.
The location of storing the data is either the FGA_LOG$ table or OS files with
an option of XML format.
·
DBMS_FGA.DB DB
in SYS.FGA_LOG$ No SQL text or bind
values
·
DBMS_FGA.DB +
DBMS_FGA.EXTENDED DB
in SYS.FGA_LOG$ Includes SQL text or
bind values
·
DBMS_FGA.XML OS
in XML file Omits SQL
text or bind values
·
DBMS_FGA.XML +
DBMS_FGA.EXTENDED OS
in XML file Includes SQL text or bind values
When configuring FGA we can specify the columns names that
we want to audit usage for. When multiple columns are involved we can configure
auditing to either audit based on access to all the columns specified or just
any column using the ALL_COLUMNS or the ANY_COLUMNS directive.
·
DBMS_FGA.ALL_COLUMNS Only audits when ALL such columns are
referenced
·
DBMS_FGA.ANY_COLUMNS Audits when ANY of the columns are
referenced
Create FGA Policy
To test this feature, let’s first start out by first
creating the application owner and an application user. We will turn on
auditing on the objects in the APP_OWNER schema when the APP_SERVER user
accesses them.
Setup Environment
CREATE USER
app_owner IDENTIFIED BY eWsdjsg#Lw DEFAULT TABLESPACE gg_data;
GRANT CONNECT,
RESOURCE TO app_owner;
CREATE USER
app_server IDENTIFIED BY hsdjfghjsg#6W DEFAULT TABLESPACE GG_DATA;
GRANT CONNECT,
RESOURCE TO app_server;
CREATE TABLE
app_owner.pim_data_tab
(NAME varchar2(1000),
SS_NUM
varchar2(1000),
DOB date,
CC_NUMBER
varchar2(1000));
GRANT
SELECT,UPDATE ON app_owner.pim_data_tab TO app_server;
INSERT INTO
app_owner.pim_data_tab values ('TEST_USER','666-56-3268',sysdate,
'256-664-646-7263');
Audit Policy on specific Table and specific
user
This creates
a policy on the object PIM_DATA_TAB and creates audit records when the uses
APP_SERVER access the PIM_DATA_TAB table.
EXECUTE
DBMS_FGA.ADD_POLICY( -
OBJECT_SCHEMA =>
'APP_OWNER', -
OBJECT_NAME =>
'PIM_DATA_TAB', -
POLICY_NAME =>
'APP_OWNER_PIM_DATA_TAB', -
AUDIT_CONDITION
=> 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APP_SERVER'' ', -
AUDIT_COLUMN =>
'CC_NUMBER', -
ENABLE => TRUE, -
STATEMENT_TYPES
=> 'UPDATE');
PL/SQL procedure
successfully completed.
SET LINES 300;
COL object_schema
FOR a15;
COL policy_name FOR
a30;
COL policy_text FOR
a55;
COL policy_column
FOR a20;
COL audit_trail FOR
a20;
SELECT
object_schema, policy_name, policy_text,
policy_column,
audit_trail, enabled
FROM
dba_audit_policies;
Audit Policy on specific Table and user LIKE
operator
Here is an
additional example of auditing set for any user whose name is LIKE ‘APP_SEC%’
accessing the PIM_DATA_TAB table.
EXECUTE
DBMS_FGA.ADD_POLICY( -
OBJECT_SCHEMA =>
'APP_OWNER', -
OBJECT_NAME => 'PIM_DATA_TAB',
-
POLICY_NAME =>
'APP_OWNER_PIM_DATA_TAB', -
AUDIT_CONDITION
=> 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') like ''APP_SE%'' ', -
AUDIT_COLUMN =>
'CC_NUMBER', -
ENABLE => TRUE, -
STATEMENT_TYPES
=> 'UPDATE');
Audit Policy to exclude user …
This policy
audits all users except APP_SERVER user.
EXECUTE
DBMS_FGA.ADD_POLICY( -
OBJECT_SCHEMA =>
'APP_OWNER', -
OBJECT_NAME =>
'PIM_DATA_TAB', -
POLICY_NAME =>
'APP_OWNER_PIM_DATA_TAB', -
AUDIT_CONDITION
=> 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') <> ''APP_SERVER'' ',
AUDIT_COLUMN =>
'CC_NUMBER', -
ENABLE => TRUE, -
STATEMENT_TYPES
=> 'UPDATE');
Audit Policy all objects in a schema
Using
‘UDIT_COLUMN => null” directs auditing when ANY of the columns in ANY of the
tables in the APP_OWNER schema are accessed.
EXECUTE
DBMS_FGA.ADD_POLICY( -
OBJECT_SCHEMA =>
'APP_OWNER', -
OBJECT_NAME =>
null, -
POLICY_NAME =>
'APP_OWNER_PIM_DATA_TAB', -
AUDIT_CONDITION
=> 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') <> ''APP_SERVER'' ',
AUDIT_COLUMN =>
null, -
ENABLE => TRUE, -
STATEMENT_TYPES
=> 'UPDATE');
Audit Policy with in-string condition
This policy
shows use of a built in function specified in the AUDIT_CONDITION option.
EXECUTE
DBMS_FGA.ADD_POLICY( -
OBJECT_SCHEMA =>
'APP_OWNER', -
OBJECT_NAME =>
'PIM_DATA_TAB', -
POLICY_NAME =>
'APP_OWNER_PIM_DATA_TAB', -
AUDIT_CONDITION
=> 'instr(NAME,''MAN'') > 0', -
AUDIT_COLUMN =>
'CC_NUMBER');
Event handling
DISABLE
POLICY
The
DISABLE_POLICY function can be used to immediately turn of the auditing policy.
However this will NOT be effective for already connected to the database.
EXECUTE
DBMS_FGA.DISABLE_POLICY(OBJECT_SCHEMA => 'APP_OWNER', -
OBJECT_NAME =>
'PIM_DATA_TAB', -
POLICY_NAME =>
'APP_OWNER_PIM_DATA_TAB');
ENABLE
POLICY
This build
in function is used to enable an existing FGA policy.
EXECUTE
DBMS_FGA.ENABLE_POLICY(OBJECT_SCHEMA => 'APP_OWNER', -
OBJECT_NAME =>
'PIM_DATA_TAB', -
POLICY_NAME =>
'APP_OWNER_PIM_DATA_TAB');
DROP
POLICY
The
DROP_POLICY function is used to drop unwanted FGA policies.
EXECUTE
DBMS_FGA.DROP_POLICY(OBJECT_SCHEMA => 'APP_OWNER', -
OBJECT_NAME => 'PIM_DATA_TAB',
-
POLICY_NAME =>
'APP_OWNER_PIM_DATA_TAB');
FGA Monitoring Views
Audited data
visibile for FGA from:
SYS.FGA_LOG$
<- audit="" built="" code="" database:="" db_user="" dba_fga_audit_trail="" from="" in="" it.="" o:p="" on="" os_user="" policies="" select="" set="" sql_text="" the="" timestamp="" view="">
SELECT
object_schema, object_name, policy_owner, policy_name, policy_text,
policy_column,
enabled, sel, ins, upd, del , audit_trail
FROM
dba_audit_policies;
FGA Monitoring
Views
DBA_FGA_AUDIT_TRAIL
However at
times we may want to view the audit trail from both the default auditing
mechanism and from the FGA table. This can easily be achieved by viewing the
DBA_COMMON_AUDIT_TRAIL which has information from both auditing methods.
You can use
the following query to view the auditing information.
SELECT
to_char(timestamp,'mm/dd/yy hh24:mi') timestamp,
object_schema,
object_name, policy_name, statement_type
fROM
dba_fga_audit_trail
WHERE db_user =
'SCOTT';
Purging
FGA Audit data
Manually
TRUNCATE TABLE
fga_log$;
DELETE FROM fga_log$
WHERE timestamp# < sysdate-14; [/code] • Using FGA Audit Purge Job
Initialize
first:
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
DEFAULT_CLEANUP_INTERVAL
=> 12 );
END;
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
USE_LAST_ARCH_TIMESTAMP
=> TRUE );
END;
Clearing
All DBMS_AUDIT_MGMT.INIT_CLEANUP settings
BEGIN
DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
AUDIT_TRAIL_TYPE
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
DELETE:
DROP USER app_owner
CASCADE;
DROP USER
app_server;
No comments:
Post a Comment