Closed TWJW-SANGER closed 2 weeks ago
Dual Passwords for MySQL Reference: MySQL 8.3 Reference
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.
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.3.0 |
+-----------+
1 row in set (0.01 sec)
sample_user
with password PASSWORD
.mysql> CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'PASSWORD';
SELECT * FROM mysql.user;
This displays all users and privileges. The user sample_user
has no privileges (i.e., privileges were not granted).
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.
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
.
PASSWORD_2
as the user's credential (i.e., the secondary password).mysql -u sample_user -pPASSWORD_2;
PASSWORD
as the user's credential (i.e., the primary password).mysql -u sample_user -pPASSWORD;
ALTER USER 'sample_user'@'localhost'
DISCARD OLD PASSWORD;
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 |
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.
Describe the Housekeeping See security related RT 798566