Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
ALTER TRIGGER to constraint_clause, 4 of 12
Use the ALTER
USER
statement:
Note:
ALTER
USER
syntax does not accept the old password. Therefore it neither authenticates using the old password nor checks the new password against the old before setting the new password. If these checks against the old password are important, use the OCIPasswordChange()
call instead of ALTER
USER
. For more information, see Oracle Call Interface Programmer's Guide.
You must have the ALTER
USER
system privilege. However, you can change your own password without this privilege.
alter_user::=
alter_user
proxy_clause
The keywords and parameters shown below are unique to ALTER
USER
or have different semantics than they have in CREATE
USER
. All the remaining keywords and parameters in the ALTER
USER
statement have the same meaning as in the CREATE
USER
statement.
See Also:
|
BY
password
to specify a new password for the user.
Specify 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 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 |
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, 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
, the default is 'X.509'.
version
, specify the version of the certificate that is to be presented. If you do not specify version
, the default is '3'.
Restriction: You cannot specify this clause as part of a REVOKE
CONNECT
THROUGH
proxy
clause.
See Also:
|
User Identification
Example
The following statement changes the demo database user oe
's password to lion
and default tablespace to the tablespace tbs_1
:
ALTER USER oe IDENTIFIED BY lion DEFAULT TABLESPACE tbs_1;
The following statement assigns the dw_manager
profile to the demo user sh
:
ALTER USER sh PROFILE dw_manager;
In subsequent sessions, sh
is restricted by limits in the dw_manager
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 demo user hr
's authentication mechanism:
ALTER USER hr IDENTIFIED GLOBALLY AS 'CN=tom,O=oracle,C=US';
The following statement causes user hr
's password to expire:
ALTER USER hr PASSWORD EXPIRE;
If you cause a database user's password to expire with PASSWORD
EXPIRE
, 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 dw_user
role when connected through the proxy sh
:
ALTER USER app_user GRANT CONNECT THROUGH sh WITH ROLE dw_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_user REVOKE CONNECT THROUGH sh;
The following examples show other methods of proxy authentication:
ALTER USER grant 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';
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|