Lab Virtual Private Database (VPD) by Context In this lab, you will implement a

Question

Lab Virtual Private Database (VPD) by Context

In this lab, you will implement a VPD case. It is a good idea to refer to the supplemental materials. Please follow the specifications below.

Your tasks are as follows:

Connect to user SYSTEM. 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;"

Grant "DBA" role to user DBSEC. This is the same user created on a previous lab. If for some reason, the user was dropped, you need to recreate the user before proceeding.

Grant "CREATE SESSION" and "CREATE TABLE" privileges to user VPD_CLERK1.

Connect to user DBSEC/ IS481%sec. Please note that the password was from our previous lab. If for some reason, the user was dropped or changed, you need to change it back to the above user name and password.

Create "CUSTOMER" table with the following columns. Please note slight changes in the table from previous project.

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)

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. If you use the link, please make sure your link works on your computer.

Query the table "CUSTOMER" to show the total number of rows from each user

Grant SELECT, DELETE, INSERT and UPDATE privileges on CUSTOMER table to user VPD_CLERK1

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

Add the policy using DBMS_RLS.ADD_POLICY function. The policy will be named as "MY_OWNER_POLICY."

Connect to VPD_CLERK1/ Nancy#481.

Query the "CUSTOMER" table to show the total number of rows from each user. If everything is successful, you will see only one row.

Tip:

It is important to make sure that you are connected to user DBSEC/ IS481%sec, before creating the policy function and running DBMS_RLS.ADD_POLICY. Otherwise, you will see ORA-28110.

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)

Explanation / Answer

CONNECT <username>/<password>

So the command is

CONNECT SYSTEM;

As the password is not provided it will ask the user to type the password.

GRANT DBA TO <username>

GRANT DBA TO DBSEC;

If user was dropped command to recreate user is

CREATE USER DBSEC IDENTIFIED BY passcode;

Where DBSEC is username and passcode is password.

GRANT CREATE SESSION,CREATE TABLE TO VPD_CLERK1;

Command is same as in part1

Connect DBSEC/IS481%sec;

If user was dropped command to recreate user is

CREATE USER DBSEC IDENTIFIED BY IS481%sec;

create table customer

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

select ctl_upd_user ,count(ctl_upd_user) from customers group by ctl_upd_user;

Syntax

GRANT SELECT, DELETE, INSERT, UPDATE ON <TABLE NAME> TO <USERNAME>

Command

GRANT SELECT, DELETE, INSERT, UPDATE ON customer TO VPD_CLERK1;

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.