question about GlassFish/Tomcat security realms

Question

I have GlassFish set up to use "JDBCRealm". The configuration looks like this and it works fine:

<JDBCRealm userTable="users" userNameCol="user_name" 
userCredCol="user_pass" userRoleTable="user_roles" 
roleNameCol="role_name" ... />

My database currently looks like this:

- USERS -
USER_NAME | USER_PASS
steve | password1

- USER_ROLES -
USER_NAME | ROLE_NAME
steve | ADMIN

My question is, if I want to normalize the data in the database, how do I configure a realm that can understand the new database design? Do I have to write a custom "realm" object or something like that?

Instead, I want my database to look something this:

- USERS -
USER_ID | USER_NAME | USER_PASS
1 | steve | password1

- ROLES -
ROLE_ID | ROLE_NAME
2 | ADMIN

- USER_ROLES -
USER_ID | ROLE_ID
1 | 2

Any help is greatly appreciated!

Answer 1

It should work straightforward. I just did it a few days ago for Glassfish server. But I think it should be similar for Tomcat. I have 3 tables:

• user (login (pk), password, ...)
• group (group_id (pk), group_name)
• group_has_user (login (fk from user table), group_id (fk from group table))

my JDBC Realm looks like the following:

<auth-realm name="Register-User" classname="com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm">
          <property name="jaas-context" value="jdbcRealm" />
          <property name="datasource-jndi" value="jdbc/ladb" />
          <property name="user-table" value="user" />
          <property name="user-name-column" value="login" />
          <property name="password-column" value="password" />
          <property name="group-table" value="group_has_user" />
          <property name="group-name-column" value="group_id" />
          <property name="digest-algorithm" value="SHA-256" />

If you experience problems, make shure that the columns group_id have the same name in group table and in the join-table.