I’m encountering an annoying problem with my JSF and Hibernate web application. This app is running on a Tomcat server and needs to connect to a MySQL database located on a separate server.
I’ve ensured that the IP address of my web server is in the list of allowed connections for the MySQL server, and I’ve granted full privileges to the database user. When I try to connect using NetBeans, everything works great, and I can perform queries without issues.
However, whenever my web application attempts to connect to the database, I receive the following error:
23:55:50,077 WARN SqlExceptionHelper:144 - SQL Error: 1142, SQLState: 42000
23:55:50,077 ERROR SqlExceptionHelper:146 - SELECT command denied to user 'xxx'@'yyyy' for table 'zzz'
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Interestingly, when both the web app and MySQL are run locally, everything functions correctly. This permission error only happens when trying to connect to the remote MySQL server. Has anyone faced a similar issue?
I’ve hit this exact problem before - super frustrating when the connection works but queries don’t. Your database user probably has different permissions depending on where you’re connecting from. MySQL treats ‘user’@‘localhost’ and ‘user’@‘remote_ip’ as totally separate accounts, even with the same username. Check your mysql.user table directly to see what permissions exist for your specific user@ip combo. Also double-check that your web app is actually using the same user account you tested in NetBeans - connection pooling and app servers sometimes use different credentials than you think.
sounds like a permissions issue. even if you granted full access, try specifying the exact IP in your GRANT command. something like GRANT SELECT ON db.* TO 'user'@'ip_address'; Sometimes wildcards don’t work as expected for remote connections.
This usually happens because MySQL’s host authentication is stricter than you’d expect. NetBeans might be connecting directly and skipping some security layers that Tomcat has to go through. Check if your Tomcat server connects through a different network interface or uses connection pooling - that could make MySQL see a different hostname. Try connecting to MySQL directly from your Tomcat server with the same credentials your app uses. You’ll probably find it fails there too. Also check if your MySQL server has skip-name-resolve enabled - hostname resolution problems can mess up permissions between what you granted and what MySQL actually sees when running.