Question

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:

Table created for this assignment is listed below:

create table CUSTOMER_VPD(

SALES_REP_ID NUMBER(4),

CUSTOMER_ID NUMBER(8) NOT NULL,

CUSTOMER_SSN VARCHAR(9),

FIRST_NAME VARCHAR(20),

LAST_NAME VARCHAR(20),

ADDR_LINE VARCHAR(40),

CITY VARCHAR(30),

STATE VARCHAR(30),

ZIP_CODE VARCHAR(9),

PHONE VARCHAR(15),

EMAIL VARCHAR(80),

CC_NUMBER VARCHAR(20),

CREDIT_LIMIT NUMBER,

GENDER CHAR(1),

STATUS CHAR(1),

COMMENTS VARCHAR(1025),

USER_NAME VARCHAR(30)

);

Tasks:

Populate the CUSTOMER_VPD table with four rows of records. Pay attention to the column CTL_UPD_USER,

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

Insert one row of data into MY_VIEW as DBSEC_CLERK by using the following data

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.

Answer 1

Below you can find everything clear and feel free to ask any doubts and please don't forget to give upvote as it means a lot. Thanks:)

/*firstly login with DBSEC*/

connect DBSEC/password

/* Task A)------->Create table customer*/
create table CUSTOMER(
SALES_REP_ID NUMBER(4),
CUSTOMER_ID NUMBER(8) NOT NULL,
CUSTOMER_SSN VARCHAR(9),
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
ADDR_LINE VARCHAR(40),
CITY VARCHAR(30),
STATE VARCHAR(30),
ZIP_CODE VARCHAR(9),
PHONE VARCHAR(15),
EMAIL VARCHAR(80),
CC_NUMBER VARCHAR(20),
CREADIT_LIMIT NUMBER,
GENDER CHAR(1),
STATUS CHAR(1),
COMMENTS VARCHAR(1025),
USER_NAME VARCHAR(30)
);

/* Task B)------>Creating view object And username is given username*/

Create view MY_VIEW as select SALES_REP_ID,CUSTOMER_ID,CUSTOMER_SSN,FIRST_NAME,LAST_NAME,
ADDR_LINE,CITY,STATE,ZIP_CODE,PHONE,EMAIL,CC_NUMBER,CREADIT_LIMIT,GENDER,STATUS,COMMENTS,USER_NAME
from customer where USER_NAME=username;

/* Task C)--------> grant */
grant select,insert on MY_VIEW to DBSEC;

/* Task D)---------->insert a row using view*/
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', '[email protected]', '2311468327372669', 20000
, 'M', 'A', 'A fun loving student', user);