Caché SQL Reference
ALTER USER
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

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.
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
See Also