ALTER END USER

Purpose

Use ALTER END USER to:

• Set or modify the database schema for name resolution purposes.

• Set or modify the effective dates.

• Set or modify the profile.

• Set password expired.

• Lock or unlock the account.

• Set or modify the password.

  An end user can change their own password, with the old_password parameter if the user has the ALTER END USER privilege. If the old password is incorrect, then the failed login count is incremented with each attempt, returning an invalid old password error. The new password is not set until the old password is correct.

  The PASSWORD command can also be used to change the end user’s own password.

Prerequisites

The ALTER END USER system privilege is required to alter an end user except when an end user changes their own password.

Syntax

Description of the illustration alter_end_user.gif

Semantics

• IF EXISTS: If you specify IF EXISTS, and an end user does not exist, no error is raised. If the end user exists, then it is altered.

  If you do not specify IF EXISTS, and an end user does not exist, an error is raised. If the end user exists, then it is altered.

• end_user: The name of the end user to be altered.

• password: The password for the end user (for direct database login).

factor: Specify the external authenticator for the end user. Valid values are duo_push and oma_push.

• external_name: Refers to the user in the external system.

• profile: The name of the profile. The profile limits the amount of database resources the end user can use. If this clause is omitted, then the assigned profile remains unchanged.

• PASSWORD EXPIRE: Expire the password of the end user. This setting forces the end user or administrator to change the password before the end user can log in.

• ACCOUNT LOCK: Lock the account of the end user and disable access.

• ACCOUNT UNLOCK: Unlock the account of the end user and enable access.

• schema: The database schema to be associated with the end user. This is optional. If it is not specified, the schema will remain unchanged.

• START TIME: The time from which the end user account becomes effective, in TIMESTAMP WITH TIME ZONE format. This will replace the old start date if an old start date exists. If it is not specified, the value will remain unchanged. If the start date needs to be removed, specify NO START TIME.

• END TIME: The time when the end user account becomes ineffective, in TIMESTAMP WITH TIME ZONE format. This will replace the old end date if an old end date exists. If it is not specified, the value will remain unchanged. If the end date needs to be removed, specify NO END TIME.

• An end user can change their own password with the ALTER END USER command under the following cases:

  • The end user is granted with the ALTER END USER privilege. In this case, the old password is not required; however, if the old password is specified, it will be validated, and an error will be raised if it is incorrect.

  • The end user is not granted with the ALTER END USER privilege. The end user must provide a correct old password (e.g., ALTER END USER u1 IDENTIFIED BY new_password REPLACE old_password).

  • Note that if an end user or database user with the ALTER END USER privilege is changing the password for other end users, the password change will succeed even if the provided old password is incorrect.

Example

The following SQL statement unlocks the account and sets the schema HR for end user emma.

ALTER END USER emma ACCOUNT UNLOCK SCHEMA HR;