Database Recovery, Distribution, and Access Control

Database Recovery Methods

Database recovery is achieved by performing a combination of methods.

Checkpointing Process:

One of the following is not a step in the checkpointing process.

None of the above

RDU Algorithm:

In the algorithm RDU (Recovery using Deferred Update), only REDO is needed.

Database Privileges

One of the following privileges is an example of a privilege granted at the account level.

CREATE TABLE

One of the following privileges is attribute specific.

UPDATE

Role-Based Access Control (RBAC)

Role-based access control (RBAC) is used by the majority of enterprises with more than 500 employees.

Redoing and Undoing Transactions

Redoing and Undoing transactions are required in RIU

Disadvantages of Distributed Data

Disadvantage of Distributed data include All of the above

Options for Distributing a Database

1. Data replication
2. Full copies of data distributed to different sites
3. Horizontal partitioning
4. Different rows of a table distributed to different sites
5. Vertical partitioning
6. Different columns of a table distributed to different sites
7. Combinations of the above

Database Access Control Example

CREATE ROLE unclassifiedRole;
CREATE ROLE confidentialRole;
CREATE ROLE secretRole;
CREATE ROLE topsecretRole;
GRANT SELECT ON DEPARTMENT, DEPT_LOCATIONS, PROJECT TO unclassifiedRole;
Text Box:
unclassifiedRole:DEPARTMENT, DEPT_LOCATIONS, PROJECT
GRANT SELECT ON DEPENDENT TO confidentialRole;
Text Box:
confidentialRole:DEPENDENT
GRANT unclassifiedRole TO confidentialRole;
Text Box:
confidentialRole:DEPARTMENT, DEPT_LOCATIONS, PROJECT,DEPENDENT
GRANT SELECT ON WORKS_ON TO secretRole;
Text Box:
secretRole:WORKS_ON
Text Box:
secretRole:DEPARTMENT, DEPT_LOCATIONS, PROJECT,DEPENDENT,WORKS_ON
GRANT confidentialRole TO secretRole;
Text Box:
topsecretRole:
DEPARTMENT, DEPT_LOCATIONS, PROJECT,DEPENDENT,WORKS_ON,EMPLOYEE
GRANT secretRole TO topsecretRole;
GRANT topsecretRole TO User1;
GRANT secretRole to User2;
GRANT confidentialRole to User3;
GRANT unclassifiedRole to User4

Privilege Granting Scenario

3) Consider the COMPANY schema. Suppose that all the relations were created by (and hence are owned by) user X, who wants to grant the following privileges to user accounts A, B, C, D, and E:

1.1 Account A can retrieve or modify DEPENDENT and WORKS_ON and can grant these privileges to other users. (1 point)

GRANT SELECT, UPDATE ON DEPENDENT, WORKS_ON TO A WITH GRANT OPTION;

1.2 Account B can delete entries of EMPLOYEE and DEPARTMENT. (1 point)

GRANT DELETE ON EMPLOYEE, DEPARTMENT TO B;

1.3 Account C can modify the Pname and Pnumber attributes of PROJECT. (1 point)

GRANT UPDATE ON PROJECT(Pname,Pnumber) TO C;

1.4 Account D can retrieve any attribute of the dependents who belong to the employee whose social security number is 333445555. (2 points)

CREATE VIEW DEP_VIEW AS
SELECT *
FROM DEPENDENT
WHERE ESSN=333445555;
GRANT SELECT ON DEP_VIEW TO D;

1.5 Account E can retrieve any attribute of EMPLOYEE but only for employees that have Dno = 5. (2 points)

CREATE VIEW EMP_VIEW AS
SELECT *
FROM EMPLOYEE
WHERE Dno=5;
GRANT SELECT ON EMP_VIEW TO E;