MYSQL::Setting Validate_Password componet for MySQL Database to ensure password policy settings

Inadequate Password Settings for MySQL Database

We observed that the `validate_password%` settings on hostname `<insert hostname>` are empty. This indicates that the **validate_password component** is either **not installed, not enabled, or not properly configured**. As a result, the required **password complexity and password history settings** are not enforced. 

 ---

 ### **References** 

 - [MySQL Validate Password Installation](https://dev.mysql.com/doc/refman/5.7/en/validate-password-installation.html) 

- [Fix MySQL Error 1819: Password Policy Requirements](https://ostechnix.com/fix-mysql-error-1819-hy000-your-password-does-not-satisfy-the-current-policy-requirements/) 

- [Percona Blog on MySQL Password Security](https://www.percona.com/blog/improving-mysql-password-security-with-validation-plugin/) 

### **Action Plan for Password Policy Implementation** 

 1. **Take backup of `my.ini`** before making changes on all relevant servers: 

   - **Servers:** `SLAVE1, SLAVE2, and .MASTER` 

 2. **Modify the `my.ini` file** on these servers with the following policy configuration: 

    ```ini

   # Password policy amended

   password_history=5 

   password_reuse_interval=365 

   validate_password.length=14 

   validate_password.policy=STRONG 

  ```

 3. **Install the `validate_password` component** **before restarting MySQL**: 

    On **slave servers first** and **master last**: 

    mysql> SET GLOBAL super_read_only = 0;  (this for SLAVE only)

   Query OK, 0 rows affected (0.00 sec)

    mysql> INSTALL COMPONENT 'file://component_validate_password'; 

   Query OK, 0 rows affected (0.04 sec)

    mysql> SET GLOBAL super_read_only = 1;  (this for SLAVE only)

   Query OK, 0 rows affected (0.00 sec)

   ```

4. **Restart MySQL services** sequentially: 

   - **Restart Sequence**: 

     1. **Slave 1 (`SLAVE2`)** 

     2. **Slave 2 (`SLAVE1`)** 

     3. **Master (`.MASTER`)**

 ---

 ### **Commands to Run at MySQL Prompt for Validation** 

 After restarting MySQL, run these commands to verify the configuration: 

 1. **Check the validate_password component installation**: 

    mysql> SHOW GLOBAL VARIABLES LIKE '%validate_password%'; 

   ```

    Example output: 

    +--------------------------------------+--------+

   | Variable_name                        | Value  |

   +--------------------------------------+--------+

   | validate_password.check_user_name    | ON     |

   | validate_password.dictionary_file    |        |

   | validate_password.length             | 14     |

   | validate_password.mixed_case_count   | 1      |

   | validate_password.number_count       | 1      |

   | validate_password.policy             | STRONG |

   | validate_password.special_char_count | 1      |

   +--------------------------------------+--------+

   7 rows in set (0.00 sec)

   ```

 2. **Check installed components**: 

      mysql> SELECT * FROM mysql.component; 

   +--------------+--------------------+------------------------------------+

   | component_id | component_group_id | component_urn                      |

   +--------------+--------------------+------------------------------------+

   |            1 |                  1 | file://component_validate_password |

   +--------------+--------------------+------------------------------------+

   1 row in set (0.00 sec)

   ```

3. **Verify replication status and policy enforcement**: 

      mysql> SHOW GLOBAL VARIABLES LIKE '%read%';

   mysql> SHOW REPLICA STATUS\G;

mysql>show global variables like ‘%password_validate%’;

   ```

---

 ### **Implementation Process & Timeline** 

Worked on slave SLAVE2 first,Worked on slave SLAVE1 first,and worked on master as lastly
Task	Date	Status	Remarks	SLAVE1 slave(20 mins)	SLAVE2 slave(time taken :15mins)	MASTERDB slave
Take backup of my.ini before task( SLAVE1, SLAVE2 and MASTERDB)				done	done	done
need to do the entry at my.ini( SLAVE1,SLAVE2  then MASTERDB) # Password policy amended password_history=5 password_reuse_interval=365 validate_password.length=14 validate_password.policy=STRONG				done	done	done
Run at MYSQL cmd prompt at slave first and then on master. mysql> set global super_read_only =0 ; Query OK, 0 rows affected (0.00 sec) install component 'file://component_validate_password'; mysql> INSTALL COMPONENT 'file://component_validate_password'; Query OK, 0 rows affected (0.04 sec) mysql> set global super_read_only =1 ; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like '%read%'; mysql> show global variables like '%validate_password%';				done	done	done
Restart mysql.				done	done	done
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'validate%'; select * from mysql.component;				done	done	done
Test one new user with low complex pwd and see able or not				NA	NA	done
show replica status				GREEN	GREEN	NA

 ---

 Note:

Even without a dictionary file, password validation can still work effectively in MySQL.

The validate_password.dictionary_file option is used to specify a file containing a list of common passwords. MySQL will check new passwords against this dictionary and reject any that match. While using a dictionary file can provide an extra layer of protection, it's not strictly necessary for password validation to function.

 ### **Conclusion** 

 All necessary tasks to implement and enforce the **password complexity policy** on the MYSQL database have been successfully completed. The **password history**, **reuse interval**, and **complexity requirements** are now in effect across all relevant servers, ensuring improved password security.