cross-database query error [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
digistuff
Forum Newbie
Posts: 3
Joined: Wed May 31, 2006 4:59 pm
Location: New Mexico

cross-database query error [SOLVED]

Post 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?
Last edited by digistuff on Fri Jun 02, 2006 9:58 am, edited 1 time in total.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

You need to GRANT auser@localhost access to table1.

Search for mysql key word GRANT
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You might need root user access to be able to do this.
digistuff
Forum Newbie
Posts: 3
Joined: Wed May 31, 2006 4:59 pm
Location: New Mexico

Adding Users

Post 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! :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
digistuff
Forum Newbie
Posts: 3
Joined: Wed May 31, 2006 4:59 pm
Location: New Mexico

Thanks

Post by digistuff »

Thanks for the assist - I did need to get root access to the db to add the users.
Post Reply