Database Auditing Models

1
Database Auditing Models

• Dr. Gabriel

2
Auditing Overview

• Audit examines documentation that reflects (from
  business or individuals) actions, practices,
  conduct
• Audit measures compliance to policies,
  procedures, processes and laws

3
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

4
Definitions (continued)

• 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

5
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

6
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

7
Auditing Activities (continued)

• 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

8
Auditing Activities (continued)

• 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

9
Auditing Activities (continued)

• Identify security issues that must be addressed
• Provide consultation to the Legal Department

10
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

11
Auditing Environment (continued)
12
Auditing Environment (continued)
13
Auditing Process

• 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

14
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

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

• Established as a part of the development process
  of the entity to be audited
• Reasons
• Complying
• Identification of policies, regulations, and
  standards that company must comply with
• Informing
• All relevant parties to be informed about these
  policies, regulations, and standards
• Planning
• Plan and document auditing procedures
• Executing
• Evaluation, verification, and review of the
  auditing entityy

18
Auditing Objectives (continued)

• Top ten database auditing objectives
• Data integrity
• Validity of data and RI
• Application users and roles
• User roles correspond to their responsibilities
  and skills
• Data confidentiality
• Data remains private for unauthorized users
• Access control
• Login time and session duration
• Data changes
• Audit train of all data changes

19
Auditing Objectives (continued)

• Top ten database auditing objectives (continued)
• Data structure changes
• Audit trail of all db structural changes
• Database or application availability
• Recording all downtimes, their duration, and
  reason
• Change control
• Tracking of changes to be made to the db or app
• Physical access
• Tracking physical access to the app or db where
  they reside
• Auditing reports
• Generation of auditing reports automatically or
  on-demand

20
Auditing Classifications and Types

• Industry and business sectors use different
  classifications of audits
• Each classification can differ from business to
  business

21
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

22
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

23
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

24
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

25
Audit Types (continued)

• 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

26
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

27
Benefits and Side Effects of Auditing (continued)

• Benefits (continued)
• 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

28
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

29
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

30
Auditing Models (continued)
31
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

32
Simple Auditing Model 1 (continued)
33
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

34
Simple Auditing Model 1 (continued)
35
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

36
Simple Auditing Model 2 (continued)
37
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

38
Advanced Auditing Model (continued)
39
Advanced Auditing Model (continued)
40
Historical Data Model

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

41
Historical Data Model (continued)
42
Auditing Applications Actions Model

• Used for auditing specific action or operation
  such as issuing a refund

43
C2 Security Rating

• Issued by National Security Administration
• Indicates satisfaction of requirements set by the
  Dept of Defense
• OK to implement in military and government
  applications
• Given to Microsoft SQL Server
• 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

44
Questions?