Auditing Oracle

Before I go back to writing more on Clemenceau; here’s a write-up on auditing Oracle database. Oracle auditing is generally considered to be a very slow and tedious set of activities. However, in my experience, when auditing is used diligently and with prejudice, it helps when it matters the most.

Introduction
This paper introduces the basics of auditing an Oracle database. Oracle is a functionally rich product and there are a number of auditing alternatives available. This paper will cover the basics of why, when and how to enforce an audit on oracle, which is practical, easy to understand and implement and the outputs are easy to comprehend.

Why is audit needed in Oracle?
A lot of organizations don’t actually use the internal audit features of Oracle, and when they do use them with the help of some external vendors, they are so overwhelmed with choice; they turn on everything for security and maintainability; then realize that there is far too much data and audit results to analyze and digest so these mechanisms are quickly put off again.

We all have come across organizations using firewalls, intrusion detection systems, and various other tools for system and network security. All these tools help us to determine if the networks or the systems are being misused or abused in any way.

So, why not audit what users are doing to the "crown jewels" of an organization, the data and the schema. Oracle audit can help detect unauthorized access and internal abuse of the data held in the database.

Audit also helps in Product Upgrades: I’ve come across cases in the past, when upgrades were a pain, just because we weren’t able to identify the changes in the development database, which needed to be incorporated in the production database. Wouldn’t it be much easier to upgrade a site, even after 6 months, if we knew the exact nature and number of DDL commands executed in the last 6 months?

When should Oracle users be audited?
A simple basic set of audit actions should be active all the time. The ideal minimum is to capture user access, use of system privileges and changes to the database schema structure. From a data management point of view, Monitoring schema change on critical tables (such as transactions, masters) should be considered.

Oracle Audits
Here are some of the methods that can be used to audit an oracle database.

• Oracle Audit


• System Triggers


• Update, Delete And Insert Triggers


• Fine - Grained Audit


• System Logs

Working Example

Here’s how this works...
Oracle recognizes DDL statements i.e. DROP, ALTER, CREATE executed on any table space (database) on the server.

For the audit to be setup, we need to create an isolated and secured table space, and a user to store the results for analyzing or publishing periodically. A table is then created in this table space for storing the information related to DDL executions.

A system level trigger, created under sys user, is fired every time a DDL is fired, and inserts data into the logging table with the following data:
Oracle username, Execution Date, DDL type, Object Type, Owner, Object Name, Oracle SID, OS Username, Machine/Terminal, Program Name, Logon Time, IP Address of the terminal.

Following are the scripts for creation of the table space, user, the table and the system level trigger.

A. Creating Audit Table space and User

CREATE TABLESPACE logging DATAFILE ‘logging'
SIZE 30M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
DEFAULT STORAGE (INITIAL 16K NEXT 16K
MINEXTENTS 1 MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;

CREATE USER logging IDENTIFIED BY logging
DEFAULT TABLESPACE logging
TEMPORARY TABLESPACE TEMP;

GRANT CONNECT, RESOURCE, DBA, UNLIMITED TABLESPACE TO logging;
GRANT SELECT ON sys.v_$session TO logging;
GRANT SELECT ON sys.v_$sql TO logging;
GRANT ALTER SYSTEM TO logging;


B. Creating Audit Table

CONNECT logging/logging @ servername;

CREATE TABLE ddl_log_nxt
(
user_name      VARCHAR2(30)      NULL,
ddl_date           DATE           NULL,
ddl_type           VARCHAR2(30)      NULL,
object_type      VARCHAR2(18)      NULL,
owner           VARCHAR2(30)      NULL,
object_name      VARCHAR2(128)      NULL,
audsid           NUMBER           NULL,
osuser           VARCHAR2(30)      NULL,
machine           VARCHAR2(64)      NULL,
terminal           VARCHAR2(30)      NULL,
program           VARCHAR2(48)      NULL,
logon_time      DATE           NULL,
ip_address      VARCHAR2(30)      NULL,
client_info      VARCHAR2(64)      NULL
);


C. Creating System Trigger

CONNECT sys/change_on_install @ servername;

CREATE OR REPLACE TRIGGER ddl_trigger
AFTER CREATE OR ALTER OR DROP
ON DATABASE
DECLARE
BEGIN
INSERT INTO LOGGING.DDL_LOG_NXT
(user_name, ddl_date, ddl_type,
object_type, owner, object_name, audsid, osuser, machine, terminal, program, IP_ADDRESS, CLIENT_INFO, logon_time)

SELECT ORA_LOGIN_USER, SYSDATE, ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME,
sys_context('USERENV', 'SESSIONID'), sys_context('USERENV', 'OS_USER'),
sys_context('USERENV', 'HOST'),
sys_context('USERENV', 'TERMINAL'), program,
sys_context('USERENV', 'IP_ADDRESS'),
sys_context('USERENV', 'CLIENT_INFO'), logon_time

FROM v$session
WHERE
audsid = sys_context('USERENV','SESSIONID')
AND terminal = sys_context('USERENV', TERMINAL');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;


Summary
Creation of this system level trigger will insert a audit record into the logging table, every time a DDL is executed on the database.