Oracles Authorization System

Chapter 8 - Laboratory Exercise: Exploring Oracles 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.