Oracle7 Server SQL Reference

ALTER USER

Purpose

To change any of the following characteristics of a database user:

password

default tablespace for object creation

tablespace for temporary segments created for the user

tablespace access and tablespace quotas

limits on database resources

default roles

Prerequisites

You must have ALTER USER privilege. However, you can change your own password without this privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the user's creation label or you must satisfy one of the following criteria:

If the user's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

If the user's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the user's creation label and your DBMS label are not, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

You can only change a user's default roles if your DBMS label matches the creation label of the user. Your DBMS label must also dominate the role's creation label or you must have READUP system privilege.

You can only establish a default or temporary tablespace if both your DBMS label and the user's creation label dominates the tablespace's creation label or if both you and the user have READUP system privilege.

You can only change a user's profile if both your DBMS label and the user's creation label dominate the profile's creation label or if both you and the user have READUP system privilege.

Syntax

Keywords and Parameters

user

is the user to be altered.

IDENTIFIED

indicates how Oracle7 permits user access.

BY specifies a new password for the user. The password is not usually quoted and must also follow the rules described in the section "Object Naming Rules" . A password can only contain single-byte characters from your database character set regardless of whether your character set also contains multi-byte characters.

EXTERNALLY indicates that Oracle7 verifies user access with the operating system, rather than with a password. See the CREATE USER command .

Although you do not need privileges to change your own password, you must have ALTER USER system privilege to change from BY password to EXTERNALLY or vice versa.

DEFAULT TABLESPACE

specifies the default tablespace for object creation.

TEMPORARY TABLESPACE

specifies the tablespace for the creation of temporary segments for operations such as sorting that require more space than is available in memory.

QUOTA

establishes a space quota of integer bytes on the tablespace for the user. This quota is the maximum space in tablespace that can be allocated for objects in the user's schema. You can use K or M to specify the quota in kilobytes or megabytes. You need not have quota on the tablespace to establish a quota on the tablespace for another user. See the CREATE USER command .

If you reduce an existing quota to a value below the space allocated for existing objects in the user's schema in the tablespace, no more space in the tablespace can be allocated to objects in the schema.

Note that an ALTER USER statement can contain multiple QUOTA clauses for multiple tablespaces.

UNLIMITED places no limit on the space in the tablespace allocated to objects in the user's schema.

PROFILE

changes the user's profile to profile. In subsequent sessions, the user is subject to the limits defined in the new profile.

To assign the default limits to the user, assign the user the DEFAULT profile .

DEFAULT ROLE

establishes default roles for the user. Oracle7 enables the user's default roles at logon. By default, all roles granted to the user are default roles.

ALL makes all the roles granted to the user default roles, except those listed in the EXCEPT clause.

NONE makes none of the roles granted to the user default roles.