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

Saturday, September 26, 2015

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

No comments: