Virtual Private Database by Views This lab requires you to use Oracle VIEW to im
ID: 3844479 • Letter: V
Question
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)
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.
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)
VARCHAR2(80)
CC_NUMBER
VARCHAR2(20)
CREDIT_LIMIT
NUMBER
GENDER
CHAR(1)
STATUS
CHAR(1)
COMMENTS
VARCHAR2(1024)
USER_NAME
VARCHAR2(30)
Explanation / Answer
Ans
/*
VPD is feature by which from 1000 of information
a user can access his own data from 1000 's of row in tale'
*/
/*firstly login with DBSEC*/
connect DBSEC/password
/* 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)
);
/* Creating view object*/
/* 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;
/* Provide authentication or grant */
grant select,insert on MY_VIEW to DBSEC;
/* 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', 'JCat@catu.edu', '2311468327372669', 20000
, 'M', 'A', 'A fun loving student', user);
/* user is a function that returns the username of a person who is logged in*/
/* check connection with DBSEC */
If log on as DESEC, USER = DBSEC
/* We add prefix schema to contain the view.
If you omit schema, then Oracle Database creates the view in your own schema*/
/* we can add trigger too to populate username automatically*/
CREATE OR REPLACE TRIGER TRG_CUSTOMER_VER1_BEFORE_INS
BEFORE INSERT
ON CUSTOMER
FOR EACH ROW
BEGIN
: USER_NAME := USER;
END;
/* fOR last one 8 point we have to add policy for the user */
/*Create a policy function*/
Return varchar2
As
user VARCHAR2(100);
Begin
if ( SYS_CONTEXT(‘userenv’, ‘ISDBA’) ) then
return ‘ ’; //Admin can access any data
else
user := SYS_CONTEXT(‘userenv’, ‘SESSION_USER’);
return ‘owner = ‘ || user;
// Users can only access their own data
end if;
End;
/*userenv = the pre-defined application context */
/* attach policy to the table*/
execute dbms_rls.add_policy (object_schema => DBSEC,
object_name => CUSTOMER,
policy_name => ‘my_policy’,
function_schema => 'DBSEC',
policy_function => ‘sec_function’,
statement_types => ‘select’, update, insert’,
update_check => TRUE );
/*check if user is correct or not*/
select * from CUSTOMER;
=> select * from CUSTOMER where owner = DBSEC;
: only shows the rows that owner is 'DBSEC'
insert into CUSTOMER values('DBSEC', ‘Some data’); OK!
insert into CUSTOMER values('abc', ‘Other data’); NOT OK!
= because of the check option.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.