1

I am trying to access information from multiple databases with one user account. Even though I am fairly sure the user has rights to both databases, MySQL is rejecting my SELECT query on the second database.

My SQL query looks like this:

SELECT a.id AS id, a.name AS a_name, b.naam AS b_name
FROM accounts a
LEFT JOIN database_b.uac_bridge bridge ON a.id = bridge.foreign_id
LEFT JOIN database_b.leden b ON b.id = bridge.master_id
WHERE a.active=1

I have removed some names from the code and transliterated it from CodeIgniter ActiveRecord so I hope there are no typo's in the query. I am fairly sure the query itself is correct, it runs fine on my development machine (Windows 7, MySQL 5.5.14) but fails on the server (not sure what OS, some Linux variant, MySQL 5.5.27).

I get the following error message:

Error Number: 1142
SELECT command denied to user 'username'@'localhost' for table 'uac_bridge'

The user that is logged in is created for database_a and has a SELECT grant (but no others) on database_b (configured through DirectAdmin). I have confirmed that the user can read data in database_b by logging in to phpMyAdmin.

Does anyone know how this can be fixed?

Pieter-Paul Kramer
  • 188
  • 1
  • 11
  • If you try a query that just accesses the `uac_bridge` table, it works? – Barmar Mar 05 '14 at 16:12
  • What does `show grants for demos_barboek@localhost` say? – Barmar Mar 05 '14 at 16:14
  • On the first; no, that doesn't work, also if I do USE database_b; first. On the second, I unfortunately don't know because I don't have any user accounts that can perform that query. I control grants through DirectAdmin. P.S.: Actually, the USE databas_b call is also denied (error 1044) – Pieter-Paul Kramer Mar 05 '14 at 16:17
  • Then the error seems genuine, and it apparently has nothing to do with using multiple databases in the same query. He just doesn't have permission to access that table. I'm not familiar with DirectAdmin. I suggest you look for help at dba.stackexchange.com, this isn't a programming issue. – Barmar Mar 05 '14 at 16:20
  • I have just tested and confirmed that I can read data from this DB when logging in with this account into phpMyAdmin. So it does seem to be a programming issue? I have found related issues on StackOverflow, I'm trying to find them now.. – Pieter-Paul Kramer Mar 05 '14 at 16:33
  • From phpMyAdmin you should be able to run `show grants`. – Barmar Mar 05 '14 at 16:35
  • You're right, here's the results: GRANT USAGE ON *.* TO username@'localhost' IDENTIFIED BY PASSWORD 'blahblah' GRANT SELECT, REFERENCES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON database_b.* TO username@'localhost' GRANT SELECT, INSERT, UPDATE, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON database_b.* TO username@'localhost' – Pieter-Paul Kramer Mar 05 '14 at 16:37
  • Looks OK, although I wonder why it's listing all the permissions on `database_b.*` twice. Or was that a copying error? – Barmar Mar 05 '14 at 16:42
  • Oh, no, that was a copying error. The second row should be database_a.* – Pieter-Paul Kramer Mar 05 '14 at 16:45
  • Obviously, these aren't the actual database names, and you're making errors when you edit the queries while copying them to SO. So I think it's possible that there's a typo in the code, which we can't see because it's gone when you copied to SO. Something like this is the only explanation I can think of. There's no good reason why a query should work from phpMyAdmin, but the same query fails from a PHP script when using the same account. – Barmar Mar 05 '14 at 16:49
  • Actually the first row should also be database_a. So the user has no USE rights on database_b. This could be the issue. I'm going to check if I can fix this. Thanks for pointing in the direction, I'll post an update here when I know more. – Pieter-Paul Kramer Mar 05 '14 at 16:50
  • @Barmar, I think the problem is related to this: http://stackoverflow.com/questions/7980032/update-command-is-denied-for-user – Pieter-Paul Kramer Mar 05 '14 at 16:57
  • Yeah, it looks similar. But no explanation is given why the query fails when specifying the database prefix explicitly. – Barmar Mar 05 '14 at 17:00
  • You were right with your previous comment - although it wasn't really a typo. Database names are case-sensitive on Linux but not on Windows (https://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html). In my query, the DB name was lowercase while in reality it has some uppercase characters. This is why it worked fine on Windows and in phpMyAdmin but not in the script on the Linux machine. Thank you for your directions and time! – Pieter-Paul Kramer Mar 05 '14 at 17:21
  • Not to mention, your question never said that you were using two different database servers, and it worked on one but failed on the other. – Barmar Mar 05 '14 at 17:37
  • I think I mentioned that in the second paragraph, after the first code block. – Pieter-Paul Kramer Mar 05 '14 at 17:48
  • Sorry, missed that.... – Barmar Mar 05 '14 at 17:49

1 Answers1

0

For reference, the answer is simple, MySQL database names are case-sensitive on Linux but not on Windows (https://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html).

This was the error, I was not referring to the database in the right capitalization and MySQL gives a no-permission error because it does not recognize the database name.

Pieter-Paul Kramer
  • 188
  • 1
  • 11