Creates a user account.
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 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:
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, 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:
&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.