TEC 5363 Database Security

Syllabus
Schedule
Reading
Discussion
Project
Test
Resource
Tip

Project 7 Virtual Private Database by Views

Task:

This project requires you to use Oracle VIEW to implement a virtual database on DBSEC schema, for example, on CUSTOMER table. Your mission is to develop a single SQL script that will perform all the following tasks:

  1. Connect to DBSEC/secc$1new, the same user you created in previous projects. (5 points)

  2. Create a CUSTOMER table with the following columns: (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)
    USER_NAME VARCHAR2(30)

    Please note everything is the same as in the script provided from the course download except that a USER_NAME column was added.

  3. Populate the CUSTOMER table with the data provided as in course download for Chapter 4. Please modify your the downloaded file so that you can insert USER_NAME. (See the tip below.) (20 points)

  4. Create a VIEW named as MY_VIEW to display only rows that belong to the logged in user. (You may refer to page 212-213 in the textbook.) (30 points)

  5. Grant SELECT and INSERT priviledge on MY_VIEW to DBSEC_CLERK. (5 points)

  6. Connect to DBSEC_CLERK/tec5363clerk. (This user was created in Project 6.) (5 points)

  7. Insert one row of data into MY_VIEW as DBSEC_CLERK by using the following data. You may need to make corrections on the statement, but keep the data as they are. (10 points)

    INSERT INTO DBSEC.MY_VIEW ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
    CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS,
    COMMENTS, USER_NAME)
    VALUES (
    7415, 901340, '969996976', 'Joe', 'Cat', '993888 Moreno St.', 'Champaign', 'IL'
    , ' 61801', '2173331613', 'JCat@catu.edu', '2311468327372669', 20000
    , 'M', 'A', 'A fun loving student', user);

  8. Verify your data insertion by query MY_VIEW. You (as DBSEC_CLERK) should only see one row of data you have inserted. This signifies the success of your implementation. (15 points)

Tips:

  1. Your data insert statement will look similar to the following:
    INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE,
    CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS,
    COMMENTS, USER_NAME) VALUES (
    6415, 201340, '969996970', 'Jeffrey', 'Antoine', '9938 Moreno St.', 'Champagne', 'SD'
    , ' 43172', '4319071613', 'JAntoine@iodmgpvjdzprccx.gov', '231146832737266', 200000
    , 'M', 'A', 'Any comments', user);

    (See page 213 for example.)
  2. Throughout the semester, exact spelling is required to conform to the specifications.
  3. Check on your login name at every step make sure your "connect" command line works as intended.
  4. On step 7 above, why the view MY_VIEW needs to be prefixed with DBSEC?
  5. In your textbook, there are two errors. One misplaced DBSEC prefix and the other misplaced the prefix.
  6. On step 8 above, if you see more than one row of data, you may have logged in with incorrect user name. If you see no data, you need to check errors in the process as well.

 

Report:

One and only 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, or is not in one file. 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.

Due:

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

Unrelated Matter:
Author: Krishnamurth Ashwini (kashwini)
Date: Monday, September 11, 2006 3:00pm

The teacher asked little Johnny if he knows his numbers.
"Yes," he said. "I do. My father taught me."
"Good. What comes after three."
"Four," answers the boy.
"What comes after six?"
"Seven." "Very good," says the teacher. "Your dad did a good job.
What comes after ten?"
"A Jack."