I have six different sites set up with their respective databases on my server all linking to one localhost ip. Three of those users I set up from root have access only to the one database associated but the other three when show databases command reveal all databases. Not sure how that happened.

How do I make it so each user has only access to respective database? Must have set them up differently but cannot remember how.

It's a drupal multi-site and ultimately I'd like to have just one user and password to access all databases which I do have with root but for drupal settings each database has a necessary set user and pass and before I created individual ones I was making changes to one database which was effecting all else. So until I learn how to prefix them I am trying to keep all separate. Does it matter, what is the best practice - I don't know.


There is no particular practice, it is all depends on your environment and security concern. when you are using Drupal multi-site configuration, in situations where administrators for all of the sites being run from the same code base are not either the same person or a small group with a high level of mutual trust. The reason is that anyone with full administrative privileges on a Drupal site can execute arbitrary PHP code on that site through various means (even without FTP access to the site), and that arbitrary PHP code could be used from one site to affect another site, if the two sites are in the same HTTP document root and sharing the same Drupal code.

You can create mysql users using this command on mysql :

    mysql> CREATE USER 'www1'@'localhost' IDENTIFIED BY 'password123';

The following will allow www1 user to access to site1 database:

    mysql> GRANT ALL PRIVILEGES ON site1_database.* To 'www1'@'localhost' IDENTIFIED BY 'password123';

On your web server where you have settings.php for each site can be configure for mysql server address, database name, user name and password.

| improve this answer | |
  • how then did I manage to have some users created access to all databases? – cea Jul 4 '13 at 11:01
  • Ok sorry I didn't read your answer properly, thanks. So that is as simple as it is for prefixing. – cea Jul 4 '13 at 15:12

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.