Implement a virtual database on DBSEC schema, for example, on CUSTOMER CUSTOMERS table(depending on how your spelled it) Your mission is to develop a single SQL script that will perform all the following tasks(scripts are already attached here) Please submit a screenshot of the final output Total 5 steps Drop Customer Customers table and re create it or alter the table and include the new column Insert the new data(data file is attached) provided here Create a view called MY VIEW (Syntax and the data are attached) Grant permissions as listed below to the user DBSEC CLERK Login as DBSEC CLERK Insert the one record attached in the notepad here Query as DBSEC CLERK LAB STARTS HERE Connect to DBSEC secc$1new, the same user you created in previous projects Createe a CUSTOMER CUSTOMERS table with the following columns (You need to drop alter the current table and re create it to accommodate the new field USER NAME ) 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) 3 Populate the CUSTOMER table with the data file attached here 4 Createe a VIEW named as MY VIEW to display only rows that belong to the logged in user 5 Grant SELECT and INSERT privilege on MY VIEW to DBSEC CLERK 6 Connect to DBSEC CLERK inft6157clerk (This user was created in Project 4 ) 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 8 Verify your data insertion by querying MY VIEW You (as DBSEC CLERK) should only see one row of data you have inserted This signifies the success of your implementation (Please submit a snapshot of this step even if it's not successful)

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 18, 2023

Implement a virtual database on DBSEC schema, for example, on CUSTOMER/CUSTOMERS table(depending on how your spelled it). Your mission is to develop a single SQL

Implement a virtual database on DBSEC schema, for example, on CUSTOMER/CUSTOMERS table(depending on how your spelled it). Your mission is to develop a single SQL script that will perform all the following tasks(scripts are already attached here):

Please submit a screenshot of the final output.

Total 5 steps:

Drop Customer/Customers table and re-create it or alter the table and include the new column. Insert the new data(data file is attached) provided here.
Create a view called MY_VIEW. (Syntax and the data are attached)
Grant permissions as listed below to the user DBSEC_CLERK.
Login as DBSEC_CLERK. Insert the one record attached in the notepad here.
Query as DBSEC_CLERK.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

LAB STARTS HERE:

Connect to DBSEC/secc$1new, the same user you created in previous projects.
Createe a CUSTOMER/CUSTOMERS table with the following columns: (You need to drop/ alter the current table and re-create it to accommodate the new field "USER_NAME")

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)

3. Populate the CUSTOMER table with the data file attached here .

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

5. Grant SELECT and INSERT privilege on MY_VIEW to DBSEC_CLERK.

6. Connect to DBSEC_CLERK/inft6157clerk. (This user was created in Project 4.)

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.

8. Verify your data insertion by querying MY_VIEW. You (as DBSEC_CLERK) should only see one row of data you have inserted. This signifies the success of your implementation. (Please submit a snapshot of this step even if it's not successful)