Database Recovery Techniques and Security

Posted on Jan 3, 2025 in Computers

Purpose of Database Recovery

To bring the database into the last consistent state, which existed prior to the failure.

To preserve transaction properties (Atomicity, Consistency, Isolation, and Durability).

Types of Failure

Transaction failure: Transactions may fail because of incorrect input, deadlock, or incorrect synchronization.

System failure: System may fail because of addressing error, application error, operating system fault, RAM failure, etc.

Media failure: Disk head crash, power disruption, etc.

Two Main Data Update Techniques

Immediate Update: As soon as a data item is modified in the buffer and BEFORE the transaction completes execution, the disk copy is updated.

Deferred Update: All modified data items in the buffer are written to disk either AFTER a transaction ends its execution or after a fixed number of transactions have completed their execution.

Two Main Strategies for Flushing Modified Buffer

In-place updating: Writes the buffer to the same original disk location, thus overwriting the old values of data items on disk.

Shadowing: Writes the updated buffer to a different disk location, so multiple versions of data items can be maintained.

Transaction Roll-back (Undo) and Roll-Forward (Redo)

To be able to recover, a transaction’s operations are redone or undone.

Undo: Restore all BFIMs (data item values before modification) onto disk.

Redo: Restore all AFIMs (data item values after modification) onto disk.

Database recovery is achieved either by performing only Undos or only Redos or by a combination of the two.

Checkpoint Operation

The following steps define a checkpoint operation:

• Suspend execution of transactions temporarily.
• Force write modified buffer data to disk.
• Write a [checkpoint] record to the log, save the log to disk.
• Resume normal transaction execution.

Recovery Techniques

Recovery Techniques Based on Deferred Update

Recovery Techniques Based on Immediate Update

Deferred Update Protocol

A typical deferred update protocol works as follows:

A transaction cannot change the database on disk until it reaches its commit point.

A transaction does not reach its commit point until its update operations are recorded in the log and the log is force-written to disk.

Using the COMPANY database schema provided and assuming that

User X can access the first name, middle initial, and last name of all female employees. (2 points)

CREATE VIEW FEMALE_EMPS AS
SELECT FNAME,MINIT,LNAME
FROM EMPLOYEE
WHERE SEX='F';
GRANT SELECT ON FEMALE_EMPS TO X;

User Y can only update the attributes Mgr_ssn and Mgr_start_date of DEPARTMENT

GRANT UPDATE ON DEPARTMENT(Mgr_ssn,Mgr_start_date) TO Y;
CREATE VIEW MALE_DEP AS
SELECT *
FROM DEPENDENT
WHERE SEX='M';
GRANT SELECT ON MALE_DEP TO Y;

1. Use Role-Based Access Control (RBAC) to implement the role hierarchy shown in Figure 1

roleA: UPDATE DEPT_LOCATIONS, UPDATE Plocation only of PROJECT, SELECT privilege on DEPARTMENT, SELECT privilege on EMPLOYEE, UPDATE privilege on DEPENDENT

roleB: UPDATE Plocation only of PROJECT, SELECT privilege on DEPARTMENT, SELECT privilege on EMPLOYEE, UPDATE privilege on DEPENDENT

roleC: SELECT privilege on DEPARTMENT, SELECT privilege on EMPLOYEE, UPDATE privilege on DEPENDENT

Figure 1: Role Hierarchy

CREATE ROLE roleA;
CREATE ROLE roleB;
CREATE ROLE roleC;
GRANT SELECT ON DEPARTMENT, EMPLOYEE TO roleC;
GRANT UPDATE ON DEPENDENT TO roleC;
GRANT roleC to roleB;
GRANT UPDATE ON PROJECT(Plocation) to roleB;
GRANT roleB to roleA;
GRANT UPDATE ON DEPT_LOCATIONS to roleA;

1. Consider the following EMPLOYEE table and then answer the question that follows: (2.5 points)

0517001 Allen Anderson 820-9090 3500 1

0517002 Ron Frank 820-8723 4500 1

0517003 Peter Jordan 820-1321 5500 1

0517004 Rob Dickson 820-8356 2500 2

0517005 Ron Frank 820-8723 3500 2

0517006 John Jordan 820-1964 1500 2

Think of a statistical query that can infer the salary of the employee Allen Anderson without using his ID, Name, or phone. Assume that all you know about him is his department number and the fact that he is the lowest paid employee in his department.

SELECT MIN(SALARY)
FROM EMPLOYEE
WHERE Dnumber=1;