10.2 Alter End User

Use the ALTER END USER command to modify the properties of an existing local end user.

You can use this command to update the password, schema association, account validity period, profile assignment, and account lock status.

A local end user can change their own password using the ALTER END USER command. When the user does not hold the ALTER END USER system privilege, they must provide the old password. A local end user can also use the PASSWORD command to change their password.

Required privilege

The ALTER END USER system privilege, except when a local end user is changing their own password.

Syntax

ALTER END USER [ IF EXISTS ] end_user
  [ IDENTIFIED BY password [ REPLACE old_password ] ]
  [ PROFILE profile ]
  [ PASSWORD EXPIRE ]
  [ ACCOUNT { LOCK | UNLOCK } ]
  [ { SCHEMA schema | NO SCHEMA } ]
  [ { START TIME timestamp | NO START TIME } ]
  [ { END TIME timestamp | NO END TIME } ];

Parameters

Parameter Description

end_user

The name of the end user to be altered.

password

The new password. If not specified, the current password remains unchanged.

old_password

The old password. Required when a local end user who does not hold the ALTER END USER privilege is changing their own password. Validated before the new password is set; failed attempts increment the login failure counter (ORA-28008).

profile

The new profile to assign. If not specified, the current profile is retained.

PASSWORD EXPIRE

A setting that expires the current password, forcing a reset at next login.

ACCOUNT LOCK

A setting that locks the account and disables access.

ACCOUNT UNLOCK

A setting that unlocks the account and enables access.

SCHEMA schema

A setting that associates the end user with the specified database schema. If not specified, the current association is retained.

NO SCHEMA

A setting that removes the current schema association.

START TIME

A setting that sets or replaces the account effective time stamp (TIMESTAMP WITH TIME ZONE format).

NO START TIME

A setting that removes the configured start time.

END TIME

A setting that sets or replaces the account expiry time stamp (TIMESTAMP WITH TIME ZONE format).

NO END TIME

A setting that removes the configured end time.

Usage notes and restrictions

  • When IF EXISTS is specified:
    • If the end user does not exist, the statement is a no-op. No error is raised.
    • If the end user exists, it is altered.
  • When IF EXISTS is omitted:
    • If the end user does not exist, an error is raised.
    • If the end user exists, it is altered.
  • An end user who holds the ALTER END USER privilege can change passwords for other end users without providing the old password. If an incorrect old password is provided, the change still succeeds.

For syntax diagrams and additional details, see ALTER END USER in Oracle AI Database SQL Language Reference.

Example 10-2 Unlock account

Unlock the account for end user Emma and associate her with the HR schema.

ALTER END USER emma ACCOUNT UNLOCK SCHEMA hr;