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.
Comments:

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.

Posted by Quo Vadis on June 02, 2007 at 09:49 PM PDT #

needed ClassName: com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm

Posted by viperomega on June 15, 2007 at 11:19 PM PDT #

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".

Posted by Muyiwa Taiwo on June 16, 2007 at 04:43 PM PDT #

Just to say that this works with Glassfish build b44b. It didn't work with b41b

Posted by Muyiwa Taiwo on June 17, 2007 at 08:15 PM PDT #

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

Posted by Richard Livingstone on September 24, 2007 at 11:10 PM PDT #

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?

Posted by Rob Castellow on February 20, 2008 at 11:09 AM PST #

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.

Posted by battery on November 27, 2008 at 08:24 PM PST #

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.

Posted by Evgeniy on March 14, 2009 at 08:42 PM PDT #

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

Posted by Sdey on July 25, 2009 at 03:49 PM PDT #

Post a Comment:
Comments are closed for this entry.
About

Shing Wai Chan

Search

Categories
Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today