Transcript Database Auditing Models

Database Auditing Models
Dr. Gabriel
Auditing Overview
• Audit examines: documentation that reflects
(from business or individuals); actions,
practices, conduct
• Audit measures: compliance to policies,
procedures, processes and laws
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
3
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
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
6
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
7
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
8
Auditing Activities (continued)
• Identify security issues that must be addressed
• Provide consultation to the Legal Department
9
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
10
Auditing Environment (continued)
11
Auditing Environment (continued)
12
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
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
• 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
17
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
18
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
19
Auditing Classifications and Types
• Industry and business sectors use different
classifications of audits
• Each classification can differ from business to
business
20
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
21
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
22
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
23
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
24
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
25
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
26
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
27
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
28
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
29
Auditing Models (continued)
30
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
31
Simple Auditing Model 1 (continued)
32
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
33
Simple Auditing Model 1 (continued)
34
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
35
Simple Auditing Model 2 (continued)
36
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
37
Advanced Auditing Model (continued)
38
Advanced Auditing Model (continued)
39
Historical Data Model
• Used when a record of the whole row is
required
• Typically used in most financial applications
40
Historical Data Model (continued)
41
Auditing Applications Actions Model
• Used for auditing specific action or operation
such as issuing a refund
42
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
43
Questions?
44