For an Oracle Database, we have several ways to audit user activity. With this posting, we will take a closer look at Fine Grained Auditing (FGA), working together with the very useful standard function SYS_CONTEXT. The demonstration is done with 11gR2 but should work very similar with older versions down to 9i, where FGA was introduced, if I recall that right.

For a general overview about auditing, see my presentation slides here.

The standard function SYS_CONTEXT shows us many interesting properties of the connected sessions and is not only useful in the context of auditing:

SQL> connect sys/oracle@orcl as sysdba
Connected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0    Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> set serveroutput on
begin
dbms_output.put_line('AUTENTICATION_METHOD: '||sys_context('userenv','authentication_method'));
dbms_output.put_line('CLIENT IDENTIFIER:    '||sys_context('userenv','client_identifier'));
dbms_output.put_line('CURRENT_SCHEMA:       '||sys_context('userenv','current_schema'));
dbms_output.put_line('CLIENT HOSTNAME:      '||sys_context('userenv','host'));
dbms_output.put_line('INSTANCE:             '||sys_context('userenv','instance_name'));
dbms_output.put_line('CLIENT IP ADDRESS:    '||sys_context('userenv','ip_address'));
dbms_output.put_line('IS SYSDBA:            '||sys_context('userenv','isdba'));
dbms_output.put_line('OS USER:              '||sys_context('userenv','os_user'));
dbms_output.put_line('SERVER HOSTNAME:      '||sys_context('userenv','server_host'));
dbms_output.put_line('USER:                 '||sys_context('userenv','session_user'));
dbms_output.put_line('SESSION ID:           '||sys_context('userenv','sid'));
end;

AUTENTICATION_METHOD: PASSWORD
CLIENT IDENTIFIER:
CURRENT_SCHEMA:       SYS
CLIENT HOSTNAME:      uhesse-PC
INSTANCE:             orcl
CLIENT IP ADDRESS:    192.168.56.10
IS SYSDBA:            TRUE
OS USER:              uhesse-pc\uhesse
SERVER HOSTNAME:      uhesse
USER:                 SYS
SESSION ID:           33

PL/SQL procedure successfully completed.

Notice that there is a difference between “current_schema” and “session_user”: The first would change after an “alter session set current_schema=someotherschema;” but the second stays the same.

Following creates a small demo schema with one table, containing the sensitive column “BUDGET”:

SQL> grant dba to adam identified by adam;
connect adam/adam
create table adam.audept (deptno number, dname varchar2(20),budget number);
insert into adam.audept values (10,'DELIVERY',10000);
insert into adam.audept values (20,'MARKETING',100000);
commit;

End-users connect with the Database user ADAM, using the “Audept Application”. Normally, they do not access the audept table directly. Instead, they use a view that does not show the budget column:

SQL> create or replace view audeptvu as select deptno,dname from audept;

View created.

Our goal is now to audit access on the audept table, but only if the sensitive column “BUDGET” is touched and even then only if the access is not done through the “Audept Application”. This goal cannot be reached with traditional auditing, because this would audit each & every select on the table:

SQL> connect / as sysdba
Connected.
SQL> truncate table aud$;

Table truncated.

AUD$ is the base table for the Data Dictionary View DBA_AUDIT_TRAIL, like FGA_LOG$ is for DBA_FGA_AUDIT_TRAIL. As sys, we can truncate it, which can be monitored as well. A more sophisticated way to restrict and monitor activities of highly privileged users like sys would be the separate products Database Vault (restrict) resp. Audit vault (monitor).

SQL> audit select on adam.audept by access;

Audit succeeded.

SQL> connect adam/adam
Connected.
SQL> exec dbms_session.set_identifier('AUDEPT_APPLICATION')

PL/SQL procedure successfully completed.


SQL> select * from audeptvu;

    DEPTNO DNAME
---------- --------------------
    10 DELIVERY
    20 MARKETING

SQL> select count(*) from dba_audit_trail where obj_name='AUDEPT';

  COUNT(*)
----------
     1

Although the access on the audept table was completely compliant with our “Business Rules”, it was audited by traditional auditing. Therefore, FGA was introduced:

SQL> connect adam/adam
Connected.

SQL> create or replace function auditif
return number
as
begin
if  
sys_context('userenv','session_user')<>'ADAM' 
or 
sys_context('userenv','client_identifier')<>'AUDEPT_APPLICATION' 
or
sys_context('userenv','client_identifier') is null
then return 1;
else return 0; 
end if;
end;
/   

Function created.

This function will be used later on to determine the Audit Condition for Fine Grained Auditing. We could specify many more conditions (from SYS_CONTEXT or else) here of course. Although it was tempting (at least for me) to use a boolean return value, it is not a good idea: You cannot test it then with select from dual. Fine Grained Auditing is implemented with the DBMS_FGA standard package:

SQL> begin
dbms_fga.add_policy(object_schema=>'ADAM',
                    object_name=>'AUDEPT',
                    policy_name=>'AUDEPT_POLICY',
                    audit_column=>'BUDGET',
                    audit_condition=>'ADAM.AUDITIF=1');
end;
/

PL/SQL procedure successfully completed.

With this policy, an audit entry is only generated if the column “BUDGET” is touched. Additionally, in order to get audited, the access must be done without using the “Audept Application”. We look at a compliant access first:

SQL> connect / as sysdba
Connected.
SQL> truncate table fga_log$;

Table truncated.

SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
     0

SQL> connect adam/adam
Connected.

A Client Identifier can be used to differentiate multiple (application or human) users, connecting with the same Database user. This is a common technique, executed (and verified) at the application layer:

SQL> exec dbms_session.set_identifier('AUDEPT_APPLICATION')

PL/SQL procedure successfully completed.

SQL> select * from audeptvu;

    DEPTNO DNAME
---------- --------------------
    10 DELIVERY
    20 MARKETING

SQL> select * from audept;

    DEPTNO DNAME            BUDGET
---------- -------------------- ----------
    10 DELIVERY             10000
    20 MARKETING            100000

SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
     0

No audit entry was generated. Now we do not follow the business rules:

SQL> connect adam/adam
Connected.
SQL> select * from audeptvu;

    DEPTNO DNAME
---------- --------------------
    10 DELIVERY
    20 MARKETING

SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
     0

SQL> select * from audept;

    DEPTNO DNAME            BUDGET
---------- -------------------- ----------
    10 DELIVERY             10000
    20 MARKETING            100000

SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
     1

The first SELECT did not show the critical column and was therefore not audited, although no Client Identifier was specified. The second select was audited with many details, though:

SQL> select db_user,os_user,object_schema,object_name,policy_name,sql_text,timestamp 
from dba_fga_audit_trail;

DB_US OS_USER          OBJEC OBJECT_NAM POLICY_NAME        SQL_TEXT         TIMESTAMP
----- --------------- ----- ---------- -------------------- -------------------- ---------
ADAM  oracle          ADAM  AUDEPT     AUDEPT_POLICY        select * from audept 22-AUG-11

Notice that FGA will show the SQL text, regardless of the setting of AUDIT_TRAIL – no “EXTENDED” necessary here:

SQL> show parameter audit_trail

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
audit_trail                 string     DB

Conclusion: With Fine Grained Auditing, we have the possibility to limit our audit entries to only non compliant access, according to our business rules. One comfortable way to specify audit conditions is the standard function SYS_CONTEXT.