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

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 sites technical examples and scripts
• A tips section
• Technical support for error messages

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

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

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

7
Case 2 Taking Care of Payroll (continued)
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

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

10
Case 3 Tracking Town Contracts (continued)
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

12
Case 4 Tracking Database Changes (continued)
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

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

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

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