11.1. Creating User Accounts

To connect to the Oracle database, a user must have an Oracle database account, also known as a username. When you create the username, you can specify various other characteristics of the account, including a password, a profile, default tablespaces, and disk space quotas.

username

An Oracle database account identifier that, along with a password, allows a user to connect to the database.

The basic syntax to create a username is as follows:

CREATE USER user <other options>;

At a minimum, you should assign a password to the account. Passwords and the other user account options are discussed in the following sections.

11.1.1. Assigning Passwords

The password for the user account is typically assigned at the time the account is created and then changed after the user logs in for the first time. Janice, the DBA, creates an account for one of the new stocking managers with an initial password of DUCTTAPE6:

create user scrawford identified by ducttape6;

User created.

Passwords are not case sensitive; for example, DucTTape6 or ductTAPE6 would both be stored as DUCTTAPE6 in the database. To ensure that the password won't be easy to guess, it's important to use a mixture of letters, numbers, and punctuation characters in the password. The DBA can define additional rules for allowable passwords by the use of a special stored function owned by the SYS schema. For example, the DBA may require that certain sensitive accounts such as HR have a password that is longer than the password for any other accounts.

The DBA or user can use the ALTER USER command to change the password:

alter user scrawford identified by circuitt40;

User altered.

To change a password from an iSQL*Plus session, the user can use the Preferences link in the upper-right area of the browser. From within SQL*Plus, the user can change the password using the SQL*Plus PASSWORD command. The advantage to these last two methods is that the old and new passwords are not echoed to the screen:

SQL> password
Changing password for SCRAWFORD

Old password: *********
New password: ********
Retype new password: ********
Password changed
SQL>

11.1.2. Creating and Assigning Profiles

Each username in the database has a profile associated with it. A profile is a set of predefined resource parameters that can be used to monitor and control various database resources. The following are some examples of resources that can be controlled in a profile:

profile

A set of predefined resource parameters that can be used to monitor and control various database resources, such as CPU time and number of disk reads against the database.

• Concurrent connections to the database

• Maximum failed login attempts before the account is locked

• Elapsed time connected

• Continuous idle time connected

• CPU time used

• Disk reads performed

• How often a password needs to be changed

When an account is created, a profile can be specified; otherwise, Oracle assigns a default profile. Not surprisingly, this profile is called DEFAULT. The initial values of the DEFAULT profile allow for unlimited use of all resources.

At Scott's widget company, the users in the stocking department are notorious for leaving their sessions connected to the database and forgetting to log off when they are finished. This consumes valuable memory resources, so Janice, the DBA, decides to create a new profile in the database to make sure that users are disconnected from the database after 15 minutes of idle time:

create profile st_user limit
   idle_time 15;

Profile created.

In the new ST_USER profile just created, all resources are set to UNLIMITED except for the IDLE_TIME resource, which has been set to 15 minutes. The DBA modifies the recently created user to use the newly created profile:

alter user scrawford profile st_user;

User altered.

For SCRAWFORD's subsequent sessions, the session will be disconnected if the session remains idle for 15 minutes.

11.1.3. Assigning Default Tablespaces and Quotas

When a user creates some type of object—a table, an index, a sequence, or another object—that object uses space in one of the database's tablespaces. In addition, a user may need temporary space for sorting and other operations. Each user has a default tablespace for permanent objects and a default tablespace for temporary objects, although a user may explicitly create objects in a different tablespace if the user has the proper permissions.

If a default permanent tablespace is not specified when the user account is created, or a database-wide default permanent tablespace is not specified when the database is created, the SYSTEM tablespace is used. It is generally not a good idea to leave SYSTEM as the default tablespace. Since the SYSTEM tablespace contains all of the data dictionary objects, there is a high level of contention in the SYSTEM tablespace already, so any new user objects in the SYSTEM tablespace might have a negative impact on overall system performance.

Janice, the DBA, remedies this situation with the new user account and changes the default tablespace:

alter user scrawford default tablespace users;

User altered.

Janice double-checks her work by querying the DBA_USERS data dictionary view:

select username, default_tablespace,
   temporary_tablespace from dba_users
where username = 'sCRAWFORD';

USERNAME     DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
------------ -------------------- --------------------
SCRAWFORD    USERS                TEMP

1 row selected.

Janice makes a mental note to use the GUI-based Oracle Enterprise Manager (OEM) tool or the web-based EM Database Control interface next time. The OEM tool's Create User facility, shown below, is not only easier to use, but it also automatically specifies the USERS tablespace as the default tablespace for new users, among other defaults.

The web-based EM Database Control interface provides similar functionality when creating users.

quota

A numeric limit on the amount of disk space that a user can allocate within a tablespace. The quota can also be specified as UNLIMITED.

Although disk space gets cheaper every day, you may also want to limit how much disk space each user can allocate in each tablespace. The limit on the amount of disk space in a tablespace is called a quota. Even though each user-name is assigned a default tablespace when the username is created, the quota defaults to zero. Therefore, you must assign a quota to the user before that user can create objects in the tablespace.

Since the new user, SCRAWFORD, is expected to create tables for other people in the stocking department, Janice allocates 15MB of disk space in the USERS tablespace for SCRAWFORD:

alter user scrawford quota 15M on users;

User altered.

If Janice had specified UNLIMITED instead of 15M, SCRAWFORD would not have any limits on how much space she can use in the USERS tablespace for database objects.