Lab Manual

In this lab you will learn how to use DDL and Oracle Enterprise server to create user accounts, modify, monitor and delete them. You will take the first look at object permissions in Oracle. The last assignment is an example of query DBAs use to report quota usage..

The requirement of the lab is listed as deliverables. The deliverable is abbreviated as "D" in this manual.

Oracle User Admin Tutorial

Example : CREATE USER steve
IDENTIFIED BY abc123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 15M ON users
PROFILE default;

Additionally, you have GRANT a user permission to connect to the database. A newly created user cannot connect to the database until granted the CREATE SESSION system privilege.

GRANT CREATE SESSION TO steve;

GRANT CONNECT TO steve; --granting role that has the only CREATE SESSION permissions. See select * from dba_sys_privs where grantee='CONNECT';

To Remove User

Use the DROP USER statement to remove a database user and optionally remove the user's objects. Oracle Database does not drop users whose schemas contain objects unless you specify CASCADE or unless you first explicitly drop the user's objects.

If the user's schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.

DROP USER steve; drops user whose schema does not contain objects

DROP USER steve CASCADE; drops user and all schema objects

When you want to prevent a user from accessing a database, but still want to retain the database objects owned by the user, then instead of dropping the user lock his account, or revoke CREATE SESSION privilege from the user.

alter user steve account lock;

REVOKE CONNECT, CREATE SESSION from steve;

To Create, Remove and Manage users from Enterprise Manager

If Oracle Enterprise Manager Service OracleDBConsoleorcl service is not started, you have to start it

using GUI: My Computer - Manage - Services - Enterprise Manager Service OracleDBConsoleorcl service - Start

using command line: net start OracleDBConsoleorcl

In browser enter https://localhost:1158/em, and logon as ‘system'

In the home page, select ‘Server' tab

In the ‘Server' tab, select user

In the User page click Create

Now you can set all values for you new user as you want.

You can also delete user from Enterprise Manager

Once you are finished working with Enterprise Manager remember to stop the OracleDBConsoleorcl service.

Deliverables

D1. Create user 1 -include SQL code and screen shot of successful query execution.

Logon as system/oracle using SQL Developer and create user user1

The default tablespace is USERS.

The temporary tablespace is TEMP.

The account is initially locked.

The user account will be assigned to DEFAULT profile.

The USERS tablespace has a 10 MB quota.

D2. Create user user2 - list the steps performed and include screen shot of successful execution.

Start Oracle Enterprise Manager Service OracleDBConsoleorcl service and openg Oracle Enterprise manager.

The default tablespace is USERS.
The temporary tablespace is TEMP.
The account is initially locked.
The user account will be assigned to DEFAULT profile.

D3a. Unlock user2 account using Oracle Enterprise manager - list the steps performed

D3b. Delete user2 Close Oracle Enterprise manager first then and stop OracleDBConsoleorcl service - list the steps performed

D4. Close SQL Developer, then Open SQL Developer and Connect as user1. In the deliverable, explain what happened and how it should be corrected.-

D5. When you connected as user1 find all objects which names starts with DBA_. Connect as system find all objects which names starts with DBA_. In the deliverable, include the SQL query and explain the difference in query result.

Hint: Use data dictionary view all_objects.

D6. When you logged on as system make user1 account password expire. -include SQL query.

D7. Login as user1, and describe how you will change password.

Hint: If you have problems changing the password through SQL Developer, use SQL+Plus

D8. Login as user1 and create a new table, Test. Do you experience problem? - include the SQL creating the table and the SQL query fix the problem. Hint: use COMMIT to save the changes.

testID
1234

D9. Login as system and select all entries from user1's table Test. - include the SQL query and screenshot.

D10. Login as system and create a query that lists all users who can login.- include the query. Hint: You will need to use a select statement and a data dictionary view DBA_USERS to generate report. Run select * from DBA_USERS; and look at data, then modify the query.

D11. Delete user1. - include the query.