Description
Overview:
This homework provides an opportunity for the student to create a Virtual Private Database in Oracle
Assignment: Total 100 points
Using the Schema you developed in Homework 1 for your e-Commerce project, design and implement a
Virtual Private Database that limits database access. Your design should be based on two of your
columns and two of your tables. (Hint: where col1 = value1 AND col2 = value2). Which columns and
tables you choose is up to you and will be based on your initial design. You may alter your tables if it
makes sense to do so.
Develop and conduct a test plan that clearly shows how access has been restricted by using the Virtual
Private Database.
In most cases you will need to create a separate policy and function for each of the two tables you
select. Use the tutorial as a reference and follow the steps for creating the policy function, creating the
virtual private database policy and testing the policy.
Deliverables:
1. Create a word or PDF document that describes your process, steps and results. Be sure to
describe your schema and why you selected the tables and columns you did for your Private
database. Provide screen shots showing the successful running of all of your SQL statements and
testing your implementation. Be sure your testing is comprehensive and clearly demonstrates
how the virtual private database restricts access to specific records. The document should be
neat, well-organized, well-written and contain minimal grammar and spelling errors.
2. A .sql script file containing all SQL statements you used for this effort. All SQL statements should
be in one file with appropriate connect statements to differentiate users who run the script. The
script file should contain comments describe each major set of SQL statements. The script
should run perfectly without error when executed from the Oracle SQL*PLUS environment.
Grading Rubric:
Attribute Meets Does not meet
Virtual Private
Database
50 points
Designs and implements a Virtual
Private Database that limits
database access. (20 points)
Design based on two of your
columns and two of your tables.
(20 points)
Creates a policy and function for
each of the tables used in Virtual
Private database. (10 points)
0 points
Does not design and implement a Virtual
Private Database that limits database
access.
Design is not based on two of your
columns and two of your tables.
Did not create a policy and function for
each of the tables used in Virtual Private
database.
2
Test plan 20 points
Develops a test plan that clearly
shows how access has been
restricted by using the Virtual
Private Database. (10 points)
Conducts the test plan showing
how the Virtual Private Database
restricted access. (10 points)
0 points
Does not develop a test plan that clearly
shows how access has been restricted
by using the Virtual Private Database.
Does not conduct the test plan showing
how the Virtual Private Database
restricted access.
Documentation and
submission
30 points
Creates a word or PDF document
that describes your process, steps
and results. (8 points)
Describes your schema and why
you selected the tables and
columns you did for your Private
database. (4 points)
Provides screen shots showing the
successful running of all of your
SQL statements and testing your
implementation. (4 points)
Testing is comprehensive and
clearly demonstrates how the
virtual private database restricts
access to specific records. (4
points)
Document is neat, well-organized,
well-written and contain minimal
grammar and spelling errors. (2
points)
Provides a .sql script file containing
all SQL statements you used for this
effort. (2 points)
All SQL statements are in one file
with appropriate connect
statements to differentiate users
who run the script. (1 points)
The script file contains comments
describing each major set of SQL
statements. (1 points)
0 points
Does not create a word or PDF
document that describes your process,
steps and results.
Does not describe your schema and why
you selected the tables and columns you
did for your Private database.
Does not provide screen shots showing
the successful running of all of your SQL
statements and testing your
implementation.
Testing is not comprehensive and does
not clearly demonstrates how the virtual
private database restricts access to
specific records.
Document is not neat, well-organized,
well-written and contain multiple
grammar and spelling errors.
Does not provide a .sql script file
containing all SQL statements you used
for this effort.
All SQL statements are not in one file
with appropriate connect statements to
differentiate users who run the script.
The script file does not contain
comments describing each major set of
SQL statements.
The script does not run perfectly
without error when executed from the
Oracle SQL*PLUS environment.
3
The script runs perfectly without
error when executed from the
Oracle SQL*PLUS environment. (4
points)