GlassFish JDBC Realm Group Membership

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!

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:

    <auth-realm name="MyRealm" classname="com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm">
      <property description="null" name="jaas-context" value="jdbcRealm"></property>
      <property name="encoding" value="Hex"></property>
      <property description="null" name="password-column" value="PASSWORD"></property>
      <property name="datasource-jndi" value="jdbc/myDS"></property>
      <property name="group-table" value="USERS_GROUPS"></property>
      <property name="user-table" value="USERS"></property>
      <property description="null" name="group-name-column" value="GROUPID"></property>
      <property name="digest-algorithm" value="SHA-512"></property>
      <property description="null" name="user-name-column" value="USERID"></property>
    </auth-realm>

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 <passwordColumn> FROM <userTable> WHERE <userNameColumn> = ?

The group query, with the user Id being passed as the parameter:

SELECT <groupNameColumn> FROM <groupTable> WHERE <groupTableUserNameColumn> = ?;

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.