Database Security, Integrity and Recovery

1
Database Security, Integrityand Recovery
2
Database Security and Integrity

• Definitions
• Threats to security
• Threats to integrity
• Resolution of Problems

3
Database Security

• SECURITY
• Protecting the database from unauthorised users
• Ensures that users are allowed to do the things
  they are trying to do

4
Database Security

• INTEGRITY
• Protecting the database from authorised users
• Ensures that what users are trying to do is
  correct

5
Database Security

• TYPES OF SYSTEM FAILURES
• 1. HARDWARE
• DISK , CPU , NETWORK
• 2. SOFTWARE
• SYSTEM, DATABASE, PROGRAM

6
Database Security

• Important security features include
• Views
• Authorisation controls
• User defined procedures
• Encryption procedures

7
Authorisation Rules

• An example a person who can supply a particular
  password may be authorised to read any record,
  but cannot modify any of those records.
• Authorisation Table for subjects i.e. Salesperson

8
Authorisation Rules

• Authorisation Table for Objects i.e. Order Records

9
Database Integrity

• CONSTRAINTS
• Can be classed in 3 different ways
• 1. Business constraints
• 2. Entity constraints
• 3. Referential constraints

10
Database Integrity

• BUSINESS CONSTRAINTS
• A value in one column may be constrained by
  value of another or by some calculation
• or formulae.

11
Database Integrity

• ENTITY CONSTRAINTS
• Individual columns of a table may be constrained
  e.g. not null
• REFERENTIAL CONSTRAINTS
• Some times referred to as key constraints, e.g.
• Table 2 depends on Table 1

12
Database Integrity

• create table account_dets
• (acc_id char(6) primary key,
• acc_custid char(6) references customer(cust_id),
• acc_odraft number(4) check (acc_odraft lt 200),
• acc_type char(2) constraint type_chk
• check (acc_type in (AB, CD, EF)),
• acc_crtdate date not null)

13
Database Integrity

• BENEFITS OF USING CONSTRAINTS
• Guaranteed integrity and consistency
• Defined as part of table definition
• Applies across all applications
• Cannot be circumvented
• Application development productivity
• Requires no special programming
• Easy to specify and maintain(reduced coding)
• Defined once only

14
Database Integrity

• CONCURRENCY CONTROL
• WHAT IS IT?
• The co-ordination of simultaneous requests, for
  the same data, from multiple users

15
Database Integrity

• CONCURRENCY CONTROL
• WHY IS IT IMPORTANT?
• Simultaneous execution of transactions over a
  shared database may create several data integrity
  and consistency problems

16
Database Integrity

• Janet Time John
• 1. Read balance (1000)
• 1. Read Balance (1000)
• 2. Withdraw 200 (800)
• Balance 800 2. Withdraw 300 (700)
• 3. Write balance
• Balance 800 3. Write Balance
• Balance 700
• ERROR

17
Database Integrity

• The three main integrity problems are
• Lost updates
• Uncommitted data
• Inconsistent retrievals

18
Database Integrity

• LOCKING
• Two kinds of Locks
• 1. Shared Locks (allows read only access)
• 2. Exclusive Locks (prevents reading of a
• record)

19
Database Integrity

• Time
• User 1 User2
• 1. Lock record X
• 1. Lock record Y
• 2. Request record Y
• 2. Request Record X
• (Wait for Y) (Wait for X)
• DEADLOCK

20
Database Recovery

• The process of restoring the database to a
  correct state in the event of a failure, e.g.
• System Crashes
• Media Failures
• Application Software Errors
• Natural Physical Disasters
• Carelessness
• Sabotage

21
Database Recovery

• Basic Recovery Facilities
• Backup Facilities
• Journaling Facilities
• Checkpoint facilities
• Recovery Facilities

22
Transactions

• Basic unit of recovery
• Properties of Transaction
• Atomicity
• Consistency
• Isolation
• Durability
• Purpose of recovery manager is to enforce
  Atomicity and Durability

23
Staff Salary Update Example

• Read Operations
• Find address of the disk block that contains
  record with primary key x
• transfer block into a DB buffer in main memory
• copy salary data from DB buffer into variable
  salary
• Write Operations
• as steps 1 2 above
• copy salary data from variable salary into the DB
  buffer
• write DB buffer back to disk

24
Storing Data
Buffer contents flushed to secondary storage
permanent
Main Memory
buffer full
Database Buffer
Secondary Storage
Commit
25
Database Update Procedures
26
Back-up Facilities

• DBMS provides a mechanism for taking backup
  copies of the database and log file at regular
  intervals.
• A dump or copy or backup file contains all or
  part of the database
• backups taken without having to stop the system

27
Journal Facilities

• REDO LOGS
• This is the main logging file. The file contains
  two different types of logging records.
• AFTER IMAGES
• BEFORE IMAGES

28
Journal Facilities

• REDO LOGS - AFTER IMAGES
• After any column of any row on any table in
  the database is changed, then the new values are
  not only written to the database but also to the
  redo log. The complete row is written to the
  log. If a row is deleted then notification is
  also put on to the redo log. After images are
  used in roll forward recovery.

29
Journal Facilities

• REDO LOGS - BEFORE IMAGES
• Before a row is updated the data is copied
  to the redo log. It is not a simple copy from the
  database because a separate area of the database
  maintains the immediate pre-update version of
  each row updated in the database. The extra area
  is called the ROLLBACK SEGMENT. The redo log
  takes before image copies from the rollback
  segment in the database.

30
Sample Log File
31
Types of Recovery

• Duplicate Databases
• Rollback Recovery
• Rollforward Recovery
• Reprocessing Transactions

32
Duplicate Databases

• Requires 2 copies of the database
• Advantages
• Fast Recovery (seconds)
• Good for disk failures
• Disadvantages
• No protection against power failure
• Expensive

33
Rollback Recovery

• Changes made to the database are undone
• (Backward Recovery )
• Rollback enables the updating to be undone to a
  predetermined point in the database processing
  that provides a consistent database state.

34
Database Update Procedures
35
Rollback Recovery
Database (with changes)
Database (without changes)
ROLLBACK
Before Images
36
Roll Forward Recovery

• This recovery technique updates an out-of-date
  database up-to-the current processing position.
• If the data is inconsistent then the database may
  need to rollback to the previous consistent
  state.

37
Database Update Procedures
38
Roll Forward Recovery
Database (with changes)
ROLL FORWARD
39
Reprocessing Transactions

• Similar to Forward Recovery
• Uses update transactions instead of after images
• ADVANTAGES
• Simple
• DISADVANTAGES
• Slow

40
Database Update Procedures
41
Database Recovery Procedures
42
Summary

• This lecture has looked at security and recovery
  procedures
• Ensuring that these two are administered
  correctly cuts out the majority of problems with
  database administration

43
Further Reading

• Security
• Connolly Begg, chapter 19
• Concurrency Control
• Connolly Begg, chapter 20?
• Integrity and Recovery
• Connolly Begg, chapters 18 and 19?
• Next session
• Advanced Relational Theory