Glassfish 3 security - Form based authentication using a JDBC Realm

Question

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 [admin@gmail.com] 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 [admin@gmail.com] 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 [admin@gmail.com] into realm: DBRealm using JAAS module: jdbcRealm FINE: Login module initialized: class com.sun.enterprise.security.auth.login.JDBCLoginModule SEVERE: SEC1112: Cannot validate user [admin@gmail.com] 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 [user@gmail.com] 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 [user@gmail.com].
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 : user@gmail.com
FINE: permission check done to set SecurityContext
FINE: Set security context as user: user@gmail.com
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 : user@gmail.com
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 : user@gmail.com, 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

Answer 1

There are a few missing bits in your configuration:

• The password is stored in cleartext in the database. This is most likely incorrect. Glassfish 3.1 uses the SHA-256 algorithm by default, so the JDBC Realm would fail to authenticate users since the stored value in the database would not match the digest created by the Realm. You'll need to specify an explicit digest algorithm in the realm configuration, or rely on the default. Also, you'll need to ensure that your application creates the digests when a new user is created or when his/her password is modified. If you want to store the password in plaintext, you must specify the value of "none" for the digest algorithm.
• Specifying the digest algorithm alone is insufficient. You'll need to specify the encoding in which the digest is stored (since, a digest is simply a sequence of bytes, and may not be stored as a plain ASCII sequence). Glassfish supports Hex and Base64 encoding, and uses Hex encoding by default. Your app should therefore apply the same encoding as configured in the realm, before storing the password digest. Note that, when you specify a digest algorithm of "none" to store the passwords in plain text, you need not encode the stored password (and likewise, you need not specify an encoding either); atleast this has been my observation from reading the Glassfish sources.
• Additionally, the user-group mapping appears to be 1:1 at the moment. You might want to use a separate join table to allow for 1:N mappings between groups and users.
• You'll also need to ensure that the option of "default principal to role mapping" is enabled. Without this option, you'll need to manually map the roles in web.xml to users and groups in your realm.

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 the ViewHandler for this application, then passing that String to the encodeActionURL() method on the ExternalContext.

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.

Answer 2

On top of the answer from Vineet , i also noticed that the option security Manager is not checked in the Security tab of GlassFish, that option should be enabled in order to use Security in your Realm.

Another thing if you are using a JDBC JNDI name you don't need to supply a username/password to the JDBC realm

Answer 3

I already did this on Sailfin (based on Glassfish 2).

First, I'm not seeing any sun-web.xml file that maps the roles defined in your web.xml file to the groups defined the database.

Second, according to this tutorial: http://codepimpsdotorg.blogspot.com/2007/12/glassfish-jdbc-realm-authentication.html you must specify a digest algorithm ("none" is not an option, not sure if it remains the same in Glassfish 3).

Third, the tables and columns need to be in a particular order, we are using the following structure:

@Entity
@Table(name = "AuthenticationUser")
public class UserEntity implements Serializable, Identifiable
{   
    private static final long serialVersionUID = -1213555368237839900L;

    @Id
    @Column(name = "name", length = 20)
    private String itsName;

    @Column(name = "password", length = 1024)
    private String itsPassword;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "groupName", referencedColumnName = "name")
    private GroupEntity itsGroupEntity;
...

And

@Entity
@Table(name = "AuthenticationGroup")
public class GroupEntity implements Serializable, Identifiable
{
    private static final long serialVersionUID = -1213554368237839900L;

    private static final String USER_GROUP_COLUMN = "itsGroupEntity";

    @Id
    @Column(name = "name", length = 20)
    private String itsName;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY,
            mappedBy=USER_GROUP_COLUMN)
    private List<UserEntity> itsUsers;
...

And to define the realm:

user-table=AuthenticationUser
user-name-column=name
password-column=password
group-table=AuthenticationUser
group-name-column=groupName

IMPORTANT: The user name and group name are belonging to the same table! so the table AuthenticationGroup is only for our internal use, but is not used by Glassfish.

Answer 4

I ran into the same issue.

I solved this problem by renaming the password to(User_password) and the userName (User_name) fields in the table (anything other than username and password will do), somehow using "userName" and "password" causes some conflict while performing authentication using Realms.

Also put Digest Algorithm: = none in case you are storing the password as plain text.

In the Security menu, Enable the Default Principal to Role Mapping.

Hope this helps,