1

I have a request from a customer and I am quite sure the answer is no, but wondering if someone has a different answer.

Background

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.

Project

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

  • 1
    What MYSQL version? Different versions support multiple plugins, sometimes including some. It looks odd why are new database user is being created and for what benefit. – danblack Apr 9 '19 at 3:20
  • @danblack It is MySQL 5.7. – Eduardo Apr 9 '19 at 3:22
  • Unclear... The new (?) user requests the creation of a new database for their needs, and new login for the access to that database. Special server script authenticated as a root (using special stored procedure with SECURITY CREATOR) creates new login/password, creates new DB, grants all (?) privileges to this login, sets this DB to this login as default, and reports this everything to the client. Next time the user uses new login/password, and absolutely another server script uses them (not root!) for authentication and processing this user's DB. Users must DO NOT KNOW root password at all. – Akina Apr 9 '19 at 5:04
  • Please don't cross-post. stackoverflow.com/q/55584562/1695906 – Michael - sqlbot Apr 9 '19 at 15:42
1

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).

| improve this answer | |

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.