2

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!

Laurens
  • 2,078
  • 5
  • 29
  • 46

1 Answers1

9

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.

Vineet Reynolds
  • 76,006
  • 17
  • 150
  • 174
  • Thanks a million Vineet, that cleared it up! For the reference, I had been following this article: [link](http://blogs.oracle.com/swchan/entry/jdbcrealm_in_glassfish) 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 '12 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 '12 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](http://wiki.eclipse.org/Jetty/Tutorial/Realms#JDBCLoginService), so I wondered if GlassFish had some hidden support too. But: no.) – Arjan Jan 16 '12 at 14:49
  • @Vineet: How did you created this domain.xml ? I never read that I could use a xml for defining a Realm. I thought that the only possible ways would be to define it via command line or via Glassfish-WebConsole. – Laokoon Feb 02 '14 at 16:48
  • I was having the same trouble, this post help me a lot, also, do not forget to restart the glassfish server each time you make changes on the realm config. – CarlosJavier Nov 23 '15 at 02:00
  • I have another question, my user table and groups table has one-to-many relationship. The user table has username, password and a foreign key password id. I was wondering how am I supposed to go by setting up my realm for this scenario? or should I start a new question? – Marci-man May 09 '16 at 19:01