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