Implementing fine-grained auditing

Standard auditing is of paramount importance in certain cases, such as session, statement, and privilege tracking, but does not give granularity more than at the object level.

In fact, if we want to audit any DML operation on objects and also need to audit additional cases that violate specific conditions on sensitive columns, then we must rely on fine-grain auditing.

In this recipe we will define two fine-grained audit policies. One will be defined on Emp _ Details_View and will perform general auditing, and one the EMPLOYEES table that are using an access condition on the salary and commission_pct columns. Both objects belong to the HR schema.

Getting ready

All steps will be performed on the HACKDB database.

How to do it...

We will apply a fine-grained audit on EMP_DETAILS_VIEW.

  1. As the user system define a policy name empd_vw_fga_policy on emp_details_view as follows:
    SQL> exec dbms_fga.add_policy(object_schema=>'HR',object_name=>'EMP_DETAILS_VIEW',policy_name=>'empd_vw_fga_policy',audit_Trail=>DBMS_FGA.DB + DBMS_FGA.EXTENDED
);

PL/SQL procedure successfully completed.

SQL>
  2. Using this definition, all columns will be audited. We want to know the salaries and commission_pct for users working in a purchasing department. As the user HR issue the following statement:
    SQL> select first_name,last_name,salary,commission_pct from emp_details_view where job_title like 'Purch%';

FIRST_NAME           LAST_NAME                     SALARY COMMISSION_PCT
Den                  Raphaely                       11000
Alexander            Khoo                            3100
Shelli               Baida                           2900
Sigal                Tobias                          2800
Guy                  Himuro                          2600
Karen                Colmenares                      2500

6 rows selected.

SQL>
  3. Now check for audit information about the last SELECT statement from DBA_FGA_AUDIT_TRAIL:
    SQL> /

TIMESTAMP           DB_USER   OS_USER   POLICY_NAME  OBJECT_NAME  SQL_TEXT
29-apr-2012 15:23:49   HR   nodeorcl1orcl  EMPD_VW_FGA_POLICY EMP_DETAILS_VIEW
select first_name,last_name,salary,commission_pct from emp_details_view where job_title like 'Purch%'

29-apr-2012 15:31:53    HR   nodeorcl1orcl   EMPD_VW_FGA_POLICY
   EMP_DETAILS_VIEW
select first_name,last_name,salary,commission_pct from emp_details_view where job_title like 'Purch%'
SQL>
  4. Next, define a fine-grained audit policy using a condition on the employees table as follows:
    SQL>
  1  begin dbms_fga.add_policy(object_schema=>'HR'
  2  ,object_name=>'EMPLOYEES'
  3  ,policy_name=>'SAL_COMMISSION_POLICY',
  4  audit_column=>'SALARY,COMMISSION_PCT',
  5* audit_trail=>dbms_fga.db+dbms_fga.extended); end;
SQL> /

PL/SQL procedure successfully completed.
  5. Issue a SELECT statement to get the salary and commission_pct values for user SKING as follows:
    SQL> select salary,commission_pct from employees where email='SKING';

    SALARY COMMISSION_PCT
---------- --------------
     24000

SQL>
  6. In the dba_fga_audit trail, we should have audit records about the last issued statement:
    SQL> select timestamp,db_user,os_user,policy_name,object_name,sql_text from dba_fga_audit_trail where policy_name='SAL_COMMISSION_POLICY';

TIMESTAMP DB_USER   OS_USER  POLICY_NAME    OBJECT_NAME  SQL_TEXT
29-APR-12 HR       nodeorcl1orcl   SAL_COMMISSION_POLICY  EMPLOYEE
S
select salary,commission_pct from employees where email='SKING'
SQL>

How it works...

Usually you should not allow grant execute permission on DBMS_FGA to users who are owning the audited objects. The audit conditions are Boolean and fired if the condition defined is met.

There's more...

You can use a fine-grained audit only with scalar data types. The audit records are generated by access.

With DBMS_FGA there are some additional options:

Statement_types can be UPDATE, INSERT, DELETE, and SELECT. If not specified, the audit policy will be triggered just on SELECT statements.

Alert mechanism

We can implement an alerting mechanism by using handler parameters, handler_schema and handler_module. For example, we can create a procedure that may send a message alert (e-mail, sms, and so on) when the audit is triggered.

Other options

This can be set by using the audit_column_opts parameter. The available options are DBMS_FGA.ANY_COLUMNS and DBMS_FGA.ALL_COLUMNS. The first value specifies that the audit will be triggered when any column from the audit condition is used in a DML statement (this must be correlated with statement_types), and the last specifies that the audit will be triggered just where all columns are used in statements.

This can be set by using the Audit_trail parameter. The available options are DBMS_FGA.DB, DBMS_FGA.DB+DBMS_FGA.EXTENDED, DBMS_FGA.XML, and DBMS_FGA.XML+DBMS_FGA.EXTENDED. These are similar to the audit_trail parameter values and destinations with one difference; they can be changed dynamically.

Additional information about fine-grained audit policies might be found in Dba_audit_polices dictionary view. In the case that we direct audit to XML, XML+EXTENDED these records can be read by querying $XML_AUDIT_TRAIL.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.20.90