# ALTER USER

The `ALTER USER` command alters settings associated with the user. To change a user password, use the [SET PASSWORD](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/set-password.md) command.

## Syntax

```sql
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**: 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 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.
* **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 after which the account is locked out. For example, a value of `3` means that the account will be locked after three failed attempts. 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](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/singlestore-user-management.md).
* **REQUIRE**: `SSL` option ensures that the user connects via SSL. `NONE` specifies that SSL is not required for the connection. For the `X509` and `SUBJECT` options, refer to [CREATE USER - REQUIRE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/create-user/#section-id23562410867848.md).
* **ATTRIBUTE** and **COMMENT**: Optional arguments used to provide additional information about a user. The values can be seen in the [USERS](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/resource-and-user-management/users.md) table. If both arguments are specified in the same statement, `ATTRIBUTE` must come first. For `ATTRIBUTE`, the value must be a valid JSON object.

## Remarks

* Users must have `GRANT`or `ALTER USER` permission to execute the `ALTER USER` command.
* When any non-password attributes (for example, `REQUIRE SSL`) are modified, resetting the password is not required.
* `ALTER USER` does **not** support setting the password and locking/unlocking an account simultaneously via a syntax like:
  ```sql
  ALTER USER user IDENTIFIED BY 'password' ACCOUNT LOCK;
  ```
* 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information.
* Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) 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;

  ```
* The following example shows how to lock and unlock user accounts.
  ```sql
  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.
  ```sql
  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$

  DELIMITER ;
  ```
* 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';

  ```
  ```output

  +-------------+-------------------------+------------------------------+
  | USER        | ATTRIBUTE               | COMMENT                      |
  +-------------+-------------------------+------------------------------+
  | user1       | {"phone": "1234567890"} | some information about user1 |
  +-------------+-------------------------+------------------------------+

  ```

## 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](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables.md) for more information on `password_expiration_mode`.

Refer to [Configuring a Password Policy](https://docs.singlestore.com/db/v9.1/security/authentication/configuring-a-password-policy.md) for details on how to configure a robust password policy.

***

Modified at: June 12, 2026

Source: [/db/v9.1/reference/sql-reference/security-management-commands/alter-user/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/alter-user/)

(An index of the documentation is available at /llms.txt)