Caché SQL Reference
[Back] [Next]
Go to:

Creates a user account.
CREATE USER user-name IDENTIFY BY password

CREATE USER user-name IDENTIFIED BY password
user-name The name of the user to be created. The name is an identifier with a maximum of 128 characters. It can contain Unicode letters. user-name is not case-sensitive. For further details see the “Identifiers” chapter of Using Caché SQL.
password The password for this user. A password must be at least 3 characters, and cannot exceed 32 characters. Passwords are case-sensitive. Passwords can contain Unicode characters.
The CREATE USER command creates a user account with the specified password.
A user-name can be any valid identifier of up to 128 characters. The user name can be an SQL reserved word if it is a delimited identifier enclosed in quotation marks, and Support Delimited Identifiers=YES. User names are not case-sensitive.
The IDENTIFY BY and IDENTIFIED BY keywords are synonyms.
A password can be a numeric literal, an identifier, or a quoted string. A numeric literal or an identifier does not have to be enclosed in quotes. A quoted string is commonly used to include blanks in a password; a quoted password can contain any combination of characters, with the exception of the quote character itself. A numeric literal 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).
Passwords are case-sensitive. A password must be at least three characters, and less than 33 characters, in length. Specifying a password that is too long or too short generates an SQLCODE -400 error, with a %msg value of “ERROR #845: Password does not match length or pattern requirements”.
You cannot use a host variable to specify a user-name or password value.
Creating a user does not create any roles or grant any roles to the user. Instead, the user is given permissions for the database they are logging into, and USE permission on the %SQL/Service service if the user holds at least one SQL privilege in the namespace. To assign privileges or roles to a user, use the GRANT command. To create roles, use the CREATE ROLE command.
If you invoke CREATE USER to create a user that already exists, SQL issues an SQLCODE -118 error, with a %msg value of “User named 'name' already exists”. You can determine if a user already exists by invoking the $SYSTEM.SQL.UserExists() method:
  WRITE $SYSTEM.SQL.UserExists("Admin"),!
  WRITE $SYSTEM.SQL.UserExists("BertieWooster")
This method returns 1 if the specified user exists, and 0 if the user does not exist. User names are not case-sensitive.
The CREATE USER command is a privileged operation. Prior to using CREATE 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( /* SQL code here */  )
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.
The following embedded SQL example creates a new user named “BillTest” with a password of “Carl4SHK”. (The $RANDOM toggle is provided so that you can execute this example program repeatedly.)
   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   SET x=$SYSTEM.SQL.UserExists("BillTest")
   IF x=0 {&sql(CREATE USER BillTest IDENTIFY BY Carl4SHK)
           IF SQLCODE '= 0 {WRITE "CREATE USER error: ",SQLCODE,!
   WRITE "User BillTest exists",!
   SET toggle=$RANDOM(2)
   IF toggle=0 { 
     &sql(DROP USER BillTest)
     IF SQLCODE '= 0 {WRITE "DROP USER error: ",SQLCODE,!}
   ELSE {WRITE !,"No drop this time",!}
   WRITE "User BillTest exists? ",$SYSTEM.SQL.UserExists("BillTest"),!
See Also