Login/Register
jdbc realm with glassfish v3: Realm properties and configuration error
Asked Answered
Solved mysqlservletsjdbcglassfish-3jdbcrealm
R

2

8

I have the following database schema (MySQL):

enter image description here

My login is a form based authentication system, to which i am trying to create a jdbc realm.


enter image description here

My 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>

And the mappings in 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>

I don't know why but it is not working for me , i get the following :

    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

Am i putting the "accurate" properties based on the database schema that i have? i'd appreciate your help because i couldn't understand what i am doing wrong.

UPDATE

This if for anyone who has the same scenario.

Based on @perissf , GlassFish JDBC Realm doesn't support normalized tables. check @perissf's link for a tutorial on how should the schema be.
However , what i did is: I kept the normalized schema and created a MySQL "view" that contains all required columns : ie columns [username, pass, group_name]

-->And i modified the Jdbc properties as follow: enter image description here

The reason behind using a "view" is that , as @Matt Handy said "JdbcRealm requires that the User Name Column name needs to be the same in the User table and in the Group Table" So this creates duplicate data in my case, thus i used a view.

Reinaldoreinaldos answered 20/2, 2012 at 3:56 Comment(0)
D
8

Unfortunately, GlassFish JDBC Realm doesn't support normalized tables for users and groups. I guess that your problem is there. Check out this tutorial and modify your tables accordingly.

Disseise answered 20/2, 2012 at 8:25 Comment(5)
thx, ur absolutely right i tried your tutorial it worked. However check my UPDATE section, i ended up creating a view and using it (so that i don't have to remove normalization). Do you think using a view has an overhead, or is a bad practice in this case? – Reinaldoreinaldos
On the contrary, I think that using a view is a smart idea, and I am glad to see that it works! – Disseise
@shadesco perissf I wish I could up vote you both a million times, I've been wrestling with this garbage for days. Thank you! – Aluminium
@Aluminium my pleasure bro, Glad your problem was solved cheers! – Reinaldoreinaldos
I am having issue with migration from 4.0 to 4.1.1 #40687237 – Dinodinoflagellate
F
1

In addition to perissf's answer: JdbcRealm requires that the User Name Column name needs to be the same in the User table and in the Group Table (which is the table that assingns users to groups). So your table user_info needs a column userName instead of user_id.

Fm answered 20/2, 2012 at 9:18 Comment(2)
thank your for your help . Check my UPDATE section, i ended up creating a view and using it (so that i don't have to remove normalization). Do you think using a view has an overhead, or is a bad practice in this case? – Reinaldoreinaldos
That sounds good. Is it possible for a user to be member of more than one group with your solution? – Fm

© 2022 - 2024 — McMap. All rights reserved.