CREATE USER (SQL)

Creates a user account.

Synopsis

CREATE USER user-name IDENTIFY BY password

Description

The CREATE USER command creates a user account with the specified password.

A user-name can be any valid identifier of up to 160 characters. A user-name must follow identifier naming conventions. A user-name can contain Unicode characters. User names are not case-sensitive.

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 (*).

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 in to, 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.Security.UserExists()Opens in a new tab method:

  WRITE $SYSTEM.SQL.Security.UserExists("Admin"),!
  WRITE $SYSTEM.SQL.Security.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.

Privileges

The CREATE USER command is a privileged operation. Prior to using CREATE USER in embedded SQL, you must be logged in as a user with one of the following:

The %Admin_Secure administrative resource with USE permission
The %Admin_UserEdit administrative resource with USE permission
Full security privileges on the system

If you are not, the CREATE USER command 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( /* SQL code here */  )

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

Arguments

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 InterSystems 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.

Example

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.)

Main
   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   SET x=$SYSTEM.SQL.Security.UserExists("BillTest")
   IF x=0 {&sql(CREATE USER BillTest IDENTIFY BY Carl4SHK)
           IF SQLCODE '= 0 {WRITE "CREATE USER error: ",SQLCODE,!
                            QUIT}
          }
   WRITE "User BillTest exists",!
Cleanup
   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.Security.UserExists("BillTest"),!
   QUIT