Implement fine-grained access control

Auditing is the monitoring and recording of selected user database actions, from both database users and nondatabase users

benefits:

• Security. Associating a policy with a database table, view, or synonym can solve a potentially serious application security problem. Suppose a user is authorized to use an application, and then drawing on the privileges associated with that application, wrongfully modifies the database by using an ad hoc query tool, such as SQL*Plus. By attaching security policies directly to tables, views, or synonyms, fine-grained access control ensures that the same security is in force, no matter how a user accesses the data.
• Simplicity. You add the security policy to a table, view, or synonym only once, rather than repeatedly adding it to each of your table-based, view-based, or synonym-based applications.
• Flexibility. You can have one security policy for SELECT statements, another for INSERT statements, and still others for UPDATE and DELETE statements. For example, you might want to enable Human Resources clerks to have SELECT privileges for all employee records in their division, but to update only salaries for those employees in their division whose last names begin with A through F. Furthermore, you can create multiple policies for each table, view, or synonym.

Fine-grained auditing records are stored in the SYS.FGA_LOG$ table. To find the records have been generated for the audit policies that are in effect, you can query the DBA_FGA_AUDIT_TRAIL view. The DBA_COMMON_AUDIT_TRAIL view combines both standard and fine-grained audit log records. In addition, you can use the V$XML_AUDIT_TRAIL view to find fine-grained audit records that were written in XML formatted files

The DBA_COMMON_AUDIT_TRAIL view includes the contents of the V$XML_AUDIT_TRAIL dynamic view for standard and fine-grained audit records.

DBMS_FGA PL/SQL Package

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. You also can audit MERGE statements, by auditing the underlying actions of INSERT and UPDATE. To audit MERGE statements, configure fine-grained access on the INSERT and UPDATE statements. Only one record is generated for each policy for successful MERGE operations. To administer fine-grained audit policies, you must have the EXECUTE privilege on the DBMS_FGA package

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.

After you create the fine-grained audit policy, it does not reside in any specific schema, although the definition for the policy is stored in the SYS.FGA$ data dictionary table.

You cannot modify a fine-grained audit policy after you have created it. If you need to modify the policy, drop it and then recreate it.

The syntax for the ADD_POLICY procedure is:

DBMS_FGA.ADD_POLICY(
  object_schema      VARCHAR2,
  object_name        VARCHAR2,
  policy_name        VARCHAR2,
  audit_condition    VARCHAR2,
  audit_column       VARCHAR2,
  handler_schema     VARCHAR2,
  handler_module     VARCHAR2,
  enable             BOOLEAN,
  statement_types    VARCHAR2,
  audit_trail        BINARY_INTEGER IN DEFAULT,
  audit_column_opts  BINARY_INTEGER IN DEFAULT);

• object_schema: Specifies the schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
• object_name: Specifies the name of the object to be audited.
• policy_name: Specifies the name of the policy to be created. Ensure that this name is unique.
• audit_condition: Specifies a Boolean condition in a row. NULL is allowed and acts as TRUE. See "Auditing Specific Columns and Rows" for more information. If you specify NULL or no audit condition, then any action on a table with that policy creates an audit record, whether or not rows are returned
• audit_column: Specifies one or more columns to audit, including hidden columns. If set to NULL or omitted, all columns are audited. These can include Oracle Label Security hidden columns or object type columns. The default, NULL, causes audit if any column is accessed or affected.
• handler_schema: If an alert is used to trigger a response when the policy is violated, specifies the name of the schema that contains the event handler. See also "Tutorial: Adding an E-Mail Alert to a Fine-Grained Audit Policy".
• handler_module: Specifies the name of the event handler. Include the package the event handler is in. The default, NULL, uses the current schema.This function is invoked only after the first row that matches the audit condition in the query is processed. If the procedure fails with an exception, then the user SQL statement fails as well.
• enable: Enables or disables the policy using true or false. If omitted, the policy is enabled. The default is TRUE.
• statement_types: Specifies the SQL statements to be audited: INSERT, UPDATE, DELETE, or SELECT only.
• audit_trail: Specifies the destination (DB or XML) of fine-grained audit records. Also specifies whether to populate LSQLTEXT and LSQLBIND in FGA_LOG$. However, be aware that sensitive data, such as credit card information, can be recorded in clear text. See "Auditing Sensitive Information" for how you can handle this scenario.
• audit_column_opts: If you specify more than one column in the audit_column parameter, then this parameter determines whether to audit all or specific columns.

Using DBMS_FGA.ADD_POLICY to Create a Fine-Grained Audit Policy

SHAIKDB>BEGIN

DBMS_FGA.add_policy(

object_schema => 'HR',

object_name => 'EMPLOYEES',

policy_name => 'EMPLOYEES_CHK_AUDIT_SRC',

audit_condition => 'SALARY > 2500',

audit_column => 'SALARY');

END;

/

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

PL/SQL procedure successfully completed.

SHAIKDB>grant execute on dbms_fga to lob;

Grant succeeded.

SHAIKDB>grant select on hr.employees to lob;

Grant succeeded.

SHAIKDB>show user

USER is "LOB"

SHAIKDB>exec dbms_fga.add_policy(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'Audit_Select',STATEMENT_TYPES=>'SELECT');

PL/SQL procedure successfully completed.

SHAIKDB>select * from user_audit_policies;

no rows selected

SHAIKDB>show user

USER is "SYS"

SHAIKDB>col OBJECT_SCHEMA for a10

SHAIKDB>col OBJECT_NAME for a10

SHAIKDB>col POLICY_OWNER for a10

SHAIKDB>select OBJECT_SCHEMA,OBJECT_NAME,POLICY_OWNER,POLICY_NAME,ENABLED,SEL,AUDIT_TRAIL from dba_audit_policies;

OBJECT_SCH OBJECT_NAM POLICY_OWN POLICY_NAME           ENA SEL AUDIT_TRAIL

---------- ---------- ---------- ------------------------------ --- --- ------------

HR       EMPLOYEES  LOB     AUDIT_SELECT           YES YES DB+EXTENDED

Now lets verify the select conditions works:

Before running the SELECT statement no audit data was found.

SHAIKDB>select * from dba_fga_audit_trail;

no rows selected

SHAIKDB>col dbuid format a10

col lsqltext format a66

col ntimestamp# format a15

SHAIKDB>

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

no rows selected

SELECT the data:

SHAIKDB>show user

USER is "LOB"

SHAIKDB>select salary from hr.employees where employee_id=100;

    SALARY

----------

    24000

Afterrunning the SELECT statement audit data was found.

SHAIKDB>show user

USER is "SYS"

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

DBUID       LSQLTEXT                                 NTIMESTAMP#

---------- ------------------------------------------------------------------ ---------------

LOB       select salary from hr.employees where employee_id=100          26-SEP-15 07.36

                                        .01.161911 AM

SHAIKDB>select * from dba_fga_audit_trail;

    400099 26-SEP-15 LOB

oracle

collabn1.shaiksameer

oracle

HR       EMPLOYEES  AUDIT_SELECT           1115602

select salary from hr.employees where employee_id=100

SELECT    26-SEP-15 03.36.01.161911 AM -04:00                                                       0 24598                   13

    2                 1685298195

DISABLE Policy:

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

PL/SQL procedure successfully completed.

SHAIKDB>select OBJECT_SCHEMA,OBJECT_NAME,POLICY_OWNER,POLICY_NAME,ENABLED,SEL from dba_audit_policies;

OBJECT_SCH OBJECT_NAM POLICY_OWN POLICY_NAME           ENA SEL

---------- ---------- ---------- ------------------------------ --- ---

HR       EMPLOYEES  LOB     AUDIT_SELECT           NO  YES

Drop the policy:

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

PL/SQL procedure successfully completed.

SHAIKDB>select OBJECT_SCHEMA,OBJECT_NAME,POLICY_OWNER,POLICY_NAME,ENABLED,SEL from dba_audit_policies;

no rows selected

Create the policy with audit conditions and audit column options:

Test the select condition:

SHAIKDB>exec dbms_fga.add_policy(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'audit_salary_col',audit_condition=>'salary>1000', audit_column=>'SALARY',statement_types=>'SELECT,INSERT');

PL/SQL procedure successfully completed.

SHAIKDB>select OBJECT_SCHEMA,OBJECT_NAME,POLICY_OWNER,POLICY_NAME,ENABLED,SEL from dba_audit_policies;

OBJECT_SCH OBJECT_NAM POLICY_OWN POLICY_NAME           ENA SEL

---------- ---------- ---------- ------------------------------ --- ---

HR       EMPLOYEES  LOB     AUDIT_SALARY_COL       YES YES

SHAIKDB>show user

USER is "LOB"

SHAIKDB>select * from hr.employees where salary>10000;

EMPLOYEE_ID FIRST_NAME        LAST_NAME           EMAIL            PHONE_NUMBER      HIRE_DATE JOB_ID       SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID     SSN

----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- ------------- ----------

   201 Michael        Hartstein           MHARTSTE            515.123.5555      17-FEB-04 MK_MAN        13000    100           20

   205 Shelley        Higgins           SHIGGINS            515.123.8080      07-JUN-02 AC_MGR        12008    101          110

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

DBUID       LSQLTEXT                                 NTIMESTAMP#

---------- ------------------------------------------------------------------ ---------------

LOB       select salary from hr.employees where employee_id=100          26-SEP-15 07.36

                                        .01.161911 AM

LOB       select * from hr.employees where salary>10000             26-SEP-15 08.11

                                        .15.378093 AM

col SQL_TEXT for a30

col os_user for a10

SHAIKDB>select timestamp,object_schema,object_name,policy_name,sql_bind,sql_text from dba_fga_audit_trail;

TIMESTAMP OBJECT_SCH OBJECT_NAM POLICY_NAME              SQL_BIND              SQL_TEXT

--------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------

26-SEP-15 HR         EMPLOYEES    AUDIT_SELECT       select salary from hr.employees where employee_id=100

26-SEP-15 HR         EMPLOYEES    AUDIT_SALARY_COL   select * from hr.employees where salary>10000

Test the insert condition:

SHAIKDB>grant select,insert,update,delete on hr.employees to pfay;

Grant succeeded.

SHAIKDB>alter user pfay identified by pfay;

User altered.

SHAIKDB>grant create session to pfay;

Grant succeeded.

SHAIKDB>conn pfay/pfay

Connected.

SHAIKDB>show user

USER is "PFAY"

SHAIKDB>insert into hr.employees values (220,'SHAIK','SAMEER','shaiksameer','515.123.4512',sysdate,'AD_VP',50000,null,null,100,null);

1 row created.

SHAIKDB>commit;

Commit complete.

TIMESTAMP OBJECT_SCH OBJECT_NAM POLICY_NAME              SQL_BIND              SQL_TEXT

--------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------

26-SEP-15 HR         EMPLOYEES    AUDIT_SELECT                             select salary from hr.employee

                                                s where employee_id=100

26-SEP-15 HR         EMPLOYEES    AUDIT_SALARY_COL                         select * from hr.employees whe

                                                re salary>10000

26-SEP-15 HR         EMPLOYEES    AUDIT_SALARY_COL                         insert into hr.employees selec

                                                t * from hr.employees where em

                                                ployee_id=100

26-SEP-15 HR         EMPLOYEES    AUDIT_SALARY_COL                         select * from hr.employees whe

                                                re employee_id=100

26-SEP-15 HR         EMPLOYEES    AUDIT_SALARY_COL                         select * from EMP_DETAILS_VIEW

26-SEP-15 HR         EMPLOYEES    AUDIT_SALARY_COL                         insert into hr.employees value

                                                s (220,'SHAIK','SAMEER','shaik

                                                sameer','515.123.4512',sysdate

                                                ,'AD_VP',50000,null,null,100,n

                                                ull)

6 rows selected.

Data Dictionary Views That Display Information about the Database Audit Trail

View	Description
ALL_AUDIT_POLICIES	Describes the fine-grained auditing policies on the tables and views accessible to the current user
ALL_AUDIT_POLICY_COLUMNS	Describes the fine-grained auditing policy columns on the tables and views accessible to the current user.
ALL_DEF_AUDIT_OPTS	Lists default object-auditing options that are to be applied when objects are created
AUDIT_ACTIONS	Describes audit trail action type codes
DBA_AUDIT_EXISTS	Lists audit trail entries produced BY AUDIT NOT EXISTS
DBA_AUDIT_MGMT_CLEAN_EVENTS	Displays the history of purge events. Periodically, as user SYS connected with the SYSDBA privilege, you should delete the contents of this view so that it does not grow too large. For example: DELETE FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;
DBA_AUDIT_MGMT_CLEANUP_JOBS	Displays the currently configured audit trail purge jobs
DBA_AUDIT_MGMT_CONFIG_PARAMS	Displays the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package
DBA_AUDIT_MGMT_LAST_ARCH_TS	Displays the last archive timestamps that have set for audit trail purges.
DBA_AUDIT_OBJECT	Lists audit trail records for all objects in the system
DBA_AUDIT_POLICIES	Lists all the fine-grained auditing policies on the system
DBA_AUDIT_SESSION	Lists all audit trail records concerning CONNECT and DISCONNECT
DBA_AUDIT_POLICY_COLUMNS	Describes the fine-grained auditing policy columns on the tables and views throughout the database.
DBA_AUDIT_STATEMENT	Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database
DBA_AUDIT_TRAIL	Lists all standard audit trail entries in the AUD$ table
DBA_COMMON_AUDIT_TRAIL	Combines standard and fine-grained audit log records, and includes SYS and mandatory audit records written in XML format
DBA_FGA_AUDIT_TRAIL	Lists audit trail records for fine-grained auditing.
DBA_OBJ_AUDIT_OPTS	Describes auditing options on all objects
DBA_PRIV_AUDIT_OPTS	Describes current system privileges being audited across the system and by user
DBA_STMT_AUDIT_OPTS	Describes current statement auditing options across the system and by user
USER_AUDIT_OBJECT	Lists audit trail records for statements concerning objects that are accessible to the current user
USER_AUDIT_POLICIES	Describes the fine-grained auditing policy columns on the tables and views accessible to the current user.
USER_AUDIT_SESSION	Lists all audit trail records concerning connections and disconnections for the current user
USER_AUDIT_STATEMENT	Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements issued by the user
USER_AUDIT_TRAIL	Lists all standard audit trail entries in the AUD$ table relating to the current user
USER_OBJ_AUDIT_OPTS	Describes auditing options on all objects owned by the current user
V$LOGMNR_CONTENTS	Contains log history information. To query this view, you must have the SELECT ANY TRANSACTION privilege.
V$XML_AUDIT_TRAIL	Shows standard, fine-grained, SYS, and mandatory audit records written in XML format files.

Manually Purging the Audit Trail

You can manually purge the audit trail right away, without scheduling a purge job. Similar to a purge job, you can purge audit trail records that were created before an archive timestamp date or all the records in the audit trail.

Note the following about the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL

Alternatively, to delete all audit records from the audit trail, enter the following statement:

DELETE FROM SYS.AUD$;

Oracle® Database Security Guide 11g Release 2 (11.2) Part Number E10574-02  --> 9 Verifying Security Access with Auditing