ALTER USER

Purpose

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.

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.

Prerequisites

You must have the ALTER USER system privilege. However, you can change your own password without this privilege.

Syntax

proxy_clause::=

Keywords and Parameters

The keywords and parameters shown below are unique to ALTER USER or have different functionality 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: CREATE USER for information on the keywords and parameters CREATE PROFILE for information on assigning limits on database resources to a user

IDENTIFIED

BY password	Specify a password for the user.
	Note: 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), 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.
GLOBALLY AS	Specify '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. See Also: CREATE USER

DEFAULT ROLE

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:

• 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 enables default roles at logon without requiring the user to specify their passwords.

See Also: CREATE ROLE

proxy_clause

The proxy_clause lets you control the ability of a proxy (an application or application server) to connect as the specified user and to activate all, some, or none of the user's roles.

See Also: Oracle8i Concepts for more information on proxies and their use of the database

GRANT	Specify GRANT to allow the connection.
REVOKE	Specify REVOKE to prohibit the connection.
proxy	Identify the proxy connecting to Oracle.
WITH Clause	Specify the roles that the application is permitted to activate after it connects as the user. If you do not include this clause, Oracle activates all roles granted to the specified user automatically.
	ROLE role_name permits the proxy to connect as the specified user and to activate only the roles that are specified by role_name. 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. NONE permits the proxy to connect as the specified user, but prohibits the proxy from activating any of that user's roles after connecting.

Examples

ALTER USER Examples

The following statement changes the user scott's password to lion and default tablespace to the tablespace tstest:

ALTER USER scott 
    IDENTIFIED BY lion
    DEFAULT TABLESPACE tstest; 

The following statement assigns the clerk profile to scott:

ALTER USER scott 
    PROFILE clerk; 

In subsequent sessions, scott restricted by limits in the clerk profile.

The following statement makes all roles granted directly to scott default roles, except the agent role:

ALTER USER scott 
    DEFAULT ROLE ALL EXCEPT agent; 

At the beginning of scott's next session, Oracle enables all roles granted directly to scott except the agent role.

User Authentication Examples

The following statement changes user tom's authentication mechanism:

ALTER USER tom IDENTIFIED GLOBALLY AS 'CN=tom,O=oracle,C=US';

The following statement causes user fred's password to expire:

ALTER USER fred 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 you to change the password on the first attempted login following the expiration.

Proxy User Examples

The following statement permits the proxy user APPSERVER1 to connect as the user JANE. It also allows APPSERVER1 to activate the role INVENTORY:

ALTER USER jane GRANT CONNECT THROUGH appserver1 WITH ROLE 
inventory;

The following statement takes away the right of proxy user appserver1to connect as the user jane:

ALTER USER jane REVOKE CONNECT THROUGH appserver1;