Chapter 8. Tracking and Analysis: Database Auditing

In this chapter we will cover:

• Determining how and where to generate audit information

• Auditing sessions

• Auditing statements

• Auditing objects

• Auditing privileges

• Implementing fine-grained auditing

• Integrating Oracle audit with SYSLOG

• Auditing SYS administrative users

Introduction

No matter how secure a system is there will always exist a risk: it can be penetrated by an outsider that has gained access or compromised by an insider that has misused their access privileges. In cases like this, one way to detect the origin of the attack or the source of the inappropriate data access or modification is to implement sensible data audits. Therefore, it is important to emphasize the necessity of implementing appropriate database and operating system audits as a major part of a system security methodology. Organizations that have implemented an effective audit policy are in a better position to protect their data assets.

In addition to auditing, we should develop and implement appropriate alerting systems to proactively detect and prevent attacks on systems and data.

There are many possible attacks that can target the database, listener, and configuration files. Normal activities such as routine system and database patching as well as application and design upgrades...

Determining how and where to generate audit information

The place and how the audit information is stored can be crucial to determine the operations performed on the database. In this recipe, we will discuss how and where this information can be collected and we will cover the possible destinations of audit trails and what options we may have.

Getting ready

All steps from this recipe will be performed on the HACKDB database.

How to do it...

For audit trail destination we have the option to store the audit records internally within the database or as external files.

1. The default value for the AUDIT_TRAIL parameter is DB. By using this option, minimal audit information will be generated. If we want to extend the audit information to include issued statements and the bind variables that were used, we must utilize the EXTENDED option. Connect as system and issue the following statement:

   SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile;
   
   System altered.
   
   SQL>
   

2. To direct the auditing...

Auditing sessions

When the audit is performed, it is important to be able to identify the originating host, username, and logon and logoff time for sessions. In this recipe we will enable an audit on sessions created by users HR, DRAPHEAL, and SMAVRIS.

Getting ready

All steps from this recipe will be performed on the HACKDB database.

How to do it...

1. Connect as user SYSTEM, and start to audit sessions for user HR, DRAPHEAL, and SMAVRIS as follows:

   SQL> conn system
   Enter password:
   Connected.
   SQL> audit session by HR, DRAPHEAL, SMAVRIS;
   
   Audit succeeded.
   
   SQL>
   

2. Next connect as user HR and wait for 10 seconds, then disconnect.

3. Connect also as user DRAPHEAL and SMAVRIS and wait for 10 seconds or more, then disconnect.

4. One source of information for audit trails related to the session can be found by querying the DBA_AUDIT_SESSION dictionary view. Check the generated audit records by OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP, ACTION_NAME, and LOGOFF_TIME as follows:

   

How it works...

The...

Auditing statements

Statement auditing along with session audits is another important tracing method for capturing suspicious operations performed by a user. Statement audits apply both for DML and DDL statements.

In this recipe we will implement statement audit and we will create a new table named HR_EMP_DETAILS_AUD from EMP_DETAILS_VIEW.

Getting ready

All steps from this recipe will be performed on the database HACKDB.

How to do it...

1. Connect as user HR and create table HR_EMP_DETAILS_AUD as follows:

   SQL> conn HR
   Enter password:
   Connected.
   SQL> create table hr_emp_details_aud as select * from emp_details_view;
   
   Table created.
   

2. Grant all privileges to SMAVRIS and DRAPHEAL on the HR_EMP_DETAILS_AUD table as follows:

   SQL> grant alter on hr.hr_emp_details_aud to smavris,drapheal;
   Grant succeeded.
   
   Audit succeeded.
   
   SQL>
   

3. You may want to limit audit scope to specific users. By default both successful and unsuccessful events will be audited. In our case, limit the audit scope to HR, SMAVRIS...

Auditing objects

A properly designed and implemented statement-level auditing policy can help to detect suspicious activity, especially in cases in which we have a small number of statements executed frequently on the same objects. However, if there are thousands of statements being executed per minute, then it may be more difficult to determine if any of those executions are tied to activities we would need to investigate. In those situations it may be more beneficial to implement object-level auditing against the sensitive objects. In this case, it would be easier to audit the sensitive objects separately using object auditing features.

In this recipe we will audit the table EMPLOYEES for all statements, and the emp_details_hr view from the schema HR for the SELECT statements.

Getting ready

All steps will be performed on the HACKDB database.

How it works...

1. Connect as the system user and start the audit for the EMPLOYEES table as follows:

   SQL> conn system
   Enter password:
   Connected.
   SQL...

Auditing privileges

Generally complex applications use multiple schemas to query and save data. Also an attacker who connects successfully to a schema, such as system, may quickly attempt to exploit the additional access provided by select any, delete any, insert, and update any privileges.

To track these activities we need to audit these higher level privileges in order to ensure that we are capturing the use of them.

In this recipe we will grant select any table, delete any table, and update any table to users SMAVRIS and DREPHNEAL. Next, we will start to audit these statements and execute select, delete, and update statements against the hr_emp_details_aud table.

Getting ready

All steps will be performed on the HACKDB database.

How it works...

1. Connect as the user system, grant select any table, delete any table, and update any table to users DRAPHEAL and SMAVRIS as follows:

   SQL> conn system
   Enter password:
   Connected.
   SQL>
   SQL> grant select any table,delete any table,update any table...

Implementing fine-grained auditing

Standard auditing is of paramount importance in certain cases, such as session, statement, and privilege tracking, but does not give granularity more than at the object level.

In fact, if we want to audit any DML operation on objects and also need to audit additional cases that violate specific conditions on sensitive columns, then we must rely on fine-grain auditing.

In this recipe we will define two fine-grained audit policies. One will be defined on Emp _ Details_View and will perform general auditing, and one the EMPLOYEES table that are using an access condition on the salary and commission_pct columns. Both objects belong to the HR schema.

Getting ready

All steps will be performed on the HACKDB database.

How to do it...

We will apply a fine-grained audit on EMP_DETAILS_VIEW.

1. As the user system define a policy name empd_vw_fga_policy on emp_details_view as follows:

   SQL> exec dbms_fga.add_policy(object_schema=>'HR',object_name=>'EMP_DETAILS_VIEW...

Integrating Oracle audit with SYSLOG

By using a standard audit, the resulting audit trails can be tampered with or deleted by database administrators or by an attacker who gained administrative privileges. This is a considerable security risk. SYSLOG is a protocol (RFC5424) designed for transmitting event messages and alerts across an IP network. The messages are generated, for example, by an application (ftp, cron, or ssh), and a syslog daemon catches them and integrates them using a device or another remote daemon. In this recipe we will integrate the Oracle audit trails with rsyslog.

Getting ready

All steps will be performed on the nodeorcl1 and HACKDB database.

How to do it...

1. Integration with syslog requires the destination of audit trails to be placed externally. Change the audit trail to OS as follows:

   SQL> alter system set audit_trail=OS scope=spfile;
   
   System altered.
   

2. rsyslog is a more advanced variant of syslog and is the default in Red Hat 6. The configuration file is /etc/rsyslog...

Auditing sys administrative users

By using standard auditing, operations performed against database objects by sys or users with sysdba and sysoper privileges are not audited. Only details about logon including the terminal and the date are audited by mandatory auditing. This recipe will show you how to enable the audit for sys users.

Getting ready

All steps will be performed on the HACKDB database.

How to do it...

1. In a separate terminal open /var/log/oracle_audit.log with the tail –f command. From a second terminal connect as sysdba and issue a count against the hr.employees table:

   SQL> conn / as sysdba  
   Connected.
   SQL> select count(*) from hr.employees;
     COUNT(*)
   ----------
          107
   

2. If you now look at /var/opt/oracle_audit.log you will see that nothing was recorded.

3. Connect as sysdba and modify audit_sys_operation to true as follows:

   SQL> alter system  set audit_sys_operations=true scope=spfile;
   

4. Bounce the database.

5. Connect as sysdba and reissue the count against hr.employees:

   SQL...