CREATE USER

Synopsis

CREATE USER user-name IDENTIFY BY password

CREATE USER user-name IDENTIFIED 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 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()Opens in a new tab 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.

Privilges

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

Examples

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.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.UserExists("BillTest"),!
   QUIT  

See Also

• SQL statements: ALTER USER, DROP USER, GRANT, REVOKE, CREATE ROLE

• “Users, Roles, and Privileges” chapter of Using Caché SQL

• SQLCODE error messages listed in the Caché Error Reference

• ObjectScript: $ROLES and $USERNAME special variables