database auditing models dr. gabriel. 2 auditing overview audit examines: documentation that...
TRANSCRIPT
![Page 1: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/1.jpg)
Database Auditing ModelsDatabase Auditing Models
Dr. Gabriel
![Page 2: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/2.jpg)
2
Auditing OverviewAuditing Overview
• Audit examines: documentation that reflects (from business or individuals); actions, practices, conduct
• Audit measures: compliance to policies, procedures, processes and laws
![Page 3: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/3.jpg)
3
DefinitionsDefinitions
• 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
![Page 4: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/4.jpg)
4
Definitions (continued)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
![Page 5: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/5.jpg)
5
Definitions (continued)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
![Page 6: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/6.jpg)
6
Auditing ActivitiesAuditing 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
![Page 7: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/7.jpg)
7
Auditing Activities (continued)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
![Page 8: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/8.jpg)
8
Auditing Activities (continued)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
![Page 9: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/9.jpg)
9
Auditing Activities (continued)Auditing Activities (continued)
• Identify security issues that must be addressed• Provide consultation to the Legal Department
![Page 10: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/10.jpg)
10
Auditing EnvironmentAuditing 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
![Page 11: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/11.jpg)
11
Auditing Environment (continued)Auditing Environment (continued)
![Page 12: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/12.jpg)
12
Auditing Environment (continued)Auditing Environment (continued)
![Page 13: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/13.jpg)
13
Auditing ProcessAuditing 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
![Page 14: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/14.jpg)
14
Auditing Process (continued)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
![Page 15: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/15.jpg)
15
Auditing Process (continued)Auditing Process (continued)
![Page 16: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/16.jpg)
16
Auditing Process (continued)Auditing Process (continued)
![Page 17: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/17.jpg)
17
Auditing ObjectivesAuditing 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
![Page 18: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/18.jpg)
18
Auditing Objectives (continued)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
![Page 19: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/19.jpg)
19
Auditing Objectives (continued)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
![Page 20: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/20.jpg)
20
Auditing Classifications and TypesAuditing Classifications and Types
• Industry and business sectors use different classifications of audits
• Each classification can differ from business to business
![Page 21: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/21.jpg)
21
Audit ClassificationsAudit 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
![Page 22: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/22.jpg)
22
Audit Classifications (continued)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
![Page 23: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/23.jpg)
23
Audit Classifications (continued)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
![Page 24: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/24.jpg)
24
Audit TypesAudit 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
![Page 25: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/25.jpg)
25
Audit Types (continued)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
![Page 26: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/26.jpg)
26
Benefits and Side Effects of AuditingBenefits 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
![Page 27: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/27.jpg)
27
Benefits and Side Effects of Auditing Benefits and Side Effects of Auditing (continued)(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
![Page 28: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/28.jpg)
28
Benefits and Side Effects of Auditing Benefits and Side Effects of Auditing (continued)(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
![Page 29: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/29.jpg)
29
Auditing ModelsAuditing 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
![Page 30: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/30.jpg)
30
Auditing Models (continued)Auditing Models (continued)
![Page 31: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/31.jpg)
31
Simple Auditing Model 1Simple 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
![Page 32: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/32.jpg)
32
Simple Auditing Model 1 (continued)Simple Auditing Model 1 (continued)
![Page 33: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/33.jpg)
33
Simple Auditing Model 1 (continued)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
![Page 34: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/34.jpg)
34
Simple Auditing Model 1 (continued)Simple Auditing Model 1 (continued)
![Page 35: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/35.jpg)
35
Simple Auditing Model 2Simple 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
![Page 36: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/36.jpg)
36
Simple Auditing Model 2 (continued)Simple Auditing Model 2 (continued)
![Page 37: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/37.jpg)
37
Advanced Auditing ModelAdvanced 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
![Page 38: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/38.jpg)
38
Advanced Auditing Model (continued)Advanced Auditing Model (continued)
![Page 39: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/39.jpg)
39
Advanced Auditing Model (continued)Advanced Auditing Model (continued)
![Page 40: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/40.jpg)
40
Historical Data ModelHistorical Data Model
• Used when a record of the whole row is required
• Typically used in most financial applications
![Page 41: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/41.jpg)
41
Historical Data Model (continued)Historical Data Model (continued)
![Page 42: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/42.jpg)
42
Auditing Applications Actions ModelAuditing Applications Actions Model
• Used for auditing specific action or operation such as issuing a refund
![Page 43: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/43.jpg)
43
C2 Security RatingC2 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
![Page 44: Database Auditing Models Dr. Gabriel. 2 Auditing Overview Audit examines: documentation that reflects (from business or individuals); actions, practices,](https://reader031.vdocuments.us/reader031/viewer/2022032705/56649db25503460f94aa0e3c/html5/thumbnails/44.jpg)
44
Questions?Questions?