mysql - 带有 glassfish v3 的 jdbc 领域 : Realm properties and configuration error

Question

我有以下数据库架构 (MySQL):

我的登录是一个基于表单的身份验证系统，我正在尝试为其创建一个jdbc 领域。

我的 web.xml:

<login-config>
    <auth-method>FORM</auth-method>
    <realm-name>emdJDBCRealm</realm-name>
    <form-login-config>
    <form-login-page>/index.jsp</form-login-page>
    <form-error-page>/WEB-INF/loginerror.jsp</form-error-page>
    </form-login-config>
</login-config>
<security-constraint>
    <web-resource-collection>
        <web-resource-name>Login Page</web-resource-name>
        <url-pattern>/*</url-pattern>
    </web-resource-collection>
    <auth-constraint>
        <role-name>adm</role-name>
        <role-name>usr</role-name>
    </auth-constraint>
    <user-data-constraint>
            <transport-guarantee>CONFIDENTIAL</transport-guarantee>
    </user-data-constraint>
</security-constraint>
<security-role>
    <description/>
    <role-name>usr</role-name>
</security-role>
<security-role>
    <description/>
    <role-name>adm</role-name>
</security-role>

以及 sun-web.xml 中的映射:

<security-role-mapping>
  <role-name>adm</role-name>
  <group-name>adm</group-name>
</security-role-mapping>
<security-role-mapping>
  <role-name>usr</role-name>
  <group-name>usr</group-name>
</security-role-mapping>

我不知道为什么，但它对我不起作用，我得到以下信息:

    FINE: Cannot load group
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'group_name' in 'field list'
            at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
            at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
            at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
            at com.mysql.jdbc.Util.getInstance(Util.java:382)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
            at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
            at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
            at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
            at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
            at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281)
            at com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40.executeQuery(PreparedStatementWrapper40.java:641)
            at com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm.findGroups(JDBCRealm.java:480)
            at com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm.authenticate(JDBCRealm.java:312)
            at com.sun.enterprise.security.auth.login.JDBCLoginModule.authenticate(JDBCLoginModule.java:72)
            at com.sun.enterprise.security.auth.login.PasswordLoginModule.authenticateUser(PasswordLoginModule.java:90)
            at com.sun.appserv.security.AppservPasswordLoginModule.login(AppservPasswordLoginModule.java:141)
            at sun.reflect.GeneratedMethodAccessor209.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
            at java.lang.reflect.Method.invoke(Method.java:597)
            at javax.security.auth.login.LoginContext.invoke(LoginContext.java:769)
            at javax.security.auth.login.LoginContext.access$000(LoginContext.java:186)
            at javax.security.auth.login.LoginContext$4.run(LoginContext.java:683)
            at java.security.AccessController.doPrivileged(Native Method)
            at javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)
            at javax.security.auth.login.LoginContext.login(LoginContext.java:579)
            at com.sun.enterprise.security.auth.login.LoginContextDriver.doPasswordLogin(LoginContextDriver.java:341)
            at com.sun.enterprise.security.auth.login.LoginContextDriver.login(LoginContextDriver.java:199)
            at com.sun.enterprise.security.auth.login.LoginContextDriver.login(LoginContextDriver.java:152)
            at com.sun.web.security.RealmAdapter.authenticate(RealmAdapter.java:479)
            at com.sun.web.security.RealmAdapter.authenticate(RealmAdapter.java:418)
            at org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:264)
            at org.apache.catalina.authenticator.AuthenticatorBase.processSecurityCheck(AuthenticatorBase.java:1015)
            at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:614)
            at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:615)
            at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:97)
            at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:85)
            at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:185)
            at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:325)
            at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:226)
            at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:165)
            at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:791)
            at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:693)
            at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:954)
            at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:170)
            at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:135)
            at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:102)
            at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:88)
            at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:76)
            at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:53)
            at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
            at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
            at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:330)
            at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:309)
            at java.lang.Thread.run(Thread.java:619)

    FINE: JAAS authentication aborted.
    FINEST: doPasswordLogin fails
    javax.security.auth.login.LoginException: Security Exception
            at javax.security.auth.login.LoginContext.invoke(LoginContext.java:856)
            at javax.security.auth.login.LoginContext.access$000(LoginContext.java:186)
            at javax.security.auth.login.LoginContext$4.run(LoginContext.java:683)
            at java.security.AccessController.doPrivileged(Native Method)
            at javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)
            at javax.security.auth.login.LoginContext.login(LoginContext.java:579)
            at com.sun.enterprise.security.auth.login.LoginContextDriver.doPasswordLogin(LoginContextDriver.java:341)
            at com.sun.enterprise.security.auth.login.LoginContextDriver.login(LoginContextDriver.java:199)
            at com.sun.enterprise.security.auth.login.LoginContextDriver.login(LoginContextDriver.java:152)
            at com.sun.web.security.RealmAdapter.authenticate(RealmAdapter.java:479)
            at com.sun.web.security.RealmAdapter.authenticate(RealmAdapter.java:418)
            at org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:264)
            at org.apache.catalina.authenticator.AuthenticatorBase.processSecurityCheck(AuthenticatorBase.java:1015)
            at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:614)
            at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:615)
            at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:97)
            at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:85)
            at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:185)
            at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:325)
            at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:226)
            at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:165)
            at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:791)
            at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:693)
            at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:954)
            at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:170)
            at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:135)
            at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:102)
            at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:88)
            at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:76)
            at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:53)
            at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
            at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
            at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:330)
            at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:309)
            at java.lang.Thread.run(Thread.java:619)
    Caused by: java.lang.SecurityException
            at javax.security.auth.login.LoginContext.invoke(LoginContext.java:857)
            ... 34 more

    WARNING: Web login failed: Login failed: javax.security.auth.login.LoginException: Security Exception

我是否根据我拥有的数据库模式设置了“准确”的属性？我很感激你的帮助，因为我不明白我做错了什么。

更新

如果遇到相同情况的任何人。

基于@perissf，GlassFish JDBC Realm 不支持规范化表。查看@perissf 的链接以获取有关模式应该如何的教程。
然而，我所做的是:我保留了规范化模式并创建了一个包含所有必需列的 MySQL“ View ”:即列 [username, pass, group_name]

-->我修改了 Jdbc 属性如下:

使用“ View ”背后的原因是，正如@Matt Handy 所说“JdbcRealm 要求用户表和组表中的用户名列名必须相同”所以这会在我的案例中创建重复数据，因此我使用了一个 View 。

Answer 1

不幸的是，GlassFish JDBC Realm 不支持用户和组的规范化表。我想你的问题就在那里。看看这个 tutorial并相应地修改您的表格。