Transcript Database Administration

DATABASE ADMINISTRATION
LECTURE NO 3
Muhammad Abrar
DATABASE ADMINISTRATION

A technical function that is responsible for
physical database design and for dealing with
technical issues such as security enforcement,
database performance, and backup and recovery
DATABASE SECURITY

Protection of the data against accidental or
intentional loss, destruction or misuse
THREATS TO DATA SECURITY
Accidental losses, including human error,
software, and hardware caused breaches
 Theft and fraud
 Loss of privacy or confidentiality
 Loss of data integrity
 Loss of availability

DATA SECURITY PLAN

A comprehensive data security plan will include
establishing administrative policies and
procedures, physical protections, and data
management software protections
SECURITY FEATURES OF DATA
MANAGEMENT SOFTWARE
Views or Schemas: Which restrict user views of
the database
 Authorization Rules: Which identify users and
restrict the action they may take against the
database
 User-defined procedures: Which define additional
constraints or limitations in using a database

DATA MANAGEMENT SOFTWARE
FEATURES
Encryption Procedures: Which encodes data in an
unrecognizable form
 Authorization Schemes: Which positively identify
a person attempting to gain access to the
database
 Backup, journaling, and checkpoint capabilities:
These facilitate recovery procedures

VIEWS

A subset of the database that is presented to one
or more users. A view is created by querying one
or more of the base tables, producing a dynamic
result for the user at the time of request
AUTHORIZATION RULES
 Controls
incorporated in the data
management systems that restrict access
to data and also restrict the action s that
people may take when they access data.
 For
example, a person who can supply a
password may be authorized to read any
record in the database but can not
necessarily modify those records.
AUTHORIZATION TABLES
A table that specifies who is authorized to
perform which type of action against an object
and under what condition.
 Example

AUTHORIZATION
TABLES/MATRICES

Most of the customary DBMSs don’t implement
the authorization tables directly . They rather
use simplified versions of authorization tables.
Usually two types of simple tables are used.
Authorization table for subjects
 Authorization tables for objects

AUTHORIZATION TABLE FOR
SUBJECTS(SALESPERSONS)

TABLE
READ
CUSTOMER ORDER
RECORDS RECORDS
Y
Y
INSERT
Y
Y
MODIFY
Y
N
AUTHORIZATION TABLE FOR
OBJECTS(ORDER RECORDS)

Salesperson Order Entry
(Password
(Password
BATMAN)
JOKER)
Accounting
(Password
TRACY)
Read
Y
Y
Y
Insert
N
Y
N
Modify N
Y
Y
Delete
N
Y
N
ORACLE AUTHORIZATION SCHEME

Oracle defines the following privileges that can
be granted to users at database, table, or column
levels
ORACLE PRIVILEGES

TABLE OF ORACLE PRIVILEGES
PRIVILEGES
CAPABILITY
SELECT
QUERY THE OBJECT
INSERT
INSERT RECORDS INTO THE TABLE/VIEW,
CAN BE GIVEN FOR SPECIFIC COLUMNS
UPDATE
UPDATE RECORDS IN A TABLE/VIEW, CAN
BE GIVEN FOR SPECFIC COLUMNS
DELETE
DELETE RECORDS FROM TABLE/VIEW
ALTER
ALTER ABLE
INDEX
CREATE INDEXES ON THE TABLE
REFRENCES
CREATE FOREIGN KEYS THAT REFRENCES
THE TABLE
EXECUTE
EXECCUTE THE PROCEDURE, FUNCTION, OR
TABLE
ENCRYPTION


The coding or scrambling of data so that humans
cannot read them
This facility can be used to secure sensitive data
during storage and transmission
BIOMETRIC DEVICES

Measures or detects personal characteristics such
as fingerprints, voice points, eye pictures, or
signature dynamics
BACKING UP DATABASES


Uses mechanisms for restoring a database
quickly and accurately after loss or damage
These are called recovery mechanisms
BASIC RECOVERY FACILITIES
BACKUP FASCILITIES: Which provide
periodic backup copies of the database
 JOURNALISTING FACILITIES: Which
maintain an audit trail of transactions and
database changes
 CHECKPOINT FACILITY: By this the DBMS
periodically suspends all processing and
synchronize its files and journals
 RECOVERY MANAGER: Which allows the
DBMS to restore the database to a correct
condition and restart processing transactions

JOURNALISTING FACILITIES


An audit trail of transactions and database
changes. This facility is based on the following
concepts
TRANSACTION: A discrete unit of work that
must be completely processed or not processed at
all within a computer system. entering a
customer order is an example of a transaction
JOURNALISTING FACILITIES
 TRANSACTION
LOG: Contains a record
of the essential data for each transaction
that is processed against the database
 DATABASE CHANGE LOG: Contains
before and after images of records that
have been modified by transactions
 BEFORE-IMAGE: A copy of a record or
page of memory before it has been
modified
 AFTER-IMAGE: A copy of records or
page of memory after it has been modified
CHECKPOINT FACILITY

A facility by which the DBMS periodically refuses
to accept any new transactions. The system is in
a quite state, and the database and transaction
logs are synchronized
CHECKPOINT FACILITY
 The
system refuses to accept any new
transaction. All current transactions are
completed and journal files are brought
update. Then the database and
transaction logs are synchronized. The
DBMS writes a special record called
checkpoint record to the log file which is
like the snapshot of the database. This
record contains information to restart the
system. Any dirty data blocks are written
to the system

RECOVERY PROCEDURES


Procedures used to restore the database after it
has been lost or damaged
RECOVERY MANAGER: A module of the DBMS
that restore the database to a correct condition
when a failure occur and resumes processing user
requests
TYPES OF DATABASE FAILURES
ABORTED TRANSACTIONS: A Transaction
In Processing That Terminates Abnormally
 SYSTEM FAILURE: Power failure, network
failure, hardware failure etc
 DATABASE DESTRUCTION: The database
itself is lost or destroyed or cannot be read

CONTROLLING CONCURRENT
ACCESS

The process of managing simultaneous
operations against a database so that data
integrity is maintained and the operations do not
interfere with each other in a multiuser
environment
CONCURRENCY PROBLEMS

An unrepeatable read, one that occurs when one
user reads data that have been partially updated
by another user