Justin,
maybe tomcat is complaining about your userRoleTable ("user_roles"). Does
this table have a row named userNameCol ("USERNAME"), too? AFAIK the exact
same column name is required to put users and roles together.
I assume this because you first get
> 2003-11-20 08:54:01 JDBCRealm[Standalone]: Username justin successfully
authenticated
which seems like the password has been successfully checked, but then you
get
> 2003-11-20 08:54:01 JDBCRealm[Standalone]: Exception performing
authentication
> java.sql.SQLException: Column not found, message from server: "Unknown
column 'username' in 'where clause'"
which should come from the attempt to get the roles for this user.
Greetings
Andreas Mohrig
-----Original Message-----
From: Justin Wesbrooks [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 20, 2003 3:59 PM
To: Tomcat Users List
Subject: Re: JDBC Realm
By the way,
I know my userCredCol from the original configuration I sent was wrong. It
should be PASSWORD. I've corrected this and still get the same errors.
I just tried changing all of my field names to lower case, but get the same
results. Here is the exception I get when I don't use the single quotes.
2003-11-20 08:54:01 JDBCRealm[Standalone]: Username justin successfully
authenticated
2003-11-20 08:54:01 JDBCRealm[Standalone]: Exception performing
authentication
java.sql.SQLException: Column not found, message from server: "Unknown
column 'username' in 'where clause'"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1825)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2030)
at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1563)
at
org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:476)
at
org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:394)
at
org.apache.catalina.authenticator.BasicAuthenticator.authenticate(BasicAuthe
nticator.java:161)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase
.java:528)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180
)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
java:171)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:641)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172
)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
:174)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:193)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConne
ction(Http11Protocol.java:549)
at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:589)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
a:666)
at java.lang.Thread.run(Thread.java:534)
Jon Wingfield
<[EMAIL PROTECTED]
odo.com> To
Tomcat Users List
11/20/2003 05:01 <[EMAIL PROTECTED]>
AM cc
Subject
Please respond to Re: JDBC Realm
"Tomcat Users
List"
<[EMAIL PROTECTED]
rta.apache.org>
mySQL is case-sensitive on column names.
In mysql describe the users and user_roles tables. Use the column names
as returned.
eg:
mysql>describe bobbins;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| bobbins_id | bigint(20) | | PRI | 0 | |
| description | varchar(255) | | | | |
+-------------------+--------------+------+-----+---------+-------+
2 rows in set (0.09 sec)
The single quotes you are using have unanticipated side effects:
The sql generated by the JDBCRealm will be something like
select 'USERPASS' from users where 'USERNAME' = ?;
This will bring back zero rows unless the password is actually the
string literal 'USERNAME'. In that case the value returned will be the
string literal 'USERPASS' not the data within the password column.
I would guess your columns are defined in lowercase so the realm setup
should be:
>><Realm className="org.apache.catalina.realm.JDBCRealm"
>> debug="99" driverName="org.gjt.mm.mysql.Driver"
>>connectionURL="jdbc:mysql://localhost:3306/cofc?user=root;password=root"
>>userTable="users"
>>userNameCol="username"
>>userCredCol="userpass"
>>userRoleTable="user_roles"
>>roleNameCol="'ur_rolename'"/>
HTH,
Jon
Justin Wesbrooks wrote:
> When I get rid of the single quotes, the Mysql JDBC driver throws an
> SQLException that says something like "column USERNAME not found"
>
> I originally had it without the single quotes. To solve the error I
added
> them just to try it and it worked, so I left them.
>
> Justin
>
>
>
>
> Tim Funk <[EMAIL PROTECTED]>
> 11/19/2003 05:37 PM
> Please respond to
> "Tomcat Users List" <[EMAIL PROTECTED]>
>
>
> To
>
> Tomcat Users List <[EMAIL PROTECTED]>
> cc
>
>
>
>
>
> Subject
> Re: JDBC Realm
>
>
>
>
>
>
> Get rid of the single quotes.
> userNameCol="USERNAME"
>
> -Tim
>
> Justin Wesbrooks wrote:
>
>>
>>
>>I have a JDBCRealm set up in Tomcat 4.1.29. The realm config is as
>>follows..
>>
>><Realm className="org.apache.catalina.realm.JDBCRealm"
>> debug="99" driverName="org.gjt.mm.mysql.Driver"
>>connectionURL="jdbc:mysql://localhost:3306/cofc?user=root;password=root"
>>userTable="users"
>>userNameCol="'USERNAME'"
>>userCredCol="'USERPASS'"
>>userRoleTable="user_roles"
>>roleNameCol="'UR_ROLENAME'"/>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
