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 procedure that adds SQL Server tables, restricts access to those tables, checks the tables data integrity, and monitors those tables using a database audit.

Requirements

This Assignment is designed to use ORACLE. Review the links below before starting this work.

·








Creating ORACLE Virtual Private Database Policies --

https://docs.oracle.com/database/121/DBSEG/vpd.htm#DBSEG278

·








Setting up Oracle’s auditing features -

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

·

Instructions

Create a new WORD document as your submission file.

Each instruction (e.g. 1, 2, 3….) must show all SQL (scripts/results) and ensure the screenshots are identified by which instruction they relate to.

Each step must be supported by a set of SQL scripts and the scripts/results must be screenshots and pasted in the submission document.

Remember to include your name, your student number, section, and the name of the assignment.

1.








Create a new table – ASSIGNMENT1. Includes steps to create this table.

a.





The table has one column - COLUMNA and 100 rows.

b.





Create a script to insert rows into ASSIGNMENT1 table. There needs to be several rows which have a text string beginning with the letter ‘A’, several rows beginning with the letter ‘M’ and several rows beginning with the letter ‘Z’.

c.





Provides a ‘select * from ASSIGNMENT1’ showing the contents of your new table

2.








Create three new users: USERA, USERB, USERC

a.





Include a screen shot of the scripts used to create this user.

3.








Create five roles. RL_READONLY, RL_TBLACCESS, RL_ROWA_READ, RL_ROWM_READ and RL_ROWZ_READ.

a.





Include a screen shot of the scripts used to create these roles.

b.





When creating each role, give each ROLE their correct privileges. For example, RL_READONLY should only be granted the ‘select’ privilege on ASSIGNMENT1.

4.








Create the security access rules


for your new table (


at

both


the




table level





and the




row level





access


) --




DO NOT







use views to implement this. This link will provide details on to set up the ‘security policies’ for row-level access -- https://docs.oracle.com/database/121/DBSEG/vpd.htm#DBSEG007

Read through the details related to using Oracle’s

row level security options chose the one you feel supports the following business requirement. Be sure to indicate the ‘choice’ you have made (e.g. VPD, Label security or Data Redaction) -- Note, if you feel you need to ‘alter’ your table – ASSIGNMENT1 feel free to do so.

a.





Provide each of the three new users with the appropriate privileges that allow the following access:

i.





Read-only access to ASSIGNMENT1

ii.





USERA can only see rows in ASSIGNMENT1 which have values in

columnA


which begin with ‘A’

iii.





USERB can only see rows in ASSIGNMENT1 which have values in

columnA


which begin with ‘M’

iv.





USERC can only see rows in ASSIGNMENT1 which have values in

columnA


which begin with ‘Z’

5.








Setup Database Audit. Includes the steps to setup the database audit in Oracle (refer to link in the resource section above).

a.





Use

scripts


to implement the audit. DO NOT use the menu options.

b.





You will test to make sure changes are being added to the log.

c.





Create a new table with one or two columns

d.





Insert five rows into the table.

e.





Delete one row from the table.

f.





Update one row.

g.





Select one row.

h.





Use a different row for each of the statements (e, f and g)

i.





Query the log showing the transactions and screenshot/paste the audio log

Please see attached the full instructions document.