Database Auditing Ch' 7

1
Database Auditing (Ch. 7)

• Overview of Auditing
• Overview of Database Auditing

2
Auditing Overview

• Audit examines documentation that reflects
  actions/practices,
• AND
• Audit measures compliance to policies/procedures/
  processes and laws

3
Definitions

• Audit/auditing process of examining/validating
  documents, data, processes, procedures, systems
• Audit log contains all activities that are being
  audited ordered in a chronological manner
• Audit objectives validate compliance to business
  rules, system controls, government regulations,
  or security policies
• Auditor person authorized to audit
• Audit procedure set of instructions for the
  auditing process
• Audit report document that contains the audit
  findings
• Audit trail chronological record of document
  changes, data changes, system activities, or
  operational events

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

5
Auditing Activities

• Evaluate the effectiveness and adequacy of the
  audited entity
• Ascertain and review the reliability and
  integrity of the audited entity
• Ensure the organization complies with policies,
  procedures, regulations, laws, and standards of
  the government and the industry
• Establish plans, policies, and procedures for
  conducting audits
• Keep abreast of all changes to audited entity
• Keep abreast of updates and new audit regulations
  
• Provide all audit details to all company
  employees involved in the audit
• Publish audit guidelines and procedures
• Act as liaison between the company and the
  external audit team

6
Auditing Activities (cont.)

• Act as a consultant to architects, developers,
  and business analysts
• Organize and conduct internal audits
• Ensure all contractual items are met by the
  organization being audited
• Identify the audit types that will be used
• Identify security issues that must be addressed
• Provide consultation to the Legal Department

7
Auditing Environment

• Auditing examples
• Financial auditing
• Security auditing
• Audit also measures compliance with government
  regulations and laws
• Audits take place in an environment
• Auditing environment
• Database auditing environment

8
Auditing Environment (continued)

• Components
• Objectives an audit without a set of objectives
  is useless
• Procedures step-by-step instructions and tasks
• People auditor, employees, managers
• Audited entities people, documents, processes,
  systems

9
Auditing Environment (cont.)
10
Auditing Environment (cont.)
11
Auditing Environment (cont.)

• Database auditing environment differs slightly
  from generic auditing environment
• Security measures are inseparable from auditing

12
QA versus Auditing

• Quality Assurance (QA)
• Ensure system is bug free and functioning
  according to its specifications
• Ensure product is not defective as it is being
  produced
• Auditing process ensures that the system is
  working and complies with the policies,
  regulations and laws

13
Auditing Process (continued)

• Performance monitoring observes if there is
  degradation in performance at various operation
  times
• Auditing process flow
• System development life cycle
• Auditing process
• Understand the objectives
• Review, verify, and validate the system
• Document the results

14
Auditing Process (continued)
15
Auditing Process (continued)
16
Auditing Objectives

• Top ten database auditing objectives
• Data integrity
• Application users and roles
• Data confidentiality
• Access control
• Data changes
• Data structure changes
• Database or application availability
• Change control
• Physical access
• Auditing reports

17
Auditing Classifications and Types

• Industry and business sectors use different
  classifications of audits
• Each classification can differ from business to
  business
• Audit classifications also called types/purposes

18
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

19
Audit Classifications

• 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
• Example Price Waterhouse Coopers, Arthur Andersen

20
Audit Classifications (cont.)

• 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

21
Audit Types

• Financial audit ensures that all financial
  transactions are accounted for and comply with
  the law
• Security audit evaluates if the system is as
  secure
• Compliance audit system complies with industry
  standards, government regulations, or partner and
  client policies
• Operational audit verifies if an operation is
  working according to the policies of the company
• Investigative audit performed in response to an
  event, request, threat, or incident to verify
  integrity of the system
• Product audit performed to ensure that the
  product complies with industry standards

22
Benefits 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
• Provides a sense of security and confidence
• Identifies or removes doubts
• Makes the organization more accountable
• Develops controls that can be used for purposes
  other than auditing

23
Side Effects of Auditing

• 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

24
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

25
Auditing Models (continued)
26
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 or logon and off times

27
Simple Auditing Model 1(cont.)
28
Simple Auditing Model 1 (cont.)

• 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

29
Simple Auditing Model 1 (cont.)
Difference between backup archive ?
30
Backup, Archive explained

• backup - short-term insurance policy to help in
  disaster recovery,
• High media capacity High-performance
  read/write streaming Low storage cost per GB
• archive for ongoing rapid access to decades of
  business information.
• Data authenticity Extended media longevity
  High-performance random read access Low total
  cost of ownership

31
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

32
Simple Auditing Model 2 (cont.)
33
Advanced Auditing Model

• Called advanced because of its flexibility
• Repository is more complex
• Registers all entities fine grained auditing
  level
• Can handle users, actions, tables, columns

34
Advanced Auditing Model (cont.)
35
Advanced Auditing Model (cont.)
36
Historical Data Model

• Used when a record of the whole row is required
• Typically used in most financial applications

37
Historical Data Model (cont.)
38
Auditing Applications Actions Model
39
C2 Security

• Given to Microsoft SQL Server 2000
• Utilizes DACLs (discretionary access control
  lists) for security and audit activities
• Requirements
• Server must be configured as a C2 system
• Windows Integrated Authentication is supported
• SQL native security is not supported
• Only transactional replication is supported

40
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
• 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