New user here, excuse me if that isn't grammatically correct, but this is what I want to do - I have a server set-up with multiple sites on one VM instance - I want to create an admin mysql database which only the account holder has access to and multiple "child" databases for each individual site on the instance which do not have root user but can be accessed via the parent/admin user.

Is there a way to do this?

1 Answer 1

  • "Site" usually refers to different physical locations.

  • Perhaps, your "site" is "customer" ("account holder"); that is someone who owns his own "database". And then you, as the owner of the MySQL instance, need some table(s) in the "admin" database to keep track of your customers?

  • One instance of MySQL can contain several "databases".

  • Each database can contain several "tables".

  • About the only purpose for "database" is, as you suggest, for "admin", etc. That is, a way of organizing "tables".

  • "Child database" is not a valid term.

The above bullet items help you with terminology and nudge you toward some of the answers you seek.

As for "security", you should give each account holder his own login(s), but grant access to only his one database.

You, as the admin, should have the password for the user root so that you can do things like CREATE DATABASE and CREATE USER and GRANT when adding a new account holder. The account holder will not (or probably should not) be granted privileges to do those things. They can, however, be allowed to do virtually anything to the the tables in their database.

It sounds like you are building a DBaaS. You need to learn a lot more about DBA activities. When things crash (or whatever), you have to be ready to jump in and fix them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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