orcale

TEC 5323

Assignment 10 – User Creation and Management (Extra Credits)

Task:

You will create a SQL script to accomplish the following tasks.

1. Assume that we have connected to the system account. Write a script to create “testdb” schema. Use “password” without quote as the password. You should review example in Week 14 Lecture

Slides (page 28-29).

Connect to “testdb” schema and write a script that creates tables below. Adding data is not

necessary.

user_id in users table and download_id in downloads table are primary keys.

user_id in downloads table is foreign key.

Column attributes and constraints should be assigned as appropriate.

2. Continue adding to the script; create the roles for “db_user” and “db_manager” by granting the following privileges (5 points):

a. db_user: i. CREATE SESSION

ii. CREATE PUBLIC SYNONYM iii. SELECT ON users iv. SELECT ON downloads

b. db_manager: i. db_user role

ii. SELECT, INSERT, UPDATE, DELETE ON users iii. SELECT, INSERT, UPDATE, DELETE ON downloads

3. Continue adding to the script; create two users with a username and password of your choosing. Assign one user to db_user role and another user to db_manager role. Then, create public

synonym of both tables for all users. (5 points)

Tip:

• You should review example script in our Week 14 Lecture Slides (page 28-39). It should be straightforward to adapt the example.

Submission:

1. Create a single SQL script for the assignment (10% deduction if not in one file). Your SQL script is only in “.sql” format. The script should be run in Oracle SQL Plus as

“SQL>@filepath\filename.sql”. For example, if your SQL script is saved as myscript.sql, then we

will be able to execute it in SQL Plus as “SQL>@C:\temp\myscript.sql”.

2. Make sure to test your script before submission. Ten (10) % will be automatically taken if the script is not running (even with minor syntax error). Please note this practice is very important for

a database professional in the field.

3. Please use file convention as follows. Name your file as “firstname_lastname_user.sql”, where firstname and lastname are your first and last name, respectively. Five (5) % will be deducted if

the file name is not followed this convention. There should not be any space in your file name;

otherwise the script will not be run.

4. Submit your project in Drobpox in D2L.