2

Our users can only access the slave database.

How can I let them change their passwords on their own or when their passwords expire?

  • ,Welcome to the site, what is the MySQL version(x,y,z)? – Md Haidar Ali Khan Apr 8 at 5:30
  • version is 5.7.12 – Dan Go Apr 8 at 5:35
  • Is the slave read_only ? – Subrata Das Apr 8 at 7:57
  • Yes, it is read only – Dan Go Apr 8 at 8:28
0

We maintain this very same setup. In order to handle read only on the replicas, we have a cron job with SUPER permissions that synchronizes their password with the one on the database.

Alternatively, you could handle passwords on an external authentication mechanism, such as LDAP, so passwords are not handled on MySQL. We don't do it like this because there are MySQL-specific changes (different grants) that we still need to handle on the database. To facilitate such changes, we use roles so that global permissions can be handled easily.

Don't let the users execute commands to the database as SUPER, even if wrapped appropriately, or that could be exploited. Have an API at least in between which simplifies the executable actions. If you don't want to handle external code, you could even have stored procedures with definer permissions, although those are prone to mistakes/SQL injection.

  • LDAP is the solution. However, I forgot to mention that this will be on AWS and we are using RDS. So, we have no access on my.cnf. Do you have other ways we can do this? – Dan Go Apr 15 at 2:05
  • That is an important distinction. Not having access to my.cnf doesn't mean you cannot configure it. I know for example, you can enable IAM authentication: docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… Maybe you would need to setup an external service to allow that easily, which proxies the database connections? All options suggested takes the authentication out of the database to avoid it being too dynamic. – jynus Apr 15 at 7:12
0

If you are required to keep strict READ ONLY on the slave, then there likely isn't a way to have the users update their password there in a convenient manner at all.

You could (your situation permitting) relax the READ ONLY status of the slave and start managing your users grants such that they only have READ access (grant select on <resource> to <user@host>;). This could allow you to give them enough access to change their password (usage on . is sufficient for this, though your users would undoubtedly have more grants so they could actually read table data) but not mess with the integrity of the slave.

Letting the users connect to the R/W master doesn't seem like a good idea since you're already relegating them to a RO slave. Any time I have used a slave as a "safe query quarantine" I have kept it R/W and strictly controlled the users' permissions there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.