Manage the password of a PolarDB database account - PolarDB

If you change the password or permissions of a database account by executing the UPDATE or INSERT statement on the permission table in the MySQL database, the modifications cannot be synchronized to read-only nodes. We recommend that you use the methods in this topic to change the password or permissions of a database account. This topic describes how to change the password of a database account, configure the expiration time of the password, and enable or disable the password-based authentication feature.

Background information

You can create and manage privileged accounts and standard accounts in the PolarDB console.

Note

To ensure data security, you cannot create nor use a root account in PolarDB.

Account type	Description
Privileged Account	Privileged accounts can be created and managed only in the console. Only one privileged account can be created for each cluster. The privileged account can manage all the standard accounts and databases in the corresponding cluster. A privileged account can be used to create databases and standard accounts and authorize a standard account to perform add, delete, modify, and view operations on a database. A privileged account has the permissions to implement fine-grained control over user permissions based on your business requirements. For example, you can use a privileged account to grant different users the permissions to query different tables. A privileged account has the permissions to disconnect all standard accounts on the cluster.
Standard Account	Standard accounts can be created and managed in the console or by using SQL statements. Multiple standard accounts can be created for each cluster. The maximum number of standard accounts that you can create depends on the database engine. A standard account cannot be used to create databases or standard accounts. A standard account can only manage databases on which they have permissions. A standard account does not have permissions to manage or disconnect other accounts of the cluster on which the standard account is created.

Change the password of a database account

Log on to the PolarDB console.
In the top navigation bar, select the region in which the cluster that you want to manage is deployed.
Find the cluster and click its ID.
In the left-side navigation pane, choose Settings and Management > Accounts.
Find the account that you want to manage and click Change Password in the Actions column.
In the Change Password dialog box, enter and confirm the new password, and then click OK.

Execute the statements to change the password or permissions of a database account

You can execute the following statements to change the password of a database account by using the privileged account:

PolarDB for MySQL 8.0
```
ALTER USER 'username'@'host' IDENTIFIED BY '{password}'
```
Parameter
Description
username
The account whose password you want to change.
password
The password of the account.

PolarDB for MySQL 5.6 or 5.7

SET PASSWORD FOR 'username'@'host' = PASSWORD('password')

Parameter	Description
username	The account whose password you want to change.
host	The host from which the account can be used to log on to the database. If you set this parameter to a percent sign (%), you can log on to the database from all hosts by using the account.
password	The password of the account.

Configure the expiration time of an account password

PolarDB allows you to configure the expiration time of an account password only by using the privileged account. Perform the following steps:

Use the privileged account to connect to the database. For more information, see Connect to a cluster.
Execute the following statement to configure the expiration time of an account password.
```
ALTER USER 'username'@'hostname' PASSWORD EXPIRE INTERVAL N DAY;
```
username: the name of the account. hostname: the host from which the account can be used to log on to the database. N: the number of days that the password is valid.

Note

PolarDB for MySQL 5.6 does not allow you to configure the expiration time of an account password.

Enable or disable the password-based authentication feature

You can enable or disable the password-based authentication feature by installing or uninstalling the validate_password extension. Perform the following steps:

Use the privileged account to connect to the database. For more information, see Connect to a cluster.
Execute the following statements to install or uninstall the validate_password extension.
- PolarDB for MySQL 8.0
  - Install the validate_password extension:
```
INSTALL COMPONENT 'file://component_validate_password';
```
  - Uninstall the validate_password extension:
```
UNINSTALL COMPONENT 'file://component_validate_password';
```
- PolarDB for MySQL 5.7
  - Install the validate_password extension:
```
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
```
  - Uninstall the validate_password extension:
```
UNINSTALL PLUGIN validate_password;
```
- PolarDB for MySQL 5.6 does not allow you to install or uninstall the validate_password extension to enable or disable the password-based authentication feature.

Modify parameters of the validate_password extension

You cannot modify the parameters of the validate_password extension in the PolarDB console. To modify the parameters of the validate_password extension, you can join DingTalk group 35365027095 to obtain technical support. You can consult with experts in the group to obtain answers to your questions. A PolarDB for MySQL chatbot assistant is also ready to assist you online 24/7 in the group.