Description
This statement is mainly used to perform the following operations:
Change the password of an OceanBase AI 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 USERprivilege, in addition to being able to change the current user's password.Sets the default role activated for users upon login.
Modify user resource options.
Privilege requirements
To execute the ALTER USER statement, the current user must have the CREATE USER privilege. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
Change the password of a user:
ALTER USER 'user_name' IDENTIFIED [WITH auth_plugin] BY 'password' | IDENTIFIED [WITH auth_plugin] AS 'auth_string'Locked users:
ALTER USER 'user_name' [ACCOUNT LOCK | ACCOUNT UNLOCK]Set the default role activated 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
Parameters |
Description |
|---|---|
| user_name | Specify the username. |
| password | Specify the new password. |
| IDENTIFIED WITH auth_plugin AS 'auth_string' | Set the account authentication plugin to auth_plugin, and set the auth_string value stored in the mysql.user table. If a plugin requires a hash string, it is assumed that the string is already in the hash format required by the plugin. |
| ACCOUNT LOCK | Specifies the locking user. |
| ACCOUNT UNLOCK | Specifies the user to unlock. |
| NONE | Specify all roles granted to the disabled user. |
| ALL | Specify to activate all roles under the user. |
| role_name | Specifies the role name. When activating multiple roles, separate the role names with commas (,) separated. |
| resource_option | Resource options for the specified user. Separate multiple resource options with spaces.
|
Examples
Run the following command to change the password of user
sqluser01to******.obclient> CREATE USER 'sqluser01' IDENTIFIED BY '******'; obclient> ALTER USER 'sqluser01' IDENTIFIED BY '******';Run the following command to specify the authentication plugin.
obclient> ALTER USER 'sqluser01' IDENTIFIED WITH mysql_native_password AS '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';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
user001logs in, therole001androle002roles 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;