Instructor's Notes

(If applicable)

• Lesson 8: Creating and Managing Users
• In this lesson you will learn many (not all*) of the concepts and tasks related to the management of Oracle users. This includes:
• Creating, alter and drop users.
• Assigning privileges to a user.
• Assign default tablespace and temporary tablespace to a user.
• Manage quota – the ability for a user to use disk space in a tablespace.
• Query the data dictionary views related to users.
• Terminate active user sessions (often called “killing” a session”).
• Identifying and securing the “default” users, i.e. the users created as part of the database installation.
• Believe it or not, there is more to learn about users than what is covered in this lesson. Subsequent lessons in this course will cover:
• Password management, e.g. establishing password expiration periods.
• Auditing user activity, i.e. keeping a record of what actions have been taken.
• Securing users, beyond granting simple privileges.
• But, for now, let’s concentrate on the basics of user management.

Instructor's Notes

(If applicable)

• A user account is required to connect to the database. The user (i.e. user account) is created with the CREATE USER SQL statement.
• USERNAME – The username adheres to standard Oracle naming conventions - 1 to 30 alpha-numeric characters, first character must be alphabetic. Special characters _, $ and # are allowed.
• IDENTIFIED BY defines the password. Password must also adhere to Oracle naming conventions unless the password complexity verification feature is enabled.
• EXTERNALLY creates an operating system-authenticated user. This user can, once connected to the OS, connect to Oracle with a slash (/) and not provide a password (the OS has already verified the user). Refer to the Oracle9i SQL Reference manual for more information.
• DEFAULT TABLESPACE - specify the tablespace that should be used for objects created by this user when no specific tablespace is specified for the object.


• Notes for this slide continue on the next page…

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• TEMPORARY TABLESPACE - specify the tablespace that will be used for disk sorts and temporary tables. The default temporary tablespace is controlled by the DEFAULT TEMPORARY TABLESPACE clause on the CREATE DATABASE command. (You will study the CREATE DATABASE command later in the Creating a New Database lesson.
• QUOTA - Quota is required to create an object in a tablespace. More on this later in this lesson.
• PROFILE - Specify the name of the profile that controls the resources and password features used by this user. More on this later in this lesson.
• Refer to the Oracle9i SQL Reference for a complete description of the CREATE USER statement.

Instructor's Notes

(If applicable)

• This example demonstrates that, if you have CREATE USER privilege, it is a simple matter to create a new user. The user requires CREATE SESSION privilege to logon – a DBA who is an administrator of the CREATE SESSION privilege can grant the privilege to the user.
• By default, if the user can login, the new user can access the DUAL table and the USER_ data dictionary views.
• Supplemental Notes
  • The new user's default tablespace is set to the SYSTEM tablespace. Do not leave this as is. You will learn how to correct this later in this lesson.

Instructor's Notes

(If applicable)

• A user cannot do anything - even connect to the database - without privilege. Some typical privileges a user will need include:
• CREATE SESSION is required to connect to the database.
• Object privileges such as SELECT allow a user access to objects owned by another user (by default a user has all rights to any object they create).
• System level privileges are required to to execute DDL. E.g. To execute the CREATE TABLE command a user needs CREATE TABLE privilege.
• Privileges will be covered in detail in the Managing Security lesson later in this course.
• Note that to create an object that requires disk storage, a user also requires QUOTA on a tablespace. We will study quotas later in this lesson.

Instructor's Notes

(If applicable)

• When a user creates a permanent object such as a table, the object requires disk space. If, at creation time, the user does not explicitly specify a tablespace for the object, the object is placed in the users default tablespace.
• Quota is necessary on the tablespace in which the user wishes to put the object. Quota is the privilege to use space within the tablespace. In this example, I have set the quota to "unlimited", which means that the user can use all of the available space in the tablespace. We will look at quotas in more detail later in this lesson.
• Temporary space is required for sort operations that do not fit in PGA memory, and for temporary tables.
• Supplemental Notes
  • The PGA memory available to a user under Oracle8i is largely controlled by the SORT_AREA_SIZE parameter. Oracle9i provides automatic PGA memory size management. Refer to the WORKAREA_SIZE_AUTO and PGA_AGGREGATE_TARGET initialization parameters in the Oracle9i Database Reference and Oracle9i Database Performance Tuning Guide and Reference.

Instructor's Notes

(If applicable)

• All database users are recorded in the data dictionary. Query the DBA_USERS view for a complete list of all users.
• The query above displays only active (i.e. "OPEN") users. We will discuss open, expired and locked user accounts in the next lesson, Password Management.
• Supplemental Notes
  • Other details about the set-up of the user accounts needs to be accessed from different data dictionary views. For example, DBA_TS_QUOTAS contains information about the quotas on tablespaces for that user.

Instructor's Notes

(If applicable)

• The ALTER USER statement can be used to change the attributes of a user. ALTER USER system privilege is required, with one exception: any user can execute ALTER USER to change their own password. ( This can also be accomplished with the SQL*Plus PASSWORD command.)
• Quota and profile will be discussed in more detail later in this lesson. "Default role" will be discussed in the Managing Security lesson.
• Here is the basic syntax for this statement. Refer to the Oracle9i SQL Reference for complete details:
• ALTER USER username
• IDENTIFIED { BY password | EXTERNALLY }
• [ DEFAULT TABLESPACE d-tspace ]
• [ TEMPORARY TABLESPACE t-tspace ]
• [ QUOTA {integerK | integerM | UNLIMITED}
• ON q-tspace … ]
• [ PROFILE profile-name ]
• [ DEFAULT ROLE { role, role, … |
• ALL [EXCEPT role, role,…] | NONE }

Instructor's Notes

(If applicable)

• To remove an Oracle user from the database we can use the DROP USER SQL statement. The entry for the user is deleted from the DBA_USERS data dictionary view removing all trace that the Oracle user ever existed. If the Oracle user owns any database objects then we can either ensure that we drop each of the objects before dropping the user or we can use the CASCADE option. This will drop all of the user's objects automatically when the user account is removed.
• Warning: Consider exporting all the users objects prior to DROP CASCADE. A point-in-time database restore will be required to recover the objects if this is not done.
• The example above demonstrates how to export (I.e. backup) a user's objects with the export utility before dropping the user. "exp" is an operating system command, so preface it with the HOST ($) command (! In UNIX) if executing from within SQL*Plus.
• "SYSTEM/DAVE" is the username and password used to perform the export. User SYSTEM requires the EXP_FULL_DATABASE role because we are exporting another users objects.


• Notes continue…

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• GRANTS=Y tells Oracle to export all grants that have been granted on the exported objects. The grants are reapplied when importing.
• OWNER=DAVE tells Oracle to export all objects owned by user DAVE.
• You will study the export utility in-depth in the lesson Logical Backups later in SkillBuilders Oracle9i Database Administration course.

Instructor's Notes

(If applicable)

• Killing (i.e. terminating) a user session is done with the ALTER SYSTEM KILL SESSION command. This may be necessary when a user session is holding resources needed by other processes or users.
• User SCOTT receives the following message:
• SQL> SELECT * FROM user_tables;
• SELECT * FROM user_tables
• *
• ERROR at line 1:
• ORA-00028: your session has been killed

Instructor's Notes

(If applicable)

• User Basics Workshop
  1. Create an Oracle user account with the following settings :
• Oracle User Name - yourname2 e.g. geoff2
• Password - yourname e.g. geoff
• Default tablespace - USERS
• Temporary tablespace - TEMP
2. Query the Oracle data dictionary to ensure that this user has been created correctly.
3. Attempt to make a connection to the database using the user name and password specified.
4. Any ideas as to why it does not work? Fix the problem and re-connect.
5. Change the user’s password to yourname2 e.g. geoff2 .
6. Query the USER_USERS data dictionary view while logged into the new user.
• Workshop continues on the next page…

Instructor's Notes

(If applicable)

• Workshop continued from the previous page:
  7. Open a second SQL*Plus window, logon to the SYSTEM user (or your new DBA-level user), and kill the original session.
  8. Do a SELECT from the original session. What happens?

Instructor's Notes

(If applicable)

• For each Oracle user we can control how much storage can be used by that user’s objects on the database. This is done with “quota”.
• The CREATE USER and ALTER USER commands are used to specify which tablespace(s) the user has a quota for and how much storage they are allowed to use in that tablespace. The amount of storage allocated to that user can be used by any type of database object which takes up storage (table, index, cluster). If the amount of storage used by the Oracle user exceeds the amount allocated to it, then an error message will be generated showing that the user has exceeded his quota. It is then up to the user to either drop some objects or to ask the database administrator to increase his quota on the tablespace. (‘UNLIMITED’ can be used to eliminate limits; see details later in this lesson.)
• The quota may be exceeded either when the user creates an object or when an object attempts to use more storage by attempting to allocate an additional extent worth of storage (this can happen, for example, when the user executes and INSERT statement).

Instructor's Notes

(If applicable)

• Quota is provided to the user account either with the CREATE USER or ALTER USER statements.
• In the first example above I provided 64k of quota. The second example shows that user “DAVE” can now create a table in tablespace USERS.
• Supplemental Notes
  • I chose 64k because this was the default initial extent for the USERS tablespace. I determined this by executing this query:
  • SQL> select initial_Extent
  • 2 from dba_tablespaces
  • 3 where tablespace_name = 'USERS'
  • 4 /
  • INITIAL_EXTENT
  • --------------
  • 65536

Instructor's Notes

(If applicable)

• If the quota for a user is increased, there is a new maximum amount of storage that the user may use in total - the storage already allocated for that Oracle user for its own objects is taken account of.
• If quota is set to zero, any objects that the user has already created will not be affected – except that they cannot request additional extents (i.e. expand).

Instructor's Notes

(If applicable)

• DBA_TS_QUOTAS contains:
• BYTES – Number of bytes charged to user.
• MAX_BYTES – Maximum bytes user can consume. -1 for UNLIMITED quota.
• BLOCKS – Number of ORACLE blocks charged to the user
• MAX_BLOCKS – User's quota in ORACLE blocks. -1 if no limit
• Note that quota does not exist (there is no row in DBA_TS_QUOTAS) when quota is set to 0.


• Notes for this slide continue on the next page…

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• Another handy query for the DBA_TS_QUOTAS view uses the DECODE function to decode “-1” into the string “unlimited”, otherwise perform a subtraction to return the number of bytes still available to the user:
• SQL> SELECT tablespace_name,
• 2 DECODE(max_bytes,
• 3 -1, 'UNLIMITED',
• 4 max_bytes - bytes) AVAILABLE
• 5 FROM dba_ts_quotas WHERE username = 'DAVE’;

• This query is available in QUOTA_AVAILABLE.SQL in your supplied scripts.

Instructor's Notes

(If applicable)

• As this example demonstrates, any user can easily determine how much space an object they own is consuming.
• Remember that an object such as a table is stored in a segment and that the segment name is equal to the object name. That is why this query contains “WHERE SEGMENT_NAME = ‘TEST’”.

Instructor's Notes

(If applicable)

• Users SYS and SYSTEM are created during by the CREATE DATABASE command. (Technically, by the “SQL.BSQ” script located in $ORACLE_HOME/RDBMS/ADMIN directory. Do not change this script.)
• Be sure to protect these user accounts.
• Other users will be created by the database installation scripts (and later as you add tools and products to the database). For example, user OUTLN is created to support plan stability. OUTLN owns a series of tables that are required for stored plans.
• Additional users created by installation scripts are listed on the next page.

Instructor's Notes

(If applicable)

• Depending on the options that you choose to install with you database, all or some of the users listed above will be created as part of the database creation process. They can be created at a later time if you subsequently install these products.
• Warning: The users created changes with every release; See the Oracle9i Getting Started guide for your operating system for a complete description of these users. Be sure to secure these users.
• In all cases the default password is the same as the username, except for user SCOTT where the password is TIGER (legend has it that Scott was one of the original Oracle developers and TIGER was Scott’s cat).

Instructor's Notes

(If applicable)

• Oracle users can be locked and optionally expired. As the example above demonstrates, a locked account cannot logon to the database until an administrator unlocks the account.
• If the user is expired, the user is prompted for a new password by SQL*Plus the next time they login. (This required SQL*Plus!):
• SQL> connect gary/gary
• ERROR:
• ORA-28001: the password has expired


• Changing password for gary
• New password:
• Retype new password:
• Password changed
• Connected.

Instructor's Notes

(If applicable)

• User Quota & Account Status Workshop
  1. Create user yourname3 (e.g. geoff3) with a password of yourname3 (e.g. geoff3), set the default tablespace to USERS and the temporary tablespace to TEMP, and give the user a quota of 64K on the USERS tablespace.
  2. Grant the necessary privileges to allow the user to connect and create tables.
  3. In a second SQL*Plus session login to the new user and create this table:
• CREATE TABLE test (dummy1 CHAR(2000));
3. In the new-user session, determine how much space the test table is consuming.
4. In the new-user session, insert rows until a quota error is received:
• INSERT INTO test VALUES ('test');
• insert into test select * from test;
• /
• /
• /
5. In the 1st SQL*Plus session session, set the users quota to unlimited.
• Workshop continues on the next page…

Instructor's Notes

(If applicable)

• Workshop continued from the previous page:
6. In the new-user session, retry an insert:
7. In the new-user session, again determine how much space the test table is consuming.
8. Display all usernames and user account statuses.
9. Secure one of the open accounts.