Re: mysterious SQLException: Access denied for user

[Christopher Schultz <ch...@christopherschultz.net>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Lloyd,

Moving this discussion on-list.

On 12/4/2009 7:35 PM, DIGLLOYD INC wrote:
> I have been running a server just fine for 16 month. It is
> running Tomcat 6.0.20, using the following JDBCRealm:

Before we continue, let's just say up front that using JDBCRealm is not
recommended. It had some synchronization problems in the past, and
fixing them basically means that its operation is serialized, since it
uses only one Connection to the database. Considering switching to
DataSourceRealm instead.

>  <Realm className="org.apache.catalina.realm.JDBCRealm"
>       connectionName="tomcat_auth"
>       connectionPassword="xxxxxx"
>       connectionURL="jdbc:mysql://192.168.1.12/diglloyd"
>       driverName="com.mysql.jdbc.Driver"
>       userTable="UserAuth" userNameCol="username" userCredCol="password"
>       userRoleTable="UserRole" roleNameCol="role" digest="MD5" />

That looks okay to me. You should upgrade from MD5 if at all possible
(yes, I know that changing password digests sucks, I've done it twice...
it takes years to complete) because MD5 is not very secure these days.

> I've made a new server, duplicating the setup, but with one change: I'm
> running MySQL 5.1.41 instead of 5.0.51b (different IP address, I've
> adjusted the connectionURL accordingly).
> 
> I've set up the new database with user 'tomcat_auth', to be used for
> authenticating users. For now, I've done a 'grant all' to rule out silly
> errors. 

What do you get when you run:

mysql> SHOW GRANTS FOR tomcat_auth@localhost;

What is the host from which tomcat_auth is connecting? Tomcat maintains
different permissions for users connecting from different hosts. For
instance, tomcat_auth may be allowed from localhost, but not from
192.168.1.10.

> But with the new setup using MySQL 5.1.41, Tomcat always reports this
> exception shown below.   However, I can connect locally and remotely as
> user 'tomcat_auth' via the 'mysql' command line, do a 'select', etc.

Hmm... you can connect remotely? What is the command line that you use
for that? "mysql -h 192.168.1.12 -u tomcat_auth -p"? Does that work from
the machine on which Tomcat is installed but not properly connecting?

> So
> clearly something is wrong on the tomcat end, yet that configuration is
> identical to the other server.

So, the only difference is the IP address used in the connection URL?

> Any help would be MUCH appreciated.
> 
> Dec 4, 2009 3:15:40 PM org.apache.catalina.realm.JDBCRealm getPassword
> SEVERE: Exception performing authentication
> java.sql.SQLException: Access denied for user
> 'tomcat_auth'@'192.168.1.13' (using password: YES)

So, the password is being used (that's good: it's correct, right?). It
also says you're connecting as tomcat_auth@192.168.1.13. What does this
command return:

mysql> SHOW GRANTS FOR tomcat_auth@192.168.1.13;

or

mysql> SHOW GRANTS FOR tomcat_auth;

> at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2031)
> at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718)
> at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
> [...]
> at
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)

Looks like it's failing to connect (rather than failing to access a
specific table or column).

Let's see what the grants look like.

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAksec1gACgkQ9CaO5/Lv0PDoSgCeJnVVu/A1hK+b+lpqX7luGCAz
IO4An2gnQOq2m1p7dXWRLRKRjNd/YR0T
=a3z4
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org