ALTER USER Statement

Changes user settings.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

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)

Examples

(back to top)

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 Sybase Database product–Not supported by Adaptive Server.

Permissions

(back to top)

Related reference
COMMENT Statement
CREATE LOGIN POLICY Statement
CREATE USER Statement
DROP LOGIN POLICY Statement
DROP USER Statement
ALTER LOGIN POLICY Statement
GRANT ROLE Statement
GRANT System Privilege Statement
REVOKE System Privilege Statement
REVOKE ROLE Statement