Oracle Virtual Private Database

SDEV 350 Homework

3

–

Oracle

Virtual Private Database

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 Da

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

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.

D

eliverables:

1.

Create a w

ord or PDF document that

describ

es 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 testi

ng is comprehensive and clearly demonstrate

s

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