Chapter 8 - Individual Assignment: Exploring Oracles Authorization System

Chapter 8 - Individual Assignment: Exploring Oracles Authorization System

Step 8.1 - Open the text file called:

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

Note: Log into the Oracle Database as your Oracle User ID to create the four tables. (Do not use SYS or SYSTEM for this step).

Note: Show your work by providing screenshots that include the current user id, current system date, executing the SQL statements in the database, and the results. To display the current user and current date, run the following statement before running each SQL Statement:

SQL> select user||' '||sysdate from dual;

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.

Note: Use your Oracle User ID to run the Insert and Update commands for the four tables (Do not use SYS or SYSTEM for this step).

Note: Show your work by providing screenshots that include the current user id, current system date, executing the SQL statements in the database, and the results.

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.

- Execute the SQL statements in the database.

Note: To display all of the data properly, run the following statement after you log into the Oracle Database (before running the SQL statements):

SET LINESIZE 120

Each step should include:

- An authorization graph. Hand-written authorization graphs are not acceptable submissions.

- Write down the SQL statements that will be executed in the database (in addition to showing the SQL statements in the screenshot)

- Provide a screenshot that includes the current user id, current system date, executing the SQL statements in the database, and the results.

Step 8.4.a - Create five users: U111, U222, U333, U444, and U555.

Note: Log into the Oracle Database as the SYS ID. Then switch to the pluggable database - XEPDB1 to create the five users.

SQL > ALTER SESSION SET CONTAINER = XEPDB1;

Step 8.4.b Log into the Oracle Database as your Oracle User ID (owner of the four tables) and pass the SELECT, INSERT, DELETE, UPDATE privileges on all four tables to u111, with grant option.

Step 8.4.c - Connect as U111 and pass the DELETE privilege on all four tables to U222 and U333, with grant option.

Step 8.4.d - Still acting as U111, pass the SELECT privilege on Assign to U444 and U555, without the grant option.

Step 8.4.e - Connect as U222 and pass the DELETE privilege on Dept to U444 and U555, without the grant option.

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

Step 8.4.g - Still acting as U111, revoke all of the privileges that you granted to U555.

Step 8.4.h - Determine if there are any remaining user privileges (after running the previous steps). Then do the following:

- Write out any remaining user privileges.

- Create a final authorization graph (that includes any remaining user privileges).

Step 8.4.i Confirm the privileges in the database by selecting the table privileges for each user from the sys.dba_tab_privs data dictionary view.

Note: Log into the Oracle Database as the SYS ID. Then switch to the pluggable database - XEPDB1 to select the table privileges for each user.

SQL > ALTER SESSION SET CONTAINER = XEPDB1;

Use the following format and run the following SQL to display your results:

col owner format a20

col table_name format a15

col privilege format a10

col grantee format a10

SELECT owner,privilege,table_name,grantee FROM sys.dba_tab_privs WHERE grantee like 'U%'

ORDER BY grantee, table_name, privilege;

Note: The results should match the list of remaining user privileges and final authorization graph from Step 8.4.h.

Chapter 9 Individual Assignment: Oracle Transaction Management

Step 9.1 - Open the text file called: Chapter_9_Individual_Assignment_SQL_Transaction_Management.txt

Note: This script uses the University entities that you created in the Chapter 5 Individual Assignment. If you need to re-create the University entities/data, use the script: Chapter_5_Individual_Assignment_DDL_for_Univerity_Example.txt

Step 9.2 - Copy each statement in Section A and paste them into SQL*Plus at the SQL> prompt to see the effects of ROLLBACK and COMMIT.

Note: Log into the Oracle Database as your Oracle User ID to run the statements in Section A. (Do not use SYS or SYSTEM for this step).

Note: Show your work by providing screenshots that include the current user id, current system date, executing the SQL statements in the database, and the results. To display the current user and current date, run the following statement before running the SQL statements:

SQL> select user||' '||sysdate from dual;

Step 9.3 - Copy each statement in Section B and paste them into SQL*Plus at the SQL> prompt to see the effects of a SAVEPOINT.

Note: Use your Oracle User ID to run the statements in Section B. (Do not use SYS or SYSTEM for this step).

Note: Show your work by providing screenshots that include the current user id, current system date, executing the SQL statements in the database, and the results.

Step 9.4 - Copy each statement in Section C and paste them into SQL*Plus at the SQL> prompt to see the effects of a SET TRANSACTION READ ONLY versus READ WRITE and naming a transaction.

Note: Use your Oracle User ID to run the statements in Section C. (Do not use SYS or SYSTEM for this step).

Note: Show your work by providing screenshots that include the current user id, current system date, executing the SQL statements in the database, and the results.

Step 9.5 Design your own named transaction containing all of the options (rollback, commit, savepoint, read only, and read write) you used in this exercise. Then execute the SQL statements in the database. When designing your own named transaction, use the faculty table (do not use the student table).

This step should include:

- Write down the SQL statement that will be executed in the database (in addition to showing the SQL Statement in the screenshot).

- Provide a screenshot that includes the current user id, current system date, executing the SQL statements in the database, and the results.

Instructions for Installing Microsoft Project and Visio

1) Click on the following link, which will take you to Microsoft Azure page:

https://azureforeducation.microsoft.com/devtools

2) Click on the Sign In

3) Then login to your account or to create a new account.

4) Under the Education | Overview screen and under Learning resources (left of screen) , Click on Software

5) Under the Education | Software screen, use the search bar to search for Project Professional 2019 and Visio Professional 2019 (separately).

6) If you need to enter the Product Key, you can click on View Key button before downloading software.

7) To download the software, click on Download

8) Once the iso file has been downloaded, click on the iso file to open.

9) Click on setup.exe to install the software.

Instructions for installing Oracle Database 21c XE onWindows

1) Click on the following link to go to the Oracle site for Oracle Database Express Edition (XE) zipfile:

HYPERLINK "https://www.oracle.com/database/technologies/xe-downloads.html" https://www.oracle.com/database/technologies/xe-downloads.html

2) For Windows installation, click on: Oracle Database 21c Express Edition for Windows x64

3) If you have an Oracle Account, log into your account.

If you do not have an Oracle Account, create an account.

4) Once you have created/logged into your Oracle account, the following file will be downloaded to your laptop:

OracleXE213_Win64.zip

5) Once the OracleXE213_Win64.zip has been downloaded, extract it to a temporary directory called: OracleXE213_Win64

6) Go to the temporary directory: OracleXE213_Win64 and double click on setup.exe

7) Click Yes when Windows asks you whether you want to allow this app to make changes to your device.

8) Click Next when the Oracle Database 21c Express Edition installer starts.

9) Read the license agreement and click I accept the terms in the license agreement

and then click Next.

10) Click Next to install Oracle to the default location usually: c:appusernameproduct21c

Note: Be sure that your username does not have any spaces.

Choose a destination folder for where Oracle 18c XE should be installed into, or accept the default location. Then click Next.

11) Specify and confirm a database password. This is the password that will be set for the SYS, SYSTEM and PDBADMIN accounts, which you will later use to connect to the database directly. Then click Next.

12) On the next screen you will see the summary and confirmation of the destination folders for Oracle Database 18c XE. Click Install.

13) Now 21c XE is being installed, this can take up 30 minutes to a couple of hours depending on your laptop configuration.

14) If you have the Windows Defender Firewall activated, you will be asked to allow network access to the Java Platform SE binary. Check the checkboxes to allow access to the networks of your choice and click the Allow access button. Note: If you do not allow network access for Java, some features of Oracle Database 18c XE might not work correctly.

15) The installation will proceed. Once it is finished, you will be presented with a Oracle Database Installed Successfully summary screen that also lists the container database, pluggable database and Enterprise Manager Express connection details. Click on Finish.

Beginning with Oracle 21c, the Multitenant Container Database (CDB) is the default and only supported architecture for the Oracle database. In the previous releases Oracle supported non-CDBs. Oracle Multitenant allows multiple tenant databases to share a single instance of the Oracle Database software. Each tenant database runs in a separate container, called a Pluggable Database (PDB), which is isolated from the other tenant databases. This allows for a more efficient use of resources and makes it easier to manage and maintain large numbers of databases. The Oracle 21c installation automatically creates a default pluggable database - XEPDB1 and configures the listener at the default port (1521).

The main benefits of Oracle Multitenant are:

Resource sharing: Tenant databases share the same memory, CPU, and storage resources, which can lead to significant cost savings and improved performance.

Patching and upgrades: Maintenance operations such as patching and upgrades can be performed on the container database as a whole, rather than on each individual tenant database, which can save a lot of time and effort.

Backup and recovery: Tenant databases can be backed up and recovered independently of one another, which allows for more granular control over data protection.

Data isolation: Each tenant database is isolated from the others, which provides a higher level of security and reduces the risk of data breaches.

16) Once the Oracle Database installation is complete, log into the Oracle Database as the SYS ID using SQL Plus.

Enter the username/password when prompted:

Example:

Enter user-name: sys/<password> as sysdbaNote: Substitute password with the password that you used during Oracle installation.

Note: SYS connects to the root container. You need to switch to the pluggable database - XEPDB1 to create the users.

SQL > ALTER SESSION SET CONTAINER = XEPDB1;

17) As the SYS ID, create a user ID which you will use for the semester:

Example:

SQL> CREATE USER bill IDENTIFIED BY bill;

Note: Substitute bill with your username and password that you will use for the semester.

Note: Do not use the SYS id for your assignments.

Note: the create user command only creates the user in the database. You need to grant connect and resource to the new user. Connect will allow the user to connect to the database. Resource allows the user to create objects in their own schema.

SQL> GRANT CREATE VIEW TO RESOURCE;

Note: this adds an additional privilege so users with the resource role can also create views.

SQL> GRANT CONNECT, RESOURCE TO bill;

Note: This statement grants connect and resource to the new user.

SQL> ALTER USER bill QUOTA UNLIMITED ON USERS;

Note: This statement ensures that the userid has unlimited space in the users tablespace.

18) Now connect to the pluggable database (XEPDB1) using port 1521 as the new user to confirm connection:

SQL> connect bill/bill@localhost:1521/XEPDB1

You are now ready to interact with the database.

Note: Always use your user id to complete your assignments. Do not use the SYS id for your assignments.