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.
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
|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
|bool[ean]||Derby does not implement the boolean datatype. A
|timestamp||Derby does not support length on the timestamp type – those always just map to the
|now()||This is a PostgreSQL feature. Same in Derby (and a lot of other databases) is:
|md5()||Derby does not have the
|date||Derby does not implement “intelligent typecasting” and does not allow a
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';
- Informix buys Cloudscape
- IBM buys Informix
- About IBM Informix
- IBM about Cloudscape…
- About Cloudscape…
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 JEE 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…