Transcript Recovery Security and Authorization

Enterprise Database
Systems Recovery Security
and Authorization
Technological Educational Institution of Larissa
in collaboration with Staffordshire University
Larissa 2006
Dr. Georgia Garani [email protected]
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 UNDOtype 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
Example 1/2
T1
Read_item(A)
Read_item(D)
Write_item(D)
T2
Read_item(B)
Write_item(B)
Read_item(D)
Write_item(D)
T3
Read_item(C)
Write_item(B)
Read_item(A)
Write_item(A)
15
Example 2/2
[start_transaction,T3]
[Read_item(),T3,C]
* [Write_item,T3,B,15,12]
[Read_item(),T2,B]
** [Write_item,T2,B,12,18]
[start_transaction,T1]
[Read_item(),T1,A]
[Read_item(),T1,D]
** [Write_item,T1,D,20,25]
[Read_item(),T2,D]
[Write_item,T2,D,25,26]
[Read_item(),T3,A]
SYSTEM CRASH
A B C D
30 15 40 20
12
18
25
26
16
Operations before crash
READ(C)
WRITE(B)
READ(A)
T3
BEGIN
READ(B)
WRITE(B)
READ(D)
WRITE(D)
T2
BEGIN
READ(A)
READ(D)
WRITE(D)
T1
BEGIN
TIME
SYSTEM CRASH
17
A NO-UNDO/REDO algorithm
1.
2.
A transaction cannot change the database on disk until
it reaches its commit point
A transaction does not reach its commit point until all
its update operations are recorded in the log and the
log is force-written to disk. (restatement of WAL)
The database is never updated on disk until after the
transaction commits so there is no need to UNDO any
operation. REDO is needed in case the system fails
after a transaction commits but before all its changes
are recorded in the database on disk.
18
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.
19
Recovery in M-user Environment
Deferred update
We consider a system where concurrency control uses strict twophase 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.
20
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.
21
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.
22
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.
23
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
24
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.
25
Recovery in M-user Environment
Immediate update (UNDO/REDO)
The log uses checkpoints and the concurrency control protocol
produces strict schedules.
PROCEDURE RIU_M
1. Use two lists of transactions maintained by the system: the
committed transactions since the last checkpoint and the active
transactions.
2. 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.
3. Redo the write_item operations of the committed transactions from
the log, in the order in which they were written in the log.
26
Shadow Paging
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
27
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
28
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.
29
Catastrophic failures
Backup both the database and the log.
Restore both the database and the log.
30
Example SQL server
Exercise: Backup a database using
Transact SQL commands
31
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.
32
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.
33
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.
34
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.
35
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.
36
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.
37
The DBA
The DBA performs the following actions
Account creation
Privilege granting
Privilege revocation
Security level assignment
38
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.
39
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.
40
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
41
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
42