Database Security

https://www.oercommons.org/

OER- UNIT 5 AUDIT

PART 1 - INTRODUCTION

Dr. Girija Narasimhan 1

 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.

Dr. Girija Narasimhan 2

 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/O’s 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.

Dr. Girija Narasimhan 3

 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

Dr. Girija Narasimhan 4

 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.

Dr. Girija Narasimhan 5

 AUDIT ACTIVITIES Create policies that define specific conditions

Auditing of queries-INSERT, UPDATE,

DELETE operations.

STANDARD FINE-GRAINED Action

Configure
statement • Accessing a table between 9 p.m. and 6
AUDIT a.m. or on Saturday and Sunday
NO AUDIT • Using an IP address from outside the
corporate network
Level • Selecting or updating a table column
SYS.AUD$ table • Modifying a value in a table column
Statements
store
CREATE TABLE, TRUNCATE TABLE, COMMENT ON
TABLE, and DELETE [FROM] TABLE statements. SYS.FGA_LOG$ tables by
Privileges
non-SYS users
CREATE ANY TRIGGER system privilege
Schema objects

ALTER TABLE on the emp, specific object

 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. Like Grant AUDIT … no need.

 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

Dr. Girija Narasimhan 7

 Database Security
https://www.oercommons.org/

OER- UNIT 5 AUDIT

PART 2 – AUDIT TRAIL

Dr. Girija Narasimhan 8

 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.

Dr. Girija Narasimhan 9

 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.

Dr. Girija Narasimhan 10

 Audit Trail
If the purpose of the audit is to provide a record of historical activity.

Operating system files

Data Dictionary table
Store

SYS.AUD$ table
•Audit records generated by the operating system
•Database audit trail records
Store •Database actions that are always audited
•Audit records for administrative users (SYS)
• 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
 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 stored as XML format OS files.

•Xml,extended – As xml, but the SQL_BIND and SQL_TEXT columns are also populated.

Dr. Girija Narasimhan 12

SQL> alter system set audit_trail=db,extended scope=spfile;
System altered

Restart the instance.

SQL> audit select on your_table;

Audit succeeded

SQL> select sqltext from sys.aud$ where obj$name = 'YOUR_TABLE';

SQLTEXT
--------------------------------------------------------------------------------
null

SQL> select count(*) from your_table;

COUNT(*)
----------
3

SQL> select sqltext from sys.aud$ where obj$name = 'YOUR_TABLE';

SQLTEXT
--------------------------------------------------------------------------------
select count(*) from your_table

Sql text of the Query

Dr. Girija Narasimhan 13

 Database Security
https://www.oercommons.org/

OER- UNIT 5 AUDIT

PART 3 – ENABLE AUDIT

Dr. Girija Narasimhan 14

 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
Dr. Girija Narasimhan 15
 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

Dr. Girija Narasimhan 16

 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.
Dr. Girija Narasimhan 17
 5.7 AUDIT OPTION
BY SESSION
BY ACCESS
Auditing BY SESSION produces a single
audit trail record per audit option Auditing BY ACCESS generates an audit trail
regardless of the number of successful record for every user attempt.
or unsuccessful attempts within that
session. An ACCESS record with a nonzero
DBA_AUDIT_TRAIL.
There is no field which provides an
occurrence count or an error code
RETURNCODE indicates a
for failed attempts (RETURNCODE is
failed attempt.
always 0)

using an operating system file for the Audit trail shows the number of times
audit trail (that is, the AUDIT_FILE_DEST the audited action was attempted,
initialization parameter is set to OS), then the sequence of audited actions, and
the database may write multiple records the result (either success or the
to the audit trail file even if you specify failure code) of each action.
BY SESSION.

BY SESSION is default 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.

Dr. Girija Narasimhan 19

 Database Security
https://www.oercommons.org/

OER- UNIT 5 AUDIT

PART 4 – STANDARD AUDIT

Dr. Girija Narasimhan 20

 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.

Dr. Girija Narasimhan 21
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.

AUDIT ALL STATEMENTS IN SESSION CURRENT;

Dr. Girija Narasimhan 22

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;

Dr. Girija Narasimhan 23

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;

Dr. Girija Narasimhan 24

 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;

Dr. Girija Narasimhan 25

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.

AUDIT NOT EXISTS;

Auditing Successful Statements on a Schema Table

AUDIT SELECT, INSERT, DELETE ON HR.EMPLOYEES BY ACCESS WHENEVER SUCCESSFUL;

Dr. Girija Narasimhan 26

 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;

Dr. Girija Narasimhan 27

 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;

Dr. Girija Narasimhan 28

 Database Security
https://www.oercommons.org/

OER- UNIT 5 AUDIT

PART 5 – FINE-GRAINED AUDIT

Dr. Girija Narasimhan 29

 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.

Dr. Girija Narasimhan 30

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);

Dr. Girija Narasimhan 31

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 any column specified in the audit_column parameter or only when all
columns 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

Dr. Girija Narasimhan 32

Dr. Girija Narasimhan 33
Dr. Girija Narasimhan 34
 5.10 Remove FGA Policy

BEGIN

DBMS_FGA.DROP_POLICY(

OBJECT_SCHEMA=>'SCOTT',

OBJECT_NAME=>'PROJECT',

POLICY_NAME=>'PROAUD_POLICY');

END;

Dr. Girija Narasimhan 35

Dr. Girija Narasimhan 36
5.11 FGA_LOG$

Dr. Girija Narasimhan 37

Dr. Girija Narasimhan 38
 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$;

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:

Dr. Girija Narasimhan 39