In this project, you will implement a VPD case as illustrated on pages 225-231 in your textbook. It is also a good idea to refer to the supplemental materials posted for this week. Please note some slight difference between the project and textbook. If any difference arises, please follow the specifications below.

Your tasks are as follows:

1. 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;"
   
   
2. 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)
   
   
3. Grant "CREATE SESSION" and "CREATE TABLE" privileges to user VPD_CLERK1. (5 points)
   
   
4. 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)
   
   
5. 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)

   
   
6. 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)
   
   
7. Query the table "CUSTOMER" to show the total number of rows from each user.(5 points)
   
   
8. Grant SELECT, DELETE, INSERT and UPDATE privileges on CUSTOMER table to user VPD_CLERK1.(5 points)
   
   
9. 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.)
   
   
10. Add the policy using DBMS_RLS.ADD_POLICY function. The policy will be named as "MY_OWNER_POLICY." (20 points)
    
    
11. Connect to VPD_CLERK1/Jessie#22. (5 points)
    
    
12. 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:

1. 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.
2. 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.
3. Make sure to use the exact spelling as specified. Misspelling is considered wrong by computer system. My grading script will not like it either.

One SQL script file will be required and submitted through WebCT. Please note that 20 points will be taken off if the script file is not executable under SQL Plus. Thus, please test it so that you can perform all above tasks in one file.

Convention for File Name: The file naming convention will be enforced throughout this semester. Five (5) points will be taken off if the file is not named according to the convention.

At the end of this week. Please see the course schedule for exact due date.

Engineers and Lawyers
On a college field trip, four Engineering and four Pre-Law students were traveling on the same train. The law students each had a ticket, but the Engineers had but one ticket amongst them. One of the Engineers shouted "conductor's coming!", and the four Engineers crowded into one of the bathrooms.

The conductor comes by and knocks on the bathroom door saying "Tickets, please". The Engineers slip their one and only ticket under the door. The conductor punches it an moves on to the next car.

On the return trip, the four Lawyers, impressed by the Engineers' trick, purchase only one ticket. The Engineers, however have no tickets at all!. Suddenly, one of the Engineers shouts "Conductor's coming". All four Engineers head for the bathroom, and all four lawyers crowd into the other one.

Then, one of the Engineers slips out of his bathroom and knocks on the other bathroom door saying "Tickets, please". The lawyers then slip their only ticket under the door, and the Engineer then picks up the ticket and joins his friends, waiting for the real conductor.

Contributed by Raju (Fall 2006)