Transcript Reliability and Security by Neyha Amar (5/06)

Chapter 15:
Reliability and Security
in Database Servers
Neyha Amar
CS 157B
May 6, 2008
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;
References

Principles of Database Systems With Internet and Java
Applications by Greg Riccardi , 2001, Addison-Wesley

Database System: The Complete Book by Hector Garcia-Molina,
2002, Prentice Hall

http://downloadwest.oracle.com/docs/cd/A87860_01/doc/server.817/a76993/back
upst.htm