CST$8276-$ Advanced Database Topics

Assignment $3$: Implementing Data Integrity and Security $(10\%)$

This assignment relates to the following Course Learning Requirements:

CLR $2-$ Administer a DBMS using knowledge of SQL$,$ database security features, globalization and database architecture $($storage$,$ memory and processes$)$

CLR $3-$ Manage database system security and privacy controls

CLR $6-$ Build database systems that directly support internationalization and globalization

CLR $7-$ Explore and gain practical experience in current advanced database technology

Objectives of the Assignment

You will draft a script to create a table and restrict it$$s access at the table and row level. You will then setup an audit on the table to track CRUD operations on that table.

Resources

Review the following Oracle resources before starting the assignment.

$$ Creating ORACLE Virtual Private Database Policies

$$ Auditing CDB and PDB level in Oracle Multitenant $($managescript$.$com$)$

Instructions

Copy$/$paste all screenshots within their designated areas in the assignment instructions.

$1--$ Using SQLPLUS $,$ connect to SYS and create a new USER for this assignment named USER$\_$A$3.$ Connect to that user and create a table called ASSIGN$\_3$ with one column. Name that column after your own first name $($eg$.$ Bob, Sarah, Mehmet$)$ and make it a primary key.

Create a series of FOR..LOOPs and the $||$ operator to populate the table with test data $-$

$-40$ rows where the first letter in the column is A$.$ Have A be followed by a number.

$-30$ rows where the first letter in the column is M$.$ Have M be followed by a number.

$-20$ rows where the first letter in the column is Z$.$ Have Z be followed by a number.

$-10$ rows where the first letter in the column is B$.$ Have B be followed by a number.

$-$ all rows must be unique

Run SELECT $*$ FROM ASSIGN$\_3$ to confirm your table was populated.

Provide screenshots showing your SQL statements used that were successfully ran to create the user, create the table, populate the table, and the SELECT showing the population was successful.

$2--$ Use SQLPLUS, connect as SYS and create the following roles and grant them the table$-$level privileges to the ASSIGN$\_3$ table

$-$ RL$\_$READ$\_$ONLY access

$-$ RL$\_$CRUD access

$-$ RL$\_$ROW$\_$A access

$-$ RL$\_$ROW$\_$M access

$-$ RL$\_$ROW$\_$Z access

Do not use views. Provide screenshots showing your SQL statements used that were successfully ran to create each role and apply each grant.

$3--$ Use SQLPLUS, connect to SYS and create the users USER$\_$A$,$ USER$\_$B$,$ USER$\_$C$,$ USER$\_$D$,$ USER$\_$ E with the needed privileges to access tables owned by the ASSIGN$\_3$ user. Then assign the roles to the corresponding user.

$-$ grant RL$\_$ROW$\_$A to USER$\_$A

$-$ grant RL$\_$ROW$\_$M to USER$\_$B

$-$ grant RL$\_$ROW$\_$Z to USER$\_$C

$-$ grant RL$\_$READ$\_$ONLY to USER$\_$D

$-$ grant RL$\_$CRUD to USER$\_$E

Provide a screenshot showing your SQL statements used that were successfully ran to create each role and apply each grant.

$4--$ Using SQLPLUS, connect to USER$\_$A$3$ and create a VPD policy that checks the role of the user accessing the table. To determine the role in your VPD function, use SELECT $...$ INTO, the SYS$.$DBA$\_$ROLES table, the SYS$\_$CONTEXT function, and the COUNT function. To avoid a compile error, you will need to grant select access on DBA$\_$ROLES to USER$\_$A$3.$ Code the following logic $--$

$-$ IF the role is RL$\_$READ$\_$ONLY or RL$\_$CRUD then return a blank predicate

$-$ IF the role is anything else, return the predicate $1<>1$

Connect to USER$\_$C$,$ USER$\_$D$,$ and USER$\_$C to test your VPD policy. For each user, run the access test SELECT $*$ FROM USER$\_$A$3.$ASSIGN$\_3$

Provide screenshots showing your SQL statements used that were successfully ran to create the function, create the policy, and shows the testing SELECT with its results for each user.

$5--$ Modify the function in Step $4.$ IF the role is RL$\_$ROW$\_$A then return a predictate that would filter out and only return rows that start start with an A$.$ Do not use double quotes. Using SQLPLUS, create your function and policy then test your modification by connecting to USER$\_$A and running the access test. Provide screenshots showing your SQL statements used that were successfully ran to create the function, create the policy, and shows the testing SELECT with its results.

$6--$ Modify the function in Step $5.$ IF the role is RL$\_$ROW$\_$M then return a predictate that would filter out and only return rows that start start with an M$.$ Using SQLPLUS, create your function and policy then test your modification by connecting to USER$\_$B and running the access test. Provide screenshots showing your SQL statements used that were successfully ran to create the function, create the policy, and shows the testing SELECT with its results.

$7--$ Modify the function in Step $6.$ IF the role is RL$\_$ROW$\_$Z then return a predictate that would filter out and only return rows that start sta