|Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
To change the authentication or database resource characteristics of a database user
To permit a proxy server to connect as a client without authentication
See Also:Oracle Database Security Guide for detailed information about user authentication methods
You must have the
USER system privilege. However, you can change your own password without this privilege.
The keywords, parameters, and clauses described in this section are unique to
USER or have different semantics than they have in
USER. Keywords, parameters, and clauses that do not appear here have the same meaning as in the
Note:Oracle Corporation recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform. Please refer to Oracle Database Administrator's Guide for more information about this recommendation.
password to specify a new password for the user.
Note:Oracle Database expects a different timestamp for each resetting of a particular password. If you reset one password multiple times within one second (for example, by cycling through a set of passwords using a script), then the database may return an error message that the password cannot be reused. For this reason, Oracle recommends that you avoid using scripts to reset passwords.
You can omit the
REPLACE clause if you are setting your own password for the first time or you have the
USER system privilege and you are changing another user's password. However, unless you have the
USER system privilege, you must always specify the
REPLACE clause if a password complexity verification function has been enabled, either by running the
UTLPWDMG.SQL script or by specifying such a function in the
PASSWORD_VERIFY_FUNCTION parameter of a profile that has been assigned to the user.
Oracle Database does not check the old password, even if you provide it in the
REPLACE clause, unless you are changing your own existing password. If such a check is important in other cases (for example, when a privileged user changes another user's password), then ensure that the password complexity verification function prohibits password changes in which the old password is null, or use the
OCIPasswordChange() call instead of
USER. For more information, see Oracle Call Interface Programmer's Guide.
See Also:Oracle Database Administrator's Guide for information on the password complexity verification function
You can change a user's access verification method to
external_name' only if all external roles granted directly to the user are revoked.
You can change a user created as
Use this clause to assign or reassign a tablespace for the user's permanent segments. This clause overrides any default tablespace that has been specified for the database.
Use this clause to assign or reassign a tablespace or tablespace group for the user's temporary segments.
tablespace to indicate the user's temporary tablespace.
tablespace_group_name to indicate that the user can save temporary segments in any tablespace in the tablespace group specified by
Any individual tablespace you assign or reassign as the user's temporary tablespace must be a temporary tablespace and must have a standard block size.
See Also:"Assigning a Tablespace Group: Example"
Specify the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a
GRANT statement. You cannot use the
ROLE clause to enable:
Roles not granted to the user
Roles granted through other roles
Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory
Oracle Database enables default roles at logon without requiring the user to specify their passwords or otherwise be authenticated. If you have granted an application role to the user, you should use the
role clause to ensure that, in subsequent logons by the user, the role will not be enabled except by applications using the authorized package.
See Also:CREATE ROLE
proxy_clause lets you control the ability of a proxy (an application or application server) to connect as the specified database or enterprise user and to activate all, some, or none of the user's roles.
proxy_clause provides several varieties of proxy authentication of database and enterprise users. For information on proxy authentication of application users, see Oracle Database Application Developer's Guide - Fundamentals.
GRANT to allow the connection. Specify
REVOKE to prohibit the connection.
Identify the proxy connecting to Oracle Database. Oracle Database expects the proxy to authenticate the user unless you specify the
role_name permits the proxy to connect as the specified user and to activate only the roles that are specified by
role_name permits the proxy to connect as the specified user and to activate all roles associated with that user except those specified for
ROLES permits the proxy to connect as the specified user, but prohibits the proxy from activating any of that user's roles after connecting.
If you do not specify any of these
WITH clauses, then Oracle Database activates all roles granted to the specified user automatically.
Use this clause to indicate how you want the proxy authenticated. This clause is valid only as part of the
proxy clause (not
REQUIRED to ensure that authentication credentials for the user must be presented when the user is authenticated through the specified proxy. The credential is a password.
This clause has been deprecated and is ignored if you use it in your code. Oracle recommends that you specify the proxy clause either with or without the
The following statement changes the password of the user
sidney (created in "Creating a Database User: Example")
second_2nd_pwd and default tablespace to the tablespace
ALTER USER sidney IDENTIFIED BY second_2nd_pwd DEFAULT TABLESPACE example;
The following statement assigns the
new_profile profile (created in "Creating a Profile: Example") to the sample user
ALTER USER sh PROFILE new_profile;
In subsequent sessions,
sh is restricted by limits in the
The following statement makes all roles granted directly to
sh default roles, except the
ALTER USER sh DEFAULT ROLE ALL EXCEPT dw_manager;
At the beginning of
sh's next session, Oracle Database enables all roles granted directly to
sh except the
The following statement changes the authentication mechanism of user
app_user1 (created in "Creating a Database User: Example"
ALTER USER app_user1 IDENTIFIED GLOBALLY AS 'CN=tom,O=oracle,C=US';
The following statement causes user
sidney's password to expire:
ALTER USER sidney PASSWORD EXPIRE;
If you cause a database user's password to expire with
EXPIRE, then the user (or the DBA) must change the password before attempting to log in to the database following the expiration. However, tools such as SQL*Plus allow the user to change the password on the first attempted login following the expiration.
The following statement assigns
tbs_grp_01 (created in "Adding a Temporary Tablespace to a Tablespace Group: Example") as the tablespace group for user
ALTER USER sh TEMPORARY TABLESPACE tbs_grp_01;
The following statement alters the user
app_user1. The example permits the
app_user1 to connect through the proxy user
sh. The example also allows
app_user1 to enable its
warehouse_user role (created in "Creating a Role: Example") when connected through the proxy
ALTER USER app_user1 GRANT CONNECT THROUGH sh WITH ROLE warehouse_user;
To show basic syntax, this example uses the sample database Sales History user (
sh) as the proxy. Normally a proxy user would be an application server or middle-tier entity. For information on creating the interface between an application user and a database by way of an application server, please refer to Oracle Call Interface Programmer's Guide.
The following statement takes away the right of user
app_user1 to connect through the proxy user
ALTER USER app_user1 REVOKE CONNECT THROUGH sh;
The following hypothetical examples shows another method of proxy authentication:
ALTER USER sully GRANT CONNECT THROUGH OAS1 AUTHENTICATED USING PASSWORD;