6.1. Creating and Managing User Accounts

One of the most basic administrative requirements for a database is to identify the users. Each user who connects to your database should have an account. Shared accounts are difficult to troubleshoot and audit, and having them are a poor security practice.

You create a new database account with the CREATE USER statement. When you create a new account, at a minimum, you must assign a unique username and authentication method. You can optionally assign additional attributes to the user account with the CREATE USER statement. To change or assign new attributes to an existing user account, use the ALTER USER statement.

In the following sections, you will learn how to create a new account, how to assign and change the authentication mechanism, and how to define how this account will allocate and use certain database resources.

6.1.1. Configuring Authentication

When you connect to an Oracle database instance, your user account must be authenticated. Authentication involves validating the identity of the user and confirming that they have the authority to use the database. Oracle offers three authentication methods for your user accounts: password authentication (the most common), external authentication, and global authentication.

We will look at each of these authentication methods in the following sections.

6.1.1.1. Password Authenticated Users

When a user with password authentication attempts to connect to the database, the database verifies that the username is a valid database account and that the password supplied matches that user's password as stored in the database.

Password authenticated user accounts are the most common and are sometimes referred to as database authenticated accounts. With a password authenticated account, the database stores the encrypted password in the data dictionary. For example, to create a password authenticated user named rajesh with a password of welcome, you execute the following:

CREATE USER rajesh IDENTIFIED BY welcome;

The keywords IDENTIFIED BY password (in this case, password is welcome) tell the database that this user account is a password authenticated account.

6.1.1.2. Externally Authenticated Users

When an externally identified user attempts to connect to the database, the database verifies that the username is a valid database account and trusts that the operating system has performed authentication.

Externally authenticated user accounts do not store or validate a password in the database. These accounts are sometimes referred to as OPS$ (pronounced ahps dollar) accounts, because when Oracle introduced them in Oracle 6, the account had to be prefixed with the keyword OPS$.

With all releases of the database since then, including Oracle 10g, you can configure this OS_AUTHENT_PREFIX in the initialization or SPFILE file. For example, to create an externally authenticated user named oracle, using the default OS_AUTHENT_PREFIX, you execute the following:

CREATE USER ops$oracle IDENTIFIED EXTERNALLY;

The keywords IDENTIFIED EXTERNALLY tell the database that this user account is an externally authenticated account.

6.1.1.3. Globally Authenticated Users

When a globally identified user attempts to connect to the database, the database verifies that the username is valid and passes the connection information to the advanced security option for authentication. The advanced security option supports several mechanisms for authentication, including biometrics, X.509 certificates, Kerberos, and RADIUS.

Globally authenticated user accounts do not store or validate a password in the database as a password authenticated account does. These accounts rely on authentication provide by a service supported through the advanced security option.

The syntax for creating a globally authenticated account depends on the service called, but all use the keywords IDENTIFIED GLOBALLY, which tell the database to engage the advanced security option for authentication. Here is an example:

CREATE USER spy_master IDENTIFIED GLOBALLY AS 'CN=spy_master, OU=tier2,
    O=security, C=US';

6.1.2. Assigning a Default Tablespace

Every user is assigned a default tablespace. The default tablespace for a user is that tablespace where schema objects are stored when no TABLESPACE clause is given in statements that create tables or indexes. If you execute a CREATE TABLE statement and do not explicitly specify a tablespace, the database uses your default tablespace.

If you do not explicitly assign a default tablespace to a user at the time you create the user, the database assigns the database's default tablespace to the new user account. Use the keywords DEFAULT TABLESPACE tablespace_name to assign a default tablespace to either a new user via a CREATE USER statement or an existing user, like this:

CREATE USER rajesh IDENTIFIED BY welcome
DEFAULT TABLESPACE users;
Or via an ALTER USER statement:

ALTER USER rajesh
DEFAULT TABLESPACE users;

To change the database default tablespace (the value that users inherit if no default tablespace is provided), use the ALTER DATABASE statement, like this:

ALTER DATABASE DEFAULT TABLESPACE users;

6.1.3. Assigning a Temporary Tablespace

Every user is assigned a temporary tablespace in which the database stores temporary segments. Temporary segments are created during large sorting operations, such as ORDER BY, GROUP BY, SELECT DISTINCT, MERGE JOIN, or CREATE INDEX.

Temporary segments are also used when a temporary table is used. The database creates and drops temporary segments transparently to the user. Because of the transitory nature of temporary segments, you must use a dedicated tablespace of type TEMPORARY for your user's temporary tablespace setting.

If you do not explicitly assign a temporary tablespace at user creation time, the database assigns the database default temporary tablespace to the new user account. Use the keywords TEMPORARY TABLESPACE tablespace_name to assign a temporary tablespace either to a new user via the CREATE USER statement:

CREATE USER rajesh IDENTIFIED BY welcome
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

Or to an existing user via an ALTER USER statement:

ALTER USER rajesh
TEMPORARY TABLESPACE temp;

To change the database default temporary tablespace, use the ALTER DATABASE statement, like this:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

To avoid having to set the default and temporary tablespace for each user account that you create, change the database defaults with the ALTER DATABASE statement.

6.1.4. Assigning a Profile to a User

In addition to default and temporary tablespaces, every user is assigned a profile. A profile serves two purposes: first, it can limit the resource usage of some resources, and second, it can enforce password-management rules.

The default profile is appropriately named default. To explicitly assign a profile to a user, include the keywords PROFILE profile_name in the CREATE USER or ALTER USER statement. For example, to assign the profile named resource_profile to the new user jiang as well as to the existing user hamish, execute the following SQL:

CREATE USER jiang IDENTIFIED BY "kneehow.ma"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE resource_profile;

ALTER USER hamish
PROFILE resource_profile;

6.1.5. Removing a User from the Database

You use the DROP USER statement to remove a user from the database. You can optionally include the keyword CASCADE to tell the database to recursively drop all objects owned by that user.

To drop both user rajesh and all objects he owns, execute the following:

DROP USER rajesh CASCADE;

Dropping a user implicitly drops any object (but not role or system) privileges in which the user was the grantor. The data dictionary records both grantee and grantor for object privileges, but only the grantee is recorded for role and system privileges.