Principles of Database Systems With Internet and Java Applications

1
Principles of Database SystemsWith Internet and
Java Applications
Todays TopicChapter 15 Reliability and
Security in Database Servers

• Instructors name and information goes here
• Please see the notes pages for more information.

2
Chapter 15 Reliability and Security

• Backup and Recovery
• When and why
• Security
• How and who
• Distributed Databases
• What and where

3
Causes of Failure, Possibilities of Recovery

• Database server
• computer crashes
• server program crashes
• disk drive corruption
• Client failure
• computer crashes
• client program crashes
• Network failure
• connection fails, often temporary
• Transaction failure
• executes rollback (voluntary)
• executes illegal operation (server created)
• deadlock
• introduces errors into the database

4
Backups, Checkpoints, Transaction Logs

• Backup is a copy of the state at a specific time
• Checkpoint is an incremental backup of changes
  since a full backup
• Database can be restarted after failure
• At backup state
• At checkpoint
• Transaction log records all actions of
  transactions
• Redo log records new values of all objects
• Undo log records old values of all objects
• Read/write logs, to check for interference

5
Recovery from failure

• Primary technique, restart from consistent
  backup/checkpoint
• Reprocessing
• ask all committed transactions to execute again
• Roll Forward
• Back to consistent backup state
• Apply redo transaction log
• Roll Back
• Remove the effect of each transaction with undo
  log
• Can be used to cancel the effects of rogue
  transactions

6
Automatic recovery

• Database server maintains control file
• Contains ID of last committed transaction
• Multiple copies in multiple locations
• Small but of crucial importance
• Server restart using control file and transaction
  logs
• Bring database back by restoring all committed
  transactions
• Remove effects of uncommitted transactions

7
Security in Relational Database Systems

• Account security for validation of users
• Database accounts
• Operating system accounts
• SQL statements for security
• create user
• alter user
• create profile
• create role
• grant privileges to users, roles

8
Stored Procedures

• Define numberRented function
• create function numberRented (accId int) return
  int as select sum() from Rental where
  Customer.accountId accId
• Define checkIn procedure
• create procedure checkIn (vidId int, cost double)
  as begin insert into PreviousRental
• Grant privileges to procedures
• grant update on PreviousRental to checkIn
• grant checkIn to clerk
• revoke update on PreviousRental to public
• User in the clerk role can update the table, no
  one else can

9
Client-Server Systems
Database
OSnet
OSdm
DBMS
OSnet
AP 2
Osnet Network Communications portion of
Operating System Osdm Data management portion
of Operating System
10
Distributed Database Systems
Osnet Network Communications portion of
Operating System Osdm Data management portion
of Operating System DDBMS Distributed Database
System
11
Distributed Databases

• Single schema with multiple servers
• Not one application connecting to multiple
  servers
• An application connects to a single server
• Fragmentation of tables
• Horizontal, rows in different servers
• Vertical, columns in different servers
• Replicated, some rows or columns in multiple
  servers
• Distributed Transactions
• Two phase commit
• Discussion in class