ALTER USER

Purpose

This statement is mainly used for the following operations:

• Modifying the password of an OceanBase Database user.

• Locking or unlocking a user. A locked user cannot log in to the database.

  Note

  Except for modifying the password of the current user, you must have the CREATE USER privilege to execute this statement.

• Setting the default activated role for a user when the user logs in.

• Modifying the resource options of a user.

Privilege requirements

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

Syntax

• Modify the password of a user:

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

• Lock a user:

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

• Set the default activated role for a user when the user logs in:

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

• Modify the resource options of a user:

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

Parameters

Examples

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

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

• Execute the following command to specify the authentication plugin.

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

• Lock user obsqluser01.

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

• Unlock user obsqluser01.

  obclient> ALTER USER 'obsqluser01' ACCOUNT UNLOCK;
  

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

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

• Modify the resource options of a user. First, create user user001, and 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
• View roles
• CURRENT_ROLE