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!

16 comments:

Anonymous said...

Hi Brian
Thank you for a very good blog, I have found it most useful.
I am using Netbeans6.8, Java EE6, JSF2 and GlassfishV3, MySql.
However when I try the steps and I get to the realm mapping in Netbeans in the web config, I get stuck. Our users are contained in a MySql database. What does the following mean to me; "".

When I go to sun-web.xml and then to the Security Role Mapping, I have to enter a principal (Add Principal). Who is the principal in my database?

As the name of the principal must match the name specified on Glassfish - I am to put my table, column name in? Surely I can not go through this process for each user that registers to login.
Any help and advice would be greatly appreciated.

Kind regards
Andre-JHB

Anonymous said...

Hi Brian
Thank you for a very good blog, I have found it most useful.
I am using Netbeans6.8, Java EE6, JSF2 and GlassfishV3, MySql.
However when I try the steps and I get to the realm mapping in Netbeans in the web config, I get stuck. Our users are contained in a MySql database. What does the following mean to me; "".

When I go to sun-web.xml and then to the Security Role Mapping, I have to enter a principal (Add Principal). Who is the principal in my database?

As the name of the principal must match the name specified on Glassfish - I am to put my table, column name in? Surely I can not go through this process for each user that registers to login.
Any help and advice would be greatly appreciated.

Kind regards
Andre-JHB

Anonymous said...

Hi Brian
Thank you for a very good blog, I have found it most useful.
I am using Netbeans6.8, Java EE6, JSF2 and GlassfishV3, MySql.
However when I try the steps and I get to the realm mapping in Netbeans in the web config, I get stuck. Our users are contained in a MySql database. What does the following mean to me; "".

When I go to sun-web.xml and then to the Security Role Mapping, I have to enter a principal (Add Principal). Who is the principal in my database?

As the name of the principal must match the name specified on Glassfish - I am to put my table, column name in? Surely I can not go through this process for each user that registers to login.
Any help and advice would be greatly appreciated.

Kind regards
Andre-JHB

Amr said...

It goes fine w/ no problem until I come to the point of testing my new html file under test folder although it asks me for username & password as expected yet I can not know what is the password for admin user!! Pls help.

Amr said...

What is the password I can use to access secured html files under test folder for username admin?

Brian Silberbauer said...

Hi Amr

Sorry to get back to you so late, the password is 'admin'. For future reference, a google search brought that back for me :)

Sebastian Ovide said...

Hi All,

is there any way to map roles to groups using a DB table (dynamically) ? or otherwise groups to groups ?

say for example of having a role called "CAN_EDIT" and one called "CAN_DELETE" and one group called "POWER_USERS"... so we could assigne POWER_USERS to both roles.... etc...

Thanks

Eddie said...

I have the same question as Sebastian Ovide.

Beyond Good and Evil said...

I have followed this to the T and when I went to the /test/ I get the userName and password diag but admin/admin doesn't work, I have added new users and they don't work either, I have used MD5 hash and plain text passwords. Can someone please help me figure this out???

Anonymous said...

To everyone, who is having the same issues as Beyond Good and Evil:
Use Netbeans 7.0 instead of 6.9. I used 6.9 and it has not executed my sql-commands correctly (but it has pretended that they were successful).
So there where no user-entrys in the users-table.
Upgrading to Netbeans 7.0 and executing the INSERT-querys again solved the problem for me.

Gurnah said...

Hi Brian,

I followed all the instruction very accurately, but still can login..

i cant paste anything here... http://forums.oracle.com/forums/thread.jspa?messageID=9736183#9736183

Unknown said...

Nice. Finally a JDBCrealm guide for glassfish that is pretty complete. Most had things missing. Now I am just going to have to get it to work with mysql. Thanks.

Unknown said...

Got it to work with mysql. Complete this tutorial first, make sure it works and then do the following.


Open glassfish admin page, go to Resourses>JDBC>ConnectionPools and select security which was created in your tutorial.
Change Datasource Classname to com.mysql.jdbc.jdbc2.optional.MysqlDataSource and hit save.
Create a new mysql database called security.
Then excute the following on the security database:

CREATE TABLE IF NOT EXISTS `usertable` (
`username` VARCHAR(128) NOT NULL ,
`password` VARCHAR(128) NOT NULL ,
PRIMARY KEY (`username`) ,
UNIQUE INDEX `username_UNIQUE` (`username` ASC) )
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `grouptable` (
`groupid` VARCHAR(128) NOT NULL ,
`username` VARCHAR(128) NOT NULL ,
INDEX `USER_FK` (`username` ASC) ,
INDEX `GROUP_PK` (`groupid` ASC, `username` ASC) ,
CONSTRAINT `USER_FK`
FOREIGN KEY (`username` )
REFERENCES `usertable` (`username` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

Once this is done put data into it.

insert into usertable(username,password) values ('mysql', '81c3b080dad537de7e10e0987a4bf52e');
insert into grouptable(username,groupid) values ('mysql', 'USER');
insert into grouptable(username,groupid) values ('mysql', 'ADMIN');

Navigate to http://localhost:4747/WebApplicationJDBC-test/test/

username: mysql
password: mysql

Cheers

Gabriel said...

it doesnt work for me :S glassfish 3.0.1, netbeans 6.9.1 JSF 2.0 and DB in postgres9

testblog said...

Hi

Can you provide jdbcRealm configuration for EJB based web service application?





Anonymous said...

If you follow this and most other tutorials you will such a solution. Yes it is a solution, but you should take care of what you develop in terms a creating database tables. Therefore the showed solution contrasts to any normal form. Either you should use a many to many relation and use the realted table to configure the Realm. I think that this is what most tutorials will tell us, but it is more confusing than a realy helpful. It would be better to write two slides more and get a full working solution. The official tutorial from oracle is hard to read but shows it correctly. Nevertheless thanks for this very good introduction.