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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…