Reliability and Security - Data Base Management System - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Reliability and Security - Data Base Management System - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Chapter 15:

Reliability and Security

in Database Servers

Agenda

• Reasons why backup and recovery are important to database

servers

• Strategies for recovery from failures
• Ways in which DB systems define and enforce security control

Potential Reasons of Database Failure

• 1. The database server computer crashes.
• 2. The database server program crashes.
• 3. A database client computer crashes.
• 4. A client program crashes.
• 5. The network connection between client and server fails.
• 6. A transaction executes a rollback operation.
• 7. A transaction executes an illegal operation.
• 8. Two or more transactions deadlock.
• 9. One or more transactions introduce errors into the database.
• 10. Data on a disk drive is corrupted.

Recovery Strategies

• Recovery via Reprocessing:
  • Recover the database state from a backup and reprocess all transactions that have occurred since the backup was created
  • Drawback: does not guarantee durability by reprocessing, except in the most limited cases (i.e. applications that do not respond to direct user input)

Recovery Strategies (cont’d)

• Recovery via Roll Forward:
  • Recover database state from a backup and then reapply all changes of committed transactions in same order that they were originally committed using redo logs
  • Advantage: Full durability using physically remote backups and redo logs

Recovery Strategies (cont’d)

• Recovery via Rollback:
  • Recover to a previous correct database state by removing the effect of corrupted transactions by using undo logs
  • Advantage: More efficient than Rollforward when database has not crashed and only few bad transactions

Recovery Strategies (cont’d)

• Recovery from Disk Corruption:
  • Mostly recovery done by backup and roll forward
  • If some disk pages corrupted might be possible to recover from disk cache
  • Else, recreate corrupted pages using transaction logs

Security in Relational

Database Systems

• Database security starts with physical security
• DBMS uses two main types of security:
  • Account security for User Authorization
  • Access security for Protection of Database Objects

Security in Relational

Database Systems (cont’d)

• User Authorization
  • Database stores identifiers and passwords in system tables
  • Each connection by user or client program must be authenticated as a valid database user
  • System provides commands to create, alter, and drop users
  • Examples: sample SQL statements to manipulate user accounts create user Jane identified by starfish; drop user Jane;

Security in Relational

Database Systems (cont’d)

• Protection of Database Objects
  • Database defines a collection of privileges granted to users
  • Access privileges restrict (and allow) access by specific users to specific operations on specific objects.
  • Role capability used to grant collection of privileges to many users
  • Examples: sample SQL statements affecting database privileges grant insert on Customer to Jane ; grant select on Customer to public ; create role FloorManager identified by ImInCharge ; grant role John to FloorManager ;