This process will be documented schematically in the table below that will:
- identify all SQL commands (using proper role names, table names, user names, etc. and proper syntax),
- identify which user (DBA or NEW USER) should execute each command, and
- detail the expected outcome based on your knowledge of Oracle data administration, roles, and granting and revoking
privileges to roles.
Initially, the new user will not be able to do anything, including any selects, inserts, updates, or deletes on any of the DBA’s tables.
The DBA will create a role and assign the new user to that role. Then, the DBA will grant and revoke various select, insert, update
or delete privileges to the role. It is your responsibility to determine the outcome.
You will not be able to execute any of these commands in a live environment. The DBA would like for you to complete this
documentation to help a new intern learn the process. The intern will arrive early next week.
The database relational schema and sample data are shown below. You should study the tables and data to become familiar with the
layout prior to completing this work.

Task

Description of Task

Oracle SQL Commands Needed to Complete Task

Executed by

DBA?

Executed by NEW USER?

Expected Outcome

1.

Identify the Primary Keys in each of the four (4) tables above.

2.

Identify the Foreign Keys in each of the four (4) tables above.

3.

Create a new user named tstusr281 with password tstusr281 and grant that user session privileges (2 commands). Log in as the new user (no command for this).

4.

To determine if the new user can do an insert, update, or delete on the CARD_HOLDERS table in the DBA’s schema, have him attempt each of these commands with sample data as follows:

Insert:

Insert a record for card holder number 11, for
Mercy Jackson, 1146 Howison Avenue, Memphis, TN, 50134.

Update:
change Last_Name to Black for card holder number 11

Delete:
delete all records for all last names of Black

5.

The DBA will create a role called USERROLE and will grant the role to the new user and commit his work. He will also do a set role and commit his work.

6.

The DBA will grant select, insert, and update privileges on the CARD_HOLDERS table in his schema to the role.

       7.

The new user will attempt to do a select, insert, update and delete on the CARD_HOLDERS table in the DBA’s schema using the data below:

Select:
all records

Insert:

Insert a record for card holder number 11, for
Mercy Jackson, 1146 Howison Avenue, Memphis, TN, 50134.

Update:
change Last_Name to Black for card holder number 11

Delete:
delete all records for all last names of Black

8.

The DBA would like to revoke insert privileges on the CARD_HOLDERS table to the role and commit his work.

9.

The new user will attempt to do a select, insert, update and delete on the CARD_HOLDERS table in the DBA’s schema using the data below:

Select:
all records

Insert:

Insert a record for card holder number 12, for Carol Adams, 101 Arc Street, Seattle, WA, 01256

Update:
change Last_Name to Camel for card holder number 1

Delete:
delete all records for all last names of Camel

10.

The DBA needs to create and execute a view called S_LIST that lists the Cardholder Number, last name, first name and due date for all cardholders who have not returned a book.

11.

The DBA would like to execute a select on this view and make it available to all users in the role and commit his work.

12.

The new user hears about this new view and wishes to try it out by doing a select of all records on the view.

13.

The DBA would like to create another role and grant INSERT and DELETE access to all four (4) of his tables from this database to the following users: student1, student2, student3, student4 and student5.

14.

How are VIEWS important?

15.

What advantages or benefits do ROLEs provide?

Transcribed Image Text:A Dennis Dashboard x O Module 9 HW O M9HW.pdf b Answered: You ha X G You have been giv b My Questions | ba x New Tab O File C:/Users/kwaku/Downloads/M9HW-1.pdf M9HW.pdf 3 / 7 110% + BOOKS CHECKED OUT Cardholder_Number Book_Number Date_Checked_Out Due_Date Actual_Retum_Date BOOK LIST Book Number ISBN Number Book Name Category_Num CARD HOLDERS Cardholder_Number First Name Last Name Address City State Zip_Code BOOK_CATEGORIES Category_Num Category 3 O M9HW-1 (1).pdf Show all 1:46 AM hp X N W 7/17/2021 II
Transcribed Image Text:A Dennis Dashboard x O Module 9 HW O M9HW.pdf b Answered: You ha X G You have been giv x b My Questions | ba x New Tab + O File C:/Users/kwaku/Downloads/M9HW-1.pdf M9HW.pdf 4 / 7 100% +| 0 0 BOOK LIST Book Number - ISBN Number - Book Name • Category_Num - 10-385-19237-1 San Francisco Encore 20-9604222-0-X The Memphis Cookbook Charlotte Cooks Again Colorado Cache Cookbook 30-9613214-1-5 1 40-9603946-5-6 50-960-79142-6 Atlanta Cooknotes BOOK.CATEGORIES 60-9607076-1-1 Beyond Parsley 7 09605788-0-3 8 0811826848 Category_Num - Category 1 Biography 2 Fiction 3 Non Fiction 4 Cookbooks 5 History Out of Our League The Beatles Anthology 90-425-17139-6 Net Force Nothing Like It in The World Tuesdays with Morri Flags of Our Fathers 10 0684846098 11 0385484528 12 OS53111337 I CARD HOLDERS Address Cardholder First Name Last_Name- Downs Johnson City San Francisco CA - Zip_Code 95409 State 2 1 Robert 100 E. Town St. 2345 7th Avenue 2 John Denver co 80222 3 Judy Toon 4012 Main Street Memphis 2310 W. Fifth Avenue Richmond TN 38118 4 Barbara Brown VA 23286 5 Robert 6 Carol 7 Janice 8 Will 9 Judy 10 Betty Smith 25110 Central Park 199 South Fourth 2020 State St. Denver Richmond CO co 80222 Carter VA 23286 Johns Greensboro Columbus San Francisco CA NC 27408 Hamilton 56 E. Main St. он 43215 95409 94588 Smith 490 E. Lincoln Olivette 55 Sunshine Coast San Francisco CA BOOKS. CHECKED OUT Cardholder_Number - Book_Number - Date Checked Out - Due_Date- Actual Return_Date - 1 10 29-Dec-00 08-lan-02 06-Dec-00 16-Dec-00 20-Dec-00 07-Dec-00 17-Dec-00 15-Dec-00 15-Dec-00 25-Dec-00 28-Dec-00 12 02-Jan-02 12-Jan 02 01-lan-02 11-lan-02 02-Jan-02 12-Jan-02 11-Dec-00 05- Dec 00 15-Dec 00 10 01-Dec-00 05-Dec-00 10 20 Dec 00 4 O M9HW-1 (1).pdf Show all 1:47 AM hp 7/17/2021 II

Question

This process will be documented schematically in the table below that will:
- identify all SQL commands (using proper role names, table names, user names, etc. and proper syntax),
- identify which user (DBA or NEW USER) should execute each command, and
- detail the expected outcome based on your knowledge of Oracle data administration, roles, and granting and revoking
privileges to roles.
Initially, the new user will not be able to do anything, including any selects, inserts, updates, or deletes on any of the DBA’s tables.
The DBA will create a role and assign the new user to that role. Then, the DBA will grant and revoke various select, insert, update
or delete privileges to the role. It is your responsibility to determine the outcome.
You will not be able to execute any of these commands in a live environment. The DBA would like for you to complete this
documentation to help a new intern learn the process. The intern will arrive early next week.
The database relational schema and sample data are shown below. You should study the tables and data to become familiar with the
layout prior to completing this work.

Task	Description of Task	Oracle SQL Commands Needed to Complete Task	Executed by DBA?	Executed by NEW USER?	Expected Outcome
1.	Identify the Primary Keys in each of the four (4) tables above.
2.	Identify the Foreign Keys in each of the four (4) tables above.
3.	Create a new user named tstusr281 with password tstusr281 and grant that user session privileges (2 commands). Log in as the new user (no command for this).
4.	To determine if the new user can do an insert, update, or delete on the CARD_HOLDERS table in the DBA’s schema, have him attempt each of these commands with sample data as follows: Insert: Insert a record for card holder number 11, for Mercy Jackson, 1146 Howison Avenue, Memphis, TN, 50134. Update: change Last_Name to Black for card holder number 11 Delete: delete all records for all last names of Black
5.	The DBA will create a role called USERROLE and will grant the role to the new user and commit his work. He will also do a set role and commit his work.
6.	The DBA will grant select, insert, and update privileges on the CARD_HOLDERS table in his schema to the role.
7.	The new user will attempt to do a select, insert, update and delete on the CARD_HOLDERS table in the DBA’s schema using the data below: Select: all records Insert: Insert a record for card holder number 11, for Mercy Jackson, 1146 Howison Avenue, Memphis, TN, 50134. Update: change Last_Name to Black for card holder number 11 Delete: delete all records for all last names of Black
8.	The DBA would like to revoke insert privileges on the CARD_HOLDERS table to the role and commit his work.
9.	The new user will attempt to do a select, insert, update and delete on the CARD_HOLDERS table in the DBA’s schema using the data below: Select: all records Insert: Insert a record for card holder number 12, for Carol Adams, 101 Arc Street, Seattle, WA, 01256 Update: change Last_Name to Camel for card holder number 1 Delete: delete all records for all last names of Camel
10.	The DBA needs to create and execute a view called S_LIST that lists the Cardholder Number, last name, first name and due date for all cardholders who have not returned a book.
11.	The DBA would like to execute a select on this view and make it available to all users in the role and commit his work.
12.	The new user hears about this new view and wishes to try it out by doing a select of all records on the view.
13.	The DBA would like to create another role and grant INSERT and DELETE access to all four (4) of his tables from this database to the following users: student1, student2, student3, student4 and student5.
14.	How are VIEWS important?
15.	What advantages or benefits do ROLEs provide?

A Dennis Dashboard x
O Module 9 HW
O M9HW.pdf
b Answered: You ha X
G You have been giv
b My Questions | ba x
New Tab
O File
C:/Users/kwaku/Downloads/M9HW-1.pdf
M9HW.pdf
3 / 7
110%
+
BOOKS CHECKED OUT
Cardholder_Number
Book_Number
Date_Checked_Out
Due_Date
Actual_Retum_Date
BOOK LIST
Book Number
ISBN Number
Book Name
Category_Num
CARD HOLDERS
Cardholder_Number
First Name
Last Name
Address
City
State
Zip_Code
BOOK_CATEGORIES
Category_Num
Category
3
O M9HW-1 (1).pdf
Show all
1:46 AM
hp
X
N
W
7/17/2021
II

A Dennis Dashboard x
O Module 9 HW
O M9HW.pdf
b Answered: You ha X
G You have been giv x b My Questions | ba x
New Tab
+
O File
C:/Users/kwaku/Downloads/M9HW-1.pdf
M9HW.pdf
4 / 7
100% +| 0 0
BOOK LIST
Book Number - ISBN Number -
Book Name
• Category_Num -
10-385-19237-1
San Francisco Encore
20-9604222-0-X The Memphis Cookbook
Charlotte Cooks Again
Colorado Cache Cookbook
30-9613214-1-5
1
40-9603946-5-6
50-960-79142-6
Atlanta Cooknotes
BOOK.CATEGORIES
60-9607076-1-1 Beyond Parsley
7 09605788-0-3
8 0811826848
Category_Num - Category
1 Biography
2 Fiction
3 Non Fiction
4 Cookbooks
5 History
Out of Our League
The Beatles Anthology
90-425-17139-6 Net Force
Nothing Like It in The World
Tuesdays with Morri
Flags of Our Fathers
10 0684846098
11 0385484528
12 OS53111337
I CARD HOLDERS
Address
Cardholder First Name Last_Name-
Downs
Johnson
City
San Francisco CA
- Zip_Code
95409
State
2
1 Robert
100 E. Town St.
2345 7th Avenue
2 John
Denver
co
80222
3 Judy
Toon
4012 Main Street
Memphis
2310 W. Fifth Avenue Richmond
TN
38118
4 Barbara
Brown
VA
23286
5 Robert
6 Carol
7 Janice
8 Will
9 Judy
10 Betty
Smith
25110 Central Park
199 South Fourth
2020 State St.
Denver
Richmond
CO
co
80222
Carter
VA
23286
Johns
Greensboro
Columbus
San Francisco CA
NC
27408
Hamilton
56 E. Main St.
он
43215
95409
94588
Smith
490 E. Lincoln
Olivette
55 Sunshine Coast San Francisco CA
BOOKS. CHECKED OUT
Cardholder_Number - Book_Number - Date Checked Out - Due_Date- Actual Return_Date -
1
10
29-Dec-00
08-lan-02
06-Dec-00
16-Dec-00
20-Dec-00
07-Dec-00 17-Dec-00
15-Dec-00
15-Dec-00
25-Dec-00
28-Dec-00
12
02-Jan-02
12-Jan 02
01-lan-02
11-lan-02
02-Jan-02
12-Jan-02
11-Dec-00
05- Dec 00 15-Dec 00
10
01-Dec-00
05-Dec-00
10
20 Dec 00
4
O M9HW-1 (1).pdf
Show all
1:47 AM
hp
7/17/2021
II

Accepted Answer

SELECT * FROM books;