Transcript Enterprise Database Systems Recovery Security and Authorization

Enterprise Database
Systems Recovery Security
and Authorization
Technological Educational Institution of Larissa
in collaboration with Staffordshire University
Larissa 2009
Dr. Georgia Garani
Dr. Theodoros Mitakos [email protected]
1
Agenda
Recovery techniques
Database security
Database authorization
2
Recovery concepts



Recovery from transaction failures means that
database is restored to the most recent consistent
state
In case of extensive damage the recovery method
restores a past copy of the database that was
backed up to archival storage
If there is not a physical damage but there is some
inconsistency the changes are reversed either by


Undoing some operations or
Redoing some operations
3
NO-UNDO/REDO Algorithm

Deferred update techniques do not update
the database on disk until after a transaction
reaches its commit point.

If a transaction fails before reaching its commit
point it will not have changed the database in any
way so UNDO is not need. It may be necessary to
REDO the effect of the operations of a committed
transaction from the log.
4
UNDO/REDO Algorithm

Immediate update techniques: The database may
be updated by some operations of a transaction
before the transaction reaches its commit point.

If a transaction fails after recording some changes in the
database but before reaching its commit point the effect of
its operations on the database must be undone; the
transaction must be rolled back.
In the general case both undo and redo may be required
during recovery.
5
UNDO/NON-REDO Algorithm

A variation of the previous algorithm where all
updates are recorded in the database before
a transaction commits requires undo only.
This called UNDO/NO-REDO algorithm.
6
Disk blocks








Cashed blocks
A directory for the cache is used to keep track of which database
items are in the buffers.
Page replacement strategy is used to select buffers for
replacement (LRU, FIFO)
Flush-replace cache buffers to make space available.
Dirty bit: Indicates whether or not a buffer has been modified
Pin-unpinned bit: If (or not) a page in the cach can be written
back to disk.
In-place updating writes the buffer back to the same original disk
location overwritting the old value of any changed data items on
disk.
Shadowing writes an updated buffer at different disk location so
multiple versions of data items can be maintained.
7
Write-Ahead logging


Write ahead logging: The BFIM of the data item is
recorded in the appropriate log entry and the log entry
is flushed to disk before the BFIM is overwritten with
the AFIM in the database on disk.
 REDO type log entry includes new value (AFIM) of the
item
 UNDO type log entry includes the old value (BFIM) of
the item
Write ahead logging: The log blocks that contain the
associated log records for a particular data block
update must first be written to disk before the data
block itself can be written back to disk.
8
Steal/no-steal


No-Steal: If a cache page updated by a
transaction cannot be written to disk before
the transaction commits.
Steal: Writing a an updated buffer before a
transaction commits. (the cache manager
needs a buffer frame for another transaction
and replaces an existing page.)
9
Force/no-force


Force approach: All pages updated by a
transaction are immediately written to disk
when the transaction commits. (otherwise is
called no-force)
Deferred update recovery scheme follows a
no-steal approach. (steal/no-force avoids the
need for very large buffer space to store all
updated pages in memory)
10
Advantages Steal/No-force


(steal: avoids the need for very large buffer
space to store all updated pages in memory)
(no-force: an updated page of a committed
transaction may still be in buffer when
another transaction needs to update it, thus
eliminating the I/O cost to read the page
again from disk)
11
Example WAL protocol




The before image of an item cannot be overwritten
by its after image in the database on disk until all
UNDO-type log records for the transaction have
been force-written to disk.
The commit operation of a transaction cannot be
completed until all the REDO-type and UNDO-type
log records for that transaction have been force
written to disk.
Active transactions: Transactions that have started
but not committed yet.
Committed, aborted transactions
12
Checkpoints
A checkpoint record into a log marks where the
system writes out to the database on disk all
DBMS buffers that have been modified.
Taking a checkpoint consists of the following
actions


1.
2.
3.
4.
Suspend execution of transactions temporarily
Force-write all main memory buffers that have been
modified to disk
Write a checkpoint record to the log, and force-write the
log to disk.
Resume executing transactions
13
Transaction Rollback


If transaction fails for whatever reason after updating
the database it may be necessary to roll back. If any
data item values have been changed by the
transaction and written to the database, they must be
restored to their previous values (BFIM).
Cascading rollback: If a transaction T is rolled back,
any transaction S that has, in the interim, read the
value of some data item X written by T must also be
rolled back. Similarly, once S is rolled back any
transaction R that has read the value of some data
item Y written by S must also rolled back and so on.
(recoverable but not strict or cascadeless schedules).
14
Recovery in S-user Environment Deferred update

PROCEDURE RDU_S: Use two lists of transactions: the committed
transactions since the last checkpoint, and the active transactions.
Apply the REDO operation to all the WRITE_ITEM operations of the
committed transactions from the log in the order in which they were
written to the log. Restart active transactions.

REDO(WRITE_OP): Redoing a write_item operation WRITE_OP
consists of examining its log entry [write_item,T,X,new_value] and
setting the value of item X in the database to new_value, which is the
after image (AFIM). (redo is idempotent)

An operation is idempotent if executing it over and over is equivalent to
executing it just once.

The result of recovery from a system crash during recovery should be
the same as the result of recovering when there is no cash during
recovery.
15
Recovery in M-user Environment
Deferred update



We consider a system where concurrency control uses strict two-phase
locking, so the locks on items remain in effect until the transaction
reaches its commit point. This ensures strict and serializable schedules.
PROCEDURE RDU_M: Use two lists of transactions: the committed
transactions T since the last checkpoint, and the active transactions T’.
REDO all WRITE operations of the committed transactions from the log,
in the order which the were written into the log. Transactions that are
active and did not commit are effectively canceled and must be
resubmitted.
Note that if a data item X has been updated more than once by
committed transactions it is only necessary to REDO the last update of
X from the log during recovery.
16
Pros and Cons of the method


It limits the concurrent execution of transactions
because all items remain locked until the transaction
reaches its commit point.
Transaction operations never need to be undone


A transaction does not record any changes n the database
on disk until after it reaches its commit point (never rolled
back during transaction execution)
A transaction will never read the value of an item that is
written by an uncommitted transaction because items
remain locked until a transaction reaches its commit point.
17
Transaction actions that do not
affect the database




E.g. generating reports, printing messages
If a transaction fails before completion
erroneous reports may be produced.
Such reports should be produced only after
the report reaches its commit point.
Do not generate reports immediately but
keep them as batch jobs which are executed
only after the transaction reaches its commit
point.
18
Recovery based on Immediate
Update

When a transaction issues an update
command the database can be updated
immediately without any need to wait for the
transaction to reach its commit point.
However an update operation must still be
recorded in the log before it is applied to the
database so we can recover in case of
failure.
19
Categories of immediate update
algorithms


If the recovery technique ensures that all updates of
a transaction are recorded in the database on disk
before the transaction commits, there is never a
need to REDO any operation of committed
transactions (UNDO/NON_REDO algorithm)
If the transaction is allowed to commit before all its
changes are written to the database we have the
most general case known as the UNDO/REDO
recovery aalgorithm
20
Recovery in S-user Environment
Immediate update (UNDO/REDO)

1.
2.
3.
PROCEDURE RIU_S
Use two lists of transactions maintained by the system: the
committed transactions since the last checkpoint and the active
transactions.
Undo all the write_item operations of the active transaction from the
log, using the UNDO procedure described below
Redo the write_item operations of the committed transactions from
the log, in the order in which they were written in the log, using the
REDO procedure described earlier.
UNDO(WRITE_OP): Undoing a write_item operation write_op consists of
examining its log entry[write_item, T,X,,old_value, new_value] and
setting the value of item X in the database to old_vale which is te
before image (BFIM). Undoing a number of write_item operations
from one or more transacions from the log must proceed in the
reverse order from the order in which the operations were written in
the log.
21
Recovery in M-user Environment
Immediate update (UNDO/REDO)


1.
2.
3.
The log uses checkpoints and the concurrency control protocol
produces strict schedules.
PROCEDURE RIU_M
Use two lists of transactions maintained by the system: the committed
transactions since the last checkpoint and the active transactions.
Undo all the write_item operations of the active transaction from the log,
using the UNDO procedure. The operations should be undone in the
reverse order from the order in which the operations were written in the
log.
Redo the write_item operations of the committed transactions from the
log, in the order in which they were written in the log.
22
Shadow Paging

Shadow paging considers the database to be made up of a number of fixed –size disk pages –
let’s say n – for recovery purposes. A directory of n entries is constructed where the ith entry
points to the ith database on the disk. The directory is kept in main memory if it is not too large
and all references –reads or writes- to database pages on disk go through it. When a transaction
begins executing the current directory –whose entry point to the most recent or current database
page o disk- is copied into a shadow directory. The shadow directory is then shaved on disk while
the current directory is used by the transaction. During transaction execution the shadow directory
is never changed. To recover from a failure during transaction execution, it is sufficient to free the
modified database pages and to discard the current directory.

This recovery scheme does not require the use of a log in a single user
environment. In multiuser environment logs and checkpoints must be
incorporated in the shadow paging technique.
Current directory – shadow directory of database pages on disk
During execution the shadow directory is never modified. Only the current
directory entries are modified.
To recover from a failure during transaction execution it is sufficient to free the
modified database pages and to discard the current directory.
It is considered as NO-REDO/NO-UNDO technique




23
Disadvantages



Difficult to keep related pages together on
disk
The overhead of writing shadow directories to
disk as transactions commit is significant
Handling of garbage collection is difficult
24
Recovery in Multidatabase
systems



Need a global recovery manager
Two phase protocol
Either all participating databases commit the
effect of transaction or one of them do.
25
Catastrophic failures


Backup both the database and the log.
Restore both the database and the log.
26
Example SQL server

Exercise: Backup a database using Transact
SQL commands
27
Types of security




Legal ad ethical issues regarding the right to
access information
Policy issues at enterprise level
System-related issues
The need to categorize the data and the
users identifying multiple security levels.
28
Threats to databases



Loss of integrity: The information must be
protected from improper modification.
Loss of availability: It refers to making objects
available to users.
Loss of confidentiality: It refers to the
protection of data from unauthorized
disclosure.
29
Database security mechanisms for
the users


Discretionary security mechanisms: These
are used to grant privileges to users,
including the capability to access specific
data files, records, or fields in a specific
mode.
Mandatory security mechanisms: These are
used to enforce multilevel security by
classifying the data and users into various
security classes and then implementing the
appropriate security policy of the enterprise.
30
Access control

Preventing unauthorized persons from
accessing the system itself either to obtain
information or to make malicious changes in
a portion of the database.
31
Statistical databases

Statistical database users are allowed to
access the database to retrieve statistical
information about a population but not to
access the detailed confidential information
on specific individuals. Security for statistical
databases must ensure that information on
individuals cannot be accessed. The
countermeasures for this security problem is
called inference control problem.
32
Flow Control Data encryption


It prevents information from flowing in such a
way that it reaches unauthorized users.
Data encryption is used to protect sensitive
data that is being transmitted via some type
of communications network. The data is
encoded using some coding algorithm. An
unauthorized user who access encoded data
will have difficulty deciphering it.
33
The DBA





The DBA performs the following actions
Account creation
Privilege granting
Privilege revocation
Security level assignment
34
Database audit





User account: Login and password
Login session: Time from log in to log out
The system log keeps track of the operations a user
applies to the database
If any tampering is suspected a database audit is
performed which consists of reviewing the log to
examine all accesses and operations applied to the
database during a certain period of time.
A database log that is used mainly for security
purposes is sometimes called audit trail.
35
SQL types of privileges



SELECT privilege on R: This gives the
account the privilege to use the select
statement to retrieve tuples from R.
MODIFY privilege on R: This gives the
account the capability to modify tuples of R.
REFERENCES privilege on R: This gives the
account the capability to reference relation R
when specifying integrity constraints.
36
EXAMPLES








GRANT CREATETAB TO A1;
The CREATETAB privilege gives account A1 the capability to create
new database tables
CREATE SCHEMA EXAMPLE AUTHORIZATION A1;
User account A1 can create tables under the schema called EXAMPLE.
It has all the relation privileges.
GRANT INSERT, DELETE ON EMPLOYEE, DEPARTMENT TO A2
GRANT INSERT, DELETE ON EMPLOYEE, DEPARTMENT TO A3
WITH GRANT OPTION
GRANT SELECT ON EMPLOYEE TO A4
REVOVE SELECT ON EMPLOYEE TO A4
37
EXAMPLES cont






CREATE VIEW A3EMPLOYEE AS
SELECT NAME, BDATE, ADDRESS
FROM EMPLOYEE
WHERE DNO=5
GRANT SELECT ON A3EMPLOYEE TO A3 WITH
GRANT OPTION
GRANT UPDATE ON EMPLOYEE(SALARY) TO A4
38