ALTER USER

Purpose

This statement is mainly used to perform the following operations:

• Change the password of an OceanBase Database user.

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

  Note

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

• Set the default activated role for the user upon login.

• Modify the resource options of the user.

Privilege requirements

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

Syntax

• Change the user password:

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

• Lock the user:

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

• Set the default activated role for the user upon login:

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

• Modify the resource options of the 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 statement to change the password of the sqluser01 user to ******.

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

• Execute the following statement 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