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?