CIS 421b: Database Administration I

Chapter 11: Users and Resource Control


This chapter presents methods for managing users and resources in your system. The objectives important to this chapter are:

  1. Creating and managing users
  2. Managing profiles
  3. Managing passwords
  4. Viewing information about users, profiles, passwords, and resources

The chapter begins with a few more details about two of the default users in Oracle9i. We are told that the SYS user owns many tables needed to run a database, many of the views, and many packages and procedures. The SYS user has the rights needed to start and stop an instance, and to back up and recover a database. This user ID also has enough rights to drop critical tables in the database, so the text recommends using the SYSTEM user ID for routine maintenance tasks.

The SYSTEM user ID is able to use the DBA role (explained in Chapter 12) which allows it to create new users, monitor the database, and manage resources.

The command to create a user is
CREATE USER username
This command uses several parameters. The IDENTIFIED parameter can have three variations:

  • IDENTIFIED BY - this variation allows you to set a password for the user.
  • IDENTIFIED EXTERNALLY - this one allows the user access to the database as long as the user has logged in to the local operating system. The Oracle user name for the user may be identical to the operating system name or it may include a prefix. In either case, the user can log on to an aspect of the database by calling that aspect and entering a slash afterward.
  • IDENTIFIED GLOBALLY AS username - this method establishes a global username that can be used by any number of users.

The CREATE USER command can also set several other options for a user ID:

  • DEFAULT TABLESPACE - this sets the default tablespace for the user. If this is not set, the user is assigned the tablespace of the user who created it.
  • TEMPORARY TABLESPACE - as above, if this is not set, the user will be assigned the temporary space of the user who create it
  • QUOTA - this is a limit to the storage space this user may use. The default is no limit. This can be set to 0 to keep users from adding more files.
  • PROFILE - all users must be assigned to a profile. The default assignment is to the DEFAULT profile.
  • PASSWORD EXPIRE - if this setting is applied, the password set above will expire immediately, causing the user to have to change his/her password when the user logs in.
  • ACCOUNT - this parameter can be used to LOCK or UNLOCK the user's account. The default setting is UNLOCK.

After creating a user, you should grant the right to log in to the user. The command is

You can run a query to see the settings for a user:
SELECT parameter_list
WHERE USERNAME = 'username' ;

Once the settings for a user are known, you can use the ALTER USER command to change the settings. The syntax is similar to the syntax used when setting the initial values.

Users can be deleted with the DROP USER username command. It should be followed by the keyword CASCADE if the user being dropped owns any database resources.

The text continues with a discussion of profiles. As noted above, each user must be assigned to one and only one profile. You can assign as many users as you wish to any profile. You can restrict what a user can do with a profile, but profiles are not active unless RESOURCE LIMIT is set to TRUE in the init.ora file for the database. Profiles are created with the CREATE PROFILE command, and changed with the ALTER PROFILE command. They are deleted with the DROP PROFILE command.

A profile can be used to set seven settings for passwords. These are standard features including limit on FAILED_LOGIN_ATTEMPTS before the account is locked; number of days, or fraction of a day for PASSWORD_LOCK_TIME; number of days in a PASSWORD_LIFE_TIME, and number of days before an expired password is no longer honored (PASSWORD_GRACE_TIME).

A profile can also be used to set nine settings about system resources. Some useful ones are:

  • SESSIONS_PER_USER - limit to the number of concurrent sessions for a user
  • CONNECT_TIME - limit to the number of minutes a user session can last
  • IDLE_TIME - limit to the number of minutes a user session can be idle

The text continues with discussion about managing passwords. It looks like the kind of discussion that is meant to be a clue to certification questions. We are told that only two settings can be made to a password with the ALTER USER command: setting a password, and setting it to expire immediately. Other settings that affect a password are set through profiles. The last variation is that password complexity (requiring that a password be a certain length, composed of certain characters) in done with a profile, an SQL script, and a function (PASSWORD_VERIFY_FUNCTION).

The text lists four views that can be used to view data about profiles, passwords, and resources.

  • DBA_USERS - settings for all users, including password, user account, and profile data
  • DBA_TS_QUOTAS - storage information for users
  • RESOURCE_COST - settings for all values used in COMPOSITE_COST
  • DBA_PROFILES - settings for all profiles