Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Oracle Users

Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid username defined in the database. This section explains how to manage users for a database, and includes the following topics:

Creating Users

To create a database user, you must have the CREATE USER system privilege. When creating a new user, tablespace quotas can be specified for any tablespace in the database, even if the creator does not have a quota on a specified tablespace. Due to such privileged power, a security administrator is normally the only type of user that has the CREATE USER system privilege.

You create a user with either the Create User property sheet of Server Manager/GUI, or the SQL command CREATE USER. Using either option, you can also specify the new user's default and temporary segment tablespaces, tablespace quotas, and profile.

The following statement creates a new user named JWARD, identified externally:

CREATE USER OPS$jward
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE data_ts
   TEMPORARY TABLESPACE temp_ts
   QUOTA 100M ON test_ts
   QUOTA 500K ON data_ts
   PROFILE clerk;

See Also: A newly-created user cannot connect to the database until granted the CREATE SESSION system privilege; see [*].

Specifying a Name

Within each database, a username must be unique with respect to other usernames and roles; a user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have unique names.

Usernames in Multi-Byte Character Sets In a database that uses a multi-byte character set, each username should contain at least one single-byte character. If a username contains only multi-byte characters, the encrypted username/password combination is considerably less secure.

Setting a User's Authentication

In the previous CREATE USER statement, the new user is to be authenticated using the operating system. The username includes the default prefix "OPS$." If the OS_AUTHENT_PREFIX parameter is set differently (that is, if it specifies either no prefix or some other prefix), modify the username accordingly, by omitting the prefix or substituting the correct prefix.

Alternatively, you can create a user who is authenticated using the database and a password:

CREATE USER jward
   IDENTIFIED BY airplane
   . . . ;

In this case, the connecting user must supply the correct password to the database to connect successfully.

User Passwords in Multi-Byte Character Sets In a database that uses a multi-byte character set, passwords must include only single-byte characters. Multi-byte characters are not accepted in passwords. See Also: For more information about valid passwords, see the Oracle7 Server SQL Reference.

Assigning a Default Tablespace

Each user has a default tablespace. When a user creates a schema object and specifies no tablespace to contain it, Oracle stores the object in the user's default tablespace.

The default setting for every user's default tablespace is the SYSTEM tablespace. If a user does not create objects, this default setting is fine. However, if a user creates any type of object, consider specifically setting the user's default tablespace. You can set a user's default tablespace during user creation, and change it later. Changing the user's default tablespace affects only objects created after the setting is changed.

Consider the following issues when deciding which tablespace to specify:

In the previous CREATE USER statement, JWARD's default tablespace is DATA_TS.

Assigning a Temporary Tablespace

Each user also has a temporary tablespace. When a user executes a SQL statement that requires a temporary segment, Oracle stores the segment in the user's temporary tablespace.

If a user's temporary tablespace is not explicitly set, the default is the SYSTEM tablespace. However, setting each user's temporary tablespace reduces file contention among temporary segments and other types of segments. You can set a user's temporary tablespace at user creation, and change it later.

In the previous CREATE USER statement, JWARD's temporary tablespace is TEMP_TS, a tablespace created explicitly to only contain temporary segments.

Assigning Tablespace Quotas

You can assign each user a tablespace quota for any tablespace. Assigning a quota does two things:

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they will create objects.

You can assign a user either individual quotas for a specific amount of disk space in each tablespace, or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from consuming too much space in the database.

You can assign a user's tablespace quotas when you create the user, or add or change quotas later. If a new quota is less than the old one, then the following conditions hold true:

Revoking Tablespace AccessYou can revoke a user's tablespace access by changing the user's current quota to zero. After a quota of zero is assigned, the user's objects in the revoked tablespace remain, but the objects cannot be allocated any new space. UNLIMITED TABLESPACE System PrivilegeTo permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, explicit quotas again take effect. You can grant this privilege only to users, not to roles. Before granting the UNLIMITED TABLESPACE system privilege, consider the consequences of doing so:

Advantage

Disadvantages

Setting Default Roles

You cannot set a user's default roles in the CREATE USER statement. When you first create a user, the user's default role setting is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER command to change the user's default roles.

Warning: When you create a role, it is granted to you implicitly and added as a default role. You will get an error at login if you have more than MAX_ENABLED_ROLES. You can avoid this error by altering the user's default roles to be less than MAX_ENABLED_ROLES. Thus, you should change the DEFAULT ROLE settings of SYS and SYSTEM before creating user roles.

Altering Users

Users can change their own passwords. However, to change any other option of a user's security domain, you must have the ALTER USER system privilege. Security administrators are normally the only users that have this system privilege, as it allows a modification of any user's security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.

You can alter a user's security settings with either the Alter User property sheet of Server Manager/GUI, or the SQL command ALTER USER. Changing a user's security settings affects the user's future sessions, not current sessions.

The following statement alters the security settings for user AVYRROS:

ALTER USER avyrros
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE data_ts
   TEMPORARY TABLESPACE temp_ts
   QUOTA 100M ON data_ts
   QUOTA 0 ON test_ts
   PROFILE clerk;

The ALTER USER statement here changes AVYRROS's security settings as follows:

Changing a User's Password, for Non-DBAs

While most non-DBA users do not use Server Manager, they can still change their own passwords with the ALTER USER command, as follows:

ALTER USER andy
   IDENTIFIED BY swordfish;

Users can change their own passwords this way, without any special privileges (other than those to connect to the database). Users should be encouraged to change their passwords frequently.

A user must have the ALTER USER privilege to change between Oracle authorization and operating system authorization; usually only DBAs should have this privilege.

Passwords in Multi-Byte Character Sets In a database that uses a multi-byte character set, passwords must include only single-byte characters. Multi-byte characters are not accepted in passwords. See Also: For more information about valid passwords, see the Oracle7 Server SQL Reference.

Changing a User's Default Roles

A default role is one that is automatically enabled for a user when the user creates a session. You can assign a user zero or more default roles. Any role directly granted to a user can potentially be a default role of the user; you cannot specify an indirectly granted role when listing default roles in an ALTER USER DEFAULT ROLE command. However, if the role that the indirectly granted role is granted to is a default role, then all indirectly granted roles of that role are enabled by default. The number of default roles for a user should not exceed the maximum number of enabled roles that are allowed per user; if it does, when the user tries to connect, errors are returned and the connection is not allowed.

Note: Oracle automatically enables a user's default roles when the user creates a session. Placing a role in a user's list of default roles bypasses authentication for the role, whether the role is defined to be authorized using a password or the operating system.

If you specify a list of roles, all other roles granted to that user are removed from the user's default role list.

Suppose user AVYRROS has been granted the roles DEVELOPER and CLERK, and CLERK is his only default role. The following statement removes CLERK from his default role list and adds DEVELOPER:

ALTER USER avyrros
   DEFAULT ROLE DEVELOPER;

In this case, any roles subsequently granted to AVYRROS will not be default roles, and will be disabled on connection.

If you specify ALL for the user's list of default roles, every role granted directly to the user is automatically added to the user's list of default roles. Subsequent modification of a user's default role list can remove newly granted roles from a user's list of default roles. The following example causes all roles currently granted to AVYRROS to be added to his list of default roles, as well as all roles granted in the future:

ALTER USER avyrros
   DEFAULT ROLE ALL;

Furthermore, you can specify ALL EXCEPT with a list of roles, and those roles will be the only roles granted to the user not on the default role list. For example, the following statement adds all roles currently granted to AVYRROS (except the role PAYROLL) to the user's default role list. Any roles granted to AVYRROS in the future are also added to the default role list:

ALTER USER avyrros
   DEFAULT ROLE ALL EXCEPT payroll;

To ensure a user has no default roles, specify NONE for the user's list of default roles:

ALTER USER avyrros
   DEFAULT ROLE NONE;

Changing a user's default role list affects subsequent sessions; it does not affect any session in progress at the time.

Revoking a role from a user automatically removes the role from the user's default role list.

Dropping Users

When a user is dropped, the user and associated schema is removed from the data dictionary and all schema objects contained in the user's schema, if any, are immediately dropped.

Note: If a user's schema and associated objects must remain but the user must be revoked access to the database, revoke the CREATE SESSION privilege from the user.

A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user's sessions using either Server Manager/GUI, or the SQL command ALTER SYSTEM with the KILL SESSION clause.

To drop a user and all the user's schema objects (if any), you must have the DROP USER system privilege. Because the DROP USER system privilege is so powerful, a security administrator is typically the only type of user that has this privilege.

You can drop a user from a database using either the Drop menu item of Server Manager/GUI, or the SQL command DROP USER.

If the user's schema contains any schema objects, use the CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify CASCADE and the user's schema contains objects, an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them before the user is dropped. Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, check whether any views or procedures depend on that particular table.

The following statement drops the user JONES, all objects in JONES' schema, and any dependent foreign keys:

DROP USER jones CASCADE;

See Also: For more information about terminating sessions, see "Terminating Sessions" [*].

Contents Index Home Previous Next