CST8276 – 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 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.

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

The table has one column – COLUMNA and 100 rows.

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’.

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

Create three new users: USERA, USERB, USERC

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

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

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

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

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.

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

Read-only access to ASSIGNMENT1

USERA can only see rows in ASSIGNMENT1 which have values in columnA which begin with ‘A’

USERB can only see rows in ASSIGNMENT1 which have values in columnA which begin with ‘M’

USERC can only see rows in ASSIGNMENT1 which have values in columnA which begin with ‘Z’

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

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

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

Create a new table with one or two columns

Insert five rows into the table.

Delete one row from the table.

Update one row.

Select one row.

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

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

Submission Requirements

To submit this assignment, submit your file as a WORD File, using the assignment upload tool in Brightspace. To access this, navigate to the Activities/Assignments link in the left-hand sidebar, and select Assignment 3 – Implementing Data Integrity and Security.

Please note: Algonquin College has a policy regarding Plagiarism. The consequences are immediate zeroes for assignments and potentially an instant fail for the whole course. It is only natural for students to work together to complete assignments.

DO NOT PLAGIARIZE each other’s work.

Assignment 3 Grading Rubric (10%)

Table 1 – Assignment 3 Grading Rubric

	Exemplary	Accomplished	Developing	Incomplete
Criteria	3	2	1	0
Add Table	All steps are included. Each step is clearly titled and is supported by SQL scripts and manual steps.	Included at least 80% of steps that are clearly titled and supported by SQL Scripts.	At least 60% of steps are included and supported with SQL scripts.	Not completed OR No SQL scripts are specified OR SQL Scripts cannot be copied & pasted for execution.
Restrict Access (Steps / Scripts)	All steps are included. Each step is clearly titled and is supported by SQL scripts and manual steps.	Included at least 80% of steps that are clearly titled and supported by SQL Scripts.	At least 60% of steps are included and supported with SQL scripts.	Not completed OR No SQL scripts are specified OR SQL Scripts cannot be copied & pasted for execution.
Setup Database Audit (Steps / Scripts)	All steps are included. Each step is clearly titled and is supported by SQL scripts and manual steps.	Included at least 80% of steps that are clearly titled and supported by SQL Scripts.	At least 60% of steps are included and supported with SQL scripts.	Not completed OR No SQL scripts are specified OR SQL Scripts cannot be copied & pasted for execution.
Setup Database Audit (Screen Shots)	Included screen shots that show all change entries in the database log file.	Included screen shots that show at least 75% of the change entries in the database file.	Included screen shots that show at least 50% of the change entries in the database file.	Not completed OR Screen shots were missing key information.

End of Assignment