CREATE USER
Create a new user account.
Note
Refer to Manage Organizations, Manage Organization Users, and Manage Database Users for more information about managing users and groups.
Refer to Authenticate with SSO for more information on authenticating users.
Syntax
CREATE USER user[@host]
[IDENTIFIED BY 'password']
WITH [DEFAULT RESOURCE POOL = poolname]
[FAILED_LOGIN_ATTEMPTS = integer]
[PASSWORD_LOCK_TIME = integer]
[REQUIRE {SSL | NONE}]
[ATTRIBUTE <valid json>]
[COMMENT <comment string>]
Arguments
user: The name of the user. Maximum length of 32 characters.
host: The host that the user can connect from. For example, specifying localhost
means the user account can be used only when connecting from the local host. If no host is explicitly specified, the %
wildcard will be used, which allows the user to connect from any host.
password: An optional database connection password.
poolname: The default resource pool for the user.
FAILED_LOGON_ATTEMPTS: Together with PASSWORD_LOCK_TIME
, specifies the failed login attempt lockout behavior. FAILED_LOGIN_ATTEMPTS
is the number of failed attempts allowed after which the account is locked out. A value of 3 would mean that the account would be locked after three failed attempts. Default is 0 which means there is no restriction. When set to a value >=1, PASSWORD_LOCK_TIME
must also be specified.
PASSWORD_LOCK_TIME: Together with FAILED_LOGIN_ATTEMPTS
, specifies the failed login attempt lockout behavior. PASSWORD_LOCK_TIME
is the number of seconds a locked out account must wait before reattempting to log in.
REQUIRE: SSL
option ensures that the user connects via SSL. NONE
specifies that SSL will not be required for the connection.
Note
You must set both FAILED_LOGIN_ATTEMPTS
and PASSWORD_LOCK_TIME
to enable the lockout feature.
To modify the FAILED_LOGIN_ATTEMPTS
and PASSWORD_LOCK_TIME
settings for a user, or to unlock a locked user, use ALTER USER.
ATTRIBUTE and COMMENT: Optional arguments used to provide additional information about a user. The values can be seen in the USERS table. If both arguments are specified in the same statement, ATTRIBUTE
must come first. For ATTRIBUTE, the value just be a valid JSON object.
Remarks
The
CREATE USER
command must be run by a user with administrative privileges on the database. Also, the new user does not have any privileges granted by default. Use GRANT to specify privileges for the new user.This command causes implicit commits. See COMMIT for more information.
In SingleStoreDB Cloud, you do not have privileges to create your own resource pool. You need to raise a ticket with Support to create it. Once it is created you can assign it to users.
The
CREATE USER
command only applies to the current node unless thesync_permissions
engine variable is set toON
.The
CREATE USER
command holds a global lock on the cluster until the processing of the command is completed. See Workspace Level Locks for more info.See the Permission Matrix for the required permission.
Examples
The following example demonstrates how to add a new user and grant it the SELECT
privilege on all tables in the mydb
database:
CREATE USER joe; GRANT SELECT ON mydb.* TO 'joe'@'%';
The following example demonstrates how to create a resource pool and assign it to a new user along with controlling the login attempts"
CREATE RESOURCE POOL test_pool WITH QUERY_TIMEOUT = 20; CREATE USER user1 IDENTIFIED BY 'User123' WITH DEFAULT RESOURCE POOL = test_pool FAILED_LOGIN_ATTEMPTS = 3 PASSWORD_LOCK_TIME = 14400;
The following example demonstrates the use of the default_require_ssl
variable and the REQUIRE
option:
CREATE USER `alice`@`%`; -- uses default_require_ssl var (off by default) CREATE USER `alice`@`%` REQUIRE NONE; -- does not require SSL CREATE USER `alice`@`%` REQUIRE SSL; -- requires SSL
The following examples demonstrate the use of ATTRIBUTE and COMMENT:
CREATE USER 'user1'@'localhost' ATTRIBUTE '{"phone": "1234567890"}'; CREATE USER 'user1'@'localhost' COMMENT 'Some information about user1';
Retrieving ATTRIBUTE and COMMENT values from the INFORMATION_SCHEMA.USERS view:
CREATE USER singlestore@localhost ATTRIBUTE '{"a": 1, "b": 2}' COMMENT 'some information about user singlestore'; Query OK, 0 rows affected (0.026 sec) SELECT USER, ATTRIBUTE, COMMENT from INFORMATION_SCHEMA.USERS WHERE user='singlestore'; **** +-------------+---------------+-----------------------------------------+ | USER | ATTRIBUTE | COMMENT | +-------------+---------------+-----------------------------------------+ | singlestore | {"a":1,"b":2} | some information about user singlestore | +-------------+---------------+-----------------------------------------+ 1 row in set (0.127 sec) SELECT USER, ATTRIBUTE, COMMENT, ATTRIBUTE::a + ATTRIBUTE::b AS c FROM INFORMATION_SCHEMA.USERS WHERE user='singlestore'; **** +-------------+---------------+-----------------------------------------+------+ | USER | ATTRIBUTE | COMMENT | c | +-------------+---------------+-----------------------------------------+------+ | singlestore | {"a":1,"b":2} | some information about user singlestore | 3 | +-------------+---------------+-----------------------------------------+------+ 1 row in set (0.114 sec)