ALTER USER Statement

Quick Links:

Syntax

Syntax 1 – Change the definition of a database user

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

Syntax 2 – Refresh the Distinguished Name (DN) for an LDAP user

ALTER USER <user-name> 
   REFRESH DN

Syntax 3 – Revert a user's login policy to the original values

ALTER USER <user-name> 
   RESET LOGIN POLICY

Syntax 4 – Change a user's password when CHANGE_PASSWORD_DUAL_CONTROL is enabled in a user's login policy.

ALTER USER <user-name> 
   IDENTIFIED [ FIRST | LAST ] BY <password_part>

Parameters

(back to top)

user-name name of the user.

IDENTIFIED BY the password for the user. Clause is not supported (ERROR) when CHANGE_PASSWORD_DUAL_CONTROL option is enabled in a user's login policy

IDENTIFIED[ FIRST | LAST ] BY clause mandatory when CHANGE_PASSWORD_DUAL_CONTROL option is enabled in a target user's login policy. FIRST | LAST keyword specifies the part of the dual password part being defined.

policy-name name of the login policy to assign the user. No change is made if you do not specify a login policy. No change is made if the LOGIN POLICY clause is not specified.

FORCE PASSWORD CHANGE
controls whether the user must specify a new password upon logging in. This setting overrides the PASSWORD_EXPIRY_ON_NEXT_LOGIN option setting in the user's login policy.
Note This functionality is not currently implemented when logging in to SAP IQ Cockpit. However, when logging in to SAP IQ outside of SAP IQ Cockpit (for example, using Interactive SQL), users are then prompted to enter a new password.

RESET LOGIN POLICY
reverts the settings of the user's login to the original values in the login policy. This usually clears all locks that are implicitly set due to the user exceeding the failed logins or exceeding the maximum number of days since the last login. When you reset a login policy, a user can access an account that has been locked for exceeding a login policy option limit such as MAX_FAILED_LOGIN_ATTEMPTS or MAX_DAYS_SINCE_LOGIN.

REFRESH DN clears the saved DN and timestamp for a user, which is used during LDAP authentication.

Examples

(back to top)

Example 1
alters a user named SQLTester. The password is set to welcome. The SQLTester user is assigned to the Test1 login policy and the password does not expire on the next login:
```
ALTER USER SQLTester 
IDENTIFIED BY welcome
LOGIN POLICY Test1
FORCE PASSWORD CHANGE OFF
```
Example 2
clears the distinguished name (DN) and timestamp for a user named Mary used for LDAP authentication:
```
ALTER USER Mary REFRESH DN
```
Example 3
sets the password for user3 to PassPart1PassPart2. This assumes that user1 and user2 have the CHANGE PASSWORD system privilege and the change_password_dual_control option is enabled (ON) in the login policy for user3:
User1 enters:
```
ALTER USER user3 IDENTIFIED FIRST BY PassPart1
```
User2 enters:
```
ALTER USER user3 IDENTIFIED LAST BY PassPart2
```
Once set, user3 logs on by entering the password PassPart1PassPart2.

Usage

(back to top)

User IDs and passwords cannot:

Begin with white space, single quotes, or double quotes
End with white space
Contain semicolons

Passwords cannot exceed 255 characters.

If you set the PASSWORD_EXPIRY_ON_NEXT_LOGIN value to ON, the passwords of all users assigned to this login policy expire immediately when he or she next logs in. You can use the ALTER USER and LOGIN POLICY clauses to force users to change their passwords at the next login.

If the CHANGE_PASSWORD_DUAL CONTROL login policy option is disable (OFF) during the dual password change process:

the target user will be unable to log in with the single password part already defined. The ALTER USER command must be reissued using single password control syntax.
If the option is disabled after the dual password change process is complete, but before the target user logs in, there is no impact on the target user. The target user must log in using both password parts.

If the target user is already logged in when the dual password change process occurs, the user cannot change their password in the current session until both parts of the new password are set. Once the dual password change process is complete, the target user can use GRANT CONNECT, ALTER USER, sp_password, or sp_iqpassword to the password without first logging out. The prompt to enter the current password, use the new dual control password, not the password originally entered for the current session.

The GRANT CONNECT statement is not supported during for the dual password change process to set either password part. However, once the dual password change process is complete, the target user can use the GRANT CONNECT statement, ALTER USER, sp_password, or sp_iqpassword to change their password without first logging out.

As soon as both parts of the password are successfully specified by users with the CHANGE PASSWORD system privilege, the password for the target user is automatically expired. This forces the target user to change the password the next time he or she logs in.

The encryption algorithm used for hashing the user passwords is FIPS-certified encryption support:

The DLL is called dbfips10.dll
The HASH function accepts the algorithms: SHA1_FIPS SHA256_FIPS
If the -fips server option is specified and an algorithm that is not FIPS-certified is given to the HASH function, the database server uses SHA1_FIPS instead of SHA1, SHA256_FIPS instead of SHA256, and returns an error if MD5 is used (MD5 is not a FIPS-certified algorithm).
If the -fips option is specified, the database server uses SHA256_FIPS for password hashing.

Standards

(back to top)

SQL–Vendor extension to ISO/ANSI SQL grammar.
SAP Database products–Not supported by SAP ASE.

Permissions

(back to top)

To change own password – None required.
To change the password of any user – Requires the CHANGE PASSWORD system privilege.
To use the LOGIN POLICY, FORCE PASSWORD CHANGE, RESET LOGIN POLICY, or REFRESH DN clauses requires the MANAGE ANY USER system privilege.