Description
Overview:
This homework provides an opportunity for the student to create Oracle 12 C audit policies to help
better secure sensitive data stored in Oracle database tables.
Assignment: Total 100 points
Using the information and examples provided in the readings for week 7 and 8, create a test user and
associated policies and test scenarios to audit the test user against the following privileges (available in
the system_privilege_map Oracle 12C object):
1. Create Any Table
2. Drop Any Table
3. Create User
4. Drop User
5. Update any Table
The following are some additional guidance, requirements and hints for this assignment:
a. Create a test user named SDEV350User with access to unlimited space on the User’s tablespace
b. Allow the SDEV350User to be able to create sessions, create any table, drop any table, create
users, drop user, and update any table.
c. Create policies for each of the 5 possible privileges
d. Create test scenarios (e.g. SQL scripts that you can run) that will demonstrate the audit of the
specific privileges is taking place.
e. Provide the specific results of running the test scenarios and explain the output of each query.
f. Discuss how this type of audit policy could help protect the integrity of the database.
g. Provide detailed, step-by-step instructions on how to run your SQL scripts and test scenarios.
Be sure your SQL scripts work perfectly and your supporting documentation is neat, well-organized and
well-written.
Deliverables:
1. Create a word or PDF document that describes your process, steps and results. Be sure to
describe your schema and the queries you are using for your application. Provide screen shots
showing the successful running of all of your SQL statements and testing your scenarios. Be sure
your testing is comprehensive demonstrating all functionality. The document should be neat,
well-organized, well-written and contain minimal grammar and spelling errors.
2. A single SQL script file that contains all of the SQL statements used to set-up and run the test
scenarios. Be sure to include the connection statements as well as often the sys account would
need to run. Clearly, define with SQL statements and comments which user is running which SQL
statements.
2
Grading Rubric:
Attribute Meets Does not meet
Audit Policy 75 points
Creates a test user named
SDEV350User with access to
unlimited space on the User’s
tablespace. (10 points)
Allows the SDEV350User to be able
to create sessions, create any table,
drop any table, create users, drop
user, and update any table. (10
points)
Creates policies for each of the 5
possible privileges. (15 points)
Creates test scenarios (e.g. SQL
scripts that you can run) that will
demonstrate the audit of the
specific privileges is taking place.
(20 points)
Provide the specific results of
running the test scenarios and
explain the output of each query.
(10 points)
Discusses how this type of audit
policy could help protect the
integrity of the database. (5 points)
Provides detailed, step-by-step
instructions on how to run your
SQL scripts and test scenarios. (5
points)
0 points
Does not create a test user named
SDEV350User with access to unlimited
space on the User’s tablespace.
Does not allow the SDEV350User to be
able to create sessions, create any table,
drop any table, create users, drop user,
or update any table.
Does not create policies for each of the
5 possible privileges.
Does not create test scenarios (e.g. SQL
scripts that you can run) that will
demonstrate the audit of the specific
privileges is taking place.
Does not provide the specific results of
running the test scenarios or explain the
output of each query.
Does not discuss how this type of audit
policy could help protect the integrity of
the database.
Does not provide detailed, step-by-step
instructions on how to run your SQL
scripts or test scenarios.
Documentation and
submission
25 points
Creates a word or PDF document
that describes your process, steps
and results. (5 points)
Describes your schema and the
queries you are using for your
application. (2 points)
0 points
Does not create a word or PDF
document that describes your process,
steps and results.
Does not describe your schema or the
queries you are using for your
application.
3
Provides screen shots showing the
successful running of all of your
SQL statements and testing your
scenarios. (3 points)
The document should be neat,
well-organized, well-written and
contain minimal grammar and
spelling errors. (5 points)
Includes a single SQL script file that
contains all of the SQL statements
used to set-up and run the test
scenarios. (5 points)
Includes the connection statements
as well as often the sys account
would need to run. (3 points)
Clearly defines with SQL
statements and comments which
user is running which SQL
statements. (2 points)
Does not provide screen shots showing
the successful running of all of your SQL
statements and testing your scenarios.
The document is not neat, wellorganized, well-written and contain
multiple grammar and spelling errors.
Does not include a single SQL script file
that contains all of the SQL statements
used to set-up and run the test
scenarios.
Does not include the connection
statements as well as often the sys
account would need to run.
Does not clearly define with SQL
statements or comments which user is
running which SQL statements.