Syntax

ALTER USER user[@host] [IDENTIFIED BY 'password'] [ACCOUNT {LOCK | UNLOCK}]
[SET [DEFAULT RESOURCE POOL = poolname] [FAILED_LOGIN_ATTEMPTS = integer] [PASSWORD_LOCK_TIME = integer]]
[REQUIRE {SSL | X509 | SUBJECT | NONE}] [ATTRIBUTE <valid JSON>] [COMMENT <comment string>]

Arguments

• user: The name of the 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 will be used, which lets the user connect from any host.

• password: An optional database connection password.

• poolname: The new default resource pool.

• ACCOUNT LOCK: Locks an account.

• ACCOUNT UNLOCK: Unlocks an account that has been locked out due to exceeding the failed login attempts limit or via the ALTER USER ACCOUNT LOCK command.

• ALTER USER does not support setting the password of and locking/unlocking an account simultaneously via syntax like:

  SQL

  Copy

  ALTER USER user IDENTIFIED BY 'password' ACCOUNT LOCK;

• 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. X509 enables mutual authentication of both client (mySQL CLI) and server (memsqld). for more information. SUBJECT checks the subject in the client certificate provided during mTLS connection. The subject specified here should exactly match the subject in the client certificate and should be specified in OpenSSL oneline format (“/” between every field. Specifying REQUIRE SUBJECT automatically means that the user is configured with REQUIRE X509 and has to provide a client certificate when connecting. For more information refer CREATE 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.

For more information about failed login attempt lockout behavior, see SingleStore User Management.

Remarks

• Users must have GRANTor ALTER USER permission to execute the ALTER USER command.

• 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 Permission 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.

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.

ALTER USER 'db_mgr'@'localhost' REQUIRE SSL;

The following example shows how to lock and unlock user accounts.

ALTER USER 'test'@'%' ACCOUNT LOCK;

ALTER USER 'test'@'%' ACCOUNT UNLOCK;

This example shows how you could use a procedure to lock all user accounts (except for root) as a kind of "single user" mode. You could then use a similar procedure to unlock all accounts when desired.

use db;
delimiter $
create or replace procedure test() as
declare
    qry query(name text) = select user from information_schema.users;
    arr array(record(name text));
begin
    arr = collect(qry);
    for x in arr loop
        if substr(user(), 1, instr(user(), '@') - 1) <> x.name and x.name <> 'root' then
            execute immediate concat('alter user ', x.name, ' account lock');
        end if;
    end loop;
end$

The following example shows how to set the values for the ATTRIBUTE and COMMENT arguments:

ALTER USER 'user1'@'localhost' ATTRIBUTE '{"phone": "1234567890"}';

ALTER USER 'user1'@'localhost' COMMENT 'some comment about user1';

ALTER USER 'user1'@'localhost' ATTRIBUTE '{"phone": "1234567890"}' COMMENT 'some comment about user1';

SELECT USER, ATTRIBUTE, COMMENT from INFORMATION_SCHEMA.USERS WHERE user='user1';

+-------------+-------------------------+------------------------------+
| USER        | ATTRIBUTE               | COMMENT                      |
+-------------+-------------------------+------------------------------+
| user1       | {"phone": "1234567890"} | some information about user1 |
+-------------+-------------------------+------------------------------+
1 row in set (0.127 sec)

Manually Expire a User’s Password

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

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

Example

 ALTER USER 'joe'@'localhost' PASSWORD EXPIRE;

If you accidentally expire a password in error, you can reactivate the password using:

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

Example

 ALTER USER 'joe'@'localhost' PASSWORD ACTIVATE;