Transcript Reliability and Security in Database Servers

Reliability and
Security in Database
Servers
By Samuel Njoroge
Overview
Role of Database Management Systems
in creating reliable & secure systems.
 Sources of failure.
 Recovery from failure.
 Distribution, consistency, and managing
databases.
 DBMS security and access.

Database failure and recovery




Database must be able to recover from
catastrophic failure.
Database designers must always have a
recovery plan
Recovery plan ensures database is restored to a
correct state ASAP.
The plan must include what happens with new
incoming data in the interim.
Sources of database Failures.

Database server computer crashes
 Information
may be corrupted as it still resides in
memory & not HDD

Database server program crashes
 Generally

database program should never crash
Database client computer crashes
 Results
in locks and inactive transactions.
Network connection between client and
server fails
 A transaction executes a rollback operation

 A transaction
has voluntarily cancelled itself
therefore its updates must be removed from
database.
Sources of database Failures

A transaction executes an illegal operation



Two or more transactions deadlock


System must abort one or more transactions to break
dreadlock
One or more transactions induce errors into the
database


Systems detects that transaction has violated a protocol
Server must abort and recover through a rollback.
Application program runs incorrectly and updates database
introducing inconsistent or nonfactual information
Data on the disk is corrupted.

A hardware error causes some information on database to
become unavailable.
Backup and checkpoints.


Backup: Copy of the database at a certain time.
 Contains enough info for restoration to same
state prior to failure.
 Updates that happened after the backup are not
part of the state
Checkpoint: Operation that forces the
database on the disk to be in a state that is
consistent with all committed transactions.
 If disk is not corrupted use the last
checkpoint
 However if disk is corrupted, backup is used.
Transaction logs


Records all activity within the server.
Log contains:
 Unique
Transaction ID for every transaction upon
execution.
 Name of action preformed
 Object that is referenced by the action
 The Effect of the action on the object.


Transaction log used to recreate state of
database prior to failure
Log should be in remote locations
Recovery: Roll Forward

Steps:
Recover database in a correct state
Reapply all changes of the committed
transactions in same order they were
originally committed.
Pre-requisite: System must have a redo log,
which maintains all committed changes in
order.
Recovery: Roll Back.
Involves removing all the effect of transactions
since failure.
Begins from current state and undoes each
update in reverse order
Prerequisite: System must keep undo log
Undo log contains original image of each
updated value.
Once in correct state, it may be possible to redo
some transactions as long as there is no conflict.
Automatic recovery





Rollback is supported by most commercial DBMSs
Oracle has control file, containing redo logs, timestamps,
names, locations of Databases.
When failure occurs DB admin must first copy the
damaged file from best backup.
Recover command prompts for each redo log that must
be run.
Oracle has both “hot” and “cold” backups. Hot backup
when in use, cold backup - when database is unavailable
to users
Security in Relational Database
System.
Security always begins with the physical
security of the actual systems.
 Database system must have support for 3
types of security:

 Account
security: validates users
 Access security: protection of DB objects
 Operating System security: Protects files
User Authorization: MySQL .
*







Create user Jane identified by ‘crockette’;
alter user Dick account lock;
alter user Dick identified by stop-please;
alter user Dick password expire;
create profile LimitedUser limit
CONNECT_TIME 10;
create user OPS$hannibal profile LimitedUser;
alter user OPS$hannibal identified by use-thispassword;
Protection of Database objects

Involves privileges, to read, updated,
append, create and drop.
 grant
insert on Customer to Jane;
 grant select on Customer to public;
 grant all on Employee to Jane;
 revoke delete on Employee from Jane;
 grant update on Customer(street, city, state,
zip) to Jane;
Protection of Database objects
create role Clerk not identified;
 grant all on Rental, PreviousRental to
Clerk;
 grant role Clerk to Dick;
 create role FloorManager identified by
ImInCharge;
 grant role Clerk to FloorManager;

Summary
Reliability
 Backups and checkpoints
 Recovery via Roll Forward and Rollback
 Recovery from disk corruption
 Security: User authorization
 Security: protection of database objects

Works cited:

Principles of Database Systems with
Internet and Java Applications
- by Greg Riccardi