Transcript Training

Principles of Database Systems
With Internet and Java Applications
Today’s Topic
Chapter 15: Reliability and Security in
Database Servers
Instructor’s name and information goes here
Please see the notes pages for more information.
1
Chapter 15 Reliability and Security

Backup and Recovery
– When and why

Security
– How and who

Distributed Databases
– What and where
2
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
3
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
4
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
5
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
6
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
7
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
8
Client-Server Systems
AP 1
Database
OSnet
AP 2
AP 2
OSnet
AP 3
OSnet
AP 2
OSnet
DBMS
OSdm
Osnet = Network Communications
portion of Operating System
Osdm = Data management portion of
Operating System
9
Distributed Database Systems
AP 1
AP 2
AP 1
AP 2
AP 2
AP 3
DDBMS
DDBMS
DDBMS
OSnet
OSdm
OSnet
OSdm
OSnet
OSdm
Database
Database
Database
Osnet = Network Communications
portion of Operating System
Osdm = Data management portion of
Operating System
DDBMS = Distributed Database System
10
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
11