Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
which DBMS do you use? –  kasia.b Sep 20 '13 at 15:14
    
MySQL Database with PHP / Zend Technology. –  AshwinP Sep 21 '13 at 3:42
add comment

2 Answers

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.

share|improve this answer
    
Can you give me the what command I have to execute for that? I have access of database with PuTTy. I can connect it with following command: mysql -h hostname -u username -p –  AshwinP Sep 21 '13 at 3:19
add comment
up vote 0 down vote accepted

I found the command and it will be working.

GRANT ALL PRIVILEGES ON DBNAME.* TO username@host;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.