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

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?