Database
Unit Objective
5.1 Introduction
Auditing is the monitoring and recording of selected user database actions, from both database users and non-database users.
Non-database users refers to application users who are recognized in the database using the CLIENT_IDENTIFIER attribute. To audit this type of user, you can use a fine-grained audit policy.
The base auditing on individual actions, such as the type of SQL statement executed, or on combinations of data that can include the user name, application, time, and so on. You can audit both successful and failed activities.
The actions that you audit are recorded in either data dictionary tables or in operating system files.
Auditing to perform the following activities:
Enable accountability for actions. These include actions taken in a particular schema, table, or row, or affecting specific content.
Deter users (or others, such as intruders) from inappropriate actions based on their accountability.
Investigate suspicious activity. For example, if a user is deleting data from tables, then a security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
Notify an auditor of the actions of an unauthorized user. The user has more privileges than expected, which can lead to reassessing user authorizations.
Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
Detect problems with an authorization or access control implementation. For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies generate audit records, then you will know the other security controls are not properly implemented.
Address auditing requirements for compliance. Regulations such as the following have common auditing-related requirements:
• Sarbanes-Oxley Act
• Health Insurance Portability and Accountability Act (HIPAA)
• International Convergence of Capital Measurement and Capital Standards: a Revised Framework (Basel II)
• Japan Privacy Law
• European Union Directive on Privacy and Electronic Communications
5.2 Auditing Activities
There are two types of auditing activities: Standard Auditing and Fine-Grained Activities.
Standard Auditing: In standard auditing, you audit SQL statements, privileges, schema objects, and network activity. You configure standard auditing by using the AUDIT SQL statement and NOAUDIT to remove this configuration. You can write the audit records to either the database audit trail or to operating system audit files.
Fine-Grained Activities Fine-grained auditing enables you to create policies that define specific conditions that must take place for the audit to occur. This enables you to monitor data access based on content. It provides granular auditing of queries, and INSERT, UPDATE, and DELETE operations.
Audit Activity
5.3 Authorization to perform Audit
Any user can configure auditing for the objects in his or her own schema, by using the AUDIT statement. To undo the audit configuration for this object, the user can use the NOAUDIT statement. No additional privileges are needed to perform this task. Users can run AUDIT statements to set auditing options regardless of the AUDIT_TRAIL parameter setting. If auditing has been disabled, the next time it is enabled, Oracle Database will record the auditing activities set by the AUDIT statements.
To audit objects in another schema, the user must have the AUDIT ANY system privilege. To audit system privileges, the user must have the AUDIT SYSTEM privilege. If the O7_DICTIONARY_ACCESSIBILITY initialization parameter has been set to FALSE (the default), then only users who have the SYSDBA privilege can perform DML actions on the audit data in the SYS.AUD$ and SYS.FGA_LOG$ tables
5.4 Audit Trail
Oracle Database writes the database audit trail to the SYS.AUD$ and SYS.FGA_LOG$ tables. Audit records generated as a result of object audit options set for the SYS.AUD$ and SYS.FGA_LOG$ tables can only be deleted from the audit trail by someone who has connected with administrator privileges.
When standard auditing is enabled (that is, you set AUDIT_TRAIL to DB or DB,EXTENDED), Oracle Database audits all data manipulation language (DML) operations, such as INSERT, UPDATE, MERGE, and DELETE on the SYS.AUD$ and SYS.FGA_LOG$ tables by non-SYS users. Non-SYS users do not have access to these tables, except if they have been explicitly granted access.
Sessions for users who connect as SYS, this includes all users connecting as SYSDBA or SYSOPER, can be fully audited. Use the AUDIT_SYS_OPERATIONS initialization parameter to specify if user SYS is audited. For example, the following setting specifies that SYS is to be audited:
AUDIT_SYS_OPERATIONS = TRUE
A value of FALSE, which is the default, disables SYS auditing.
The maximum size allowed for an audit trail written to the database is determined at the time the database is created. By default, the size reflects the system tablespace default values. The sql.bsq script, which is executed when the database is created, sets the size of the SYS.AUD$ table. It is very important that the audit trail be cleaned up regularly. otherwise export the data and truncate the SYS.AUD$ table on a regular basis.
The SYS.AUD$ store following information:
- Operating system login user name
- User name
- Session identifier
- Terminal identifier
- Name of the schema object accessed
- Operation performed or attempted
- Completion code of the operation
- Date and time stamp
The operating system file that contains the audit trail can contain any of the following:
- Audit records generated by the operating system
- Database audit trail records
- Database actions that are always audited
- Audit records for administrative users (SYS)
AUDIT_TRAIL={none|os|db|db,extended|xml|xml,extended}
- None or false- Auditing is disabled
- os-Auditing is enabled, with all audit records directed to the operating system‘s audit trail
- db or true-Auditing is enabled, with all audit records stored in the database audit trail (SYS.AUD$)
- db,extended –As db,but the SQL_BIND and SQL_TEXT columns are also populated.
- Xml-Auditing is enabled, with all audit records stroed as XML format OS files.
- Xml,extended – As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
fig5-2.jpgAudit Trial
Audit Trial
5.5 ENABLE AUDIT
SQL> CONN SYS AS SYSDBA
Enter password: ***
SQL> show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\APP\USER\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
Check the current value of the AUDIT_TRAIL Initialization parameter.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- --------
audit_trail string DB
Enable the Standard Audit Trail
SQL>alter system set audit_sys_operations=TRUE scope=SPFILE;
SQL>alter system set audit_trail=’DB_EXTENDED’ scope=SPFILE;
SQL> shutdown immediate
SQL> startup
If it is disabled, then no audit records are created. Uses the SCOPE clause because the database instance had been started using server parameter file(SPFILE). The generation and insertion of an audit trail record is independent of a user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.
5.6 Auditing Statement Executions
Oracle Database permits the selective auditing of successful executions of statements, unsuccessful attempts to execute statements, or both. This enables you to monitor actions even if the audited statements do not complete successfully. Monitoring unsuccessful SQL statement can expose users who are snooping or acting maliciously, though most unsuccessful SQL statements are neither.
This method of auditing is also useful in that it reduces the audit trail, helping you to focus on specific actions. This can aid in maintaining good database performance.
The options are as follows:
■WHENEVER SUCCESSFUL clause: This clause audits only successful executions of the audited statement.
■WHENEVER NOT SUCCESSFUL clause: This
clause audits only unsuccessful executions of the audited statement.
Auditing an unsuccessful statement execution generates an audit report only if a valid SQL statement is issued but fails, because it lacks proper authorization or references a nonexistent schema object. Statements that fail to execute because they were not valid cannot be audited.
5.7 Audit Option
Audit Option
Audit return codes
The DBA_AUDIT_TRAIL table has a RETURNCODE column which indicates the
results of the auditing action. The code is the Oracle error message (ORA-nnnn)
that was audited.
return code
5.8 Standard Auditing Levels
Statement and privilege audit options in effect
at the time a database user connects to the database remain in effect for the
duration of the session. When the session is already active, setting or
changing statement or privilege audit options does not take effect in that
session. The modified statement or privilege audit options take effect only
when the current session ends and a new session are created.
5.8.1 Statement level
The statements that you can audit are in the following categories:
- DDL statements. For example, AUDIT TABLE audits all CREATE and DROP TABLE statements
- DML statements. SELECT, INSERT, UPDATE, DELETE, EXECUTE. For example, AUDIT SELECT TABLE audits all SELECT ... FROM TABLE/VIEW statements, regardless of the table or view
- System events –LOGON, LOGOFF etc.
Audit to Enable SQL Statement Auditing
AUDIT SELECT TABLE BY ACCESS;
Auditing Unsuccessful Statements
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;
Auditing all SQL statements for individual users
AUDIT ALL BY Arvind BY ACCESS;
Auditing all SQL statements for the current session, regardless of user
AUDIT ALL STATEMENTS IN SESSION CURRENT BY ACCESS WHENEVER NOT SUCCESSFUL;
The logon trigger functionality can establish that this connection should be audited more fully. Issue the following SQL command:
AUDIT ALL STATEMENTS IN SESSION CURRENT;
Auditing login and logoff connections and disconnections
The AUDIT SESSION statement generates an independent audit record for every login and logoff event. This enables you to audit all successful and unsuccessful connections to and disconnections from the database, regardless of user.
For example:
AUDIT SESSION BY ACCESS;
You can set this option selectively for individual users also, as in the following example:
AUDIT SESSION BY Arvind, Sinduja BY ACCESS;
Removing SQL Statement Auditing
To remove SQL statement auditing, use the use the NOAUDIT SQL statement
NOAUDIT session;
NOAUDIT session BY Arvind,Sinduja;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
Using NOAUDIT to Remove ALL STATEMENTS Auditing
NOAUDIT ALL STATEMENTS;
5.8.2 Object Level
Schema object auditing monitors actions
performed on the audited schema objects, such as tables or views. Object
auditing applies to all users but is limited to the audited object only. Users can use
the AUDIT and NOAUDIT statements on objects in their own schema.
AUDIT SELECT ON HR.EMPLOYEES BY ACCESS;
CREATE VIEW employees_departments AS SELECT employee_id, last_name, department_id
FROM employees, departments WHERE employees.department_id = departments.department_id;
AUDIT SELECT ON employees_departments BY ACCESS;
Auditing statements for object does not exist:
The NOT EXISTS option of the AUDIT statement specifies auditing of all SQL statements that fail because the target object does not exist.
For example:
AUDIT NOT EXISTS;
Auditing Successful Statements on a Schema Table
AUDIT SELECT, INSERT, DELETE ON HR.EMPLOYEES BY ACCESS WHENEVER SUCCESSFUL;
ON DEFAULT
Use the ON DEFAULT clause to apply to any new objects (tables, views, and sequences) that are created after you set the AUDIT statement. Oracle object auditing supports a default option in the syntax. It is possible to issue “AUDIT INSERT ON DEFAULT BY ACCESS.” Note that this does not enable any auditing. Default auditing has no effect on existing objects, rather it creates an audit whenever a new object is subsequently created.
In this example, new objects
AUDIT SELECT ON DEFAULT BY ACCESS WHENEVER NOT SUCCESSFUL;
5.8.3. Privilege Auditing
Audits statements that use a system privilege, such as SELECT ANY TABLE. If SCOTT selects his own table (for example, SCOTT.EMP), then the SELECT ANY TABLE privilege is not used. Because he performed the SELECT statement within his own schema, no audit record is generated. On the other hand, if SCOTT selects from another schema (for example, the HR.EMPLOYEES table), then an audit record is generated.
Using AUDIT to Configure Privilege
Auditing
AUDIT DELETE ANY TABLE BY ACCESS;
Removing Privilege Auditing
The following statement removes all privilege audit options:
NOAUDIT ALL PRIVILEGES;
Disables the audit settings
NOAUDIT DELETE ANY TABLE;
5.9 FGA AUDIT POLICY
To create a fine-grained audit policy, use the DBMS_FGA.ADD_POLICY procedure. This procedure creates an audit policy using the supplied predicate as the audit condition. Oracle Database executes the policy predicate with the privileges of the user who created the policy. The maximum number of fine-grained policies on any table or view object is 256
Oracle Database stores the policy in the data
dictionary table, but you can create the policy on any table or view that is
not in the SYS schema. The definition
for the policy is stored in the SYS.FGA$ data dictionary table.
SQL> CONN SYS AS SYSDBA
Enter password: SYS
begin
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA=>'SCOTT',
OBJECT_NAME=>'PROJECT',
POLICY_NAME=>'PROAUD_POLICY',
AUDIT_CONDITION=>'USERID<13',
AUDIT_COLUMN=>'USERID',
ENABLE=>TRUE,
STATEMENT_TYPES=>'INSERT,UPDATE,SELECT,DELETE',
AUDIT_TRAIL=>DBMS_FGA.DB);
END;
/
As you can see, this feature is enormously beneficial. It not only enables you to pinpoint particularly important types of data to audit, but it provides increased protection for columns that contain sensitive data, such as Social Security numbers, salaries, patient diagnoses, and so on.
If the audit_column lists more than one column, you can use the audit_column_opts parameter to specify whether a statement is audited when the query references anycolumn specified in the audit_column parameter or only when allcolumns are referenced. For example:
audit_column_opts => DBMS_FGA.ANY_COLUMNS,
audit_column_opts => DBMS_FGA.ALL_COLUMNS,
If you do not specify a relevant column, then auditing applies to all columns.
SQL> SELECT POLICY_NAME FROM DBA_AUDIT_POLICIES;
POLICY_NAME
------------------------------
PROAUD_POLICY
DBA FGA Audit data Dictionary
SELECT PROJECT TABLE
FGA AUDIT TRAIL
5.10 Remove FGA Policy
BEGIN
DBMS_FGA.DROP_POLICY(
OBJECT_SCHEMA=>'SCOTT',
OBJECT_NAME=>'PROJECT',
POLICY_NAME=>'PROAUD_POLICY');
END;
Remove Policy
5.11 FGA_LOG$
FGA LOG
select FGA LOG
fga project table
To purge fine-grained audit records, you can delete them records from the SYS.FGA_LOG$ table. For example, to delete all fine-grained audit records, enter
the following statement:
DELETE FROM FGA_LOG$;
SQL> SELECT DBUID,STATEMENT,STMT_TYPE FROM FGA_LOG$ WHERE OBJ$NAME='AUDITTEST';
FGA display
Alternatively, to delete all audit records from the fine-grained audit trail generated as a result of auditing the table PROJECT, enter the following statement:
DELETE
Unit Summary
This unit explains the purpose of audit, various audit activities and audit option. It also explain various level of standard audit. Fine grain audit policy creation and how to drop the policy and also how to remove audit trail record information.
Unit 5- Exercise
1. Enable the standard audit trial.
2. Don’t do any audit to current session user Induja
3. Do audit for unsuccessful DML STATEMENTS.
4. Audit INSERT, DELETE privileges in new object which will create in future
5. Remove audit from user Induja whose created procedures.
6. Do audit for Select, insert table of user Lakshmi.
7. Audit entire statement in the current session of all users.
8. Remove all privileges in auditing.
9. Don't do any audit for Hr. employees table.
10. Audit all the update on future table.
11. Create view “empview” using hr.employees table, do audit using select privilege on empview.
12. Write a Fine grained audit policy “project_fgapolicy” which will either audit User_id column or No_of_hours column. Display statement type, object information of the project_fgspolicy. Login Arvind display the project table. Display the fine grain audit records of project table and remove the audit information.
Questions
1. Explain various Audit level of standard audit
2. Distinguish database user and non database user audit activities
3. Differentiate By session and By access Audit Option.
4. Explain the importance of FGA Audit.
Video Tutorial Link
Lecture Notes For Unit-5
References
1. http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4007.htm#BABEFEAC
2. https://www.giac.org/paper/gsec/4085/understanding-oracle-auditing/106607