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?
cross-database query error [SOLVED]
Moderator: General Moderators
cross-database query error [SOLVED]
Last edited by digistuff on Fri Jun 02, 2006 9:58 am, edited 1 time in total.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Adding Users
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! 
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.