KB: JDBC Realm on Glassfish

Guide for creating a JDBC Security Realm on Glassfish.

Contents

Overview

This guide was created to document the process of creating a JDBC security realm on the Glassfish application server. This article assumes that you already have Netbeans and Glassfish installed on your system and that you will be using JavaDB (Derby) to store your security information.

Start JavaDB

The asadmin utility makes it easy to start and stop the database server. The following command will start JavaDB in network client mode. If you are starting Glassfish on system boot up, you will need to add this command to your init script to make the database available for the JDBC security realm.

[GF_DIR]/bin/asadmin start-database --dbhome [GF_DIR]/databases

Create the Connection Pool

  1. Start Glassfish (domain1):
    [GF_DIR]/bin/asadmin start-domain domain1
  2. Open the Glassfish server administration console e.g. http://localhost:4848.
  3. Navigate to Resources -> JDBC -> Connection Pools.
  4. Click ‘New’.
  5. Set the name and type of pool as shown below and click ‘Next’.

    Create Connection Pool - Step 1

  6. Set the remaining properties as shown below and click ‘Finish’. If you set connectionAttributes to “;create=true”, you will be able to create the database when you ping the connection pool after it is created.

    Create Connection Pool - Step 2

  7. Select the new pool that was just created and click ‘Ping’. A new database will be created and your pool should be accessible.

Create the JDBC Resource

Create a JDBC resource that will be used by the Realm to access the database.

  1. Navigate to Resources -> JDBC -> JDBC Resources.
  2. Click ‘New’.
  3. Set the properties as shown below and click ‘Save’.

    Create JDBC Resource

Database Schema

Create the tables that will store the user and group information. Copy the SQL script below into a file named schema.sql and run it using the JavaDB ij utility:

  [GF_DIR]/javadb/bin/ij
  ij> connect 'jdbc:derby://localhost:1527/openeprs;user=sa;password=changeit';
  ij> run 'schema.sql';
-- User table
CREATE TABLE users (
    userid varchar(32) NOT NULL CONSTRAINT PK_USER PRIMARY KEY,
    password varchar(32) NOT NULL
);

-- group table
CREATE TABLE groups (
    userid varchar(32) NOT NULL,
    groupid varchar(32) NOT NULL,
    CONSTRAINT PK_GROUP PRIMARY KEY(userid, groupid),
    CONSTRAINT FK_USER FOREIGN KEY(userid) REFERENCES users(userid)
);

-- Sample user 'admin' with an encypted password (MD5) of 'admin'
INSERT INTO users (userid, password) VALUES ('admin', '21232f297a57a5a743894a0e4a801fc3');
INSERT INTO groups(userid, groupid) VALUES ('admin', 'Administrators');

Create the Realm

  1. Navigate to Configuration -> Security -> Realms.
  2. Click ‘New’.
  3. Set the properties as shown below and click ‘Save’. The Digest Algorithm is set to MD5, the same algorithm that was used to create the encrypted password used by the SQL script in preceding section.

    Create Realm

NetBeans Configuration

If you are not going to use NetBeans for development of your Glassfish databases, you can skip this section. If you are using Netbeans, the following instructions will configure NetBeans to start the JavaDB server included with Glassfish and mount the databases located in [GF_DIR]/databases.

  1. Start NetBeans.
  2. From the menu, select Tools -> Java DB Database -> Settings…
  3. Fill in the dialog using the JavaDB installed with Glassfish, [GF_DIR]/javadb and [GF_DIR]/databases.

    Java DB Database Settings

  4. Click ‘OK’ to save the changes.

To Top

Please follow and like us:

Leave a Reply

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