Week2 - Data Control and Transaction Control Language / Assess

The questions can come in any order, so make sure you are selecting right option for all questions.

1. In a database application, a user initiates a complex transaction involving multiple steps. You want to provide a mechanism for the user to roll back to a specific point in the transaction in case of errors. Describe the sequence of TCL statements you wou

(a)
BEGIN TRANSACTION;
-- Step 1 --
SAVEPOINT step1;
-- Step 2 --
SAVEPOINT step2;
-- If an issue occurs --
ROLLBACK TO step1;
-- Continue with additional steps --
COMMIT;

2. What does the following DCL statement do in Oracle?

GRANT SELECT ON employees TO user1;

(c) Grants USER1 permission to select data from the EMPLOYEES table.

3. Your organization is implementing a new database system to store confidential project files. Different teams in the organization should have specific privileges based on their roles. Follow the below DCL plan to ensure that only authorized individuals can access

1. For ProjectManager role, grant select and update privileges on the project_files
2. For Developer role, grant select, insert and update privileges on the project_files
3. For Admin role, grant all privileges on the project_files

(a)
CREATE ROLE ProjectManager;
GRANT SELECT, UPDATE ON project_files TO ProjectManager;
CREATE ROLE Developer;
GRANT SELECT, INSERT, UPDATE ON project_files TO Developer;
CREATE ROLE Admin;
GRANT ALL PRIVILEGES ON project_files TO Admin;

4. You are tasked with designing access control for a new database system that will store sensitive customer information. Outline the DCL statements to create a role-based access control strategy where different user roles have distinct privileges based on the below

1. For CustomerServiceAgent role, grant select and update privileges on customer_data
2. For Manage role, grant select, update and delete privileges on customer_data
3. For Admin role, grant all privileges on customer_data

(a)
CREATE ROLE CustomerServiceAgent;
GRANT SELECT, UPDATE ON customer_data TO CustomerServiceAgent;
CREATE ROLE Manager;
GRANT SELECT, UPDATE, DELETE ON customer_data TO Manager;
CREATE ROLE Admin;
GRANT ALL PRIVILEGES ON customer_data TO Admin;

5. What is the purpose of the following DCL statement in Oracle?

REVOKE DELETE ON employees FROM user1;

(a) Revokes DELETE permission from USER1 for the EMPLOYEES table.

6. In a large database with multiple user roles, the HR manager needs access to view salary information of employees, while the Finance manager needs access to both view and update salary information. Describe the sequence of DCL statements you would use to

(c)
GRANT SELECT(salary) ON employees TO HR_manager;
GRANT SELECT(salary), UPDATE(salary) ON employees TO Finance_manager;

7. What does the following DCL statement do in Oracle?

GRANT SELECT, INSERT ON employees TO hr_user;

(b) Grants HR_USER permission to SELECT and INSERT data into the EMPLOYEES table.

8. What does the following REVOKE statement do in Oracle?

REVOKE UPDATE ON employees FROM user2;

(d) Revokes UPDATE permission from USER2 for the EMPLOYEES table.

9. Which of the following DCL statements in Oracle grants the most extensive access to a table?

(a) GRANT ALL PRIVILEGES

Note: Only a few questions from the test are listed here. If you are asked any other question that is not on the list, just comment the question and I'll answer the question as soon as I can.