Database Security and Auditing: Protecting Data Integrity and Accessibility

1
Database Security and Auditing Protecting Data
Integrity and Accessibility

• Chapter 7
• Database Auditing Models

2
Objectives

• Gain an overview of auditing fundamentals
• Understand the database auditing environment
• Create a flowchart of the auditing process
• List the basic objectives of an audit

3
Objectives (continued)

• Define the differences between auditing
  classifications and types
• List the benefits and side effects of an audit
• Create your own auditing models

4
Auditing Overview

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

5
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

6
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

7
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

8
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

9
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

10
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

11
Auditing Activities (continued)

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

12
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

13
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

14
Auditing Environment (continued)
15
Auditing Environment (continued)
16
Auditing Environment (continued)

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

17
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

18
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

19
Auditing Process (continued)
20
Auditing Process (continued)
21
Auditing Objectives

• Part of the development process of the entity to
  be audited
• Reasons
• Complying
• Informing
• Planning
• Executing

22
Auditing Objectives (continued)

• Top ten database auditing objectives
• Data integrity
• Application users and roles
• Data confidentiality
• Access control
• Data changes

23
Auditing Objectives (continued)

• Top ten database auditing objectives (continued)
• Data structure changes
• Database or application availability
• Change control
• Physical access
• Auditing reports

24
Auditing Classifications and Types

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

25
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

26
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

27
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

28
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

29
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

30
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

31
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

32
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

33
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

34
Auditing Models (continued)
35
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

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

38
Simple Auditing Model 1 (continued)
39
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

40
Simple Auditing Model 2 (continued)
41
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

42
Advanced Auditing Model (continued)
43
Advanced Auditing Model (continued)
44
Historical Data Model

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

45
Historical Data Model (continued)
46
Auditing Applications Actions Model
47
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

48
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

49
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