|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-02
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
Oracle Corporation recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform. Please refer to Oracle9i Database Administrator's Guide for more information about this recommendation.
passwordto specify a new password for the user.
Oracle 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 Oracle may return an error message that the password cannot be reused. For this reason, Oracle Corporation 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 does not check the old password, even if you provide it in the
Oracle9i Database Administrator's Guide for information on the password complexity verification function
external_name' to indicate that the user must be authenticated by way of an LDAP V3 compliant directory service such as Oracle Internet Directory.
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
The tablespace you assign or reassign as the user's temporary tablespace must be a temporary tablespace and must have a standard block size.
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:
Oracle enables default roles at logon without requiring the user to specify their passwords.
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.
GRANT to allow the connection. Specify
REVOKE to prohibit the connection.
Identify the proxy connecting to Oracle. Oracle 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 by
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 activates all roles granted to the specified user automatically.
USING clause if you want proxy authentication to be handled by a source other than the proxy. This clause is relevant only as part of a
PASSWORD if you want the proxy to present the database password of the user for authentication. The proxy relies on the database to authenticate the user based on the password.
NAME to allow the proxy to act as the globally identified user indicated by the distinguished name.
CERTIFICATE to allow the proxy to act as the globally identified user whose distinguished name is contained in the certificate.
In both the
CERTIFICATE cases, the proxy has already authenticated and is acting on behalf of a global database user.
type, specify the type of certificate to be presented. If you do not specify
type, then the default is 'X.509'.
version, specify the version of the certificate that is to be presented. If you do not specify
version, then the default is '3'.
You cannot specify this clause as part of a
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
The following statement assigns the
new_profile profile "Creating a Profile: Example") to the sample user
In subsequent sessions,
sh is restricted by limits in the
The following statement makes all roles granted directly to
sh default roles, except the
At the beginning of
sh's next session, Oracle 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"
The following statement causes user
sidney's password to 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 alters the user
app_user. The example permits the
app_user to connect through the proxy user
sh. The example also allows
app_user to enable its
warehouse_user role (created in "Creating a Role: Example") when connected through the proxy
To show basic syntax, this example uses the sample database Sales History user (
The following statement takes away the right of user
app_user to connect through the proxy user
The following hypothetical examples show other methods of proxy authentication: