ALTER USER statement

Alters user settings.

Syntax

Change the definition of a database user

ALTER USER user-name 
 [ IDENTIFIED BY password ]
 [ LOGIN POLICY policy-name ]
 [ FORCE PASSWORD CHANGE { ON | OFF } ]

Unlock a database user

ALTER USER user-name
[ RESET LOGIN POLICY ]

Refresh the Distinguished Name (DN) for an LDAP user
```
ALTER USER user-name
REFRESH DN 
```

Change a password part

ALTER USER user-name 
 [ IDENTIFIED { FIRST | LAST } BY password-part ]

Parameters

user-name
The name of the user.
IDENTIFIED clause
The password of the user. A user without a password cannot connect to the database. This is useful if you are creating a group and do not want anyone to connect to the database using the group user ID.
- IDENTIFIED BY clause
  Use this clause to reset the password for a user. To reset a user to not have a password, set password to NULL.
- IDENTIFIED { FIRST | LAST } BY clause
  Use this clause to reset part of the password for a user who has a dual control password. A user with dual control password has the CHANGE_PASSWORD_DUAL_CONTROL login policy option enabled for their login policy.
  
  Two administrators are required to reset a dual control password. One administrator executes the IDENTIFIED FIRST BY clause to set the first part of the password and another administer executes the IDENTIFIED LAST BY clause to set the last part of the password. The user combines the two password parts and uses this combined password to connect to the database.
policy-name
The name of the login policy to assign the user. No change is made if the LOGIN POLICY clause is not specified.
FORCE PASSWORD CHANGE clause
Controls whether the user must specify a new password when they log in. This setting overrides the password_expiry_on_next_login option setting in the user's policy.
RESET LOGIN POLICY clause
Resets the number of failed login attempts, as well as the user's last login time and last failed login time. If a user account was locked for exceeding login policy limits, it is unlocked.
REFRESH DN clause
REFRESH DN clears the Distinguished Name (DN) and timestamp of the user so that at the time of the next LDAP authentication, the search for the DN is done. If the authentication succeeds during the next LDAP authentication of this user then both the DN and the timestamp are updated with the new DN and current time.

Remarks

The following list describes the requirements for user IDs and passwords. The requirements and restrictions for a password part are the same as those described for a password except that the maximum length of each part is 127 bytes.

The verify_password_function login policy option can be used to specify a function to implement password rules (for example, passwords must include at least one digit). If a password verification function is used, you cannot specify more than one user ID and password in the GRANT CONNECT statement.

If you set the password_expiry_on_next_login value to ON, the user's password expires immediately when they next login even if they are assigned to the same policy. You can use the ALTER USER and LOGIN POLICY clauses to force a user to change their password when they next login.

The ALTER USER...REFRESH DN syntax clears the Distinguished Name (DN) and timestamp of a user so that during the next LDAP authentication, a search for the DN is performed, instead of using the cached DN, which can become out of date. If the authentication succeeds, then both the DN and the timestamp are updated with the new DN and current time.

If you use this statement in a procedure, do not specify the password (IDENTIFIED BY clause) as a string literal because the definition of the procedure is visible in the SYSPROCEDURE system view. For security purposes, specify the password using a variable that is declared outside of the procedure definition.

Privileges

Any user can change their own password.

To change passwords for other users, you must have the CHANGE PASSWORD system privilege.

For all other changes to other users, including forcing users to change their password, you must have the MANAGE ANY USER system privilege.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following statement alters a user named SQLTester, setting their password to welcome123, setting their login policy to Test1, and allowing them to bypass a forced password change.

ALTER USER SQLTester IDENTIFIED BY welcome123
LOGIN POLICY Test1
FORCE PASSWORD CHANGE off;

The following example refreshes the LDAP Distinguished Name for user myusername.

ALTER USER myusername REFRESH DN;