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?