Securing J[2]EE applications, part 3

In this third article, the samples are modified to be run on JBoss (4.0.3+) and JavaDB (Derby version 10.2).

The first article showed how to setup Glassfish authentication with only a single database table.

The second article evovled the simple setup into a more mature setup, where users, logins and roles/groups are separated.

JBoss

A couple of years ago (before Glassfish/Sun application server was open-sourced), I spend some time setting up my own JBoss instances for testing and hobby projects. JBoss back then was pretty much “state-of-the-art” and more.

Then, JBoss (and Hibernate :-)) was “overtaken” by Red Hat and I felt that JBoss more or less stagnated… Why I completely lost interest in JBoss when Glassfish came around. Glassfish seemed to be a much more “mature” product.

This is why the “port” to JBoss is tested with version 4.0.4 only; but I would expect it to work with newer versions too.

The PostgreSQL datasource is configured by placing a recent PostgreSQL JDBC driver (downloaded from http://jdbc.postgresql.org/) in the …/lib directory and placing the following datasource definition file in the …/deploy directory:

<?xml version="1.0" encoding="UTF-8"?>
<!--
Modify url, userid and password and dump into deploy folder 
(along with sectest.ear)
-->
<datasources>
 <local-tx-datasource>
   <jndi-name>testdbDS</jndi-name>
   <connection-url>jdbc:postgresql:testdb</connection-url>
   <driver-class>org.postgresql.Driver</driver-class>
   <user-name>userid</user-name>
   <password>********</password>
   <idle-timeout-minutes>5</idle-timeout-minutes>
 </local-tx-datasource>
</datasources>

This defines the PostgreSQL datasource to a local database named “testdb”, with the userid/password combination given, in JNDI named “java:/testdbDS”.

The next section should be copy’n’pasted into the conf/login-config.xml file to define the “sectest” security domain:

<!--
To be inserted into conf/login-config.xml 
 -->
<application-policy name="sectest">
 <authentication>
   <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule"
      flag="required">
      <module-option name="unauthenticatedIdentity">guest</module-option>
      <module-option name="dsJndiName">java:/testdbDS</module-option>
      <module-option name="principalsQuery">
SELECT md5_password FROM v_user WHERE user_id=?
      </module-option>
      <module-option name="rolesQuery">
SELECT group_name, 'Roles' FROM v_group WHERE user_id=?
      </module-option>
      <module-option name="hashAlgorithm">MD5</module-option>
      <module-option name="hashEncoding">hex</module-option>
      <module-option name="ignorePasswordCase">true</module-option>
   </login-module>
 </authentication>
</application-policy>
dsJndiName Name of the datasource to use to get connection to the database
principalsQuery Query to the view/table where input is user-ID (the login user id given by the user) and a single column containing the password as output
rolesQuery Query to the view/table where input is user-ID (the login user id given by the user) and output are the roles/groups the user is connected to. The second column must contain the word ‘Roles’…
hashAlgorithm Algorith to use on the password given before comparing with the output from the principalsQuery
hashEncoding Encoding of the hashed (MD5) password in the database. Hex is the output of the PostgreSQL “md5()” function
ignorePasswordCase We don’t care if the HEX is with lower- or upper-case letters for A..Z

Note that Glassfish supports group to role mapping in the deployment descriptor (sun-web.xml), whereas JBoss does not (jboss-web.xml).

To keep things simple and portable, application developers should try to keep the user-group/role mapping in the database and have a simple mapping in the deployment descriptors. This should help portability between application servers…

JavaDB / apache Derby

Apache Derby is a great database. It looks very much like DB2, it supports transactions, views, stored procedures and lots of other nice stuff not expected in a “simple” open source JAVA database implementation.

Apache Derby is actually a DB2 clone “given” to apache by IBM: Back in the late 90’s a small startup company (Cloudscape) worked on developing a database written entirely in JAVA (those were the days where JAVA was hopelessly immature and lots of companies tried to implement everything in JAVA….). Cloudscape was bought by Informix. Informix was bought by IBM, who after a couple of years realized it could not make any money on Cloudscape and gave it to Apache…

And, for some reason, someone decided that Apache Derby should be included in the JDK from version 6 on (probably because Microsoft has a similar offer for crap-Net).

I don’t know how much IBM should actually be credited for the features of Derby, but this little database is a lot “stronger” than one should think.

I use it for test-setups and for UnitTesting where a “real” DB2 would be “too much”. Derby keeps all files for a single database in a simple directory structure. Clearing all schemas, tables etc. is a simple matter of deleting a directory structure from the file system.

Derby even supports a “read-only” mode, where everything is packed in a ZIP-file. Neat for static “lookup” data!

As mentioned above, Derby supports Transactions with several isolation levels; and the syntax is very close to DB2.

Translating the PostgreSQL scripts to Derby is a matter of “converting” features:

serial Derby does not now of a ‘serial’ data type. The most similar is the INT GENERATED BY DEFAULT AS IDENTITY stanza. The outcome is not a database sequence that can be modified, why the t_login table has as “STARTS WITH 1001”.
bool[ean] Derby does not implement the boolean datatype. A CHAR(1) with values ‘Y’/’N’ is used instead. Scripts and views are modified accordingly.
timestamp Derby does not support length on the timestamp type – those always just map to the java.sql.Timestamp type.
now() This is a PostgreSQL feature. Same in Derby (and a lot of other databases) is: CURRENT_TIMESTAMP
md5() Derby does not have the md5() function, why the actual md5 hex-value of the bytes given by ‘password’ (US-ASCII or ISO-8859-1 encoded) is inserted directly. It is the resposibility of the application to ensure the MD5 and hex digest/encodings why this really should not be a problem.
date Derby does not implement “intelligent typecasting” and does not allow a TIMESTAMP to be put into a DATE column. The valid_from column is defaulted to CURRENT_DATE

This leaves us with some search-replace actions to change the PostgreSQL DDL into Derby DDL. The DDL’s can be found in the bottom of the article, but a few examples are shown below:

-- t_login table
CREATE TABLE t_login
(
  id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1001, INCREMENT BY 1),
  login_id VARCHAR(30) NOT NULL,
  md5_password CHAR(32) NOT NULL,
  enabled CHAR(1) NOT NULL DEFAULT 'Y',
  id_user INT,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by INT NOT NULL,
  CONSTRAINT pk_login PRIMARY KEY (id),
  CONSTRAINT fk_login_user FOREIGN KEY (id_user)
    REFERENCES t_user (id),
  CONSTRAINT fk_login_created_by FOREIGN KEY (created_by)
    REFERENCES t_login (id),
  CONSTRAINT uk_login_id UNIQUE (login_id)
);
 
ALTER TABLE t_user ADD CONSTRAINT fk_user_created_by
 FOREIGN KEY (created_by) REFERENCES t_user(id);
 
-- ...
 
INSERT INTO t_login 
 (login_id, md5_password, enabled, created_by)
 VALUES ('admin', '5f4dcc3b5aa765d61d8327deb882cf99', 'Y', 1);
 
-- ...
 
-- create connector table
 
CREATE TABLE t_login_group
(
  id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
  id_login INT NOT NULL,
  id_group INT NOT NULL,
  valid_from DATE NOT NULL DEFAULT CURRENT_DATE,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by INT NOT NULL,
  CONSTRAINT pk_login_group PRIMARY KEY (id),
  CONSTRAINT fk_login_group_login FOREIGN KEY (id_login)
    REFERENCES t_login (id),
  CONSTRAINT fk_login_group_group FOREIGN KEY (id_group)
    REFERENCES t_group (id),
  CONSTRAINT fk_login_group_created_by FOREIGN KEY (created_by)
    REFERENCES t_login (id),
  CONSTRAINT uk_login_group_ids UNIQUE (id_login, id_group)
);
 
-- ...
 
-- create group view
 
CREATE VIEW v_group AS
SELECT l.login_id AS user_id, g.group_name 
FROM t_login_group lg, t_login l, t_group g
WHERE lg.valid_from <= CURRENT_DATE AND lg.id_group=g.id 
AND lg.id_login=l.id AND l.enabled='Y' AND g.enabled='Y';

Background information

Nowadays, Oracle bought Sun. Sun owns JAVA and MySQL. Owning JAVA also means having strong influence on JavaDB aka Derby aka IBM Cloudscape. Most of the JCA and EJB and lots of other J[2]EE stuff was actually engineered by IBM, with BEA being the most important competitor. BEA later bought by… Oracle!

Does this mean that Oracle owns the closed-source (Oracle) AND open-source (Derby+MySql) database market; the closed-source (BEA, OC4J, SunOne) AND open-source (Glassfish) application server market?

I hope not – that would be the end of the happy-JAVA days I, as a freelance Enterprise JAVA developer, has experienced…

By the way, please note that even if Sun and PostgreSQL has relations, PostgreSQL is still the most advanced Open Source Database not owned by a proprierary database company…

Source stuff

About Jesper Udby

I'm a freelance computer Geek living in Denmark with my wife and 3 kids. I've done professional software development since 1994 and JAVA development since 1998.
This entry was posted in Databases, Java, Open Source and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *