Database Security and

Auditing: Protecting Data

Integrity and Accessibility

Chapter 7
Database Auditing Models
 Auditing Overview

• Audit examines: documentation that reflects

(from business or individuals); actions,
practices, conduct
• Audit measures:
– compliance to policies,
– procedures,
– processes and laws

Database Security & Auditing: Protecting Data Integrity & Accessibility 2

 Definitions

• Audit/auditing: process of examining and

validating documents, data, processes,
procedures, systems
• Audit log: document that contains all activities
that are being audited ordered in a
chronological manner
• Audit objectives: set of business rules, system
controls, government regulations, or security
policies

Database Security & Auditing: Protecting Data Integrity & Accessibility 3

 Definitions (continued)

• Auditor: a person authorized to audit

• Audit procedure: set of instructions for the
auditing process
• Audit report: a document that contains the audit
findings
• Audit trail: chronological record of document
changes, data changes, system activities, or
operational events

Database Security & Auditing: Protecting Data Integrity & Accessibility 4

 Definitions (continued)

• Data audit: chronological record of data changes

stored in log file or database table object
• Database auditing: chronological record of
database activities
• Internal auditing: examination of activities
conducted by staff members of the audited
organization
• External auditing

Database Security & Auditing: Protecting Data Integrity & Accessibility 5

 Auditing Activities

• Identify security issues that must be addressed

• Establish plans, policies, and procedures for
conducting audits
• Organize and conduct internal audits
• Ensure all contractual items are met by the
organization being audited
• Act as liaison between the company and the
external audit team
• Provide consultation to the Legal Department

Database Security & Auditing: Protecting Data Integrity & Accessibility 6

 Auditing Process

• Auditing process: ensures that the system is

working and complies with the policies,
regulations and laws
• Auditing process is done after the product is
commissioned into production.

Database Security & Auditing: Protecting Data Integrity & Accessibility 7

 Auditing Process (continued)

• Auditing process flow:

– System development life cycle
– Auditing process:
• Understand the objectives
• Review, verify, and validate the system
• Document the results

Database Security & Auditing: Protecting Data Integrity & Accessibility 8

 Auditing Process (continued)

Database Security & Auditing: Protecting Data Integrity & Accessibility 9

 Auditing Objectives

• Part of the development process of the entity to

be audited
• Reasons:
– Complying
– Informing
– Planning
– Executing

Database Security & Auditing: Protecting Data Integrity & Accessibility 10

 Auditing Objectives

• Database auditing
objectives:
– Data integrity – Data structure
– Application users and changes
roles – Database or
– Data confidentiality application availability
– Access control – Change control
– Data changes –Auditing reports

Database Security & Auditing: Protecting Data Integrity & Accessibility 11

 Audit Classifications

• Internal audit:
– Conducted by a staff member of the company
being audited
– Purpose:
• Verify that all auditing objectives are met
• Investigate a situation prompted by an internal
event or incident
• Investigate a situation prompted by an external
request

Database Security & Auditing: Protecting Data Integrity & Accessibility 12

 Audit Classifications (continued)

• External audit:
– Conducted by a party outside the company that
is being audited
– Purpose:
• Investigate the financial or operational state of the
company
• Verify that all auditing objectives are met

Database Security & Auditing: Protecting Data Integrity & Accessibility 13

 Audit Classifications (continued)

• Automatic audit:
– Prompted and performed automatically (without
human intervention)
– Used mainly for systems and database systems
– Administrators read and interpret reports;
inference engine or artificial intelligence
• Manual audit: performed completely by humans
• Hybrid audit

Database Security & Auditing: Protecting Data Integrity & Accessibility 14

 Audit Types

• Financial audit: ensures that all financial

transactions are accounted for and comply with
the law
• Security audit: evaluates if the system is
secure
• Compliance audit: system complies with
industry standards, government regulations, or
partner and client policies

Database Security & Auditing: Protecting Data Integrity & Accessibility 15

 Benefits and Side Effects of Auditing

• Benefits:
– Enforces company policies and government
regulations and laws
– Lowers the incidence of security violations
– Identifies security gaps and vulnerabilities
– Provides an audit trail of activities
– Provides means to observe and evaluate
operations of the audited entity
– Makes the organization more accountable

Database Security & Auditing: Protecting Data Integrity & Accessibility 16

 Benefits and Side Effects of Auditing
(continued)

• Side effects:
– Performance problems
– Too many reports and documents
– Disruption to the operations of the audited entity
– Consumption of resources, and added costs
from downtime
– Friction between operators and auditor
– Same from a database perspective

Database Security & Auditing: Protecting Data Integrity & Accessibility 17

 Auditing Models

• Can be implemented with built-in features or

your own mechanism
• Information recorded:
– State of the object before the action was taken
– Description of the action that was performed
– Name of the user who performed the action

Database Security & Auditing: Protecting Data Integrity & Accessibility 18

 Auditing
User-name
Models
(continued)

Action

Object

Previous
values and
record

Database Security & Auditing: Protecting Data Integrity & Accessibility 19

 Simple Auditing Model 1

• Easy to understand and develop

• Registers audited entities in the audit model
repository
• Chronologically tracks activities performed
• Entities: user, table, or column
• Activities: DML transaction (update, insert,
delete) or logon and off times
• Status: active, deleted, inactive

Database Security & Auditing: Protecting Data Integrity & Accessibility 20

 Simple Auditing Model 1 (continued)

user, table, update, insert,

or column delete, logon
and off
active,
deleted,
inactive

Database Security & Auditing: Protecting Data Integrity & Accessibility 21

 Simple Auditing Model 1 (continued)

• Control columns:
– Placeholder for data inserted automatically when
a record is created or updated (date and time
record was created and updated)
– Can be distinguished with a CTL prefix

Database Security & Auditing: Protecting Data Integrity & Accessibility 22

 Simple Auditing Model 1 (continued)

Database Security & Auditing: Protecting Data Integrity & Accessibility 23

 Simple Auditing Model 2

• Only stores the column value changes

• There is a purging and archiving mechanism;
reduces the amount of data stored
• Does not register an action that was performed
on the data
• Ideal for auditing a column or two of a table

Database Security & Auditing: Protecting Data Integrity & Accessibility 24

 Simple Auditing Model 2 (continued)

A list of
columns to
be audited

Database Security & Auditing: Protecting Data Integrity & Accessibility 25

 Historical Data Model

• Used when a record of the whole row is

required
• Typically used in most financial applications

Database Security & Auditing: Protecting Data Integrity & Accessibility 26

 Historical Data Model (continued)

Database Security & Auditing: Protecting Data Integrity & Accessibility 27

 Auditing Applications Actions Model
• Used to audit specific operations or actions.
• You may want to audit a credit to an invoice, the reason for it
being credited, the person who credited it, and the time it was
credited.

Database Security & Auditing: Protecting Data Integrity & Accessibility 28

 C2 Security
• C2 security is a government rating for security
in which the system has been certified for
discretionary resources protection and auditing
capabilities.
• SQL server has a C2 certification, but this
certification is only valid for a certain evaluated
configuration.
• You must install SQL server in accordance with
the evaluated configuration.

Database Security & Auditing: Protecting Data Integrity & Accessibility 29

 C2 Security Requirements
• A system must be able to identify a user.
– Implement the notion of user credentials (e.g., username
and a password)
– Require a user to login using this credentials
– Have a well-defined process by which a user enters
these credentials,
– Protect these credentials from capture by an attacker.
• Users are accountable for their activities
– Audit any user activity.
• An owner of an object can grant permissions for access
to the object for other users or groups. (what
discretionary means)

Database Security & Auditing: Protecting Data Integrity & Accessibility 30

 C2 Security

• If all auditing counters are turned on for all

objects, there could be a significant
performance impact on the server.
• References: Implementing Database Security
and Auditing By Ron Ben-Natan, Chapter 1,
page 33-34

Database Security & Auditing: Protecting Data Integrity & Accessibility 31

 DML Action Auditing Architecture

• Data Manipulation Language (DML):

companies use auditing architecture for DML
changes
• DML changes can be performed on two levels:
– Row level
– Column level
• Fine-grained auditing (FGA)

Database Security & Auditing: Protecting Data Integrity & Accessibility 32

 DML Action Auditing Architecture
(continued)

Database Security & Auditing: Protecting Data Integrity & Accessibility 33

 DML Action Auditing Architecture
(continued)

Database Security & Auditing: Protecting Data Integrity & Accessibility 34

 Oracle Triggers
• Stored PL/SQL procedure executed whenever:
– DML operation occurs
– Specific database event occurs
• Six DML events (trigger timings): INSERT,
UPDATE, and DELETE
• Purposes:
– Audits, controlling invalid data
– Implementing business rules, generating values

Database Security & Auditing: Protecting Data Integrity & Accessibility 35

 Oracle Triggers (continued)

Database Security & Auditing: Protecting Data Integrity & Accessibility 36

 Oracle Triggers (continued)
• CREATE TRIGGER
• Executed in a specific order:
– STATEMENT LEVEL triggers before COLUMN
LEVEL triggers
– BEFORE triggers before AFTER triggers
• USER_TRIGGERS data dictionary view: all
triggers created on a table
• A table can have unlimited triggers: do not
overuse them

Database Security & Auditing: Protecting Data Integrity & Accessibility 37

 Oracle Triggers (continued)

Database Security & Auditing: Protecting Data Integrity & Accessibility 38

 Fine-grained Auditing (FGA) with
Oracle

• Oracle provides column-level auditing: Oracle

PL/SQL-supplied package DBMS_FGA
• DBMS_FGA procedures:
– ADD_POLICY
– DROP_POLICY
– DISABLE_POLICY
– ENABLE_POLICY

Database Security & Auditing: Protecting Data Integrity & Accessibility 39

 Fine-grained Auditing (FGA) with
Oracle (continued)
• ADD_POLICY parameters:
– OBJECT_SCHEMA
– OBJECT_NAME
– POLICY_NAME
– AUDIT_CONDITION
– AUDIT_COLUMN
– HANDLER_SCHEMA
– HANDLER_MODULE
– ENABLE
– STATEMENT_TYPES
• DBA_FGA_AUDIT_TRAIL: view the audit trail of the
DML activities

Database Security & Auditing: Protecting Data Integrity & Accessibility 40

 DML Action Auditing with Oracle

• Record data changes on the table:

– Name of the person making the change
– Date of the change
– Time of the change
• Before or after value of the columns are not
recorded

Database Security & Auditing: Protecting Data Integrity & Accessibility 41

 DML Action Auditing with Oracle
(continued)

Database Security & Auditing: Protecting Data Integrity & Accessibility 42

 DML Action Auditing with Oracle
(continued)
• Steps:
– Use any user other than SYSTEM or SYS; with
privileges to create tables, sequences, and
triggers
– Create the auditing table
– Create a sequence object
– Create the trigger that will record DML
operations
– Test your implementation

Database Security & Auditing: Protecting Data Integrity & Accessibility 43

History Auditing Model Implementation
Using Oracle

• Historical data auditing is simple to implement;

main components are TRIGGER objects and
TABLE objects
• Keeps record of:
– Date and time the copy of the record was
captured
– Type of operation applied to the record

Database Security & Auditing: Protecting Data Integrity & Accessibility 44

History Auditing Model Implementation
Using Oracle (continued)

• Steps:
– Use any user other than SYSTEM or SYS; with
privileges to create tables, sequences, and
triggers
– Create history table
– Create the trigger to track changes and record
all the values of the columns
– Test your implementation

Database Security & Auditing: Protecting Data Integrity & Accessibility 45

 Project 6: Auditing

• Report your experiences on using fine-grained

auditing.
• http://www.oracle.com/technetwork/articles/idm/
fga-otn-082646.html

Database Security & Auditing: Protecting Data Integrity & Accessibility 46

 Summary

• Audit examines, verifies and validates

documents, procedures, processes
• Auditing environment consists of objectives,
procedures, people, and audited entities
• Audit makes sure that the system is working
and complies with the policies, standards,
regulations, and laws
• Auditing objectives established during
development phase

Database Security & Auditing: Protecting Data Integrity & Accessibility 47

 Summary (continued)

• Objectives: compliance, informing, planning,

and executing
• Classifications: internal, external, automatic,
manual, hybrid
• Models: Simple Auditing 1, Simple Auditing 2,
Advanced Auditing, Historical Data, Auditing
Applications, C2 Security

Database Security & Auditing: Protecting Data Integrity & Accessibility 48