Oracle Security Practices

Oracle, like many other databases, uses the combination of specific user definitions, privileges, and roles to control access to the data in the database. In turn, it provides various layers of security. When the database is first created, there are several users created for the purpose of not only installing various components of the database, but also to manage and administer the database functionality. You have already used the SYS and SYSTEM users in previous labs. In this lab you will be creating a series of different users, administering various privileges to those users, as well as exploring how Roles help provide additional functionality to the user picture.
VERY IMPORTANT:
Be sure that you start a spool session before you start executing your SQL code when working on the lab. Your SPOOL file name should be DATABASE_lab6. This will be the only way to capture the results of your work and will be required for grading. Remember that anytime you see the ### in the instructions (not the expected results), it indicates that you are supposed to replace the number signs with your instance number. Last, if you do the lab in several sessions, be sure that you use a different file name, so as not to overwrite the existing file (for example DATABASE_lab6, DATABASE_lab6a, DATABASE_lab6b etc.)
Environment Set Up:
Before starting this lab you will need to download two files from Doc Sharing and run them to make some changes to the environment.  To make these change do the following:

1. Download the pupbld.sql file from Doc Sharing to a directory on your computer.
• Open the file and edit the login information at the top for the SYSTEM user (change the DB### to your instance name).
• Now at the bottom of the file make the same change to the login string to go back to the SYS user.
• Now run the file in your Oracle enstance.
2. Download the Lab6_support.SQL file from Doc Sharing to a directory on your computer.
• Open the file and edit the login information for the connection to SCOTT to reflect your instance number in the host string. This is the only change you should have to make to the script.
• When finished, save the file and then run the script file logged onto your instance as the SYS AS SYSDBA.

L A B   S T E P S

Step 1: Creating a new user

First, connect to your instance as the SYS AS SYSDBA user and set the line size for the session to 132 (Hint: issue SETLINESIZE 132 from the command prompt). Now, write the commands to create user Bob, with a password ALONG. Make sure that any objects created by Bob are created in the USERS01 tablespace and temporary segments created by Bob go to the TEMP01 tablespace. Ensure that Bob cannot create objects greater than 512K in size in the USERS01 tablespace.
Your response will look similar to this:

User Created

Now that the user is created, make sure that Bob can create a session, by granting the needed privilege.
Your answer will look similar to this:

Grant succeeded.

Step 2: Finding information on users

Write and execute the query to display the following information on Bob from the data dictionary.

Hint: This information can be obtained by querying the DBA_USERS.

Your response will look similar to this:

USERNAME                             DEFAULT_TABLESPACE            TEMPORARY_TABLESPACE
------------------------------ ------------------------------ -----------------------
BOB                                      USERS01                               TEMP01

Step 3: Finding information about user storage

Write and execute the query that will displayinformation on the amount of space that Bob can use in this tablespace.

Hint:This information can be obtained from DBA_TS_QUOTAS.

Your answer will look similar to this:

TABLESPACE_NAME     USERNAME               BYTES     MAX_BYTES  BLOCKS     MAX_BLOCKS
--------------------- -------------------- ---------- ------------ ---------- ------------
USERS01                   BOB                        0             524288        0              64

Step 4: Finding user privileges

As Bob (you will need to change your session connection for this and connect as the user Bob), change his password to SAM.

Hint: At the command line prompt enter CONNECT bob/along@host_string_goes_here

Your answer will look similar to this:

Connected.
User altered.

While connected as Bob, query the USER_SYS_PRIVS data dictionary view to see what privileges Bob currently has. This view is accessible by any user and can be very helpful.
Your answer will look similar to this:

USERNAME                  PRIVILEGE
---------------------- ------------------------
BOB                           CREATE SESSION

Step 5: Changing user specifications

Connect back as the SYS AS SYSDBA user and remove Bob's quota on his default tablespace.
Your answer will look similar to this:

Connected.
User altered.

Bob has forgotten his password. Assign him a password of OLINK and require that Bob change his password the next time he logs on.
Your answer will look similar to this:

User altered.

Step 6: Listing privileges associated with a Role

Examine the data dictionary view and list the system privileges of the resource role.

Hint: The information is available from DBA_SYS_PRIVS.

Your answer will look similar to this:

GRANTEE                               PRIVILEGE                                           ADM
------------------------------ ---------------------------------------- ----
RESOURCE                             CREATE CLUSTER                                  NO
RESOURCE                             CREATE INDEXTYPE                               NO
RESOURCE                             CREATE OPERATOR                                NO
RESOURCE                             CREATE PROCEDURE                              NO
RESOURCE                             CREATE SEQUENCE                                NO
RESOURCE                             CREATE TABLE                                      NO
RESOURCE                             CREATE TRIGGER                                   NO
RESOURCE                             CREATE TYPE                                       NO
8 rows selected.

Step 7: Creating a Role

Now you are going to create a new Role for your users to take advantage of.
Create a role called DEV and assign privileges to the Role that will allow a user to:

1. Create a session
2. Create a table
3. Create a view
4. Enable a user select from Scott's EMP table.

Hint: Remember that a member assigned to this roll must be able to create the table in any tablespace. Also, you cannot assign System privileges and Object privileges in the same GRANT statement.

You should get responses that look similar to this:

Role created.
Grant succeeded.

Step 8: Verifying Role content

Query the DBA_SYS_PRIVS and DBA_TAB_PRIVS to verify that the role has the proper privileges assigned.
Your response will look similar to this:

GRANTEE                               PRIVILEGE                                           ADM
------------------------------ ---------------------------------------- ---
DEV                                      CREATE VIEW                                       NO
DEV                                      CREATE SESSION                                  NO
DEV                                      CREATE ANY TABLE                               NO
GRANTEE           OWNER                      TABLE_NAME           GRANTOR
---------------- ---------------------- ------------------- -------------------
DEV                  SCOTT                       EMP                      SCOTT

Step 9: Assigning a user to a Role

Now create a new user named JACK, with a password of HORNER. Use the users01 and temp01 tablespaces for the default and temporary tablespaces, and set a quota of 512k on the USERS01 tablespace. After the user is created, assign the DEV role to Jack.
Your response will look similar to this:

User created.
Grant succeeded.

Step 10: Using privileges in a Role

Now connect as JACK and create a GLOBAL TEMPORARY table named TEST1 that is a copy of the EMP table in Scott's schema. If you need to, refer back to chapter 7 for help with this.
Your answer will look similar to this:

Connected.
Table created.

Step 11: Assigning multiple Roles to a user

Now, go back and connect as the SYS AS SYSDBA user and give Jack the ability to read all of the data dictionary information.

Hint: there is a system-level role that will accomplish this but you have to be connected as the correct user to assign it.

Your answer will look similar to this:

Connected.
Grant succeeded.

Step 12: Assigning a Default Role

Assign the RESOURCE role to JACK, but make only the role assigned above in #9 able to be automatically enabled (default) when he logs on. After you have done this,log on as Jack and verify that the role assigned as the default is accurate.

Hint: You will need to use the USER_ROLE_PRIVS view to find your information.

Your answer will look similar to this:

Grant succeeded.
User altered.
Connected.
USERNAME                             GRANTED_ROLE                      DEF             
------------------------------ ------------------------------ ---             
JACK                                     DEV                                     YES             
JACK                                     RESOURCE                            NO              
JACK                                     SELECT_CATALOG_ROLE          NO