CREATE USER (SQL)

Creates a user account.

Synopsis

CREATE USER user-name IDENTIFY BY password
CREATE USER user-name IDENTIFIED BY password
CREATE USER user-name [ WITH ] PASSWORD 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, IDENTIFIED BY, and WITH PASSWORD 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 new user. To connect from a client or run a SQL statement, users require the USE privilege on %Service_SQL and at least READ privilege on the database resource for the namespace they are connecting to. These can be granted explicitly by assigning the user the %SQL and corresponding %DB_* role with the GRANT command, but are granted implicitly when the user holds at least one SQL privilege in the namespace.

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.

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