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 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$;
No comments:
Post a Comment