Lab - Understanding Oracle’s Security Features

This assignment relates to the following Course Learning Requirements:

CLR 2 - Administer a DBMS using knowledge of SQL, database security features, globalization, and database architecture (storage, memory and processes)

CLR 3 - Manage database system security and privacy controls

Objectives of the Lab

Demonstrate the ability to create USERS, understanding the purpose of establishing PRIVILEGES and ROLES in Oracle and how a PRIVILEGE is related to a ROLE. Lastly, relating a ROLE or PRIVILEGE to a new USER. This Lab provides the example of ‘role-based’ access control security paradigm (RBAC).

Resources:

Download the RBAC document attached to this Lab (Lab RBAC – Security Features)

http://docs.oracle.com/database/121/ADMQS/GUID-7FC1D8BE-4BB9-4642-A4CE-29CD2B8A5F23.htm#ADMQS007

Lab Submission Tasks:

Part 1 - Concepts

1. Using the RBAC document, explain how the author(s) state that the RBAC support the _________________ Privilege security principle.

2. The information in the ‘About User Accounts’ section (refer to the URL provided above), list five(5) of the actions which must be completed when CREATING AN ORACLE USER. (type in the provided box)

Open a cmd window as ‘run as administrator’ then run SQLPLUS / as SYSDBA. This should connect you as SYS (ORACLE USER) in the SYSDBA (ORACLE ROLE). 

a. As a reminder on how to find the relevant V_$ or system tables containing information you need to analyze, try the following and screenshot your output in the provided box.

3. Enter the command “DESC DBA_TABLESPACES;”  (provide screenshot of your command and outputs and paste in box below)

4. Enter ‘SELECT TABLESPACE_NAME, BYTES, MAX_BYTES, (100*(BYTES/MAX_BYTES)) AS PERCENT FROM DBA_TS_QUOTAS;  (provide screenshot your statements and outputs and paste in box below)

Which tablespace is most utilized (based on %)….(Provide screenshot your statements and outputs and paste in box below)

5. Run the following command 

SELECT TABLESPACE_NAME, CONTENTS

   FROM DBA_TABLESPACES;

Indicate which tablespace is ‘permanent’ on your system.

(Provide screenshot your statements and outputs and paste in box below)

6. Unlock the ‘scott’ user by using the ‘ALTER USER’ command. (Provide screenshot your statements and outputs and paste in box below)

Consider the example below:

a. Then, using the DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS tables, determine all of the privileges that have been directly granted to the “SCOTT” username.  Show your work below (you can use multiple queries).

b. Using the DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS tables, determine the privileges that have been directly granted to the “SYSTEM” and “SYS” usernames.  Show your work below (you can use multiple queries).  Make sure the counts are visible.

7. Identify on which tablespaces the “scott” user has been allocated space. Justify your answer.

8. Use the DBA_PROFILES table to list the details of the ‘DEFAULT’ profile. 

Provide a screen shot below:

List 2 aspects of the default profile that could lead to performance issues: _________________________________

List 1 aspects of the default profile that could lead to security issues: _________________________________

9. In a sequence of “create user ….”, “alter user …” and other privilege granting steps, you are to create a new user with the following criteria:

a. Username - your last name (e.g., king)

b. Password – your last name (e.g., kingpwd)

c. Set the password to be expired. (It will need to be changed on next connection.)

d. Use the DEFAULT profile.

e. Use the default tablespaces (i.e., USERS and TEMP).

f. Use the “GRANT …. TO ….” command multiple times to grant CONNECT, RESOURCE and CREATE VIEW to the new user.

Show your work below:

g. Keep your SQLPLUS session running as SYS AS DBA, and open a new command window (not as administrator).  Then, logon to SQLPlus with your new account in the new window. You should get an 'expired password' message. When prompted enter lastnamepwd as your “New” password.  (It will let you reuse the original one….)

h. Try to create a simple empty table from your new account.  If successful, try to insert one record.  What occurs?  Show your work

i. Keep your new account window open but change focus back to your original SYS as SYSBA window to alter the quota on the ‘USERS’ tablespace for your new user to be UNLIMITED.  

e.g., “ALTER USER myname QUOTA UNLIMITED ON USERS;”  Show your work here:

j. Return to your new account in the other window and retry creating a new table and inserting a single row. Show your work here:

Data