I have to set this up quite a lot for user sharing across multiple Joomla! sites. I will eventually get round to publishing the full instructions for that, but in the meantime I will start with this.
I got started on this with the aid of this post from Simple Steps:
Multiple mysql databases with single user
Fantastically useful as this is, it's slightly out of step with Plesk 9 (or at least the config for my host), so for my reference and yours, here are updated instructions.
For the purpose of this example, we shall pretend we are going to add the user 'ac_siteuser' to the database 'fh_testing'
Connect to MySQL
1. Using PuTTy, or your preferred terminal, connect to the server over SSH
2. Connect to MySQL as the admin user. Note in Plesk the MySQL user 'root' is replaced by 'admin'
# mysql -u admin -p
You will be prompted for the admin password.
Add existing user to different database with permissions
3. Select the database named 'mysql'
mysql> USE `mysql`;
If you need to check the permissions (all the 'Y's and 'N's in the INSERT statement below) against an existing user you can view the table like this:
mysql> SELECT * FROM `db`;
I found this to be necessary as the first time I attempted this, I receieved an error: ERROR 1136 (21S01): Column count doesn't match value count at row 1
If you already have a lot of users the results may be hard to read, so to just view one user:
mysql> SELECT * FROM `db` LIMIT 1;
+------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv |
+------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+
| % | test | user | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N |
+------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+
4. Insert the user into the `db` table with priveleges for the new database.
Having checked the privelege settings against those below and satisfying yourself they are good, you do the insertion:
mysql> INSERT INTO `db` VALUES('localhost','fh_testing','ac_siteuser','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N');
Note that to prevent access from any other server with this user, set the Host field to 'localhost' as in the above example. '%' means ANY mysql connections will be allowed from ANY host.
And that is it.
Or is it? Your sites can now access another database with that user, but you have no access to that user in Plesk or through Plesk's PHPMyAdmin interface.
Add existing user and database to Plesk tables
4. Select the database named 'psa'
mysql> USE `psa`;
5. Check the account_id of the user being added to the database
mysql> SELECT * FROM `db_users`;
+----+------------------+------------+-------+
| id | login | account_id | db_id |
+----+------------------+------------+-------+
| 1 | ac_siteuser | 3 | 2 |
| 2 | ac_testinguser | 4 | 3 |
| 3 | fh_testinguser | 6 | 4 |
+----+------------------+------------+-------+
In this case, the id of user 'ac_site' is 3
6. Check the id of the database
mysql> select * from data_bases;
+----+----------------+-------+--------+--------------+-----------------+
| id | name | type | dom_id | db_server_id | default_user_id |
+----+----------------+-------+--------+--------------+-----------------+
| 2 | ac_livedb | mysql | 1 | 1 | 1 |
| 3 | ac_testing | mysql | 1 | 1 | 2 |
| 4 | fh_testing | mysql | 1 | 1 | 3 |
+----+----------------+-------+--------+--------------+-----------------+
In this case the id of the fh_testing database is 4
7. Insert user name, user account_id and database id into db_users table
INSERT INTO `db_users` VALUES ('','ac_site','3','4');
8. Finally exit and restart mysql
mysql> exit;
# service mysqld restart
