11.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, account lock status, and multifactor authentication (MFA) configuration.

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 } ]
  [ ADD FACTOR factor AS external_name ]
  [ UPDATE FACTOR factor AS external_name ]
  [ DROP FACTOR factor ];

Parameters

Parameter/Keyword	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.
ADD FACTOR	A setting that adds a second authentication factor to an existing local end user.
UPDATE FACTOR	A setting that updates the configured second authentication factor for an existing local end user.
DROP FACTOR	A setting that removes the configured second authentication factor for a local end user.
factor	The second authentication factor for a local end user through an external authenticator. Valid values are duo_push for Cisco Duo and oma_push for Oracle Mobile Authenticator.
external_name	The identifier of the end user in the external authenticator system, such as the user’s registered email address. Required when a factor is specified.

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.
• Before specifying a factor, configure the required MFA provider, SMTP settings, server wallet secrets, SQLNET.INBOUND_CONNECT_TIMEOUT value, and SMTP credential object. See Set Up Multifactor Authentication for Local End Users.
• The external_name value identifies the end user in the external authenticator system. Each local end user must have a unique external_name value.

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

Example 11-3 Unlock account

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

ALTER END USER emma ACCOUNT UNLOCK SCHEMA hr;

Example 11-4 Add a second authentication factor

Add an Oracle Mobile Authenticator push factor to an existing end user, Emma.

ALTER END USER emma
  ADD FACTOR 'oma_push' AS '[e-mail]';

Example 11-5 Update a second authentication factor

Update Emma's second authentication factor to Cisco Duo push factor.

ALTER END USER emma
  UPDATE FACTOR 'duo_push' AS '[e-mail]';

Example 11-6 Remove a second authentication factor

Remove the Cisco Duo push factor for Emma.

ALTER END USER emma
  DROP FACTOR 'duo_push';