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.
We will apply a fine-grained audit on EMP_DETAILS_VIEW
.
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>
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>
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>
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.
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>
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>
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.
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.
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.
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
.
18.222.167.183