ALTER USER

Syntax

SQL

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

Arguments

user: Username of the SingleStore database user.
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 is used, which allows the user to connect from any host.
password: An optional database connection password for the user.
poolname: The new default resource pool for the user.
ACCOUNT UNLOCK: Unlocks an account that has been locked out due to exceeding the failed login attempts limit.
FAILED_LOGIN_ATTEMPTS: Together with PASSWORD_LOCK_TIME, specifies the failed login attempt lockout behavior. FAILED_LOGIN_ATTEMPTS is the number of failed attempts allowed before the account is locked out. Default is 0, which means there is no restriction. When set to a value greater than or equal to 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.

For more information about failed login attempt lockout behavior, refer to SingleStore User Management.
REQUIRE: SSL option ensures that the user connects via SSL. NONE specifies that SSL is not required for the connection.

Remarks

Users must have GRANT permission to execute the ALTER USER command.
When any non-password attributes (for example, REQUIRE SSL) are modified, resetting the password is not required.
If an account is locked either manually using the ALTER USER … ACCOUNT LOCK command or automatically as a result of exceeding the values specified for FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME arguments, then restarting the cluster removes the lock and returns the account’s status to ONLINE. The account lock needs to be explicitly re-applied after the cluster is back online and/or automated as part of the customer’s operational procedures. Users can confirm the lock state before and after a restart by viewing the ACCOUNT_STATUS column in the information_schema.USERS view.
This command causes implicit commits. Refer to COMMIT for more information.
Refer to the Permissions Matrix for the required permissions.

Examples

The following example demonstrates how to add a new user with a resource pool, and then change the resource pool.

SQL

CREATE USER joe WITH DEFAULT RESOURCE POOL = general;
ALTER USER joe SET DEFAULT RESOURCE POOL = executive;
ALTER USER joe ACCOUNT UNLOCK;

The following example requires SSL on the user db_mgr.
SQL
```
ALTER USER 'db_mgr'@'localhost' REQUIRE SSL;
```

Manually Expire a User’s Password

You can expire a user's password using the following command:

SQL

ALTER USER 'username'@'host' PASSWORD EXPIRE;

To reactivate the password, use:

SQL

ALTER USER 'user'@'host' PASSWORD ACTIVATE;

If the user is logged in when the PASSWORD EXPIRE command is run, they can continue using the current session without interruption. Once the current session ends, whether they can start a new session or run commands depends on the password_expiration_mode engine variable.

Refer to List of Engine Variables for more information on password_expiration_mode.

Refer to Configuring a Password Policy for details on how to configure a robust password policy.