ALTER USER

docs.intersystems.com

Home / Application Development: Core Topics / InterSystems SQL Reference / SQL Commands / ALTER USER

InterSystems SQL Reference

ALTER USER

InterSystems: The power behind what matters

Changes a user’s password.

Synopsis

ALTER USER user-name IDENTIFY BY password

ALTER USER user-name IDENTIFIED BY password

Arguments

user-name	The name of an existing user whose password is to be changed. If support for delimited identifiers is on and the user name begins with an underscore, you must place the user name in quotation marks. 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.

Description

The ALTER USER command allows you to change a user's password. You can always change your own password. To change another user's password, you must have the %Admin_Secure:USE system permission.

The IDENTIFY BY and IDENTIFIED BY 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.

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() method:

$SYSTEM.Security.ChangePassword(args)

Privileges

The ALTER USER command is a privileged operation. Prior to using ALTER USER in embedded SQL, it is necessary to be logged in as a user with appropriate privileges. Failing to do so results in an SQLCODE -99 error (Privilege Violation). Use the $SYSTEM.Security.Login() 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, refer to %SYSTEM.Security in the InterSystems Class Reference.

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 
   }

As stated above, if support for delimited identifiers is on and the user name begins with an underscore, you must place the user name in quotation marks, such as:

ALTER USER "_ADMIN" IDENTIFY BY myPW4now