Oracle Database Administration – Lab #6
Profiles, Users, Roles, and Privileges
Situation: You are continuing to develop the prototype for the BOCK Company. You will demonstrate the operation of Oracle for various classes of users by creating user accounts and roles to match user work positions within the 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.
SPECIAL NOTE
During the completion of this laboratory, you may encounter errors – If you do, 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.
STEPS
1. Create a PROFILE
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
Alter the system to enforce resource limits with this command:
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE
2. Create database users
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 databasedefault 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.
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
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 connect as the user Al. What happens? Correct the problem, then finish connecting as Al.
f. Keeping Al's first connect session open, attempt to connect in another session as the user Al. What happens? Why?
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
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
a. 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
a. Connect to the system as the user named Al. Create a table named TEST with a single variable character column named TEST_COLUMN that will hold a maximum of 20 characters. Do not specify a tablespace or any default storage specifications.
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 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
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
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
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
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 table_name = 'TEST';.
11. Revoking privileges
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?
a. Connect as the user named Susan and attempt to insert a row into the TEST table. What happens?
12. Shut down the database
