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