I have the following database schema (MySQL):
My login is a form based authentication system, to which i am trying to create a jdbc realm.
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.
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:
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.
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.
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With