GlassFish JDBC 领域组成员资格 [英] GlassFish JDBC Realm Group Membership

问题描述

我一直忙于在 GlassFish 3.1 上设置身份验证，尤其是 JDBC 领域.我一直在假设:

I have been busy setting up authentication, a JDBC realm in particular, on GlassFish 3.1. I have been operating under the assumption that:

• 用户"表包含登录名(email_address")和密码(password")
• 组"表包含一组组名称(名称")
• User_Group"表匹配用户和组.

然而，我无法配置User_Group"表，所以我想知道服务器如何能够将用户与组匹配.不用说它没有用.然而，更仔细的检查表明:

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:

• 用户"表包含登录名(email_address")和密码(password")
• 组"表包含登录名(email_address")作为主键，以及单列(组")

• 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")

这是正确的吗?如果是，为什么还要麻烦地创建一个单独的组"表?既然每次登录似乎只能有一个组列表(email_address")，难道不是简单地将一个名为groups"的列添加到User"表并完全丢弃Group"表吗?

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?

谢谢！

推荐答案

我不确定您在配置 JDBC 领域时遵循了哪些材料，但它似乎不完整或不正确.以下是我用来配置 JDBC 领域的配置的描述.

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.

数据库结构(如DDL语句):

The database structure (as DDL statements):

USERS 表

CREATE TABLE USERS (
        USERID VARCHAR(50) NOT NULL,
        PASSWORD VARCHAR(128) NOT NULL
    );

--//@UNDO

DROP TABLE USERS;

GROUPS 表

CREATE TABLE GROUPS (
        GROUPID VARCHAR(20) NOT NULL
    );

--//@UNDO

DROP TABLE GROUPS;

USERS_GROUPS 连接表

CREATE TABLE USERS_GROUPS (
        GROUPID VARCHAR(20) NOT NULL,
        USERID VARCHAR(50) NOT NULL
    );

--//@UNDO

DROP TABLE USERS_GROUPS;

<小时>

来自 domain.xml 的 Glassfish JDBCRealm 配置片段:

---

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>

注意，group-name-column属性的值为GROUPID，映射到连接表的GROUPID列USERS_GROUPS 而不是组表 GROUPS.这是因为 JDBCRealm 发出以下 SQL 语句(如果您反编译 com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm 类):

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):

密码查询，用户 ID 是从 DigestLoginModule 传递的参数:

The password query, with the user Id being the parameter that is passed from the DigestLoginModule:

SELECT <passwordColumn> FROM <userTable> WHERE <userNameColumn> = ?

组查询，以用户 ID 作为参数传递:

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

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

当您考虑第二个查询的结构时，很明显组表必须包含映射到组 Id 的用户 Id(这导致映射到多个组的用户的组数据重复)，或者组表必须是将用户映射到组的连接表.

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.

这篇关于GlassFish JDBC 领域组成员资格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！