dual-password-like feature for MySQL before v 8.0

All we need is an easy explanation of the problem, so here it is.

tldr; Is there a dual-password-like feature for MySQL v.5.x ?

Problem

I have a MySQL server that needs to temporarily allow two passwords for one account, due to legal constraints that impose DBMS service accounts to periodically change password.

I don’t think I can just change service account pw because there would be some (albeit little) time gap
between DBMS pw change and DB access information on server-side code that needs to be deployed when changed.

I understand there is a dual-password feature that allows primary/secondary pw,
but the feature is from v 8.0 to which I cannot upgrade.

I am under following constraints & environments:

  • cannot add more accounts (ex. adding user accounts that change pw and fixing admin account’s pw)
  • cannot update MySQL version (current version: 5.7.12)
  • DB is hosted on AWS RDS
  • DB access information on server-side code is retrieved from AWS parameter store

I am aware of AWS CLI/API that requests change in AWS RDS and AWS parameter store respectively, yet the time-gap problem still remains.

Question

  1. Is there an old version equivalent of dual-password? Or is there a way to manually implement it?

  2. Alternatively, is there an AWS feature that could safely sync aws parameter store change and aws rds pw change?

  3. Finally, does the pw change in DBMS immediately/forcefully disconnect existing connection pool for re-authorization? or does it only apply to new connections? is there an option for this behavior?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

There is no dual-password feature in MySQL 5.x.

At my last job, we used MySQL 5.x, and authentication was done using SSL certificates instead of passwords. The authentication was satisfied if the certificate had an ISSUER property indicating it was created by the certificate authority owned by the company we worked for. But the certificate itself had an expiration date, so we had to update it periodically and redeploy the certificate to the client application.

As long as the same ISSUER was used for the certificate, either the old or new certificate would authenticate a user. So you could generate a new certificate and deploy it with the application anytime you want (before the old one expired). There was no downtime required.

This solution works for your needs, but it would require you set up a certificate authority (CA) for your company so you can generate secure certificates.

See https://dev.mysql.com/doc/refman/5.7/en/create-user.html#create-user-tls for reference on using SSL/TLS options.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply