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 comm开发者_StackOverflow中文版a-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.