ALTER USER

Purpose

This statement is mainly used to perform the following operations:

• Change the password of an OceanBase database user.

• Lock or unlock users. Locked users are not allowed to log in to the database.

  Note

  To execute this command, you must have the CREATE USER privilege, in addition to being able to change the current user's password.

• Sets the default role for users when they log in.

• Modify user resource options.

Privilege requirements

To execute the ALTER USER statement, the current user must have the CREATE USER management privilege. For more information about OceanBase Database privileges, see Privilege types in MySQL-compatible mode.

Syntax

• Change the user password:

  ALTER USER 'user_name'
  IDENTIFIED [WITH auth_plugin]  BY 'password' | IDENTIFIED [WITH auth_plugin] AS 'auth_string';
  

• Change the user password while retaining the current password (dual-password scenario, available starting from V4.6.1):

  ALTER USER 'user_name' IDENTIFIED BY 'password' RETAIN CURRENT PASSWORD;
  

• Discard the retained old password (since V4.6.1):

  ALTER USER 'user_name' DISCARD OLD PASSWORD;
  

• Locked users:

  ALTER USER 'user_name' [ACCOUNT LOCK | ACCOUNT UNLOCK]
  

• Set the default role for users upon login:

  ALTER USER user_name DEFAULT ROLE {NONE | ALL | role_name [, role_name ...]}
  

• Modify user resource options:

  ALTER USER user_name WITH  resource_option [resource_option]
  
  resource_option:
      MAX_CONNECTIONS_PER_HOUR integer
      | MAX_USER_CONNECTIONS integer
  

Parameters

Examples

• Run the following command to change the password of user sqluser01 to ******.

  obclient> CREATE USER 'sqluser01' IDENTIFIED BY '******';
  obclient> ALTER USER 'sqluser01' IDENTIFIED BY '******';
  

• Change the password of user sqluser01 to NewSecret, and retain the old password before the change (dual-password).

  obclient> ALTER USER 'sqluser01'@'%' IDENTIFIED BY 'NewSecret' RETAIN CURRENT PASSWORD;
  

• The old password is discarded after the business switchover is completed.

  obclient> ALTER USER 'sqluser01'@'%' DISCARD OLD PASSWORD;
  

• Specifies to use the mysql_native_password authentication plugin.

  obclient> ALTER USER 'sqluser01'
  IDENTIFIED WITH mysql_native_password
             AS '******';
  

• Specifies to use the caching_sha2_password authentication plugin.

  ALTER USER 'sqluser02'
  IDENTIFIED WITH caching_sha2_password
             BY '******';
  

• Lock the user obsqluser01.

  obclient> CREATE USER 'obsqluser01'
  IDENTIFIED BY '******';
  obclient> ALTER USER 'obsqluser01' ACCOUNT LOCK;
  

• Unlock the user obsqluser01.

  obclient> ALTER USER 'obsqluser01' ACCOUNT UNLOCK;
  

• Specify that when user user001 logs in, the role001 and role002 roles granted to the user are activated by default.

  obclient> CREATE USER user001 IDENTIFIED BY '******';
  -- Ensure that the roles `role001` and `role002` already exist.
  obclient> ALTER USER user001 DEFAULT ROLE role001, role002;
  

• Modify user resource options. First, create the user user001, then modify its maximum number of connections.

  obclient> ALTER USER user001 WITH MAX_USER_CONNECTIONS 30;
  

• Modify the maximum number of connections per hour for user user001.

  obclient> ALTER USER user001 WITH MAX_CONNECTIONS_PER_HOUR 50;
  

• Modify the maximum number of connections and the maximum number of connections per hour for user user001.

  obclient> ALTER USER user001 WITH MAX_USER_CONNECTIONS 300 MAX_CONNECTIONS_PER_HOUR 500;
  

References

• Activate a role
• Query roles
• CURRENT_ROLE
• Dual-password management