[#JETTY-175] JDBCUserRealm incompatible with Apache Derby / JavaDB - jira.codehaus.org

Details

Type: Improvement
Status: Resolved
Priority: Minor
Resolution: Fixed
Affects Version/s: 6.0.1
Fix Version/s: 6.1.2rc1
Component/s: Security and SSL
Labels:
None
Environment:
Derby 10.2.1

Number of attachments :
0

Description

Trying to get Jetty JDBCUserRealm to work with Derby does not work easily.
SQL generated by role query is rejected by Derby

SQL: select r.rolename from roles r, userroles u where u.userid = '1' and r.id = u.roleid;
Derby: ERROR 42818: Comparisons between 'INTEGER' and 'CHAR' are not supported.

(Assumption: table userroles has userid column of type INTEGER (or any non-char data type); I believe this assumption is sensible).

The condition u.userid = '1' is rejected because of the quotes. Request runs fine without the quotes.

Internally, org.mortbay.jetty.security.JDBCUserRealm::loadUser uses

Object key = rs.getObject(_userTableKey);
put(username, rs.getString(_userTablePasswordField));
stat.close();

stat = _con.prepareStatement(_roleSql);
stat.setObject(1, key);

where (for Derby at least)

int key = rs.getInt(_userTableKey);
put(username, rs.getString(_userTablePasswordField));
stat.close();

stat = _con.prepareStatement(_roleSql);
stat.setInt(1, key);

would be better. With the code changed as above realm access does work.

I suggest adding a further boolean configuration attribute like "isBooleanUserTableKey" (default false) to this class?
This would maintain backwards compatibility.

Or maybe a further JDBCUserRealm variant, maybe derived from that class?

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Jan Bartel added a comment - 15/Nov/06 5:48 AM

Armi, can you change the JDBCUserRealm class do use integer comparison, and test it for backwards compatibility with mysql? Thanks.

Show

Jan Bartel added a comment - 15/Nov/06 5:48 AM Armi, can you change the JDBCUserRealm class do use integer comparison, and test it for backwards compatibility with mysql? Thanks.

Hide

Permalink

Jan Bartel added a comment - 08/Feb/07 6:39 AM

Hi Armi, what's the status on this issue, is it fixed?

Show

Jan Bartel added a comment - 08/Feb/07 6:39 AM Hi Armi, what's the status on this issue, is it fixed?

Hide

Permalink

Armi Lyn B. Manlosa added a comment - 08/Feb/07 5:21 PM

Hi Jan,

I sent emails on this last 12/6/2006 and again on 01/16/2007 ( I hope they didn't get lost somewhere ). As I mentioned that time, I tried using Jetty JDBCUserRealm with Derby and it actually worked for me as it was. If you'd prefer though, I modified the class to do integer comparison and the modified file is committed in my sandbox. I used the "test" webapp example for testing with both Derby and MySQL.

Best regards,
Armi

Show

Armi Lyn B. Manlosa added a comment - 08/Feb/07 5:21 PM Hi Jan, I sent emails on this last 12/6/2006 and again on 01/16/2007 ( I hope they didn't get lost somewhere

). As I mentioned that time, I tried using Jetty JDBCUserRealm with Derby and it actually worked for me as it was. If you'd prefer though, I modified the class to do integer comparison and the modified file is committed in my sandbox. I used the "test" webapp example for testing with both Derby and MySQL. Best regards, Armi

Hide

Permalink

Jan Bartel added a comment - 08/Feb/07 5:45 PM

Matthias, can you confirm whether this is a problem under jetty 6.1.1 or 6.1.2pre0? If it is, please also include details of your platform etc. Thanks.

Show

Jan Bartel added a comment - 08/Feb/07 5:45 PM Matthias, can you confirm whether this is a problem under jetty 6.1.1 or 6.1.2pre0? If it is, please also include details of your platform etc. Thanks.

Hide

Permalink

Matthias added a comment - 09/Feb/07 6:23 PM

Sorry, I really missed those mails. I will check as requested and let you know. Thanks for addressing this.
Best regards,
Matthias

Show

Matthias added a comment - 09/Feb/07 6:23 PM Sorry, I really missed those mails. I will check as requested and let you know. Thanks for addressing this. Best regards, Matthias

Hide

Permalink

Matthias added a comment - 19/Feb/07 6:34 PM

Hello,

I just tried again with jetty-6.1.2pre0 as requested. It continues to not work.
Derby is current: db-derby-10.2.2.0-bin; JDK is Sun 1.6 or JDK 1.5 (does not matter). Since the source code has not changed a bit for JDCBUserRealm, I really did not expect any change in functionality. The code still contains setObject instead of setInt in 6.1.2pre0.

I am using the test application as well.

Starting jetty with java -jar start.jar etc\jetty.xml
with a realm definition in jetty.xml as follows:
<Set name="UserRealms">
<Array type="org.mortbay.jetty.security.UserRealm">
<Item>
<New class="org.mortbay.jetty.security.JDBCUserRealm">
<Arg>Test Realm</Arg>
<Arg>etc/derbyRealm.properties</Arg>
</New>
</Item>
</Array>
</Set>
with derbyRealm.properties containing:

-8<-
jdbcdriver=org.apache.derby.jdbc.ClientDriver
url=jdbc:derby://localhost:1527/DBWebApp
username=app
password=password
usertable=users
usertablekey=id
usertableuserfield=username
usertablepasswordfield=pwd
roletable=roles
roletablekey=id
roletablerolefield=role
userroletable=user_roles
userroletableuserkey=user_id
userroletablerolekey=role_id
cachetime=30
-8<-

and derby's derbynet.jar and derbyclient.jar in lib/ext
and a normal derby listener running on localhost:1527 with database DBWebApp

CREATE TABLE ROLES
(
ID INTEGER NOT NULL constraint roles_pk primary key,
ROLE VARCHAR(32) NOT NULL
);

CREATE TABLE USER_ROLES
(
ID INTEGER NOT NULL constraint userroles_pk primary key,
USER_ID INTEGER constraint userid_fk references USERS(ID),
ROLE_ID INTEGER constraint roleid_fk references ROLES(ID)
);

insert into USERS values ( 100, 'admin', 'password' );
insert into ROLES values ( 100, 'content-administrator' );
insert into USER_ROLES values ( 100, 100, 100 );

select r.role from roles r, users u, user_roles ur where u.id=ur.user_id and r.id=ur.role_id and u.username='admin';
ROLE
--------------------------------
content-administrator
-8<-

Acessing a protected resource from the test web application
http://admin:password@localhost:8080/test/dump/auth/admin/foo
still fails. When switching to a HashUserRealm in jetty.xml it works.

Unfortunately I do not have access to a MySQL database right now but I'm sure that code using "setInt/getInt" would run nicely there as well. Of course someone will have to test it.

Best regards,
Matthias

Show

Matthias added a comment - 19/Feb/07 6:34 PM Hello, I just tried again with jetty-6.1.2pre0 as requested. It continues to not work. Derby is current: db-derby-10.2.2.0-bin; JDK is Sun 1.6 or JDK 1.5 (does not matter). Since the source code has not changed a bit for JDCBUserRealm, I really did not expect any change in functionality. The code still contains setObject instead of setInt in 6.1.2pre0. I am using the test application as well.

Starting jetty with java -jar start.jar etc\jetty.xml
with a realm definition in jetty.xml as follows: <Set name="UserRealms"> <Array type="org.mortbay.jetty.security.UserRealm"> <Item> <New class="org.mortbay.jetty.security.JDBCUserRealm"> <Arg>Test Realm</Arg> <Arg>etc/derbyRealm.properties</Arg> </New> </Item> </Array> </Set>
with derbyRealm.properties containing:

-8<- jdbcdriver=org.apache.derby.jdbc.ClientDriver url=jdbc:derby://localhost:1527/DBWebApp username=app password=password usertable=users usertablekey=id usertableuserfield=username usertablepasswordfield=pwd roletable=roles roletablekey=id roletablerolefield=role userroletable=user_roles userroletableuserkey=user_id userroletablerolekey=role_id cachetime=30 -8<-

and derby's derbynet.jar and derbyclient.jar in lib/ext
and a normal derby listener running on localhost:1527 with database DBWebApp

The schema can be set up using follwing SQL (note that derby does not allow column name user without quoting as this is a reserved keyword, hence I chose username): -8<- CREATE TABLE USERS ( ID INTEGER NOT NULL constraint users_pk primary key, USERNAME VARCHAR(32) NOT NULL, PWD VARCHAR(32) NOT NULL ); CREATE TABLE ROLES ( ID INTEGER NOT NULL constraint roles_pk primary key, ROLE VARCHAR(32) NOT NULL ); CREATE TABLE USER_ROLES ( ID INTEGER NOT NULL constraint userroles_pk primary key, USER_ID INTEGER constraint userid_fk references USERS(ID), ROLE_ID INTEGER constraint roleid_fk references ROLES(ID) ); insert into USERS values ( 100, 'admin', 'password' ); insert into ROLES values ( 100, 'content-administrator' ); insert into USER_ROLES values ( 100, 100, 100 ); select r.role from roles r, users u, user_roles ur where u.id=ur.user_id and r.id=ur.role_id and u.username='admin'; ROLE -------------------------------- content-administrator -8<- Acessing a protected resource from the test web application http://admin:password@localhost:8080/test/dump/auth/admin/foo still fails. When switching to a HashUserRealm in jetty.xml it works. Unfortunately I do not have access to a MySQL database right now but I'm sure that code using "setInt/getInt" would run nicely there as well. Of course someone will have to test it. Best regards, Matthias

Hide

Permalink

Armi Lyn B. Manlosa added a comment - 19/Feb/07 7:21 PM

Hello, Matthias. Sorry, the code still contains setObject because the modified JDBCUserRealm is merely in my sandbox. Jan, I don't have commit rights to the jetty repo, so I committed the file in my sandbox.

Show

Armi Lyn B. Manlosa added a comment - 19/Feb/07 7:21 PM Hello, Matthias. Sorry, the code still contains setObject because the modified JDBCUserRealm is merely in my sandbox. Jan, I don't have commit rights to the jetty repo, so I committed the file in my sandbox.

Hide

Permalink

Jan Bartel added a comment - 20/Feb/07 1:40 AM

Fix from Armi applied. svn rev 1607.

Show

Jan Bartel added a comment - 20/Feb/07 1:40 AM Fix from Armi applied. svn rev 1607.

People

Assignee:

Armi Lyn B. Manlosa

Reporter:

Matthias

Vote (0)

Watch (1)

Dates

Created:

14/Nov/06 5:39 PM

Updated:

20/Feb/07 1:40 AM

Resolved:

20/Feb/07 1:40 AM

Time Tracking

Estimated:

Not Specified

Remaining:

Logged: