File=cmis565_lab6.htm; updated July 1, 2013

 

Oracle Database Administration – Lab #6

Profiles, Users, Roles, and Privileges (20 points)

 

Situation: You are continuing to develop your database.  You will demonstrate the operation of Oracle for various classes of users by creating user accounts and roles to match user work positions within a hypothetical firm.  You will allocate system and object privileges to those users and roles.  You may elect to complete the laboratory individually, or you may team up with 1 to 2 laboratory partners.  If you team up with partners, you will select your own partners and you will be graded as a group, and you will submit a single document as evidence of your team's work.  The tables and data you enter into your database during completion of this laboratory will support your work on future laboratories, so it is essential that you complete this laboratory successfully.

 

Use Notepad, Wordpad, or Microsoft Word to document your solution to the tasks in the laboratory. 

  • Label your answer to each task clearly by typing the number/letter (example 2a) in bold print.
  • Show each command you execute and the computer's response (example response:  Database Altered, Tablespace Created, etc.).  If you don't show the commands you execute, then your answer will be counted as wrong.
  • Print your output using 10 point Courier or Courier New font (so the output lines up and is easy to read).
  • Format your output as necessary to make it easy to read (does not wrap lines of output around unless absolutely necessary).  Do not submit for grading anything you would not submit to your boss for review in an actual work environment, i.e. in other words, do not turn in sloppy work that is difficult for me to grade.   

 

SPECIAL NOTE

 

During the completion of this laboratory, you are very likely encounter errors (some are built into the lab).  If you encounter errors, then you are expected to solve the problems by referencing the Oracle error messages technical manual available through the course web site.  If you cannot solve the problems after several attempts, see me for assistance.  I may help you solve the problem, or I may tell you that it is a problem that you are expected to solve on your own.

 


Tasks for this Lab

1. Create a PROFILE (2 points). 

  1. Create a PROFILE named New_User with the following limits: 

·   SESSIONS_PER_USER 1

·   CPU_PER_SESSION unlimited

·   CPU_PER_CALL 5000

·   LOGICAL_READS_PER_SESSION unlimited

·   LOGICAL_READS_PER_CALL 100

·   IDLE_TIME 45

·   CONNECT_TIME 240

  1. Alter the system to enforce resource limits with this command: 

 

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

 

2. Create database users (2 points). 

  1. Create three new database users named Al, Bill, and Susan,

·         Each new user is identified by the password PASSWORD

·         Their default tablespace is USERS.

·         Their temporary tablespace is TEMP.

·         The quota is 20K on USERS.

·         The profile is the Oracle database default profile.  If you encounter any problems (such as Oracle error messages) when working with these users, then you are expected to develop solutions to the problems. 

  1. Execute the following SQL commands:

 

COLUMN username FORMAT A8;

COLUMN default_tablespace FORMAT A18;

COLUMN temporary_tablespace FORMAT A20;

SELECT username, default_tablespace, temporary_tablespace

FROM DBA_USERS

WHERE username IN ('AL', 'BILL', 'SUSAN');

 

3.    Alter a user (2 points). 

a.    Alter the user Al to assign him the PROFILE named New_User.  Execute the following SQL commands: 

 

COLUMN username FORMAT A8;

SELECT username, profile

FROM DBA_USERS

WHERE username = 'AL';

 

b.    Alter the user named Al to change the default tablespace to the DATA01 tablespace with a quota of 20K.   Execute the following SQL commands: 

 

COLUMN default_tablespace FORMAT A18;

COLUMN temporary_tablespace FORMAT A20;

SELECT username, default_tablespace, temporary_tablespace

FROM DBA_USERS

WHERE username = 'AL';

 

c.    Alter the user named Bill to assign a quota of 10K on a non-existent tablespace named APPLICATIONS.  What error code identifier and error description is generated?  Do not attempt to fix the error – go on to the next part of this task.

d.    Alter the user named Susan to assign an unlimited quota on the USERS tablespace.  

e.    Attempt to open a PuTTY session (use your EID) and connect to your database using SQLPlus as the user Al.  What happens?  Correct the problem, then finish connecting as Al.

f.     Keeping Al's first connect session open, attempt to open another PuTTY session (use your EID) and connect to your database using SQLPlus for another session as the user Al.  What happens?  Why?  Exit the session windows for Al.

g.    Execute the following SQL commands to display information about Al, Bill, and Susan.

 

COLUMN tablespace_name FORMAT a15;

COLUMN username FORMAT A8;

SELECT tablespace_name, username, bytes, max_bytes, blocks

FROM dba_ts_quotas

WHERE username IN ('AL', 'BILL', 'SUSAN');

 

4.    Connecting as a user/granting a privilege (2 points). 

a.    Attempt to connect to the database as user Bill, and Susan, in turn.  Why can’t you connect?  Grant Bill and Susan the privilege needed to connect to the system. 

b.    Connect as each user (Al, Bill, and Susan) in turn and execute the SQL commands shown here:

 

COLUMN tablespace_name FORMAT a15;

SELECT tablespace_name, max_bytes, blocks

FROM user_ts_quotas;

 

5.  Create a role (2 points). 

a.    Connect as your DBA user.  Create a role named application_developer

b.    Assign the system privileges: create session, create table, and select any table to the new role. 

c.    Grant the roles application_developer and connect to the user named Al with the admin option. 

d.    Execute the SQL commands shown here:

 

COLUMN grantee FORMAT A8;

SELECT grantee, granted_role

FROM dba_role_privs

WHERE grantee IN ('AL', 'BILL', 'SUSAN');

 

6.  Creating a table by a user – insert row data (2 points). 

a.    Connect to the system as the user named Al.  Create a table named TEST with a single VARCHAR column named TEST_COLUMN that will hold a maximum of 20 characters.  Do not specify a tablespace or any default storage specifications.   Insert a row of data into the TEST table.  If any error(s) occur, correct the problem and try again to insert a row of data into the TEST table.

b.    Execute the SQL commands shown here:

 

COLUMN tablespace_name FORMAT a15;

SELECT tablespace_name, pct_free, pct_used, initial_extent

FROM user_tables

WHERE table_name = 'TEST';

 

c.    While connected as the user named Al, use the INSERT command to insert two additional rows of data into the table named TEST (you create your own data). 

d.    Query the TEST table to list the rows you inserted to verify their existence. 

 

7.  Granting SELECT privileges (2 points). 

a.    Connect as the user named Bill.  Attempt to select the rows from the TEST table that belongs to Al.  Why can’t you select from this table? 

b.    Now connect as Al again and grant the privilege that Bill will need in order to select rows only from the TEST table (GRANT select ON test TO bill;). 

c.    Connect again as the user named Bill.  Write the SELECT query needed to select all rows and columns from the TEST table. 

 

8.  Granting INSERT privileges (2 points). 

a.    Connect as the user Bill. 

b.    Attempt to INSERT a row into the TEST table.  What error is generated? 

c.    Connect as Al and grant Bill the privilege needed to insert rows only into the TEST table. 

d.    Connect as Bill and insert a 3rd row into the TEST table.

e.    Query the TEST table to display the row values currently stored in the table.

 

9.  Creating additional roles (2 points). 

a.    Connect as the DBA user account for your database. 

b.    Create a new role named data_entry_clerk

c.    Connect as the user Al and grant the SELECT and INSERT object privileges for the TEST table to the data_entry_clerk role. 

d.    Connect as the DBA user account for your database and grant the role data_entry_clerk to the users named Bill and Susan

e.    Connect as user Susan and try to INSERT a new row into the TEST table (INSERT INTO al.test VALUES ('Test Row-Susan');).  Could you insert the new row - Why or why not?

 

10.  Displaying object privileges (1 point). 

a.    Connect as the DBA user account for your database.

b.     Execute the commands shown here:

 

COLUMN grantee FORMAT A16;

COLUMN owner FORMAT A8;

COLUMN table_name FORMAT A10;

COLUMN grantor FORMAT A8;

COLUMN privilege FORMAT A10;

SELECT  grantee, owner, table_name, grantor, privilege

FROM sys.dba_tab_privs

WHERE owner = 'AL';

 

11. Revoking privileges (1 point). 

a.    While connected as the DBA user account for your database, revoke the INSERT privilege for the TEST table from the user named Susan ( REVOKE insert ON al.test FROM susan; ).  What message was generated by this command? 

b.    Connect as the user named Susan and attempt to insert a row into the TEST table.  What happens?

 

12.  Shut down the database (-1 point if you forget to shutdown). 

 

Do not leave your database running between laboratory sessions.

 



END OF ASSIGNMENT.