Oracle Fine Grained Auditing

Regulations such as HIPPA have placed upon Oracle Database Administrators (DBAs) the need to audit DDL (database schema changes), DML (inserts/updates/deletes) as well as select access to what is considered “protected information”. Auditing changes has always had value and was typical auditing done in most critical databases. Auditing changes would allow information to be gathered to show when a change happened and who made the change. Another audit need has become more critical due to regulations and that is select or read access to “protected information”. Auditing is now required to gather information on when and who read or accessed certain information.

Oracle auditing has existed in the Oracle database for quite some time and is used to gather information on changes and access to the data in the database. It has provided the ability to audit inserts, updates and deletes on table as well as the ability to audit object creations, modifications and removals as auditing in the database progressed later versions allowed the auditing of selects. Auditing is also able to audit log on and log off from the database and many other operations. However the auditing is always at a high level, what if the need was to only audit under more specific circumstances. To get this level of auditing might require a trigger that must be coded and there fore the code developed and maintained so that some level of criteria could be used to determine if an audit record should be written or not. Oracle Fine Grained Auditing provides the ability to add criteria based auditing to gather information (data) on what has changed or been accessed within the database without the need to write trigger code.


Components for FGA

Oracle FGA is setup and managed though the dbms_fga package and all audit information gathered is accessed via the dba_fga_audit_trail view, the actual sys table that the audit information goes into is fga_log$.

Examine the definition of the dbms_fga package, the dba_fga_audit_trail view and the sys table fga_log$ as these are the objects that will be used in the management of FGA.


SQL> desc dbms_fga
PROCEDURE ADD_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT
AUDIT_COLUMN VARCHAR2 IN DEFAULT
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
PROCEDURE DISABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE DROP_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE ENABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
ENABLE BOOLEAN IN DEFAULT

SQL> desc dba_fga_audit_trail
Name Null? Type
----------------------------------------- -------- ------------------
SESSION_ID NOT NULL NUMBER
TIMESTAMP DATE
DB_USER VARCHAR2(30)
OS_USER VARCHAR2(255)
USERHOST VARCHAR2(128)
CLIENT_ID VARCHAR2(64)
ECONTEXT_ID VARCHAR2(64)
EXT_NAME VARCHAR2(4000)
OBJECT_SCHEMA VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
POLICY_NAME VARCHAR2(30)
SCN NUMBER
SQL_TEXT NVARCHAR2(2000)
SQL_BIND NVARCHAR2(2000)
COMMENT$TEXT VARCHAR2(4000)
STATEMENT_TYPE VARCHAR2(7)
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTIONID RAW(8)
STATEMENTID NUMBER
ENTRYID NUMBER
OBJ_EDITION_NAME VARCHAR2(30)

SQL> desc sys.fga_log$
Name Null? Type
----------------------------------------- -------- -------------------------
SESSIONID NOT NULL NUMBER
TIMESTAMP# DATE
DBUID VARCHAR2(30)
OSUID VARCHAR2(255)
OSHST VARCHAR2(128)
CLIENTID VARCHAR2(64)
EXTID VARCHAR2(4000)
OBJ$SCHEMA VARCHAR2(30)
OBJ$NAME VARCHAR2(128)
POLICYNAME VARCHAR2(30)
SCN NUMBER
SQLTEXT VARCHAR2(4000)
LSQLTEXT CLOB
SQLBIND VARCHAR2(4000)
COMMENT$TEXT VARCHAR2(4000)
PLHOL LONG
STMT_TYPE NUMBER
NTIMESTAMP# TIMESTAMP(6)
PROXY$SID NUMBER
USER$GUID VARCHAR2(32)
INSTANCE# NUMBER
PROCESS# VARCHAR2(16)
XID RAW(8)
AUDITID VARCHAR2(64)
STATEMENT NUMBER
ENTRYID NUMBER
DBID NUMBER
LSQLBIND CLOB
OBJ$EDITION VARCHAR2(30)


Define Auditing Policies

To implement Oracle FGA for a successful audit trail the audit rules and policies must be determined and documented. The organization must determine the compliance rules that the organization is subject to, such as HIPPA and SOX. Identify the auditing requirements to fulfill the compliance rules such as DDL auditing (create table, alter table/index, drop table/index, insert, update and delete on protected data as well as select/read access to protected data) The organization is also free to add additional auditing for its own internal use potentially for security purposes for use in forensic activities in cases of security breaches or activity monitoring.


Setup FGA

To setup FGA policies that trigger audit records will need to be defined using policies. The policies will be created based on the audit rules and policies identified that the organization is subject to as well as auditing rule the business has determined itself wants to have possibly for better tracking for security purposes.


-- drop existing audit policy
execute dbms_fga.drop_policy ('SCOTT','EMP','SALARY_INCREASES') ;

-- Audit all Salary Changes to EMP
begin
dbms_fga.add_policy(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'SALARY_INCREASES',
audit_column => 'SAL',
enable => true, statement_types => 'UPDATE', audit_trail => DBMS_FGA.DB_EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS
);
end;
/

-- drop existing audit policy
execute dbms_fga.drop_policy ('SCOTT','EMP','NEW_EMP_HIGH_SALARY') ;

-- Audit all new employee creations with salary > 2000
begin
dbms_fga.add_policy(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'NEW_EMP_HIGH_SALARY',
audit_condition => 'SAL>=2000',
enable => true, statement_types => 'INSERT', audit_trail => DBMS_FGA.DB_EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS
);
end;
/

-- drop existing audit policy
execute dbms_fga.drop_policy ('SCOTT','BONUS','BONUS_CHANGES') ;

-- Audit all changes on BONUS
begin
dbms_fga.add_policy(
object_schema => 'SCOTT',
object_name => 'BONUS',
policy_name => 'BONUS_CHANGES',
enable => true, statement_types => 'INSERT,UPDATE,DELETE', audit_trail => DBMS_FGA.DB_EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS
);
end;
/


Show Audit Policies

set linesize 120
set pagesize 50

TTITLE 'FGA Policies'
COL object_schema FORMAT A10 HEADING 'Schema'
COL object_name FORMAT A20 HEADING 'Object Name' WRAP
COL policy_name FORMAT A16 HEADING 'Policy Name' WRAP
COL policy_text FORMAT A24 HEADING 'Policy Text' WRAP
COL policy_column FORMAT A16 HEADING 'Policy Column' WRAP
COL enabled FORMAT A05 HEADING 'Enabled'
COL siud FORMAT A04 HEADING 'SIUD'

SELECT policy_name, policy_text, policy_column, enabled, object_schema, object_name,
DECODE(sel,'YES','Y','N') DECODE(ins,'YES','Y','N')
DECODE(upd,'YES','Y','N') DECODE(del,'YES','Y','N') siud
FROM dba_audit_policies ;

TTITLE OFF


Show Audit Results

Using Fine Grained Audit Trail:

set linesize 120
set pagesize 50

TTITLE 'Fine-Grained Auditing (FGA) Audit Trail'
COL auditdate FORMAT A20 HEADING 'Date/Time'
COL policy_name FORMAT A16 HEADING 'Policy Name' WRAP
COL object_schema FORMAT A10 HEADING 'Schema'
COL object_name FORMAT A20 HEADING 'Object Name' WRAP
COL db_user FORMAT A10 HEADING 'User'
COL sql_text FORMAT A36 HEADING 'SQL Text' WRAP
SELECT TO_CHAR(timestamp,'mm/dd/yyyy hh24:mi:ss') auditdate,
db_user,
object_schema,
object_name,
policy_name,
sql_text
FROM dba_fga_audit_trail
ORDER BY timestamp ;

TTITLE OFF

Could also use the combined audit trail as well to query the FGA Audit Trail this would allow the use of a single query/report for the entire audit trail:

Combined Audit Trail:

SET linesize 120
SET pagesize 50

TTITLE 'Standard/FGA Audit Trail'
COL audittype FORMAT A03 HEADING 'Audit Type'
COL db_user FORMAT A10 HEADING 'User'
COL object_schema FORMAT A06 HEADING 'Schema'
COL object_name FORMAT A20 HEADING 'Object Name' WRAP
COL policy_name FORMAT A16 HEADING 'Policy Name' WRAP
COL auditdate FORMAT A20 HEADING 'Date/Time'
COL sql_text FORMAT A32 HEADING 'SQL Text' WRAP

SELECT DECODE(audit_type, 'Fine Grained Audit', 'FGA’, 'Standard Audit', 'STD', 'U')
auditype,
db_user,
object_schema,
object_name,
policy_name,
TO_CHAR(extended_timestamp,'mm/dd/yyyy hh24:mi:ss') auditdate,
sql_text
FROM dba_common_audit_trail
WHERE db_user NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN')
ORDER BY extended_timestamp, db_user, object_schema, object_name ;

TTITLE OFF



Testing Results

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.


Wed Jan 30 page 1
FGA Policies

Policy Name Policy Text Policy Column Enabl Schema Object Name SIUD
----------- ----------- ------------- ----- ------ ----------- ----
BONUS_CHANGES YES SCOTT BONUS NYYY
SALARY_INCREASES SAL YES SCOTT EMP NNYN
NEW_EMP_HIGH_SALARY SAL>=2000 YES SCOTT EMP NYNN

1 row created.

1 row updated.

1 row deleted.

Commit complete.

1 row created.

Commit complete.


Wed Jan 30 page 1
Fine-Grained Auditing (FGA) Audit Trail

Date User Schema Object Name Policy Name SQL Text
---------- ---------- ---------- ----------------- ---------------- ------------------------------------
01/30/2008 SYSTEM SCOTT BONUS BONUS_CHANGES delete from scott.bonus
08:07:28
01/30/2008 SYSTEM SCOTT BONUS BONUS_CHANGES insert into scott.bonus values ('BIG
08:07:28 BOSSMAN', 'THEMAN', 10000, 50000)
01/30/2008 SYSTEM SCOTT EMP SALARY_INCREASES update scott.emp set sal=4000 where
08:07:28 empno=7844
01/30/2008 SYSTEM SCOTT EMP NEW_EMP_HIGH_SAL insert into scott.emp values (8000,
08:07:28 ARY 'THEMAN', 'BIGBOSSMAN', NULL, TO_DATE('01-JAN-2008','DD-MON-YYYY'), 10000, 50000, 10)

Managing FGA

We have to manage this audit information being generated as if we do not the amount of audit information will continue to grow and potential lead to performance issues within the database. Therefore was must move audit information out of the system table to another table or even another database so that we can keep it in accordance with the business rules and regulations. After all the audit data is moved and the audit information verified that it has been properly moved we will need to purge the audit data from the system table.

A simple way to manage the fine grained auditing audit trail would be to create a package and use that package to move the data in the audit trail for more long term storage and remove the audit records that have been moved. Simple development of a PL/SQL package that can be scheduled within the database to keep the system audit trail clean by placing in a staging area that can be used by Extraction Transformation Load (ETL) processes for longer term storage and reporting.


Example Package, not complete, but just to give an idea of how this might be approached:

CREATE OR REPLACE PACKAGE manage_audit_trail
IS
PROCEDURE move_fga (p_msg OUT VARCHAR2) ;
PROCEDURE remove_fga (p_days IN NUMBER, p_msg OUT VARCHAR2) ;
PROCEDURE remove_audit (p_days IN NUMBER, p_msg OUT VARCHAR2) ;
END ;
/

CREATE OR REPLACE PACKAGE BODY manage_audit_trail
IS
-- Move Fine Grained Audit data to a staging table
PROCEDURE move_fga (p_msg OUT VARCHAR2)
IS
BEGIN
MERGE INTO AUDIT_STAGE a
USING dba_fga_audit_trail at
ON
(a.SESSION_ID = at.SESSION_ID
a.TIMESTAMP = at.TIMESTAMP
a.DB_USER = at.DB_USER
a.ENTRYID = at.ENTRYID)
WHEN NOT MATCHED THEN
INSERT (a.SESSION_ID, a.TIMESTAMP, a.DB_USER,
a.OS_USER, a.USERHOST, a.CLIENT_ID,
a.ECONTEXT_ID, a.EXT_NAME, a.OBJECT_SCHEMA,
a.OBJECT_NAME, a.POLICY_NAME, a.SCN,
a.SQL_TEXT, a.SQL_BIND, a.COMMENT$TEXT,
a.STATEMENT_TYPE, a.EXTENDED_TIMESTAMP,
a.PROXY_SESSIONID, a.GLOBAL_UID,
a.INSTANCE_NUMBER, a.OS_PROCESS,
a.TRANSACTIONID, a.STATEMENTID,
a.ENTRYID, a.OBJ_EDITION_NAME)
VALUES
(at.SESSION_ID, at.TIMESTAMP, at.DB_USER,
at.OS_USER, at.USERHOST, at.CLIENT_ID,
at.ECONTEXT_ID, at.EXT_NAME, at.OBJECT_SCHEMA,
at.OBJECT_NAME, at.POLICY_NAME, at.SCN,
at.SQL_TEXT, at.SQL_BIND, at.COMMENT$TEXT,
at.STATEMENT_TYPE, at.EXTENDED_TIMESTAMP,
at.PROXY_SESSIONID, at.GLOBAL_UID,
at.INSTANCE_NUMBER, at.OS_PROCESS,
at.TRANSACTIONID, at.STATEMENTID,
at.ENTRYID, at.OBJ_EDITION_NAME) ;

EXCEPTION
WHEN OTHERS THEN
p_msg := SQLERRM ;
END move_fga ;

-- Removes records from the fga audit trail (sys.fga_log$) <=
-- sysdate – number of days passed. # of days defaults to 30 days
PROCEDURE remove_fga (p_days IN NUMBER, p_msg OUT VARCHAR2)
IS
BEGIN
-- initialize p_msg to null
p_msg := NULL ;

-- Check number of days if not default to 30 days

IF p_days IS NULL then
p_days = 30 ;
END IF ;

-- remove the records from the fine grained audit trail
delete
from sys.fga_log$
where timestamp# <= SYSDATE – p_days ;

EXCEPTION
WHEN OTHERS THEN
p_msg := SQLERRM ;
END remove_fga ;

-- Removes records from the audit trail (sys.aud$) <=
-- sysdate – number of days passed. # of days defaults to 30 days
PROCEDURE remove_audit (p_days IN NUMBER, p_msg OUT VARCHAR2)
IS
BEGIN
-- initialize p_msg to null ;
p_msg := NULL ;

-- Check number of days if not default to 30 days
IF p_days IS NULL then
p_days = 30 ;
END IF ;

-- remove the records from the audit trail
delete
from sys.aud$
where timestamp# <= SYSDATE – p_days ;

EXCEPTION
WHEN OTHERS THEN
p_msg := SQLERRM ;
END remove_audit ;
END ;

/


Summary

Oracle Fine Grained Auditing provides the Oracle DBAs a great way to gather the required audit information, define criteria for the gathering of the audit information and actually helps simplify managing the audit data by consolidating all audit data into a single audit trail making the movement and management of audit data for long term reporting simpler. The key to a complete successful audit trail is the ability to report the audit information to answer the questions coming from regulation agencies and internal business entities, like security and compliance offices.