I want to understand form based security and JDBC realms with glassfishV3, so i decided to create a little app that just allows to sign in and out, i followed the instructions from this book to do so.
I understand how the hold thing works, but something is wrong and i cant get it to work properly.
What i did first was create a little database with JPA annotations:
@Entity
@Table(name="USERS")
public class User implements Serializable {
private static final long serialVersionUID = -1244856316278032177L;
@Id
@GeneratedValue
@Column(nullable = false)
private Long id;
@Column(nullable = false)
private String email;
@Column(nullable = false)
private String password;
@OneToMany(mappedBy = "user")
private List<Group> groups;
//GET & SET METHODS...
}
Here the other table that holds the roles for each user
@Entity
@Table(name="GROUPS")
public class Group implements Serializable {
private static final long serialVersionUID = -7274308564659753174L;
@Id
@GeneratedValue
@Column(nullable = false)
private Long id;
@Column(nullable = false)
private String groupName;
@ManyToOne
@JoinColumn(name = "USERS_ID", nullable = false)
private User user;
//GET & SET METHODS...
}
When the DB was ready i added some data manually
The next step was to configure a security realm.
Then added the security configuration to my web.xml file
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
version="3.0">
<display-name>CHAPTER x 12 Container Managed Authentication and
Authorization</display-name>
<welcome-file-list>
<welcome-file>index.xhtml</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>Faces Servlet</servlet-name>
<servlet-class>javax.faces.webapp.FacesServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>Faces Servlet</servlet-name>
<url-pattern>*.xhtml</url-pattern>
</servlet-mapping>
<security-constraint>
<web-resource-collection>
<web-resource-name>VISITOR PERMISIONS</web-resource-name>
<url-pattern>/index.xhtml</url-pattern>
<url-pattern>/visitorpanel.xhtml</url-pattern>
<http-method>GET</http-method>
<http-method>POST</http-method>
</web-resource-collection>
<auth-constraint>
<role-name>visitors</role-name>
<role-name>users</role-name>
<role-name>administrators</role-name>
</auth-constraint>
</security-constraint>
<security-constraint>
<web-resource-collection>
<web-resource-name>USERS PERMISIONS</web-resource-name>
<url-pattern>/userpanel.xhtml</url-pattern>
<url-pattern>/index.xhtml</url-pattern>
<url-pattern>/visitorpanel.xhtml</url-pattern>
<http-method>GET</http-method>
<http-method>POST</http-method>
</web-resource-collection>
<auth-constraint>
<role-name>users</role-name>
<role-name>administrators</role-name>
</auth-constraint>
</security-constraint>
<security-constraint>
<web-resource-collection>
<web-resource-name>ADMIN PERMISIONS</web-resource-name>
<url-pattern>/adminpanel.xhtml</url-pattern>
<url-pattern>/userpanel.xhtml</url-pattern>
<url-pattern>/index.xhtml</url-pattern>
<url-pattern>/visitorpanel.xhtml</url-pattern>
<http-method>GET</http-method>
<http-method>POST</http-method>
</web-resource-collection>
<auth-constraint>
<role-name>administrators</role-name>
</auth-constraint>
</security-constraint>
<login-config>
<auth-method>FORM</auth-method>
<realm-name>DBRealm</realm-name>
<form-login-config>
<form-login-page>/index.xhtml</form-login-page>
<form-error-page>/error.xhtml</form-error-page>
</form-login-config>
</login-config>
<security-role>
<role-name>visitors</role-name>
</security-role>
<security-role>
<role-name>users</role-name>
</security-role>
<security-role>
<role-name>administrators</role-name>
</security-role>
</web-app>
My objectives here were:
administrators can see all pages
visitors can see only index.xhtml and visitorpanel.xhtml
users can see index.xhtml,visitorpanel.xhtml and userpanel.xhtml
I think the configuration is correct.
Finally the last step was to create the login form in the index.xhtml page:
<form method="post" action="j_security_check" name="loginForm">
<h:outputLabel id="userNameLabel" for="j_username" value="Enter your em@il:"/>
<h:inputText id="j_username" autocomplete="off" />
<br/>
<h:outputLabel id="passwordLabel" for="j_password" value="Enter your em@il password:"/>
<h:inputSecret id="j_password" autocomplete="off"/>
<br/>
<h:commandButton type="submit" value="Login"/>
<h:commandButton type="reset" value="Clear"/>
</form>
The program builds fine, but i have the following problems:
1- When i try to login as user or as administrator(visitors don't need to login), i get redirected to the error.xhtml page and in the console i see an exception:
SEVERE: SEC1112: Cannot validate user [[email protected]] for JDBC realm. WARNING: Web login failed: Login failed: javax.security.auth.login.LoginException: Security Exception WARNING: PWC4011: Unable to set request character encoding to UTF-8 from context /CHAPTER_12_x_Container_Managed_Authentication_and_Authorization, because request parameters have already been read, or ServletRequest.getReader() has already been called
2- When i try to navigate to some of the pages via the URL, nothing happens. I think that is ok, but when i try to visit visitorpanel.xhtml, it should let me, because there is no need to be logged in to see it. Do i need to remove that page from the security configuration if a want every body to see it?
3- Also i am curious why i cant use the tag h:form instead of just form, when i implement the login?
Ill really appreciate some help, ive been a few hours reading the first chapters of the book and trying to implement, my own example but i got stuck. I think i am close to the solution.
Update
I changed the Default principal to be the visitors user name. But it still don't work
And i also added some more options to my Realm configuration
But when i try to login i still see an exception that says:
SEVERE: SEC1112: Cannot validate user [[email protected]] for JDBC realm. WARNING: Web login failed: Login failed: javax.security.auth.login.LoginException: Security Exception WARNING: PWC4011: Unable to set request character encoding to UTF-8 from context /CHAPTER_12_x_Container_Managed_Authentication_and_Authorization, because request parameters have already been read, or ServletRequest.getReader() has already been called
I still don't know what is missing.
-Could it be that the table name should not be upper case?
-Could it be that the columns names should not be upper case?
-Could it be that the tables are created wrong?
-Could it be that i cant use PASSWORD as a column name, because it makes some kind of conflict?
I really don't understand why that exception. I pinged the database from the admin panel, and all seem to be correct.
Can someone help me figuring this one out?
Update 2
I changed the 'javax.enterprise.system.core.security' logging option to level FINE, to have more information when exceptions occur, this was the result when i tried to login:
FINE: Intercept Entry: intercept: SOAP defaultServerID: null defaultClientID: null FINE: ID Entry: module class: com.sun.xml.wss.provider.ClientSecurityAuthModule id: XWS_ClientProvider type: client request policy: javax.security.auth.message.MessagePolicy@e95a72 response policy: javax.security.auth.message.MessagePolicy@310a6d options: {signature.key.alias=s1as, debug=false, dynamic.username.password=false, encryption.key.alias=s1as} FINE: ID Entry: module class: com.sun.xml.wss.provider.ClientSecurityAuthModule id: ClientProvider type: client request policy: javax.security.auth.message.MessagePolicy@1829770 response policy: javax.security.auth.message.MessagePolicy@a4461e options: {signature.key.alias=s1as, debug=false, dynamic.username.password=false, encryption.key.alias=s1as, security.config=C:\jeeAplicationServer\glassfishv3\glassfish\domains\domain1/config/wss-server-config-1.0.xml} FINE: ID Entry: module class: com.sun.xml.wss.provider.ServerSecurityAuthModule id: XWS_ServerProvider type: server request policy: javax.security.auth.message.MessagePolicy@f79c86 response policy: javax.security.auth.message.MessagePolicy@454bf7 options: {signature.key.alias=s1as, debug=false, encryption.key.alias=s1as} FINE: ID Entry: module class: com.sun.xml.wss.provider.ServerSecurityAuthModule id: ServerProvider type: server request policy: javax.security.auth.message.MessagePolicy@17e85e4 response policy: javax.security.auth.message.MessagePolicy@1887906 options: {signature.key.alias=s1as, debug=false, encryption.key.alias=s1as, security.config=C:\jeeAplicationServer\glassfishv3\glassfish\domains\domain1/config/wss-server-config-1.0.xml} FINE: [Web-Security] Setting Policy Context ID: old = null ctxID = CHAPTER_x_12_Container_Managed_Authentication_and_Authorization/CHAPTER_x_12_Container_Managed_Authentication_and_Authorization FINE: [Web-Security] hasUserDataPermission perm: (javax.security.jacc.WebUserDataPermission /j_security_check POST) FINE: [Web-Security] hasUserDataPermission isGranted: true FINE: Logging in user [[email protected]] into realm: DBRealm using JAAS module: jdbcRealm FINE: Login module initialized: class com.sun.enterprise.security.auth.login.JDBCLoginModule SEVERE: SEC1112: Cannot validate user [[email protected]] for JDBC realm. FINE: Cannot validate user javax.security.auth.login.LoginException: Unable to connect to datasource jdbc/security for database user user. at com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm.getConnection(JDBCRealm.java:550) at com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm.isUserValid(JDBCRealm.java:393) at com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm.authenticate(JDBCRealm.java:311) 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.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 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:662) Caused by: javax.naming.NamingException: Lookup failed for 'jdbc/security' in SerialContext [Root exception is javax.naming.NameNotFoundException: security not found] at com.sun.enterprise.naming.impl.SerialContext.lookup(SerialContext.java:442) at javax.naming.InitialContext.lookup(InitialContext.java:392) at javax.naming.InitialContext.lookup(InitialContext.java:392) at com.sun.enterprise.connectors.service.ConnectorResourceAdminServiceImpl.lookup(ConnectorResourceAdminServiceImpl.java:203) at com.sun.enterprise.connectors.ConnectorRuntime.lookupNonTxResource(ConnectorRuntime.java:440) at com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm.getConnection(JDBCRealm.java:538) ... 44 more Caused by: javax.naming.NameNotFoundException: security not found at com.sun.enterprise.naming.impl.TransientContext.doLookup(TransientContext.java:197) at com.sun.enterprise.naming.impl.TransientContext.lookup(TransientContext.java:168) at com.sun.enterprise.naming.impl.TransientContext.lookup(TransientContext.java:172) at com.sun.enterprise.naming.impl.SerialContextProviderImpl.lookup(SerialContextProviderImpl.java:58) at com.sun.enterprise.naming.impl.LocalSerialContextProviderImpl.lookup(LocalSerialContextProviderImpl.java:101) at com.sun.enterprise.naming.impl.SerialContext.lookup(SerialContext.java:430) ... 49 more
FINE: JAAS authentication aborted. WARNING: Web login failed: Login failed: javax.security.auth.login.LoginException: Security Exception FINE: [Web-Security] Policy Context ID was: CHAPTER_x_12_Container_Managed_Authentication_and_Authorization/CHAPTER_x_12_Container_Managed_Authentication_and_Authorization FINE: [Web-Security] hasUserDataPermission perm: (javax.security.jacc.WebUserDataPermission /error.xhtml GET) FINE: [Web-Security] hasUserDataPermission isGranted: true
Update 3
Maybe there is something wron with the connection pool. This is how my connection pool looks like:
I don't have much properties, maybe something is missing?
Also now i created a JDBC resource, that looks like this:
(The JNDI name in the Realm, was changed to jdbc/studydb)
My persistence.xml looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="CHAPTER x 12 Container Managed Authentication and Authorization">
<jta-data-source>jdbc/studydb</jta-data-source>
<class>entities.User</class>
<class>entities.Group</class>
</persistence-unit>
</persistence>
I think i made some progress, now the exception i see is:
> SEVERE: jdbcrealm.invaliduserreason
> FINE: Cannot validate user
> java.sql.SQLSyntaxErrorException: Schema 'ADMIN' does not exist
> at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> ....
>
> Caused by: org.apache.derby.client.am.SqlException: Schema 'ADMIN' does not exist
> at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
> ...
> FINE: JAAS authentication aborted.
> WARNING: Web login failed: Login failed: javax.security.auth.login.LoginException: Security Exception
> FINE: [Web-Security] Policy Context ID was: CHAPTER_x_12_Container_Managed_Authentication_and_Authorization/CHAPTER_x_12_Container_Managed_Authentication_and_Authorization
> FINE: [Web-Security] hasUserDataPermission perm: (javax.security.jacc.WebUserDataPermission /error.xhtml GET)
> FINE: [Web-Security] hasUserDataPermission isGranted: true
> WARNING: PWC4011: Unable to set request character encoding to UTF-8 from context
> /CHAPTER_12_x_Container_Managed_Authentication_and_Authorization,
> because request parameters have already been read, or
> ServletRequest.getReader() has already been called
Update 4
I changed the database, it was wrongly organized so i made some changes in my entities:
@Entity
@Table(name="USERS", schema="ADMIN")
public class User implements Serializable {
private static final long serialVersionUID = -1244856316278032177L;
@Id
@Column(nullable = false)
private String userid;
@Column(nullable = false)
private String password;
@ManyToOne
@JoinTable(name="USER_GROUP",schema="ADMIN", joinColumns = @JoinColumn(name="userid", referencedColumnName="userid"), inverseJoinColumns=@JoinColumn(name="groupid", referencedColumnName= "groupid") )
private Group group;
//GET & SET METHODS
@Entity @Table(name="GROUPS", schema="ADMIN") public class Group implements Serializable {
private static final long serialVersionUID = -7274308564659753174L;
@Id
@Column(nullable = false)
private String groupid;
@OneToMany(mappedBy="group")
private Set<User> users;
//GET & SET METHODS
So i had also to edit the DBRealm, now it looks like this:
But when i login i get again an exception:
FINE: [Web-Security] Policy Context ID was: CHAPTER_x_12_Container_Managed_Authentication_and_Authorization/CHAPTER_x_12_Container_Managed_Authentication_and_Authorization
FINE: [Web-Security] hasUserDataPermission perm: (javax.security.jacc.WebUserDataPermission /j_security_check POST)
FINE: [Web-Security] hasUserDataPermission isGranted: true
FINE: Logging in user [[email protected]] into realm: DBRealm using JAAS module: jdbcRealm
FINE: Login module initialized: class com.sun.enterprise.security.auth.login.JDBCLoginModule
SEVERE: SEC1111: Cannot load group for JDBC realm user [[email protected]].
FINE: Cannot load group
java.sql.SQLSyntaxErrorException: Column 'USERID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'USERID' is not a column in the target table.
....
....
Caused by: org.apache.derby.client.am.SqlException: Column 'USERID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'USERID' is not a column in the target table.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
....
....
FINE: JAAS login complete.
FINE: JAAS authentication committed.
FINE: Password login succeeded for : [email protected]
FINE: permission check done to set SecurityContext
FINE: Set security context as user: [email protected]
FINE: [Web-Security] Policy Context ID was: CHAPTER_x_12_Container_Managed_Authentication_and_Authorization/CHAPTER_x_12_Container_Managed_Authentication_and_Authorization
FINE: [Web-Security] hasUserDataPermission perm: (javax.security.jacc.WebUserDataPermission GET)
FINE: [Web-Security] hasUserDataPermission isGranted: true
FINE: permission check done to set SecurityContext
FINE: SecurityContext: setCurrentSecurityContext method called
FINE: [Web-Security] Policy Context ID was: CHAPTER_x_12_Container_Managed_Authentication_and_Authorization/CHAPTER_x_12_Container_Managed_Authentication_and_Authorization
FINE: [Web-Security] hasUserDataPermission perm: (javax.security.jacc.WebUserDataPermission /adminpanel.xhtml GET)
FINE: [Web-Security] hasUserDataPermission isGranted: true
FINE: [Web-Security] Policy Context ID was: CHAPTER_x_12_Container_Managed_Authentication_and_Authorization/CHAPTER_x_12_Container_Managed_Authentication_and_Authorization
FINE: [Web-Security] Generating a protection domain for Permission check.
FINE: [Web-Security] Checking with Principal : [email protected]
FINE: [Web-Security] Checking with Principal : visitors
FINE: [Web-Security] Checking with Principal : users
FINE: [Web-Security] Checking with Principal : administrators
FINE: [Web-Security] Codesource with Web URL: file:/CHAPTER_x_12_Container_Managed_Authentication_and_Authorization/CHAPTER_x_12_Container_Managed_Authentication_and_Authorization
FINE: [Web-Security] Checking Web Permission with Principals : [email protected], visitors, users, administrators
FINE: [Web-Security] Web Permission = (javax.security.jacc.WebResourcePermission /adminpanel.xhtml GET)
FINE: [Web-Security] hasResource isGranted: true
FINE: [Web-Security] hasResource perm: (javax.security.jacc.WebResourcePermission /adminpanel.xhtml GET)
FINE: SecurityContext: setCurrentSecurityContext method called
WARNING: Resource not found: com/sun/enterprise/v3/admin/adapter/theme/com/sun/webui/jsf/suntheme/images/masthead/masthead_button_over.gif
There are a few missing bits in your configuration:
On the topic of using form
instead of h:form
, the underlying reason is that the JSF runtime would not allow you to specify the action
attribute of the h:form
tag. This value is set by the JSF runtime, when encoding the response, and therefore, you'll be unable to specify the value of j_security_check
when you use the h:form
tag. The documentation states this quite explicitly:
The value of the "action" attribute must be the result of passing the view identifier of the current view to the
getActionURL()
method of theViewHandler
for this application, then passing that String to theencodeActionURL()
method on theExternalContext
.
Update
Based on the posted stack trace, I could infer that the JNDI datasource (specified in the JNDI field) to be used by the Realm, is not available in the Glassfish domain. One of the prerequisites for the JDBC Realm is to have a JNDI datasource registered in the Glassfish domain configuration, whose connection pool is used to connect to the underlying database.
Following is an snippet of my Glassfish domain configuration file (domain.xml
) where a connection pool (GalleriaPool) is used by a JNDI DataSource (jdbc/galleriaDS), that is eventually used by the JDBC Realm (GalleriaRealm):
<domain log-root="${com.sun.aas.instanceRoot}/logs" application-root="${com.sun.aas.instanceRoot}/applications" version="12">
<resources>
...
<jdbc-connection-pool validation-table-name="SYSIBM.SYSDUMMY1" driver-classname="" datasource-classname="org.apache.derby.jdbc.ClientDataSource40" res-type="javax.sql.DataSource" description="" name="GalleriaPool" is-connection-validation-required="true" fail-all-connections="true" ping="true">
<property name="User" value="APP"></property>
<property name="DatabaseName" value="GALLERIA"></property>
<property name="RetrieveMessageText" value="true"></property>
<property name="CreateDatabase" value="true"></property>
<property name="Password" value="APP"></property>
<property name="ServerName" value="localhost"></property>
<property name="Ssl" value="off"></property>
<property name="SecurityMechanism" value="4"></property>
<property name="TraceFileAppend" value="false"></property>
<property name="TraceLevel" value="-1"></property>
<property name="PortNumber" value="1527"></property>
<property name="LoginTimeout" value="0"></property>
</jdbc-connection-pool>
<jdbc-resource pool-name="GalleriaPool" description="" jndi-name="jdbc/galleriaDS"></jdbc-resource>
</resources>
<servers>
<server name="server" config-ref="server-config">
...
<resource-ref ref="jdbc/galleriaDS"></resource-ref>
</server>
</servers>
...
<configs>
<config name="server-config">
...
<security-service>
<auth-realm name="GalleriaRealm" classname="com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm">
<property name="jaas-context" value="jdbcRealm"></property>
<property name="encoding" value="Hex"></property>
<property name="password-column" value="PASSWORD"></property>
<property name="datasource-jndi" value="jdbc/galleriaDS"></property>
<property name="group-table" value="USERS_GROUPS"></property>
<property name="charset" value="UTF-8"></property>
<property name="user-table" value="USERS"></property>
<property name="group-name-column" value="GROUPID"></property>
<property name="digest-algorithm" value="SHA-512"></property>
<property name="user-name-column" value="USERID"></property>
</auth-realm>
...
</security-service>
</config>
...
</configs>
...
</domain>
Update #2 - Getting the SQL statements executed by the JDBC Realm against Derby
It looks like the structure of the SQL query does not match the database model that you've prepared. You could take a look at the SQL statements executed by the JDBC realm against the Derby instance via the derby.language.logStatementText
system property. This property can be set to true
as a static value in the derby.properties
file, and it will take effect when you restart the Derby instance. The derby.properties
file needs to have the following entry:
derby.language.logStatementText=true
and this file must be placed in the current working directory of the Derby instance. The current working directory is often the directory that contains the Derby databases, and can be explicitly specified using the derby.system.home
JVM argument, during Derby startup:
-Dderby.system.home=C:\derby
All SQL statements executed by Derby, will now be logged in the derby.log
file.
Based on the provided information, I'm under the impression that there is a separate table called GROUPS
which is used to store the Group info, and this is different from the join table - USER_GROUP
. In this scenario, your realm must be configured to have the Group table as USER_GROUP
and not GROUP
; you can confirm this by looking at the SQL queries issued by the JDBC realm.
To clarify the above point, the Group field in the JDBC realm configuration is not used to specify the table that stores the group information. Instead, it is used to specify the table that stores the Group-User mappings. In a 1:1 mapping, the Group table can store this information, but in a 1:M or typically in a M:M scenario, you would have a separate table that contains the mapping. The SQL query issued by the JDBC realm, uses the mapping table and not the actual group table (if they're different) to determine the groups that a user belongs to.