Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-04 |
|
|
PDF · Mobi · ePub |
Use the ALTER
USER
statement:
To change the authentication or database resource characteristics of a database user
To permit a proxy server to connect as a client without authentication
In an Oracle Automatic Storage Management (Oracle ASM) cluster, to change the password of a user in the password file that is local to the Oracle ASM instance of the current node
See Also:
Oracle Database Security Guide for detailed information about user authentication methodsYou must have the ALTER
USER
system privilege. However, you can change your own password without this privilege.
You must be authenticated AS
SYSASM
to change the password of a user other than yourself in an Oracle ASM instance password file.
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 recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform.See Also:
CREATE USER for information on the keywords and parameters and CREATE PROFILE for information on assigning limits on database resources to a userBY password Specify BY
password
to specify a new password for the user. Passwords are case sensitive. Any subsequent CONNECT
string used to connect this user to the database must specify the password using the same case (upper, lower, or mixed) that is used in this ALTER
USER
statement. Passwords can contain single-byte, or multibyte characters, or both from your database character set.
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 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.
In an Oracle ASM cluster, you can use this clause to change the password of a user in the password file that is local to an Oracle ASM instance of the current node. You must be authenticated AS
SYSASM
to specify IDENTIFIED
BY
password
without the REPLACE
old_password
clause. If you are not authenticated AS
SYSASM
, then you can only change your own password by specifying REPLACE
old_password
.
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.
See Also:
Oracle Database Security Guide for information on the password complexity verification functionGLOBALLY Refer to CREATE USER for more information on this clause.
You can change a user's access verification method from IDENTIFIED
GLOBALLY
to either IDENTIFIED
BY
password
or IDENTIFIED
EXTERNALLY
. You can change a user's access verification method to IDENTIFIED
GLOBALLY
from one of the other methods only if all external roles granted explicitly to the user are revoked.
EXTERNALLY Refer to CREATE USER for more information on this clause.
See Also:
Oracle Database Enterprise User Security Administrator's Guide for more information on globally and externally identified users, "Changing User Identification: Example", and "Changing User Authentication: Examples"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.
Restriction on Default Tablespaces You cannot specify a locally managed temporary tablespace, including an undo tablespace, or a dictionary-managed temporary tablespace, as a user's default tablespace.
Use this clause to assign or reassign a tablespace or tablespace group for the user's temporary segments.
Specify tablespace
to indicate the user's temporary tablespace.
Specify tablespace_group_name
to indicate that the user can save temporary segments in any tablespace in the tablespace group specified by tablespace_group_name
.
Restriction on User Temporary Tablespace 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, or roles created by the user with the CREATE
ROLE
privilege. You cannot use the DEFAULT
ROLE
clause to specify:
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
Roles that are enabled by the SET
ROLE
statement, such as password-authenticated roles and secure application roles
See Also:
CREATE ROLEThis clause is not reversible. Specify ENABLE
EDITIONS
to allow the user to create multiple versions of editionable objects in this schema using editions. Editionable objects in non-editions-enabled schemas cannot be editioned.
If the schema to be editions-enabled contains any objects that are not editionable and that depend on editionable type objects in the schema, then you must specify FORCE
to enable editions for this schema. In this case, all the objects that are not editionable and that depend on the editionable type objects in the schema being editions-enabled become invalid.
The proxy_clause
lets you control the ability of an enterprise user (a user outside the database) or a database proxy (another database user) to connect as the database user being altered.
The ENTERPRISE
USER
clause lets you expose user
to proxy use by enterprise users. The administrator working in Oracle Internet Directory must then grant privileges for appropriate enterprise users to act on behalf of user
.
The db_user_proxy
clause lets you expose user
to proxy use by database user db_user_proxy
. When specified with the GRANT
clause, you can additionally specify db_user_proxy_clauses
to activate all, some, or none of the roles of user
, and specify whether authentication is required. For information on proxy authentication of application users, see Oracle Database Advanced Application Developer's Guide.
See Also:
Oracle Database Security Guide 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 Database. Oracle Database expects the proxy to authenticate the user unless you specify the AUTHENTICATION
REQUIRED
clause.
Specify db_user_proxy_clauses
to activate all, some, or none of the roles of user, and specify whether authentication is required.
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 for 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 Database activates all roles granted to the specified user automatically.
AUTHENTICATION REQUIRED Clause
Specify AUTHENTICATION
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.
The AUTHENTICATED
USING
PASSWORD
clause has been deprecated for this release. If you use this clause, Oracle Database converts it to the AUTHENTICATION
REQUIRED
clause. If you do not specify the AUTHENTICATION
REQUIRED
clause, then Oracle Database uses either the AUTHENTICATED
USING
CERTIFICATE
clause or the AUTHENTICATED
USING
DISTINGUISHED
NAME
clause.
See Also:
Oracle Security Overview for an overview of database security and for information on middle-tier systems and proxy authenticationChanging User Identification: Example 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 (created in "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 Database enables all roles granted directly to sh
except the dw_manager
role.
Changing User Authentication: Examples 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.
Assigning a Tablespace Group: Example The following statement assigns tbs_grp_01
(created in "Adding a Temporary Tablespace to a Tablespace Group: Example") as the tablespace group for user sh
:
ALTER USER sh TEMPORARY TABLESPACE tbs_grp_01;
Proxy Users: Examples 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 sh
:
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, refer to Oracle Call Interface Programmer's Guide.
See Also:
"Creating External Database Users: Examples" to see how to create the app_user
user
"Creating a Role: Example" to see how to create the dw_user
role
The following statement takes away the right of user app_user1
to connect through the proxy user sh
:
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;
The following example exposes the user app_user1
to proxy use by enterprise users. The enterprise users cannot act on behalf of app_user1
until the Oracle Internet Directory administrator has granted them appropriate privileges:
ALTER USER app_user1 GRANT CONNECT THROUGH ENTERPRISE USERS;