I wrote a blog about this subject area-- it may or may not be useful.
Take a look...
Hi, it's me again, still crunching
on the JDBCRealm.
Now that I have found the ability to
debug Glassfish code itself (thanks again Dies!) I am making good
progress. But I noticed something weird that I was already puzzled
about when making the config.
In the JDBCRealm config, you specify
the tables that hold your users and group memberships, and the columns
in those tables that the JDBCRealm must use to be able to query the
database and check whether the given username and password were correct
and what groups that user is in. E.G:
User
Table: PVPluimen.accounts
User Name Column: username
Password Column: password
Group Table: PVPluimen.memberships
group Name Column: groupid
What puzzled me from the beginning
is that there are two colums specified for the User Table (username and
password in my case), but there is only one column for the Group Table
(groupid in my case).
"Huh?", I thought, "Where do I
specify the user name column for the group table?"
I saw two options:
1) The username column in the group
table *must* have the same name as the username column in the users
table, or
2) The JDBCRealm is very smart and
only needs to know one part of the primary key, because the other part
will automatically be the username column.
I feared it would be option 1, and
hoped it would be option 2.
Debugging has now confirmed that it
is, unfortunately, option 1.
Why?
Wouldn't it be much more flexible to
just add an extra configuration parameter here: Group Username Column?
I am in the luxury position that I
am able to tailor-make my authorization tables to fit the JDBCRealm,
but anyone coming from a legacy system with an existing datamodel may
not be so lucky. For example this seems to be a very realistic
datamodel to me:
TABLE users {
name,
password
}
TABLE groups {
name
}
TABLE memberships {
groupname,
username
}
If you have such an existing
datamodel, you would have to write conversion code to rename the 'name'
column in the 'users' table, create some extra database view or extend
the JDBCRealm in code to add the extra property, to be able to use the
JDBCRealm. I don't really understand this design decision. One extra
property would give maximum flexibility, but leaving it out immediately
puts severe restrictions on the kinds of datamodels that may benefit
from using the JDBCRealm out of the box.
What do you guys think? Should I
open up a JIRA issue (enhancement request) for it? Or am I missing
something fundamental here and is there actually a very good reason for
this design that I am just missing?
With kind regards,
-Stijn