I have a request from a customer and I am quite sure the answer is no, but wondering if someone has a different answer.
As you know MySQL installation create a database called “mysql” where it stores the databases we create and also the users.
In the user table, there is a field called “authentication_string” where the user password is saved.
On this project each time a customer creates an account a new database user and database is created.
When a customer logs in through a web interface, the system calls an API to authenticate him/her. After that the root db user is used to connect to customer database, not their own database credentials, why? because they do not want to save user and password on database (This is a temporal solution).
They want to change the application so after authentication/authorization process and they would somehow only needed root credentials to somehow get user and password from “mysql db” and then use them to create the connection using customer db credentials.
Is this possible? Or is there some mysql parent – children configuration where this scenario is possible?
Project uses MySQL 5.7
they would somehow only needed root credentials to somehow get user
and password from “mysql db” and then use them to create the
connection using customer db credentials. Is this possible?
No, this is not possible, passwords are stored hashed. Hashes are one-way functions where if you get the password, you can get the hash, but it is virtually impossible to do the opposite.
Your API must store temporarily the password, somewhere when it is created (probably on memory), and then send it securely to the client application, then discarded. If that is not desired, you should try a different authentication method that doesn’t require passwords or shared secrets.
MySQL authentication should be separate from an application authentication. You could store user credentials (properly hashed and salted) in a database table, but you should keep the users of your application and the users of a database separated (or use an external authentication mechanism, as suggested above).