Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
Use the ALTER
USER
statement:
You must have the ALTER
USER
system privilege. However, you can change your own password without this privilege.
alter_user::=
proxy_clause::=
The keywords, parameters, and clauses described in this section are unique to ALTER
USER
or have different semantics than they have in CREATE
USER
. Keywords, parameters, and clauses that do not appear here have the same meaning as in the CREATE
USER
statement.
Note: 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. |
See Also:
|
BY
password
to specify a new password for the user.
You can omit the REPLACE
clause if you are setting your own password for the first time or you have the ALTER
USER
system privilege and you are changing another user's password. However, unless you have the ALTER
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.
Note: Oracle does not check the old password, even if you provide it in the |
See Also:
Oracle9i Database Administrator's Guide for information on the password complexity verification function |
GLOBALLY
AS
'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 IDENTIFIED
GLOBALLY
AS
'external_name
' only if all external roles granted directly to the user are revoked.
You can change a user created as IDENTIFIED
GLOBALLY
AS
'external_name
' to IDENTIFIED
BY
password
or IDENTIFIED
EXTERNALLY
.
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 DEFAULT
ROLE
clause to enable:
Oracle enables default roles at logon without requiring the user to specify their passwords.
The 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.
Note: The |
See Also:
Oracle9i Database Concepts for more information on proxies and their use of the database and "Proxy Users: Examples" |
Specify 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 AUTHENTICATED
USING
clause.
WITH
ROLE
role_name
permits the proxy to connect as the specified user and to activate only the roles that are specified by role_name.
WITH
ROLE
ALL
EXCEPT
role_name
permits the proxy to connect as the specified user and to activate all roles associated with that user except those specified by role_name
.
WITH
NO
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.
Specify the AUTHENTICATED
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 GRANT
CONNECT
THROUGH
proxy
clause.
Specify 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.
Specify DISTINGUISHED
NAME
to allow the proxy to act as the globally identified user indicated by the distinguished name.
Specify CERTIFICATE
to allow the proxy to act as the globally identified user whose distinguished name is contained in the certificate.
In both the DISTINGUISHED
NAME
and 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 REVOKE
CONNECT
THROUGH
proxy
clause.
See Also:
|
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 example
:
ALTER USER sidney IDENTIFIED BY second_2nd_pwd DEFAULT TABLESPACE example;
The following statement assigns the new_profile
profile "Creating a Profile: Example") to the sample user sh
:
ALTER USER sh PROFILE new_profile;
In subsequent sessions, sh
is restricted by limits in the new_profile
profile.
The following statement makes all roles granted directly to sh
default roles, except the dw_manager
role:
ALTER USER sh DEFAULT ROLE ALL EXCEPT dw_manager;
At the beginning of sh
's next session, Oracle enables all roles granted directly to sh
except the dw_manager
role.
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 PASSWORD
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 sh
:
ALTER USER app_user1 GRANT CONNECT THROUGH sh WITH ROLE warehouse_user;
Note: To show basic syntax, this example uses the sample database Sales History user ( |
See Also:
|
The following statement takes away the right of user app_user
to connect through the proxy user sh
:
ALTER USER app_user1 REVOKE CONNECT THROUGH sh;
The following hypothetical examples show other methods of proxy authentication:
ALTER USER sully GRANT CONNECT THROUGH OAS1 AUTHENTICATED USING PASSWORD; ALTER USER green GRANT CONNECT THROUGH WebDB AUTHENTICATED USING DISTINGUISHED NAME; ALTER USER brown GRANT CONNECT THROUGH WebDB AUTHENTICATED USING CERTIFICATE TYPE 'X.509' VERSION '3';