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:


Database
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
<next>
Scroll down to additional properties:
DatabaseName: security
User: security
Password: security
ServerName: localhost

NB! Delete all other properties.

<save>
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
<OK>

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
);
and

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)
            ON DELETE CASCADE ON UPDATE RESTRICT
    );
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..

Realm
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
<OK>

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

web.xml
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/*
<OK>

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!