Lab Virtual Private Database (VPD) by Context

In this lab, you will implement a VPD case. It is a good idea to refer to the supplemental materials. Please follow the specifications below.

Your tasks are as follows:

Connect to user SYSTEM. When you need to connect to SYSTEM schema, please use a syntax so that it will ask the user to type the password. A typical example will be: "connect system;"

Grant "DBA" role to user DBSEC. This is the same user created on a previous lab. If for some reason, the user was dropped, you need to recreate the user before proceeding.

Grant "CREATE SESSION" and "CREATE TABLE" privileges to user VPD_CLERK1.

Connect to user DBSEC/ IS481%sec. Please note that the password was from our previous lab. If for some reason, the user was dropped or changed, you need to change it back to the above user name and password.

Create "CUSTOMER" table with the following columns. Please note slight changes in the table from previous project.

Column Name	Data Type
SALES_REP_ID	NUMBER(4)
CUSTOMER_ID	NUMBER(8) NOT NULL
CUSTOMER_SSN	VARCHAR2(9)
FIRST_NAME	VARCHAR2(20)
LAST_NAME	VARCHAR2(20)
ADDR_LINE	VARCHAR2(80)
CITY	VARCHAR2(30)
STATE	VARCHAR2(30)
ZIP_CODE	VARCHAR2(9)
PHONE	VARCHAR2(15)
EMAIL	VARCHAR2(80)
CC_NUMBER	VARCHAR2(20)
CREDIT_LIMIT	NUMBER
GENDER	CHAR(1)
STATUS	CHAR(1)
COMMENTS	VARCHAR2(1024)
CTL_UPD_DTTM	DATE
CTL_UPD_USER	VARCHAR2(30)
CTL_REC_STAT	CHAR(1)

Populate the CUSTOMER table using the data in the file provided. Click here to download the data file. (If your web browser is set at auto mode, you may have to right click the word here and select "Save Link Target As" in order to download the script file.) You may either put all "insert" statements within this file, or provide a link to another file. If you use the link, please make sure your link works on your computer.

Query the table "CUSTOMER" to show the total number of rows from each user

Grant SELECT, DELETE, INSERT and UPDATE privileges on CUSTOMER table to user VPD_CLERK1

Create a policy function, named "DBSEC_ROW_OWNER_FUNCTION" so that only the data that belong to the current user will be selected. In other words, you need to generate a predicate "CTL_UPD_USER=USER." You may refer to the supplement material on this week for a sample code. Please note the owner of the table (DBSEC) has to be able to access its own table. (20 points) (Please note the double quotation mark (") is not a part of the specification.)

Add the policy using DBMS_RLS.ADD_POLICY function. The policy will be named as "MY_OWNER_POLICY."

Connect to VPD_CLERK1/ Nancy#481.

Query the "CUSTOMER" table to show the total number of rows from each user. If everything is successful, you will see only one row.

Tip:

It is important to make sure that you are connected to user DBSEC/ IS481%sec, before creating the policy function and running DBMS_RLS.ADD_POLICY. Otherwise, you will see ORA-28110.