Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, August 13, 2015

Oracle: 11gR2 Fine Grain Auditing(FGA). dbms_fga

Fine Grain Auditing(FGA):


Now you can audit SELECT, INSERT, UPDATE, and DELETE statements under one policy using Fine Grain Auditing package(dbms_fga.add_policy).

To manage a fine-grained audit policy, you use the DBMS_FGA PL/SQL package. This package enables you to add all combinations of SELECT, INSERT, UPDATE, and DELETE statements to one policy. Only one record is generated for each policy for successful MERGE operations.


The audit policy is confined/limited to the table for which you created it. This simplifies the management of audit policies because the policy only must be changed once in the database. In addition, no matter how a user connects to the database—from an application, a Web interface, or through SQL*Plus or Oracle SQL Developer—Oracle Database records any actions that affect the policy. If any rows returned from a query match the audit condition that you define, then Oracle Database inserts an audit entry into the fine-grained audit trail. This entry excludes all the information that is reported in the regular audit trail. In other words, only one row of audit information is inserted into the audit trail for every fine-grained audit policy that evaluates to true.

Note: If you audit tables that have sensitive data, remember that DB+EXTENDED and XML+EXTENDED settings for the DBMS_FGA.ADD_POLICY audit_trail parameter will capture this data.

Use of audit_condition
You can define a condition under this variable for example...If anyone tries to access the salary and commission_pct columns.

Then the condition would be:
audit_condition => 'DEPARTMENT_ID = 50', audit_column => 'SALARY,COMMISSION_PCT,'


Create new policy:

SQL> show user
USER is "SEC"
SQL>

SQL> exec dbms_fga.add_policy(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'AUDIT_EMPS_SALARY',audit_condition=>null,audit_column=>'SALARY',enable=>FALSE);

PL/SQL procedure successfully completed.


Check the status of the new policy:

SQL> show user
USER is "SYS"

SQL> @sql.sql
SQL> select object_name,policy_owner,policy_name,enabled from dba_audit_policies;

OBJECT_NAME              POLICY_OWNER             POLICY_NAME            ENA
------------------------------ ------------------------------ ------------------------------ ---
EMPLOYEES              SEC                 AUDIT_EMPS_SALARY          NO


Enable Policy:

SQL> exec dbms_fga.enable_policy(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'AUDIT_EMPS_SALARY');

PL/SQL procedure successfully completed.

Verify whether the policy is enabled or not:

SQL> show user
USER is "SYS"

SQL> select object_name,policy_owner,policy_name,enabled from dba_audit_policies;

OBJECT_NAME              POLICY_OWNER             POLICY_NAME            ENA
------------------------------ ------------------------------ ------------------------------ ---
EMPLOYEES              SEC                 AUDIT_EMPS_SALARY          YES

Note the audit logs data:

col dbuid format a10
col lsqltext format a66
col ntimestamp# format a15
SELECT DBUID, LSQLTEXT, NTIMESTAMP# FROM SYS.FGA_LOG$ ;

no rows selected


SQL> select * from DBA_FGA_AUDIT_TRAIL;

no rows selected

Time to make the fga_policy work for us:

Connect as one of the user who has access to hr.employees and try to pull the salary data from the hr.employees.

SQL> show user  
USER is "LOVEME"

SQL> select salary from hr.employees where rownum<=5;

    SALARY
----------
     2600
     2600
     4400
    13000
     6000

Verify the audit trail /logs data:

SQL> SELECT DBUID, LSQLTEXT, NTIMESTAMP# FROM SYS.FGA_LOG$;

DBUID       LSQLTEXT                                 NTIMESTAMP#
---------- ------------------------------------------------------------------ ---------------
PFAY       select salary from hr.employees where rownum<=5             13-AUG-15 07.03
                                        .56.326751 PM


SQL> select * from DBA_FGA_AUDIT_TRAIL;

SESSION_ID TIMESTAMP DB_USER
---------- --------- ------------------------------
OS_USER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USERHOST                                                CLIENT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------
ECONTEXT_ID
----------------------------------------------------------------
EXT_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_SCHEMA              OBJECT_NAME                               POLICY_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
      SCN
----------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_BIND
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMMENT$TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STATEME EXTENDED_TIMESTAMP                               PROXY_SESSIONID GLOBAL_UID                INSTANCE_NUMBER OS_PROCESS       TRANSACTIONID    STATEMENTID
------- --------------------------------------------------------------------------- --------------- -------------------------------- --------------- ---------------- ---------------- -----------
  ENTRYID OBJ_EDITION_NAME           DBID
---------- ------------------------------ ----------
  3060182 13-AUG-15 PFAY
oracle
collabn1.shaiksameer

oracle
HR                  EMPLOYEES                               AUDIT_EMPS_SALARY
  1443239
select salary from hr.employees where rownum<=5


SELECT    13-AUG-15 03.03.56.326751 PM -04:00                                   0 10574                   11
    2                 1414564590




To Disable the audit policy:

SQL> exec dbms_fga.disable_policy(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'AUDIT_EMPS_SALARY');

PL/SQL procedure successfully completed.


To drop the audit policy:

SQL>  exec dbms_fga.drop_policy(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'AUDIT_EMPS_SALARY');

PL/SQL procedure successfully completed.

No comments: