Fine-Grained Auditing (FGA) hands-on

Posted on , updated on by Yannick Jaquier
Oracle ,

Table of contents

Preamble

We all know the standard audit associated with AUDIT SQL command. Main limit of standard audit feature is that it does not answer to some basic questions like how do I know if the salary column of employees table has been updated and/or selected ?

To quote official documentation that summarize very well all the extended capabilities of fine-grained auditing feature (FGA):

Accessing a table between 9 p.m. and 6 a.m. or on Saturday and Sunday
Using an IP address from outside the corporate network
Selecting or updating a table column
Modifying a value in a table column

Testing of this blog post has been done using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 running on Oracle Linux Server release 6.4.

Test data model

First let’s check that default tablespaces are correctly set:

SQL> SET lines 150
SQL> SET pages 100
SQL> SELECT * FROM database_properties
     WHERE property_name LIKE 'DEFAULT%TABLESPACE';
 
PROPERTY_NAME
------------------------------
PROPERTY_VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP
Name OF DEFAULT TEMPORARY TABLESPACE
 
DEFAULT_PERMANENT_TABLESPACE
USERS
Name OF DEFAULT permanent TABLESPACE

So USERS and TEMP which is fine, I can use the lazy approach to create users :-).

Then I create two users, one that will own my employees test object (test1) to which I grant execute on Fine-Grained Auditing package (DBMS_FGA) and a second one (test2) that will perform DML activities:

SQL> CREATE USER test1 IDENTIFIED BY test1;
 
USER created.
 
SQL> GRANT CONNECT, RESOURCE TO test1;
 
GRANT succeeded.
 
SQL> GRANT  EXECUTE ON dbms_fga TO test1;
 
GRANT succeeded.
 
SQL> CREATE USER test2 IDENTIFIED BY test2;
 
USER created.
 
SQL> GRANT CONNECT TO test2;
 
GRANT succeeded.

Then I connect with test1 account and create a test table (employees) with a typical confidential field (salary):

SQL> CONNECT test1/test1
Connected.
SQL> CREATE TABLE employees(
     id NUMBER,
     firstname VARCHAR2(50),
     lastname VARCHAR2(50),
     salary NUMBER);
 
TABLE created.
 
SQL> INSERT INTO employees VALUES(1,'Yannick','Jaquier',10000);
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SET lines 130
SQL> SELECT * FROM employees WHERE id=1;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier                                                 10000

Then I grant to test2 the update, select capability:

SQL> GRANT SELECT,UPDATE ON employees TO test2;
 
GRANT succeeded.

Default auditing

It did not come to my mind immediately but I realized it when one of my SYS.AUD$ became quite big on a new 11gR2 database (I have more or less never used 11gR1), starting with 11g Oracle has implemented, by default, quite a lot of auditing options. I thought DBA_PRIV_AUDIT_OPTS would give this list but DBA_STMT_AUDIT_OPTS is giving five more default privileges. For example the default auditing options on roles and profiles is not given in DBA_PRIV_AUDIT_OPTS:

SQL> SET lines 150
SQL> SET pages 100
SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
 
SESSION altered.
 
SQL> SELECT * FROM dba_stmt_audit_opts ORDER BY audit_option;
 
USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
                                                              ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
                                                              ALTER ANY TABLE                          BY ACCESS  BY ACCESS
                                                              ALTER DATABASE                           BY ACCESS  BY ACCESS
                                                              ALTER PROFILE                            BY ACCESS  BY ACCESS
                                                              ALTER SYSTEM                             BY ACCESS  BY ACCESS
                                                              ALTER USER                               BY ACCESS  BY ACCESS
                                                              CREATE ANY JOB                           BY ACCESS  BY ACCESS
                                                              CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS
                                                              CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS
                                                              CREATE ANY TABLE                         BY ACCESS  BY ACCESS
                                                              CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS
                                                              CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS
                                                              CREATE SESSION                           BY ACCESS  BY ACCESS
                                                              CREATE USER                              BY ACCESS  BY ACCESS
                                                              DATABASE LINK                            BY ACCESS  BY ACCESS
                                                              DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS
                                                              DROP ANY TABLE                           BY ACCESS  BY ACCESS
                                                              DROP PROFILE                             BY ACCESS  BY ACCESS
                                                              DROP USER                                BY ACCESS  BY ACCESS
                                                              EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
                                                              GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS
                                                              GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS
                                                              GRANT ANY ROLE                           BY ACCESS  BY ACCESS
                                                              PROFILE                                  BY ACCESS  BY ACCESS
                                                              PUBLIC SYNONYM                           BY ACCESS  BY ACCESS
                                                              ROLE                                     BY ACCESS  BY ACCESS
                                                              SYSTEM AUDIT                             BY ACCESS  BY ACCESS
                                                              SYSTEM GRANT                             BY ACCESS  BY ACCESS
 
28 ROWS selected.

Please note that it concerns only statements executed by non-SYS users. If you want to audit SYS top-level operations you have to set AUDIT_SYS_OPERATIONS to true (static parameter) but audit information will go to audit directory (not stored in database) and will still not contain any more classical actions (like profile creation for example) performed by SYS.

To access to audit information you can obviously acccess the root source table called SYS.AUD$ or, better, access the highed level tables called DBA_AUDIT_SESSION, DBA_AUDIT_TRAIL or DBA_COMMON_AUDIT_TRAIL (that contains standard and fine-grained auditing information).

As an example few create/drop profile and logon/logoff activities:

SQL> col OS_USERNAME FOR a10
SQL> SET lines 150
SQL> SET pages 100
SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
 
SESSION altered.
 
SQL> SELECT os_username,username,TIMESTAMP,action_name,priv_used FROM DBA_AUDIT_TRAIL WHERE timestamp>sysdate-1/24 ORDER BY TIMESTAMP DESC;
 
OS_USERNAM USERNAME                       TIMESTAMP            ACTION_NAME                  PRIV_USED
---------- ------------------------------ -------------------- ---------------------------- ----------------------------------------
yjaquier   YJAQUIER                       26-sep-2013 18:17:52 LOGON                        CREATE SESSION
oracle     TEST1                          26-sep-2013 18:04:22 LOGOFF
oracle     YJAQUIER                       26-sep-2013 17:38:27 LOGOFF
oracle     YJAQUIER                       26-sep-2013 17:38:25 DROP PROFILE                 DROP PROFILE
oracle     YJAQUIER                       26-sep-2013 17:38:12 DROP PROFILE                 DROP PROFILE
oracle     YJAQUIER                       26-sep-2013 17:37:33 CREATE PROFILE               CREATE PROFILE
oracle     YJAQUIER                       26-sep-2013 17:37:06 CREATE PROFILE
oracle     YJAQUIER                       26-sep-2013 17:37:02 LOGON                        CREATE SESSION
 
8 ROWS selected.

Remark:
The view to get object audit options is DBA_OBJ_AUDIT_OPTS.

Fine-Grained Auditing (FGA) implementation

I create a fine-grained auditing policy with (for select and update operations on salary column):

SQL> EXEC DBMS_FGA.ADD_POLICY(object_name=>'employees',policy_name=>'check_salary',audit_column=>'salary', statement_types=>'select, update');
 
PL/SQL PROCEDURE successfully completed.

Dropping it would be as easy as:

SQL> EXEC DBMS_FGA.DROP_POLICY(object_name=>'employees', policy_name=>'check_salary');
 
PL/SQL PROCEDURE successfully completed.

You can check all FGA policies with:

SQL> SET lines 150
SQL> SELECT object_schema,object_name,policy_name,policy_column,enabled,sel,ins,upd,del FROM dba_audit_policies;
 
OBJECT_SCHEMA                  OBJECT_NAME                    POLICY_NAME                    POLICY_COLUMN                  ENA SEL INS UPD DEL
------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- --- ---
TEST1                          EMPLOYEES                      CHECK_SALARY                   SALARY                         YES YES NO  YES NO
 
SQL> SELECT * FROM dba_audit_policy_columns;
 
OBJECT_SCHEMA                  OBJECT_NAME                    POLICY_NAME                    POLICY_COLUMN
------------------------------ ------------------------------ ------------------------------ ------------------------------
TEST1                          EMPLOYEES                      CHECK_SALARY                   SALARY

Fine-Grained Auditing (FGA) reporting

As user test2 if I perform a select on a “normal” column:

SQL> SELECT firstname FROM test1.employees WHERE id=1;
 
FIRSTNAME
--------------------------------------------------
Yannick

Then it does not generate any audit information

But if I access to the confidential column where an FGA policy has been created (it obviously work even if you do not explicitly access to the column):

SQL> SELECT * FROM test1.employees WHERE id=1;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier                                                 10000

To access to FGA reporting you can access to root source SYS.FGA_LOG$ table (strangely this is the one given in Oracle official documentation) or DBA_COMMON_AUDIT_TRAIL (than contains standard and FGA information, filter on AUTDIT_TYPE column equal to Fine Grained Audit or Standard Audit) but I rather prefer using DBA_FGA_AUDIT_TRAIL:

SQL> SET lines 150
SQL> col object_name FOR a30
SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
 
SESSION altered.
 
SQL> SELECT TIMESTAMP,db_user,object_schema,object_name,policy_name,sql_text FROM DBA_FGA_AUDIT_TRAIL ORDER BY TIMESTAMP DESC;
 
TIMESTAMP            DB_USER                        OBJECT_SCHEMA                  OBJECT_NAME                    POLICY_NAME
-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
27-sep-2013 11:25:42 TEST2                          TEST1                          EMPLOYEES                      CHECK_SALARY
SELECT * FROM test1.employees WHERE id=1

Same if I try to increase my salary by 10% with test2 Oracle account:

SQL> UPDATE test1.employees SET salary=salary*1.10 WHERE id=1;
 
1 ROW updated.
 
SQL> COMMIT;
 
COMMIT complete.

Then audit report shows:

SQL> SET pages 100
SQL> SET lines 150
SQL> col object_name FOR a30
SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
 
SESSION altered.
 
SQL> SELECT TIMESTAMP,db_user,object_schema,object_name,policy_name,statement_type,sql_text FROM DBA_FGA_AUDIT_TRAIL ORDER BY TIMESTAMP DESC;
 
TIMESTAMP            DB_USER                        OBJECT_SCHEMA                  OBJECT_NAME                    POLICY_NAME
-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
STATEME
-------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
27-sep-2013 11:39:36 TEST2                          TEST1                          EMPLOYEES                      CHECK_SALARY
UPDATE
UPDATE test1.employees SET salary=salary*1.10 WHERE id=1
 
27-sep-2013 11:25:42 TEST2                          TEST1                          EMPLOYEES                      CHECK_SALARY
SELECT
SELECT * FROM test1.employees WHERE id=1

Audit cleaning

I have also thought that correct way to remove standard audit information was to delete directly in SYS.AUD$ table and now so also in SYS.FGA_LOG$. Even if I do not like it too much there are tons of references on Internet suggesting to do this, even Oracle itself in My Oracle Support (MOS) note 73408.1.

In 11gR2 Oracle has created a dedicated package to execute this cleaning task more professionally I would say, package name is DBMS_AUDIT_MGMT (see note 731908.1 for further reference).

The first interesting command of this package is DBMS_AUDIT_MGMT.INIT_CLEANUP that move audit SYS tables out of SYSTEM tablespace and set a default cleaning interval of audit records. To see if procedure has already be issued (issuing it twice does not do anything):

SQL> SET serveroutput ON SIZE 999999
BEGIN
  IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD)
  THEN
    DBMS_output.put_line('True');
  ELSE
    DBMS_output.put_line('False');
  END IF;
END;
/
 
FALSE
 
PL/SQL PROCEDURE successfully completed.

Funnily moving audit objects from SYSTEM tablespace also improve performance !!:

Moving the audit trail tables out of the SYSTEM tablespace enhances overall database performance.

So let’s issue it:

SQL> EXEC DBMS_AUDIT_MGMT.INIT_CLEANUP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,1);
 
PL/SQL PROCEDURE successfully completed.

To verify it has been done:

SQL> SELECT table_name,tablespace_name FROM dba_tables WHERE owner='SYS' AND table_name IN ('AUD$','FGA_LOG$');
 
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
FGA_LOG$                       SYSAUX
AUD$                           SYSAUX

But the DBMS_AUDIT_MGMT.INIT_CLEANUP procedure does NOT clean any audit records nor create a cleanup job. To do so you have to use respectively DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL and DBMS_AUDIT_MGMT.CREATE_PURGE_JOB procedure.

Calling alone the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure does not help, first you have to tell Oracle when you last archived your audit records (using DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD is not allowed in this procedure so had to call it twice):

SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,TO_TIMESTAMP('26-sep-2013 10:00:00','dd-mon-yyyy hh24:mi:ss'));
BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,TO_TIMESTAMP('26-sep-2013 10:00:00','dd-mon-yyyy hh24:mi:ss'),1); END;
 
*
ERROR AT line 1:
ORA-46250: Invalid VALUE FOR argument 'AUDIT_TRAIL_TYPE'
ORA-06512: AT "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: AT "SYS.DBMS_AUDIT_MGMT", line 2233
ORA-06512: AT line 1
 
SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,TO_TIMESTAMP('26-sep-2013 10:00:00','dd-mon-yyyy hh24:mi:ss'));
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,TO_TIMESTAMP('26-sep-2013 10:00:00','dd-mon-yyyy hh24:mi:ss'));
 
PL/SQL PROCEDURE successfully completed.

Once audit last archived date/time has been set you can call the procedure (if second parameter is set to false then all records are deleted):

SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,TRUE);
 
PL/SQL PROCEDURE successfully completed.

To create an automatic purging job that will run every hour use something like:

SQL> EXEC DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,audit_trail_purge_interval => 1,audit_trail_purge_name => 'AUDIT_CLEANUP',use_last_arch_timestamp => TRUE);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT owner,job_name,job_action,repeat_interval FROM dba_scheduler_jobs WHERE job_name='AUDIT_CLEANUP';
 
OWNER                          JOB_NAME
------------------------------ ------------------------------
JOB_ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------
REPEAT_INTERVAL
------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                            AUDIT_CLEANUP
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(3, TRUE);  END;
FREQ=HOURLY;INTERVAL=1

What I rate a bit stupid is that you will have to create another job to change value of last archived timestamp, a parameter based on current time would have been helpful (sysdate – 7 for example)…

There are few views to display current parameter, specially the one showing the current setting of the timestamps when you archived the audit record:

SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;
 
AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL            0 26-SEP-13 10.00.00.000000 AM +00:00
FGA AUDIT TRAIL                 0 26-SEP-13 10.00.00.000000 AM +00:00
 
SQL> SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;
 
AUDIT_TRAIL                  RAC_INSTANCE CLEANUP_TIME                                                                DELETE_COUNT WAS
---------------------------- ------------ --------------------------------------------------------------------------- ------------ ---
STANDARD AUDIT TRAIL                    0 27-SEP-13 02.50.17.441767 PM +00:00                                                  895 NO
 
SQL> SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
 
JOB_NAME                                                                                             JOB_STAT AUDIT_TRAIL
---------------------------------------------------------------------------------------------------- -------- ----------------------------
JOB_FREQUENCY
----------------------------------------------------------------------------------------------------
AUDIT_CLEANUP                                                                                        ENABLED  STANDARD AND FGA AUDIT TRAIL
FREQ=HOURLY;INTERVAL=1
 
SQL> col parameter_name FOR a40
SQL> col PARAMETER_VALUE FOR a20
SQL> SET pages 100
SQL> SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
 
PARAMETER_NAME                           PARAMETER_VALUE      AUDIT_TRAIL
---------------------------------------- -------------------- ----------------------------
DB AUDIT TABLESPACE                      SYSAUX               STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE                      SYSAUX               FGA AUDIT TRAIL
AUDIT FILE MAX SIZE                      10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE                      10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE                       5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE                       5                    XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                 1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                 1000                 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL                1                    STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL                1                    FGA AUDIT TRAIL
 
12 ROWS selected.

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

4 thoughts on “Fine-Grained Auditing (FGA) hands-on”

  1. Ralph Cramden says:

    Can FGA be turned on for an entire schema, or a set of tables within the same schema, without having to drill down to the column level?
    Similarly does each add_policy call require a unique policy_name?

    • Yannick Jaquier says:

      If you refer to official documentation of DBMS_FGA.ADD_POLICY then apparently object_name must be specified and no wildcard can be set. For audit_column the default, NULL, causes audit if any column is accessed or affected. For policy name I have not tested using same next for two add_policy but documentation says should be unique.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>