GlassFish JDBC Realm Group Membership?

I'm not sure what material you've followed to configure the JDBC realm, but it appear to be incomplete or incorrect. Following is a description of the configuration I've used to configure the JDBC realm.

Up vote 1 down vote favorite 2 share g+ share fb share tw.

I have been busy setting up authentication, a JDBC realm in particular, on GlassFish 3.1. I have been operating under the assumption that: The "User" table contains the login name ("email_address") and the password ("password") The "Group" table contains a list of group names ("name") A "User_Group" table matches users and groups up. Nowhere was I able to configure the "User_Group" table however so I was left wondering how the server would ever be able to match users up to groups. Needless to say it did not work.

Closer inspection however suggests that: The "User" table contains the login name ("email_address") and the password ("password") The "Group" table contains the login name ("email_address") as primary key, and a comma-separated list of group names ("Administrator,User") in a single column ("groups") Is this correct and, if so, why go through the trouble of creating a separate "Group" table? Since it seems you can have only one grouplist per login ("email_address") wouldn't it be just as easy as to simply add a column called "groups" to the "User" table and discard the "Group" table altogether? Thanks!

Java java-ee jdbc glassfish link|improve this question asked Jul 24 '11 at 19:22Laurens16313 100% accept rate.

I'm not sure what material you've followed to configure the JDBC realm, but it appear to be incomplete or incorrect. Following is a description of the configuration I've used to configure the JDBC realm. The database structure (as DDL statements): The USERS table CREATE TABLE USERS ( USERID VARCHAR(50) NOT NULL, PASSWORD VARCHAR(128) NOT NULL ); --//@UNDO DROP TABLE USERS; The GROUPS table CREATE TABLE GROUPS ( GROUPID VARCHAR(20) NOT NULL ); --//@UNDO DROP TABLE GROUPS; The USERS_GROUPS join table CREATE TABLE USERS_GROUPS ( GROUPID VARCHAR(20) NOT NULL, USERID VARCHAR(50) NOT NULL ); --//@UNDO DROP TABLE USERS_GROUPS; The Glassfish JDBCRealm configuration snippet from domain.

Xml: Note, the group-name-column attribute having a value of GROUPID, which maps to the GROUPID column of the join table USERS_GROUPS and not the group table GROUPS. This is because the JDBCRealm issues the following SQL statements (if you decompile the com.sun.enterprise.security.auth.realm.jdbc. JDBCRealm class): The password query, with the user Id being the parameter that is passed from the DigestLoginModule: SELECT FROM WHERE =?

The group query, with the user Id being passed as the parameter: SELECT FROM WHERE =? ; When you consider the second query's structure, it is quite obvious that the group Table must either contain the user Id mapped to a group Id (which leads to duplication of group data for users mapped to multiple groups), or that the group Table must be the join table that maps users to groups.

Thanks a million Vineet, that cleared it up! For the reference, I had been following this article: link The poster's proposed database is what caused the confusion. – Laurens Jul 24 '11 at 20:13 @Laurens, yes I remember that post; it doesn't explain how to map the users to multiple groups and the database structure in that post is meant for 1:1 user-group mappings.

Luckily for me, I used the Derby connection pool to log the queries issued by Glassfish, so it was pretty easy to figure out what was going on. – Vineet Reynolds Jul 24 '11 at 20:16 Does table GROUPS have any real value here? Sure, one could define database constraints to ensure all values in USER_GROUPS exist in USER and GROUPS respectively, hence ensuring data integrity.

And this maybe also useful in the application too. But in GlassFish, table GROUPS is simply not used at all, right? (cc: @Laurens) – Arjan Jan 16 at 14:20 @Arjan, the JDBCRealm implementation does not execute any query against the actual GROUPS table if you have one.

But then, you may not have a 3NF data model if you use USER and USER_GROUP tables alone. It is quite possible that your data model may have other columns for any GROUP. – Vineet Reynolds Jan 16 at 14:36 Thanks, I just wanted to make sure I was not missing the point.

(Totally as an aside: Jetty allows for defining "true" join tables, so I wondered if GlassFish had some hidden support too. But: no. ) – Arjan Jan 16 at 14:49.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.