Database Security and Auditing: Protecting Data Integrity and Accessibility

1. Database Security and Auditing: Protecting Data Integrity and Accessibility Chapter 10 Security and Auditing Project Cases

2. Objectives • Design and implement security and auditing solutions for many common business situations Database Security & Auditing: Protecting Data Integrity & Accessibility

3. Case 1: Developing an Online Database • Web site to provide a forum for database technical tips, issues, and scripts • Technical documents • A forum where members can exchange ideas and share experiences • Online access so that members can query or try the site’s technical examples and scripts • A tips section • Technical support for error messages Database Security & Auditing: Protecting Data Integrity & Accessibility

4. Case 1: Developing an Online Database (continued) • Security requirements • 10 public host database accounts that allow multiple sessions • Password of a public host account must be reset to original setting whenever disconnects or logoffs occur • Maximum duration for a session is 45 minutes. • Allocations will be set on memory and CPU usage Database Security & Auditing: Protecting Data Integrity & Accessibility

5. Case 1: Developing an Online Database (continued) • Security requirements (continued) • Storage for public host account limited to 1 MB • Public host accounts will have privileges to create the most common database objects • Newly created database objects must be removed before logoff • Database must have the default human resources (HR) user account enabled • Session information must be recorded for future analysis Database Security & Auditing: Protecting Data Integrity & Accessibility

6. Case 2: Taking Care of Payroll • Objective • Design and implement a virtual private database for the existing payroll application • Allow each client to administer his own payroll data without violating the privacy of other clients • Platform • Oracle or SQL Server Database Security & Auditing: Protecting Data Integrity & Accessibility

7. Case 2: Taking Care of Payroll (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

8. Case 3: Tracking Town Contracts • Objective • Develop new database application to keep track of the jobs awarded to different contractors • Requirements • Track all changes made to the application data • Obtain approval of project manager before accepting contract job for more than $10,000 • Alert project manager whenever awarded job is modified to a value greater than $10,000 Database Security & Auditing: Protecting Data Integrity & Accessibility

9. Case 3: Tracking Town Contracts (continued) • Security levels • DEPARTMENT CLERK level allows clerks to add and update records. • DEPARTMENT MANAGER level allows manager to add, update, delete, and approve records • EXTERNAL CLERK level allows employees outside the department only to view data Database Security & Auditing: Protecting Data Integrity & Accessibility

10. Case 3: Tracking Town Contracts (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

11. Case 4: Tracking Database Changes • Objectives • Solve a series of database and application violations • Know who accessed these databases, modified data, and changed the data structure • Have an audit trail for all activities • Not interested in a historical data changes trail Database Security & Auditing: Protecting Data Integrity & Accessibility

12. Case 4: Tracking Database Changes (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

13. Case 5: Developing a Secured Authorization Repository • Objective • Create a security data model that will be used by the central authorization module • Model should include an auditing repository • Application users, roles, applications, and application modules • Security requirements • One database user account for the application schema owner Database Security & Auditing: Protecting Data Integrity & Accessibility

14. Case 5: Developing a Secured Authorization Repository (continued) • Security requirements (continued) • Database-assigned roles are not allowed • There must be application roles only • Application user assigned to application modules • Application user assigned a security level that indicates type of operations allowed • Operations are READ, WRITE, DELETE, and ADMINISTER • Passwords stored within security module Database Security & Auditing: Protecting Data Integrity & Accessibility

15. Case 5: Developing a Secured Authorization Repository (continued) • Security requirements (continued) • Each user has a logon identification number that will be used to logon to the application • Security model should have flexibility to logically lock, disable, and remove accounts • Application accounts must have an activation date and expiry date Database Security & Auditing: Protecting Data Integrity & Accessibility

16. Case 5: Developing a Secured Authorization Repository (continued) • Auditing requirements • Audit trail of date and time a user connects and disconnects from the application • Audit trail of application operations • Includes date and time operations were performed by the application user • Audit trail of all activities and operations performed on the security module • Auditing module coupled with security module Database Security & Auditing: Protecting Data Integrity & Accessibility