ALTER USER

Purpose

This statement is mainly used to perform the following operations:

• Modify the password of an OceanBase Database user.

• Lock or unlock a user. A locked user cannot log in to the database.

  Note

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

• Specify the default activated role for a user upon login.

• Modify 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-compatible 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];
  

• Specify the default activated role for a user upon login:

  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 the sqluser01 user to ******.

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

• Execute the following command to specify the authentication plugin.

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

• Lock the obsqluser01 user.

  obclient> ALTER USER 'obsqluser01' ACCOUNT LOCK;
  

• Unlock the obsqluser01 user.

  obclient> ALTER USER 'obsqluser01' ACCOUNT UNLOCK;
  

• Specify that the user001 user activates the role001 and role002 roles by default upon login.

  ALTER USER user001 DEFAULT ROLE role001, role002;
  

References

• Activate a role
• View roles
• CURRENT_ROLE