To improve database security, MySQL requires enabling strong password policies and periodic rotation. 1. Enable the validate_password plug-in to set strong policies and minimum password lengths; 2. Regularly rotate high-permission accounts, application accounts and third-party account passwords, combining automation tools and timed task execution; 3. Follow the principle of minimum permissions, assign permissions on demand, and use role management to simplify permission configuration; 4. Regularly review and clean useless accounts to reduce the potential risks after password leakage. These measures can effectively enhance overall system security, especially in multi-environmental collaboration.

MySQL's password security policy and rotation mechanism are the basic links of database security. Many systems are often broken not because of complex vulnerabilities, but because of weak passwords or account credentials that have not been replaced for a long time. Setting up a reasonable password strategy and regularly rotating passwords can effectively improve the security of the overall system.

Enable and configure strong password policies

MySQL comes with a password verification plug-in (validate_password), which forces users to set passwords that meet a certain level of complexity. This feature may not be enabled by default and needs to be configured manually.

• Make sure that the validate_password plugin is loaded:
  Run INSTALL PLUGIN validate_password SONAME 'validate_password.so'; (Linux) or the corresponding version of Windows.
• Configure password policy level:
  You can set it to a strong policy through SET GLOBAL validate_password.policy = STRONG; which requires the password to contain case, numbers and special characters, and have a certain length.
• Modify the default minimum password length:
  If the default 8 bits are not enough, you can adjust it through validate_password.length parameter.

Note: The stronger the strategy, the more troublesome the user is when setting passwords. It is recommended to relax appropriately in the development environment and strictly implement them in the production environment.

Regularly rotate MySQL user passwords

Account passwords that are not changed for a long time are at risk of leakage, especially those with high authority. It is recommended to rotate the following types of accounts regularly:

• Database administrator account (such as root)
• A dedicated account used for the application connection
• Accounts used by third-party services or external systems

You can use the following command to modify the password:

 ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

If you have multiple instances or automated deployment processes, it is recommended to incorporate password rotation into the operation and maintenance process, such as unified updates with tools such as Ansible and Chef.

In addition, you can also use the operating system timing tasks (such as Linux's cron job) to remind or automatically perform password changes, but you need to be careful to deal with the application interruption caused by automatic password modification.

Use the role and minimum permissions principle to reduce risk

In addition to the security of the password itself, it is also important to reasonably allocate account permissions. Even if the password is leaked, the losses caused by low-privileged accounts are relatively controllable.

• Avoid all apps using root or accounts with global permissions
• Create a dedicated account according to the business module and grant only necessary permissions
• Regularly review account permissions and delete accounts that are no longer in use

For example:

 CREATE USER 'app_user'@'%' IDENTIFIED BY 'securepassword123';
GRANT SELECT, INSERT ON mydb.mytable TO 'app_user'@'%';

The advantage of this is that even if the account password is leaked, the attacker cannot perform destructive operations such as DROP or DELETE.

In addition, MySQL 8.0 supports role (Role) management, which can package a set of permissions into roles and then allocate them to users, making it easier to manage and recycle unifiedly.

Basically that's it. Password strategy and rotation may seem simple, but if you ignore the details, it is easy to leave security risks. Especially in the case of multi-team collaboration or multi-environment deployment, unified standards and regular inspections are needed.

The above is the detailed content of Securing MySQL Password Policies and Rotation. For more information, please follow other related articles on the PHP Chinese website!