Page 1 of 1

cross-database query error [SOLVED]

Posted: Wed May 31, 2006 5:14 pm
by digistuff
I'm trying a query that pulls info from 2 separate mysql databases that are located on the same server but have different users & passwords. The query works on my local server, where the 2 db's have the same login information, but not on the server where the login information is not equal.

My query looks like this:
$rstestboth = mysql_query("SELECT l.field1, a.field2 FROM db1.table1 l LEFT OUTER JOIN db2.table2 a ON l.field2 = a.field3") or die(mysql_error());
$row_rstestboth = mysql_fetch_assoc($rstestboth);
$totalRows_rstestboth = mysql_num_rows($rstestboth);


The error that I get is: select command denied to user: 'auser@localhost' for table 'table1'

which makes sense to me because table1 is in the db with 'buser@localhost' as it's login. Can I make this query work without having the same user/pw for both dbs?

Posted: Wed May 31, 2006 5:25 pm
by hawleyjr
You need to GRANT auser@localhost access to table1.

Search for mysql key word GRANT

Posted: Wed May 31, 2006 6:01 pm
by RobertGonzalez
You might need root user access to be able to do this.

Adding Users

Posted: Thu Jun 01, 2006 9:26 am
by digistuff
Sorry to be dense... So what you're saying is that there is no way to do a cross-database query where user1 is accessing database1 and user2 is accessing database2 within the same sql statement? Which means I must provide access into each database for the same user, hence the GRANT statement while logged in as the root user. Right? Thanks! :)

Posted: Thu Jun 01, 2006 10:00 am
by RobertGonzalez
If user1 has access to DB1 and user2 has access to DB2, then they can each interact with their respective databases until the cows come home. But if user1 is not an allowed user of DB2 (and vice versa for user2 on DB1) then neither of them will be able to access each other's databases. Hence the need for the owner of one of the databases to GRANT permissions to the other person.

Thanks

Posted: Fri Jun 02, 2006 9:59 am
by digistuff
Thanks for the assist - I did need to get root access to the db to add the users.