SingleStoreDB v8.0

CREATE USER

Create a new user account.

Syntax

CREATE USER user[@host]
  [IDENTIFIED BY 'password']
  WITH [DEFAULT RESOURCE POOL = poolname]
  [FAILED_LOGIN_ATTEMPTS = integer]
  [PASSWORD_LOCK_TIME = integer]
  [REQUIRE {SSL | NONE}]

Note

Instead of authentication by password, other authentication types: Kerberos, PAM, and SAML can also be used with the clause.

IDENTIFIED WITH {authentication_gss | authentication_pam | authentication_saml}

CREATE USER user IDENTIFIED WITH authentication_gss AS ‘kerberos-spn’

CREATE USER user IDENTIFIED WITH authentication_pam AS ‘auth-string’

See section “Using PAM Pluggable Authentication” here.

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.

Notice

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.

For more information about failed login attempt lockout behavior, see Securing SingleStoreDB.

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.
The CREATE USER command only applies to the current node unless the sync_permissions engine variable is set to ON.
The CREATE USER command holds a global lock on the cluster until the processing of the command is completed. See Cluster 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

In this section: