Oracle’s Authorization System

Chapter 8 - Laboratory Exercise: Exploring Oracle’s Authorization System

 Step 8.1 - Open the text file called:

Chapter_8_Laboratory_Exercise_DDL_forWorkerDeptProjectAssign.txt

 Step 8.2 - Create the Worker, Dept, Project, and Assign tables in the database.

To help you see how the commands work, open both the text file and the SQL*Plus window in Oracle. You will move back and forth between the open windows. You should highlight and copy the CREATE TABLE commands (one at a time) and paste them into SQL*Plus at the SQL> prompt. You should see the message: Table Created after each statement is executed. Show your work by providing screenshots of executing the CREATE TABLE SQL statements in the database.

 Step 8.3 – Run the Insert and Update commands for the four tables.

To do this, copy each INSERT and UPDATE command (one at a time) and paste them into SQL*Plus at SQL> prompt. For inserted rows, you should see the message:  1 row created after each statement is executed. For updated rows, you should see the message:  1 row updated after each statement is executed. Show your work by providing screenshots of executing the INSERT and UPDATE SQL statements in the database.

 Step 8.4 - For each step: Draw an authorization graph using a drawing tool, showing the privileges given. Design SQL statements that will be executed in the database. Then execute the SQL statements in the database.

Note: Each step should include an authorization graph. Hand-written authorization graphs are not acceptable submissions.

Note: Show your work by providing screenshots of executing the SQL statements in the database along with the results.

Step 8.4.a - Create five users: U100, U200, U300, U400, and U500.

Step 8.4.b - Give user U100 the SELECT, INSERT, DELETE, UPDATE privileges on all four tables, with grant option.

Step 8.4.c - Connect as U100 and pass the SELECT privilege on all four tables to u200 and U300, with grant option.

Step 8.4.d - Still acting as U100, pass the DELETE privilege on Project to U400 and U500, without the grant option

Step 8.4.e - Connect as U200 and pass the SELECT privilege on Project to U400 and U500, without the grant option.

Step 8.4.f - Connect as U100 and revoke all of the privileges that you granted to u200.

Step 8.4.g - From the authorization graph, determine and write out what privileges, if any, the remaining users should still have.

Step 8.4.h – As SYS Oracle ID, confirm the privileges in the database by selecting the table privileges for each user from the sys.dba_tab_privs data dictionary view.