Virtual Private Database by Views

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

Connect to DBSEC/ sec%sec, the same user you created in previous projects.

Create a CUSTOMER table with the following columns:

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)

Populate the CUSTOMER table with the data provided.

Create a VIEW named as MY_VIEW to display only rows that belong to the logged in user.

Grant SELECT and INSERT privilege on MY_VIEW to DBSEC_CLERK.

Connect to DBSEC_CLERK/sec$clerk You have created this user.

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. 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);

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.

Tips:

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);

Check on your login name at every step make sure your "connect" command line works as intended.

On step 7 above, why the view MY_VIEW needs to be prefixed with DBSEC?

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.