ALTER USER

Description

This statement is mainly used to perform the following operations:

• Change the password of the seekdb user.

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

  info

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

• Set the default activated role for the user when it logs in.

• Modify the resource options of the 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 seekdb, see Classification of seekdb privileges.

Syntax

• Change the user password:

  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 the user when it logs in:

  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

Parameter description

Parameter	Description
user_name	The name of the user.
password	The new password.
IDENTIFIED WITH auth_plugin AS 'auth_string'	Sets the account authentication plugin to auth_plugin and stores the auth_string value in the mysql.user table. If the plugin requires a hashed string, it is assumed that the string is already in the required hashed format.
ACCOUNT LOCK	Locks the user.
ACCOUNT UNLOCK	Unlocks the user.
NONE	Disables all roles granted to the user.
ALL	Activates all roles granted to the user.
role_name	The name of the role. Multiple roles are separated by commas (,).
resource_option	The resource options of the user. Multiple resource options are separated by spaces. MAX_CONNECTIONS_PER_HOUR: specifies the maximum number of connections allowed per hour. integer is an integer indicating the maximum number of connections allowed. MAX_USER_CONNECTIONS: specifies the maximum number of connections allowed per user. integer is an integer indicating the maximum number of connections allowed.

Examples

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

  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.

  ALTER USER 'obsqluser01' ACCOUNT LOCK;

• Unlock the obsqluser01 user.

  ALTER USER 'obsqluser01' ACCOUNT UNLOCK;

• Specify that the user001 user activates the role001 and role002 roles by default when it logs in.

  ALTER USER user001 DEFAULT ROLE role001, role002;

References

• Activate a role
• View roles
• CURRENT_ROLE