Creating New Users

Before you actually create a user, you need to determine how you intend for that user to be authenticated in order to connect to the database. The primary means of authenticating to the database are operating system authentication and data dictionary authentication.

You can define a user such that the database is responsible for not only the accurate identification of users but also their authentication. This kind of authentication is called database authentication.

You could, alternatively, define users in such a way that authentication is performed by the operating system, also known as external authentication.

Database Authentication

Choosing database authentication for any given user means that administration for the user account, including all authentication of that user is performed entirely by Oracle. This means that you have to specify a password for a user whenever you create or alter that user. Users have the ability to change their passwords at any time, and those passwords are stored, in an encrypted format, in the data dictionary. Each password must be made up of single byte characters, even if the database uses a multibyte character set (see Chapter 7, "Globalization Support," which discusses globalizations and database character sets).

Oracle recommends that you always encode usernames and passwords using ASCII or EBCDIC characters (depending entirely on your platform). This allows usernames and passwords to function regardless of any database character set conversions that you do. This is important to remember because no username or password conversions are done during migration to a new database character set.

To facilitate enhanced security for database authentication, Oracle recommends that you judiciously manage passwords. This includes account locking, password aging, password expiration, password history, and password complexity verification.

The following statement creates a user that is database authenticated:

 CREATE USER awells IDENTIFIED BY author; 

This means that any time that user awells wants to connect to the database, he must specify the password author to connect.

There are several advantages to using database authentication:

• User accounts and all associated authentication are handled by the database. There is no need to rely on anything outside the database.

• Oracle provides highly secure and strong password management features that can enhance security when using database authentication that is not always available in operating system authentication.

• Database authentication is much easier to manage, especially when there is a small user community.

External Authentication

Whenever you choose external authentication for a user, that user's account is maintained by Oracle, but password administration and user authentication are performed by an external service, often outside the influence and control of the database administrator.

With external authentication, your database has to rely on the underlying operating system authentication as a means to restrict access to database accounts. No passwords are used as direct control for user login.

The following statement creates a user identified by Oracle and authenticated by the operating system:

 CREATE USER awells IDENTIFIED EXTERNALLY; 

In this case, the user, awells, has a database account authenticated by the operating system, and Oracle relies on the external login authentication to ensure that the user has access not only to the operating system but also to the database as a specific user.

Optionally, you can set the OS_AUTHENT_PREFIX parameter in the initialization file to specify the format of the prefix for usernames in the database. This parameter defaults to OPS$ to make Oracle 9i backward compatible with earlier releases. You can set the prefix to a NULL value as follows:

 OS_AUTHENT_PREFIX="" 

You can specify the prefix DWH for all data warehouse users as follows:

 OS_AUTHENT_PREFIX="DWH" 

Setting this parameter allows for added flexibility if desired. You can create a user who is authenticated either by password or by operating system if you specify the following command:

 CREATE USER ops$awells IDENTIFIED BY password; 

If the awells user were to log in to the database directly from the server running the database, she would not need to supply a password, but if that same user needed to connect from a remote client, she could do so by supplying the password.

By default, Oracle allows operating system authenticated logins only through secure connections. This means that, if you want to use operating system authentication, the user cannot connect to the database over Oracle Net or through a shared server configuration. This default restriction prevents users from being able to impersonate another operating system user over a network connection. You can set the initialization parameter REMOTE_OS_AUTHENT (which is by default set to FALSE) to trUE in the initialization parameter file and thereby allow the database to accept client operating system usernames received over a nonsecure connection to be used for account authentication.

External authentication offers its own advantages:

• Users can connect to Oracle faster because they don't have to specify a separate database username and password.

• All user entries in the database and operating system audit trails will correspond.

User Account Defaults

Many defaults can be associated with a user account. The following list provides many that are of particular interest:

• Tablespace quotas control the amount of physical storage allotted to a user in the tablespaces in the database. Any user may have different quotas on several different tablespaces in the database.

• Default tablespace defines the default location where segments will be created by the user. Segments will always be stored in this location unless the user explicitly specifies another tablespace at the segment creation time. It is important to remember that, if you don't specify a default tablespace for any user, the SYSTEM tablespace will become the default tablespace for that user.

• Temporary tablespace defines the place where the user can allocate temporary extents if the given user requires a location to write sort data to disk. If there is a default temporary tablespace in the database, the parameter will override that default.

• Account locking can be used to prevent a user from logging in to the database. This can either be set to occur automatically, or the database administrator can manually lock or unlock a user's account.

• Resource limits can be placed on the use of certain resources (such as CPU time, logical I/O, and the number of allowable open sessions per user).

• Direct privileges are used to maintain control over many of the actions that a user can perform.

• Role privileges can be further granted to indirectly control the actions of a user.

More information on roles and privileges can be found in Chapter 17, "Managing Privileges," and Chapter 18, "Managing Roles."

Recall that a database schema is a named collection of objects (tables, indexes, views, clusters, stored procedures, and packages). A user is created, and at the same time a corresponding schema is created. A user can exist and a schema can exist without the existence of objects reliant on either one. A user can be associated with exactly one schema, and username and schema are often used interchangeably.

When a user is created, you need to identify the tablespaces in which the user will need to store objects, decide on quotas for each of those tablespaces, and assign both a default tablespace and a temporary tablespace to the user. After you have made those decisions, create the user and grant that user the appropriate privileges and roles.

The following is an example of a CREATE USER script that sets most of the defaults for the user:

 CREATE USER awells IDENTIFIED BY password DEFAULT TABLESPACE userdata TEMPORARY TABLESPACE usertemp QUOTA 50M ON userdata PASSWORD EXPIRE; 

Generically, the command looks like the following:

 CREATE USER username IDENTIFIED {BY password |EXTERNALLY} [DEFAULT TABLESPACE tablespace_name] [TEMPORARY TABLESPACE temporary_tablespace_name] [QUOTA {integer K|M |UNLIMITED} ON tablespace_name QUOTA {integer K|M |UNLIMITED} ON tablespace_name QUOTA {integer K|M |UNLIMITED} ON tablespace_name ...] [PASSWORD EXPIRE] [ACCOUNT {LOCK | UNLOCK }] [PROFILE {profile | DEFAULT }] 

With the following descriptions:

• Username is the name of the new user.

• BY password signifies that the user will be authenticated by the database and will have to supply the given password to log in.

• EXTERNALLY signifies that the user is operating system authenticated.

• DEFAULT TABLESPACE specifies the user's default tablespace.

• TEMPORARY TABLESPACE specifies the user's temporary tablespace.

• QUOTA specifies the maximum allowed space for the user for objects in the specified tablespace and can either be specified in integer values of kilobytes or megabytes or can be set as UNLIMITED. By default, no user has any quota on any tablespace.

• PASSWORD EXPIRE forces the user to reset his password the first time that he attempts to log in to the database using SQL*Plus. This option is valid only for database authentication.

• ACCOUNT LOCK/UNLOCK specifies that the user's account is either locked or unlocked; UNLOCK is the default.

• PROFILE is often used to control resource usage and to specify the password control mechanism to be used for the given user.

The authentication method is mandatory.

If password expiration has been set, whenever the user logs in to the database with SQL*Plus, she receives the following message and is prompted to enter the new password:

 ERROR: ORA-28001: the account has expired Changing password for <user> Old password: New password: Retype new password: Password changed SQL> 

Now that you have created a user, we will look at how that user can be altered.