04 November 2009

JDBC Realm Setup with Glassfish v3 (build 70) and Netbeans 6.8 (beta) JEE6

As I am about to undertake this and I can't find instructions using the glassfish GUI admin, I might as well blog it:

Create Database
I'm going to use the Derby/JavaDB database that came with Netbeans:

Click on the 'services' tab in Netbeans
Right click on JavaDB under Databases and select 'Create Database..'
Input details for the database, I used 'security' for database name , username and password (ironically).

Create Connection Pool for Database
Then in the Glassfish admin panel:
Resources->JDBC->Connection Pools, select 'New'
Name: security
Resource Type: javax.sql.DataSource
Database Vendor: JavaDB
Scroll down to additional properties:
DatabaseName: security
User: security
Password: security
ServerName: localhost

NB! Delete all other properties.

then try 'ping' it

make sure your database is up and running, compare properties to other loaded connection pools..

Create JDBC Resource
In the Glassfish admin panel:
Resources->JDBC->JDBC resources, select 'new'
JNDI Name: jdbc/security
Pool Name: security

Create Tables
In Netbeans 'Services' tab:
Database->jdbc:derby://localhost:1527/security, right click and select 'Execute Command'

create table usertable (
        username varchar(128) NOT NULL CONSTRAINT USER_PK PRIMARY KEY ,
        password varchar(128) NOT NULL

create table grouptable(
        username varchar(128) NOT NULL,
        groupid  varchar(128) NOT NULL,
        CONSTRAINT GROUP_PK PRIMARY KEY(username, groupid),
        CONSTRAINT USER_FK FOREIGN KEY(username) REFERENCES usertable(username)
and populate:

insert into usertable(username,password) values ('admin', '21232f297a57a5a743894a0e4a801fc3');
insert into grouptable(username,groupid) values ('admin', 'USER');
insert into grouptable(username,groupid) values ('admin', 'ADMIN');

scripts modified slightly from http://blogs.sun.com/foo/resource/createschema.sql from the article http://blogs.sun.com/foo/entry/mort_learns_jdbc_realm_authentication

Which is pretty much what I am doing..

Create New
In the Glassfish admin panel:
Configuration->Security->Realm, select <new>
Name: security
Class Name: the one with 'jdbc' in it

JAAS Context: jdbcRealm
JNDI: jdbc/security
User Table: usertable
User Name Column: username
Password Column: password
Group Table: grouptable
Group Name Column: groupid
Digest Algorithm: MD5

time to test..

Web Config
Roll Mapping
In Netbeans in the projects tab:
<your war project>->Configuration Files->sun-web.xml and open it
click on <security>
<Add Security Role Mapping>
Security Role Name: USER
<Add Group>
Group Name: USER

and do the same for the admin role/group

As I am using JEE 6, I have had no need for the web.xml file as yet, so now I need to create it:
right click on the war project
new->other->Web->Standard Deployment Descriptor

now edit it:
select the 'Security' button/tab
under 'Login Configuration' select Basic
Realm Name: security

then under Security Roles <Add..>
Role Name: USER
do the same for ADMIM role

Now <Add Security Constraint>
Display Name: Test Constraint

Web Resource Collection <Add..>
Resource Name: test
URL Pattern(s): /test/*

tick 'Enable Authentication Constraint'
Role Name(s): USER

and save..

create a directory 'test' with a jsp file in it and try access it..

Worked for me, yay me!