X

Technology insights, news and tips.

  • Sun
    April 23, 2007

JDBCRealm in GlassFish with MySQL

In these few months, there were several discussions
of using GlassFish JDBCRealm with MySQL. In this blog,
I will share my experience about using GlassFish
JDBCRealm with MySQL.

  1. Download the MySQL Community Server.
    I have downloaded the Solaris 10 (x86, 32 bit TAR package),
    version 5.0.37, of the "MySQL Community Server".

  2. Expand the download file.

    gunzip mysql-5.0.37-solaris10-i386.tar.gz
    tar xf mysql-5.0.37-solaris10-i386.tar

  3. cd mysql-5.0.37-solaris10-i386

    and read INSTALL-BINARY.

  4. Set up the grant table.

    scripts/mysql_install_db

  5. Start the MySQL server.

    bin/mysqld_safe

  6. Set a password for the MySQL "root" user

    bin/mysqladmin -u root password YOUR_PASSWORD

  7. Create database and table. The following is a
    sample command.
    bin/mysql -u root --password=YOUR_PASSWORD
    create database database01;
    use database01;
    create table usertable(userid varchar(10) primary key, password varchar(32) not null);
    create table grouptable(userid varchar(10) not null, groupid varchar(20) not null, primary key (userid, groupid));
    alter table grouptable add constraint FK_USERID foreign key(userid) references usertable(userid);
    commit;
    grant all privileges on *.* to 'root'@'YOUR_HOST' identified by 'YOUR_PASSWORD' with grant option;

    Note that you may like to replace YOUR_PASSWORD and
    YOUR_HOST in above.

  8. Populate user, group and passwor data.
    For the purpose of testing the database, you may try to use
    clear text password first as follows:
    insert into usertable values ('webuser', 'webuser');
    insert into grouptable values ('webuser', 'employee');

    For MD5, please take a look at another blog on JDBCRealm.

  9. Download
    the JDBC driver from Connectors > Connector/J .
    I have downloaded mysql-connector-java-5.0.5-bin.zip

  10. Unpack the package and copy the JDBC driver to $GLASS_HOME/lib.

    unzip mysql-connector-java-5.0.5-bin.jar
    cd mysql-connector-java-5.0.5
    cp mysql-connector-java-5.0.5-bin.jar $GLASSFISH_HOME/lib

  11. Restart the GlassFish server in order to pick up the JBDC driver.

  12. Create a Connector pool in Admin Console as follows:

    NameMySQLPool
    Resource Typejavax.sql.DataSource
    Database VendorMySQL

    then click "Next" and
    add the following properties:

    serverNameYOUR_HOST
    port3306
    databaseNamedatabase01
    userroot
    passwordYOUR_PASSWORD

    Note that different versions of the JDBC driver may
    have different properties. You may need to check the
    readme file there. Furthermore, you may need to remove
    extra default properties from Admin Console.

  13. Create a DataSource jdbc/mysql
    associated with the above pool.

  14. Create a JDBCRealm, named jdbcrealm
    with the following properties:

    datasource-jndijdbc/mysql
    user-tableusertable
    user-name-columnuserid
    password-name-columnpassword
    group-tablegrouptable
    group-name-columngroupid
    jaas-contextjdbcRealm
    digest-algorithmnone

    Note that if you are using MD5 for password data, then you
    need to set value of digest-algorithm to MD5.

  15. Now a JDBCRealm is ready and it can be used by specifying
    it in deployment descriptors.
    If there is anything wrong and cannot authenticate,
    then one can turn on security log to FINE level and
    check if there is any exception in server.log.

Join the discussion

Comments ( 9 )
  • Quo Vadis Sunday, June 3, 2007
    Shing Wai Chan, you rock.
    I spent two days trying to resolve the Glassfish JDBCRealm problem only to discover that in spite of the fact that the ping worked on my connection pool, I had other supposedly inocuous properties set for that pool which caused my connections to fail.
  • viperomega Saturday, June 16, 2007
    needed ClassName:
    com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm
  • Muyiwa Taiwo Saturday, June 16, 2007
    I've tried Shing Wai Chan's procedure with both MySQL and PostgreSQL, with no luck. I'm using Sun Java EE Application Server 9.1 beta 2 (a.k.a. Glassfish v2), and although JAAS security works with fileRealm, it doensn't work with JDBCRealm. I get an ArrayOutOfBoundsException!
    I wonder if anyone else has experienced this, and what I may be doing wrong? The only thing I'm doing differently from what's been posted here is that my realm is NOT named jdbcrealm. Is this mandatory?
    By the way, if you look in domain.xml, you'll see that "password-name-column" above should be "password-column".
  • Muyiwa Taiwo Monday, June 18, 2007
    Just to say that this works with Glassfish build b44b. It didn't work with b41b
  • Richard Livingstone Tuesday, September 25, 2007

    I can get the JDBC realm working ok but no luck in mapping that to the relevant security constraints in web.xml/sun-web.xml - maybe some kind soul might spot what is wrong here as I am a Glassfish newbie (Tomcat, OC4J previously). I can see from the server.log that the logon is being validated fine (see below) but the resource is not granted. I've pasted a snippet from the 2 .xml files which I hope might help as I can't for the life of me see what is wrong here.

    web.xml extract:

    <security-constraint>

    <display-name>PP booking system</display-name>

    <web-resource-collection>



    <web-resource-name>Something</web-resource-name>

    <url-pattern>/booking/\*</url-pattern>

    </web-resource-collection>



    <auth-constraint>

    <role-name>booking</role-name>

    </auth-constraint>

    </security-constraint>

    <security-role>

    <description>Booking constraint</description>

    <role-name>booking</role-name>

    </security-role>

    and later <servlet> and <servlet-mapping elements for the booking servlet which seem to work fine

    sun-web.xml

    <sun-web-app>

    <context-root>/ppui</context-root>

    <security-role-mapping>

    <role-name>booking</role-name>

    <principal-name>democustomer_booking</principal-name>

    <group-name>booking</principal-name>

    </security-role-mapping>

    </sun-web-app>

    Log:

    [#|2007-09-25T13:59:33.671+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.appserv.security.AppservPasswordLoginModule;MethodName=initialize;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|Login module initialized: class com.sun.enterprise.security.auth.login.JDBCLoginModule|#]

    [#|2007-09-25T13:59:35.343+0100|FINEST|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.enterprise.security.auth.login.JDBCLoginModule;MethodName=authenticate;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|JDBC login succeeded for: democustomer_booking groups:[Ljava.lang.String;@7cbe54|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.appserv.security.AppservPasswordLoginModule;MethodName=login;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|JAAS login complete.|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.appserv.security.AppservPasswordLoginModule;MethodName=commit;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|JAAS authentication committed.|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.enterprise.security.auth.LoginContextDriver;MethodName=doPasswordLogin;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|Password login succeeded for : democustomer_booking|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.enterprise.security.auth.LoginContextDriver;MethodName=doPasswordLogin;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|Set security context as user: democustomer_booking|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.web.security.WebSecurityManager;MethodName=setPolicyContext;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|[Web-Security] Policy Context ID was: ppui/ppui|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.web.security.WebSecurityManager;MethodName=checkPermissionWithoutCache;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|[Web-Security] Generating a protection domain for Permission check.|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.web.security.WebSecurityManager;MethodName=checkPermissionWithoutCache;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|[Web-Security] Checking with Principal : democustomer_booking|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.web.security.WebSecurityManager;MethodName=checkPermissionWithoutCache;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|[Web-Security] Checking with Principal : booking|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.web.security.WebSecurityManager;MethodName=checkPermissionWithoutCache;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|[Web-Security] Codesource with Web URL: file:/ppui/ppui|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.web.security.WebSecurityManager;MethodName=checkPermissionWithoutCache;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|[Web-Security] Checking Web Permission with Principals : democustomer_booking, booking|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.web.security.WebSecurityManager;MethodName=checkPermissionWithoutCache;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|[Web-Security] Web Permission = (javax.security.jacc.WebResourcePermission /booking GET)|#]

    [#|2007-09-25T13:59:35.343+0100|FINEST|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.enterprise.security.provider.PolicyWrapper;MethodName=doImplies;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|JACC Policy Provider: PolicyWrapper.implies, context (ppui/ppui)- result was(false) permission ((javax.security.jacc.WebResourcePermission /booking GET))|#]

    [#|2007-09-25T13:59:35.343+0100|FINE|sun-appserver9.1|javax.enterprise.system.core.security|_ThreadID=20;_ThreadName=httpSSLWorkerThread-8085-0;ClassName=com.sun.web.security.WebSecurityManager;MethodName=hasResourcePermission;_RequestID=181440d6-5b0f-4700-a079-f126a90e1a0d;|[Web-Security] hasResource isGranted: false|#]

    I'm using the same web.xml that I previously used with no problems in Tomcat 5.5 eg


  • Rob Castellow Wednesday, February 20, 2008

    Muyiwa, I also receive ArrayIndexOutOfBoundsExceptions....

    SEC5046: Audit: Authentication refused for [rcastell].

    doPasswordLogin fails

    java.lang.ArrayIndexOutOfBoundsException: 31

    I have not found a reasonable solution to this either. My version is v2ur1. Did you find any solution to that problem?


  • battery Friday, November 28, 2008

    I can see from the server.log that the logon is being validated fine (see below) but the resource is not granted. I've pasted a snippet from the 2 .xml files which I hope might help as I can't for the life of me see what is wrong here.


  • Evgeniy Sunday, March 15, 2009

    Hello, I am using GlassFish v2.1-b60 and PostgreSQL 8.3 as RDBMS. I followed your tutorial and made exactly the same tables and configuration of realm (I store now plain passwords and put none in digest field), only realm name is different. Also in connection pool I use org.postgresql.ds.PGConnectionPoolDataSource as datasource classname and javax.sql.ConnectionPoolDataSource as resource type and connection pings (also I use similar connection for normal application need and it works perfectly). But when I try to login it doesn't work. I turned on FINEST on security log and this what I get:

    [Web-Security] Policy Context ID was: JuzyRecipeAdmin/JuzyRecipeAdmin

    [Web-Security] hasUserDataPermission perm: (javax.security.jacc.WebUserDataPermission /sec_recipe.jsp GET)

    [Web-Security] hasUserDataPermission isGranted: true

    [Web-Security] Policy Context ID was: JuzyRecipeAdmin/JuzyRecipeAdmin

    [Web-Security] Codesource with Web URL: file:/JuzyRecipeAdmin/JuzyRecipeAdmin

    [Web-Security] Checking Web Permission with Principals : null

    [Web-Security] Web Permission = (javax.security.jacc.WebResourcePermission /sec_recipe.jsp GET)

    JACC Policy Provider: PolicyWrapper.implies, context (JuzyRecipeAdmin/JuzyRecipeAdmin)- result was(false) permission ((javax.security.jacc.WebResourcePermission /sec_recipe.jsp GET))

    [Web-Security] hasResource isGranted: false

    [Web-Security] hasResource perm: (javax.security.jacc.WebResourcePermission /sec_recipe.jsp GET)

    Processing login with credentials of type: class com.sun.enterprise.security.auth.login.PasswordCredential

    Logging in user [juzyadmin] into realm: JuzySecurity using JAAS module: jdbcRealm

    Login module initialized: class com.sun.enterprise.security.auth.login.JDBCLoginModule

    JAAS authentication aborted.

    SEC5046: Audit: Authentication refused for [juzyadmin].

    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.LoginContextDriver.doPasswordLogin(LoginContextDriver.java:319)

    at com.sun.enterprise.security.auth.LoginContextDriver.login(LoginContextDriver.java:177)

    at com.sun.enterprise.security.auth.LoginContextDriver.login(LoginContextDriver.java:130)

    at com.sun.web.security.RealmAdapter.authenticate(RealmAdapter.java:522)

    at com.sun.web.security.RealmAdapter.authenticate(RealmAdapter.java:462)

    at org.apache.catalina.authenticator.BasicAuthenticator.authenticate(BasicAuthenticator.java:177)

    at com.sun.web.security.RealmAdapter.invokeAuthenticateDelegate(RealmAdapter.java:1216)

    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:643)

    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:625)

    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:593)

    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:94)

    at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:98)

    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:222)

    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:648)

    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:593)

    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:587)

    at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1096)

    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:166)

    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:648)

    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:593)

    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:587)

    at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1096)

    at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:288)

    at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.invokeAdapter(DefaultProcessorTask.java:647)

    at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.doProcess(DefaultProcessorTask.java:579)

    at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.process(DefaultProcessorTask.java:831)

    at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.executeProcessorTask(DefaultReadTask.java:341)

    at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:263)

    at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:214)

    at com.sun.enterprise.web.portunif.PortUnificationPipeline$PUTask.doTask(PortUnificationPipeline.java:380)

    at com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:265)

    at com.sun.enterprise.web.connector.grizzly.ssl.SSLWorkerThread.run(SSLWorkerThread.java:106)

    Caused by: java.lang.SecurityException

    at javax.security.auth.login.LoginContext.invoke(LoginContext.java:857)

    ... 37 more

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

    What problem can be?

    Thank you in advance.


  • Sdey Saturday, July 25, 2009

    I happened to be faced with the same problem. It seems that tablename/columnname attributes are case sensitive. I changed everything to uppercase and JAAS authentication with JDBC authentication provider started working !!


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.