TEC 5323 Advanced Database Technology

Project 6 User and Role Management

Tasks:

You will create a SQL script to perform for the following tasks:

  1. Connect to appropriate user (5 points)
  2. Create a user "testuser" with a password "mypassword." (25 points)
  3. Create a role named "testrole." (25 points)
  4. Grant CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE ANY INDEX to "testrole." (25 points)
  5. Grant role "testrole" to "testuser." (20 points)
  6. Please make sure the exact spelling is required on everything we do or create.

Tip:

1. You will have to login as a user with appropriate privileges to perform the above tasks. Otherwise, you will run into errors. This is a test if you understand the user previlege in Oracle database system.

2. Based upon students research in Summer 2016, if you have Oracle 12c, you may use one of the following three alternative methods to create a user. Any of the three (3) approaches will be acceptable for this project:

a. Add C## in front of user name. In other words, if you define the user as c##testuser, it will work in Oracle 12c and is acceptable for this project.

b. Alter session: alter session set "_ORACLE_SCRIPT"=true;

In other words, you may issue a SQL command before you run your script, as follows:

SQL>alter session set "_ORACLE_SCRIPT"=true;

After using this alter session statement, you can create user using the name as specified (testuser).

c..Set container for the session: alter session set container=PDBORCL; After using this alter session statement, you can create user using the name as specified (testuser).

I would like to encourage students to do more research to find solutions to problems we face.
 
Report:

Create a single SQL script for the project (10 points off if not in one file).  Please test your script before submission.  Twenty (20) points will be taken if the script is not running (even with minor syntax error) while the instructor tests on his system.  Please note the importance of this practice as a database professional in the field.  This practice will be reinforced for all remaining projects in the course. 

Submit your project through D2L. If you need instructions on how to submit, click here.

Please submit your SQL script for performing the tasks of this project. (SQL script is in .sql format, which can be run in Oracle as SQL>@filepath\file.sql, for example, SQL>@C:\temp\myscript.sql.)

Please use the same file convention for this course. Please note that five (5) points will be taken for all projects this semester if the file naming convention is not followed.

 
Due:

At the end of this week. Please see the course schedule for exact due date.
Class Index
 

School of Technology
College of Business & Applied Sciences
Eastern Illinois University