1. Task 1: Review the following Database Adminstration commands and replace the words in italicized below with the actual value. (For example, replace userName with c281New (everyone needs to use a different username) and userPassword could be c281pwd; use the real table name for tableName; replace roleName with role281):
a. Create a new user:
i. CREATE USER userName IDENTIFIED BY userPassword; (where userName and
userPassword are the desired username and password for the new user)
ii. GRANT CREATE SESSION TO userName; (otherwise the user won't be able to login to the Oracle server)
iii. For example:
1. CREATE USER c281New IDENTIFIED BY c281pwd;
2. GRANT CREATE SESSION TO c281New;
b. Change a user's password:
i. ALTER USER userName IDENTIFIED BY newPassword;
c. Delete/remove a user:
i. DROP USER userName;
d. Grant privileges to a user:
i. GRANT privilege(s) ON tableName TO userName; (PS: privilege(s) can be SELECT, DELETE, UPDATE, etc.)
ii. For example: GRANT SELECT on pangj.TEAM TO ryanj; (ryanj is a username).
e. Remove privileges from a user:
i. REVOKE privilege(s) ON tableName FROM userName;
f. Create a role:
i. CREATE ROLE roleName;
g. Delete a role:
i. DROP ROLE roleName;
h. Grant privileges to a role:
i. GRANT privilege(s) ON tableName TO roleName;
i. Remove privileges from a role:
i. REVOKE privilege(s) ON tableName FROM roleName;
j. Grant a role to a user:
i. GRANT roleName TO userName;
2. Task 2: Creating a new user
a. Login to the Oracle server using SQL Developer and write and execute the SQL command needed to create a new user with a username and password of your choosing.
b. Grant the user CREATE SESSION privileges in order to allow him/her to login to the Oracle server.
c. Now try to login to the Oracle server using the new username and password. In SQL Developer, click on the File tab and select New > Database Connection which will give you a screen. Then edit the connection information to the following, only replace the username and password with the ones you chose for your new user. Then, click the Connect button after. You will be login as the new user.
d. Do a test to see if the new user can access the APPLICATION table owned by your username (it is your MyFranklinUsername original account [pangj is mine]). Issue the following SQL command, only replace yourMyFranklinUsername with your actual myFranklin username (NOT the username for new user) - e.g., in my case, it would be pangj.APPLICATION:
Run "SELECT * FROM pangj.APPLICATION;" (replace pangj with your username)
The new user should SQL Error: ORA-00942: table or view does not exist when running this query since no permission on application table was granted to the new user yet.
3. Task 3: Granting privileges to the new user
a. You should now have TWO simultaneous session tabs in SQL Developer:
i. One using your myFranklin username (we'll call this one ORIGINAL session from now on)
ii. Another one for the new user (we'll call this one NEW session from now on).
iii. In my case, Codd_pangj is my ORIGINAL session using pangj and c281NewUser is the session for the new user c281New (i.e. my NEW session).
b. In your ORIGINAL session tab,
i. Execute an SQL command that grants the new user SELECT privilege on table APPLICATION.
c. Switch to NEW session tab
i. Issue the following commands
SELECT * FROM pangj.APPLICATION; (replace pangj with your actual myFranklin username)
ii. You should not receive error after the SELECT privilege was granted to the new user.
iii. Copy the SQL and output result to Word doc.
iv. Issue an INSERT statement to insert a new record in new user session.
v. Was your insert successful? If not, why?
vi. How to resolve the issue to allow the new user insert, update and delete a record on this APPLICATION table?
vii. Execute the GRANT SQL in the proper session tab and then execute the same insert statement in new user session again after.
viii. Copy the SQL and output result to Word doc with step#.
4. Task 4: Creating a new role and granting privileges
a. In your ORIGINAL session tab,
i. Create a new role with a name of your choosing (newRoleName).
ii. Grant that role INSERT and UPDATE privileges on table APPLICATION.
iii. Grant the new role (newRoleName) to the NEW user you created earlier.
iv. Issue the SET ROLE newRoleName; command (this line tells the Oracle server to enable the corresponding role privileges granted to this user)
b. Go to the NEW session tab
i. Issue same SELECT & INSERT commands you used in Task 3 again.
ii. Were SELECT and INSERT commands executed successfully?
iii. Issue an UPDATE and DELETE command on the new inserted record in APPLICATION table.
iv. Were both commands executed successfully? If not, why?
v. What needs to be done to resolve the issue and make the command execute successfully?
vi. Implement the solution and copy ALL SQL commands and output into Word. Make sure the output is next to the each query.
5. Task 5: Removing privileges
a. In your ORIGINAL session tab
i. Issue an SQL command to remove the SELECT privilege on APPLICATION table from the new user.
ii. Go to NEW session tab
1. Issue the same SELECT, INSERT, UPDATE and DELETE in Task 4.
2. Were step 1 executed successfully?
6. Task 6. Create a View to your data
a. In your ORIGINAL session tab
i. Issue a SQL command to create a view called App_Team that lists the APP_ID,
APP_NAME, TEAM_NAME
ii. Grant select access to App_Team view to the role you created in Task 4 above.
b. Switch to NEW session tab
i. Issue "SELECT * FROM pangj. App_Team;" (replace pangj with your actual
myFranklin username)
ii. Was select data from view executed without issue?
c. How would you create and use a new role to grant access to your tables from this lab to your teammates and instructor?
7. Task 7. Submit in a single zip file containing the following:
a. Your complete MS WORD (NOT PDF or TEXT) report file as described earlier (including ALL SQL commands and their output)
b. 2. One (1) .sql containing all the SQL commands (call it C281lab7.sql)