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