ALTER USER (SQL)

Changes a user’s password.

Synopsis

ALTER USER user-name IDENTIFY BY password
ALTER USER user-name IDENTIFIED BY password
ALTER USER user-name [ WITH ] PASSWORD password

Description

The ALTER USER command allows you to change a user's password. A user can change user passwords in the following situations:

A user can always change their own password
A user can change the password of a user that they created if they have the %Admin_UserEdit administrative resource with USE permission
A user can change any user’s password if they have the %Admin_Secure administrative resource with USE permission or full security privileges on the system

The IDENTIFY BY, IDENTIFIED BY, and WITH PASSWORD keywords are synonyms.

The user-name must be an existing user. Specifying a non-existent user generates an SQLCODE -400 error with a %msg such as the following: ERROR #838: User badname does not exist. You can determine if a user exists by invoking the $SYSTEM.SQL.Security.UserExists()Opens in a new tab method.

A user-name specified as a delimited identifier can be an SQL reserved word and can contain a comma (,), period (.), caret (^), and the two-character arrow sequence (->). It may begin with any valid character except the asterisk (*).

A password can be a string literal, a numeric, or an identifier. A string literal must be enclosed in quotes, and can contain any combination of characters, including blank spaces. A numeric or an identifier does not have to be enclosed in quotes. A numeric must consist of only the characters 0 through 9. An identifier must start with a letter (uppercase or lowercase) or a % (percent symbol); this can be followed by any combination of letters, numbers, or any of the following symbols: _ (underscore), & (ampersand), $ (dollar sign), or @ (at sign).

ALTER USER does not issue an error code if the new password is identical to the existing password. It sets SQLCODE = 0 (Successful Completion).

You can also change a user password using the $SYSTEM.Security.ChangePassword()Opens in a new tab method:

$SYSTEM.Security.ChangePassword(args)

Privileges

The ALTER USER command is a privileged operation. Prior to using ALTER USER in embedded SQL, you must be logged in as a user with either the %Admin_Secure administrative resource with USE permission, or the %Admin_UserEdit administrative resource with USE permission, or full security privileges on the system. Failing to do so results in an SQLCODE -99 error (Privilege Violation). Use the $SYSTEM.Security.Login()Opens in a new tab method to assign a user with appropriate privileges:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, see %SYSTEM.SecurityOpens in a new tab.

Arguments

user-name

The name of an existing user whose password is to be changed. User names are not case-sensitive.

password

The new password for the user. A password must be at least 3 characters and cannot exceed 32 characters. Passwords are case-sensitive. Passwords can contain Unicode characters.

Examples

The following embedded SQL example changes the password of user Bill from “temp_pw” to “pw4AUser”:

Main
   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(CREATE USER Bill IDENTIFY BY temp_pw)
      IF SQLCODE=0 { WRITE !,"Created user" }
      ELSE { WRITE "CREATE USER error SQLCODE=",SQLCODE,! }
   &sql(ALTER USER BILL IDENTIFY BY pw4AUser)
      IF SQLCODE=0 { WRITE !,"Altered user password" }
      ELSE { WRITE "ALTER USER error SQLCODE=",SQLCODE,! }
Cleanup
   SET toggle=$RANDOM(2)
   IF toggle=0 { 
     &sql(DROP USER Bill)
      IF SQLCODE=0 { WRITE !,"Dropped user" }
      ELSE { WRITE "DROP USER error SQLCODE=",SQLCODE }
   }
   ELSE { 
     WRITE !,"No drop this time"
     QUIT 
   }