Y24-012 Security related RT 798566

[TWJW-SANGER]

Describe the Housekeeping See security related RT 798566

[dasunpubudumal]

Dual Passwords for MySQL Reference: MySQL 8.3 Reference

[dasunpubudumal]

The following procedure could be carried out for testing dual password feature (source referenced in https://github.com/sanger/sequencescape/issues/4043#issuecomment-2051656462) in a local MySQL 8.x database.

Note: Creating the user and updating grants are not a part of this story. These steps are purely for completion purposes of the procedure.

1. Check the version of the database.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.3.0     |
+-----------+
1 row in set (0.01 sec)

2. Create new user sample_user with password PASSWORD.

mysql> CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'PASSWORD';

3. Check all users and privileges.

SELECT * FROM mysql.user;

This displays all users and privileges. The user sample_user has no privileges (i.e., privileges were not granted).

4. Grant privileges for sample_user.

mysql> GRANT ALL ON *.* TO 'sample_user'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Note that by providing all privileges, we're making this user as the same level as admin. We do this because the user needs to have APPLICATION_SUPPORT_ADMIN privilege to make it eligible for dual passwords.

5. Attach a secondary password PASSWORD_2 for user sample_user.

ALTER USER 'sample_user'@'localhost' 
IDENTIFIED BY 'PASSWORD_2' 
RETAIN CURRENT PASSWORD;

This attaches another password PASSWORD_2 as the secondary password for the user account sample_user.

---

6. Log in using PASSWORD_2 as the user's credential (i.e., the secondary password).

mysql -u sample_user -pPASSWORD_2;

7. Log in using PASSWORD as the user's credential (i.e., the primary password).

mysql -u sample_user -pPASSWORD;

8. Update the configurations of all consumer applications of the database.
9. Deploy the configurations to UAT environment, and check application's functionality.
10. Discard the old password.

ALTER USER 'sample_user'@'localhost'
  DISCARD OLD PASSWORD;

[dasunpubudumal]

Identified that the following databases are in the host psdp-db:

barcode_warehouse
dba
delegated_user_management
labwhere_production
mixtio_production
mmonit_production
monitoring
print_my_barcode_production
process_tracking_production
samples_extraction_production
sequencescape_production
sequencescape_production_archive
sm_workflow_lims_production
sys
traction_production
traction_service_production
util

The following applications use the respective databases listed below.

Application	Database Name
janitor	labwhere_production
samples_extraction	samples_extraction_production
asset_audits	process_tracking_production
labwhere	labwhere_production
print_my_barcode	print_my_barcode_production
sequencescape	sequencescape_production
sm_workflow	sm_workflow_lims_production
traction-service	traction_service_production

[dasunpubudumal]

credentials project was updated with the latest KeePassXC database, with the new password for psdp user in psdp-db.

The grace period for switching off the old password begins today, and ends in one month's time i.e., on 18th May, 2024. When the grace period ends, please use the following query to discard the old password:

ALTER USER 'psdp'@'%' DISCARD OLD PASSWORD;

Make sure to log in to the SQL console with the psdp user for this.

---

Note: How dual password mechanism work is, when you invoke ALTER USER with RETAIN CURRENT PASSWORD, it makes the current password the secondary password and makes the new password primary. When invoking DISCARD OLD PASSWORD, it will remove the secondary password, making it invalid.