MySQL. Access between cPanel accounts.

[Lironcareto]

Hi all
I have two domains in the same hosting services. Each of them has a database named dom1_db and dom2_db respectively, and each of them has a user named dom1_userdb and dom2_userdb. Is there any way for dom1_userdb to login and access to dom2_db? I'd like to share some tables, because replicating information should be a bit dangerous because it exposes to data inconsistencies. It would be a big help to access to the other database.

Thanks in advance!

[david510]

You can grant access from for the d1 to dbuser2 and vice versa. If you have shell access, you can use the following steps from the mysql prompt.

grant all privileges on db1.* to dbuser2@localhost identified by 'password';
grant all privileges on d21.* to dbuser1@localhost identified by 'password';

[Lironcareto]

Thanks, David, but when I try to grant access that way, MySQL returns me the error
Access denied for user 'dbuser2'@'xx.yy.zz.tt' to database 'db2' It's own DB!! And of course I have access with dbuser2 to db2 because it's the main user, the user which I login to DB from MySQL GUI tools with, and the admin user which I login to cPanel for that domain with.

Both databases are in the same server, as I stated before.

It seems that cPanel not only creates users with no access to other databases (which is of common sense) but also with no privilege to grant access to other users, by default. So I was asking for any other way to do that.

[cPanelDavidG]

What I do in this scenario is create another user for my DB in cPanel and grant it all privileges (or whatever privileges are desired) to the database(s) I want that user to have access to.

Then from my other website, I authenticate to MySQL as this user I created. Even though this user was created under another domain, this other website can still authenticate to it since it's on the same server and it is using the MySQL user's authentication credentials.

[Lironcareto]

Hi DavidG, and thanks for your answer.
Since MySQL users created from cPanel are preceded by the cPanel user (often the domain name) in the way "cpuser_dbuser", any user created for domain1 from domain2 are different.
And if I try to solve this executing the create user command from MySQL Query Browser I get an access denied error.

Could you explain with detail your workaround for this scenario, please?

Thanks in advance.

[cPanelDavidG]

Originally Posted by Lironcareto

Hi DavidG, and thanks for your answer.
Since MySQL users created from cPanel are preceded by the cPanel user (often the domain name) in the way "cpuser_dbuser", any user created for domain1 from domain2 are different.
And if I try to solve this executing the create user command from MySQL Query Browser I get an access denied error.

Could you explain with detail your workaround for this scenario, please?

Thanks in advance.

Sure, I was using the below setup myself up until 9 months ago:

1. Two separate cPanel accounts, let's call them DomainA and DomainB
2. I had a old database used by DomainA, so I had MySQL user under DomainA, let's call it DomainA_dbuser with access to DomainA_db
3. Eventually I created DomainB on the same server, but wanted to access DomainA's databases from DomainB.
4. I just configured my PHP script on DomainB to connect to DomainA_db while authenticating as DomainA_dbuser - connecting to the MySQL server on localhost.
5. Great Success!


Now, if you are using a tool to connect to the database remotely (e.g. MySQL's Query Browser Tool), be sure to add your workstation's IP to the allowed IP list, or else you will get an Access Denied message. You can do this by going to the cPanel interface where the database was created and clicking on the "Remote MySQL" icon. If you don't know your workstation's IP address, you can visit a website like WhatIsMyIPAddress .com

[Lironcareto]

I understand your solution, David, but I'm afraid it doesn't apply to my case. It wouldn't allow me to join tables between databases in the way select * from db1.table1, db2.table2 where... etc, which is what I really need.

Thanks anyway.