One user for Multiple databases

[urber]

Hi,

I noticed plesk cannot use one user for multiple databases, so how I should I do that? Please advice thanks.

 

[jrm213jmeyer]

Sorry to bump this old thread but I would like to do what the initial poster is asking about.

How can you create one mysql user that has access to multiple databases. Any help would be greatly appreciated.

Thanks.

 

[JMorjaria]

I would love to know as well

 

[jrm213jmeyer]

Apparently PLESK does not support this, you have to create a new user with each database.

To get around this you (after letting PLESK create the database and it's initial user) bypass PLESK and handle it directly through the MySQL interface.

So,

1. If this is a remote machine use SSH to log into the server.

2. get into the mysql command line:
mysql -u usernamehere -p
it will prompt you for the password. Then you should be at a prompt that looks like
mysql>

3. you want to use the MySQL database so type: use mysql

4. then use the GRANT command to add a user to the database you want to add a user to.

example:

GRANT ALL ON MYDATABASE.* to 'usernamehere'@'%' IDENTIFIED by 'userpasswordhere';

don't forget the ; mysql will not execute the statement until it sees a ; it will just consider <enter> as a carriage return.

please note that the above statement will give full permissions on the database to that user from any location local or remote. You can check out the specifics on the grant command at http://dev.mysql.com/doc/refman/5.1/en/grant.html and see how to restrict permissions if needed.

I hope this helps.

Just as a note, you will not see this user as a user when you are in plesk but you will be able to connect with this user using PHP, MySQL Administrator, etc...

You can modify your plesk database if it is on your local server, and add an association between the user and the database and then this user will show up in PLESK also, but I have not tried this because I do not have access to my PLESK database. If I get access to it and can try it I will post the results.

-Cheers

 

[JMorjaria]

well I hate this, I cant get it to work at all. I have used this code:

CREATE USER 'jmorjari_login'@ 'localhost' IDENTIFIED BY '******';

GRANT SELECT ,
INSERT ,

UPDATE ON * . * TO 'jmorjari_login'@ 'localhost' IDENTIFIED BY '******' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;


but I still get the error :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''localhost' IDENTIFIED BY 'bubble'' at line 1


whats going on, I don't know how to assign the user to all my databases.

 

[jrm213jmeyer]

What version of MySQL are you using?

 

[JMorjaria]

I think its 5. I managed to get round it in the end. If you access the phpmyadmin admin panel you can access all databases.