Mysql – how to join table from different database with different Users of database

join;MySQLpermissionsusers

I have Two database called.

Database1:

host: 127.0.0.1
user: root1
pass: root1
dbname: db1

Table 1: Category

ID    INT(11)          Primary Key
Name  VARCHAR(255)

Database2:

host: 127.0.0.2
user: root2
pass: root2
dbname: db2

Table 2: Product

ID    INT(11)          Primary Key
Name  VARCHAR(255)
CatID INT(11)          FK( Table1: Category: ID )

I am calling query like this;

SELECT * 
FROM db2.product AS p
LEFT JOIN db1.category AS c ON p.CatID = c.ID

It will return error:

SELECT command denied to user 'root2'@'127.0.0.2' for table 'Product'
SELECT command denied to user 'root1'@'127.0.0.1' for table 'Category'

Can anyone help me How can I resolve this issue.

Best Answer

This looks like MySQL.

But independent of the rdbms, you need to grand a user access to a database for it to be able to do anything (like reading data) in that database. In your example you have one user that has access to the one database and another user that has access to the other database. You need to give the user that you connect as when executing this query access to both databases.

How you do that is dependent on your hosting provider. If you use PHPMySQL you can go to the User section and grant the necessary rights. However, you hosting provider might have turned that option off. In that case you need to talk to them to get this resolved.

Related Question