Link to Data File:

http://castle.eiu.edu/pingliu/tec5363/Project/planning/vpd/dbsec_vpd_script_06.sql

Connect to user SYSTEM. (5 points) 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 project. If for some reason, the user was dropped, you need to recreate the user before proceeding. (5 points)

Grant "CREATE SESSION" and "CREATE TABLE" privileges to user VPD_CLERK1. (5 points)

Connect to user DBSEC/secc$1new. Please note that the password was from our previous project. If for some reason, the user was dropped or changed, you need to change it back to the above user name and password. (5 points)

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

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. Refer to the dbsec_ch4_setup.sql file in your downloaded file if you like to see how it was done. If you use the link, please make sure your link works on your computer. (10 points)

Query the table "CUSTOMER" to show the total number of rows from each user.(5 points)

Grant SELECT, DELETE, INSERT and UPDATE privileges on CUSTOMER table to user VPD_CLERK1.(5 points)

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." (20 points)

Connect to VPD_CLERK1/Jessie#22. (5 points)

Query the "CUSTOMER" table to show the total number of rows from each user. If everything is successful, you will see only one row, similar to what is on page 231 in your textbook. (5 points)

Tips:

It is important to make sure that you are connected to user DBSEC/sec$1new, before creating the policy function and running DBMS_RLS.ADD_POLICY. Otherwise, you will see ORA-28110. It took me 3 days of research to find out. The textbook is either not clear or confusing on the issue.

The sequence above was tested. Please make sure to do it right on every step. If any step is missed, it may cause a lot of headache on your part.

Make sure to use the exact spelling as specified. Misspelling is considered wrong by computer system. My grading script will not like it either.