Assignment 3: Implementing Data Integrity and Security

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

- Install the latest version of SQL Server Express using the following link: https://www.microsoft.com/en-us/sql-server/sql-server-downloads. 

- Install all SQL Server tools required to perform this assignment.

- Refer to these resources when drafting your procedure.

 https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15

 https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checktable-transact-sql?view=sql-server-ver15

 https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkalloc-transact-sql?view=sql-server-ver15

 https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-server-audit-specification?view=sql-server-ver15

 https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15

 https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/view-a-sql-server-audit-log?view=sql-server-ver15

Instructions

1. Draft your procedure according to the following group of steps. 

a. Each group must include numbered steps that are clearly titled. 

i. Each step is supported by a set of SQL scripts whose text can be copied/pasted and screen shots showing the results of those scripts.

2. Add Table. Includes steps to create a table. 

a. The table has 5 columns and 100 rows. 

b. The first column value is a letter of the alphabet.  The other columns can be any value such as blank, a different value than the first column, or the same value as the first column.

c. The first row column is A, the second row is B, until the 26th row column value is Z. This means the order of the alphabet is repeated in the first column almost four times.

d. Screen shots are not required.

3. Restrict Access. Add three user roles: A, M, and Z. 

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

b. Restrict table access onto to A, M, and Z users using these roles. 

c. Restrict row access so user A will only see rows with the column value A, user M will only see rows with value M, and user Z will only see Z rows. DO NOT use views to implement this.

4. Check Data Integrity. Includes the steps to do data integrity using the terminal commands. 

a. Ensure your parameters are correct. 

b. Include screen shots.

5. Setup Database Audit. Includes the steps to setup the database audit in SQL Server.

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. Insert one row into the table. 

d. Delete one row from the table. 

e. Update one row. And select one row. 

f. Make sure each row you do the SQL operation on is different. 

g. Once complete include screen shots of the database log.

Data