One of the goals of this walk through is to be able to limit access to our Wiki based on a list of predefined users.

This list of predefined users will be stored in a relational database system. In our case we’ll be using Microsoft Sql Server.

However, you do not have to use the Microsoft product. You can use just about any database you would like for your project. The only limitation is that the database server you will want to use must have an available JDBC driver.

Also, you must have the ability to create a database on your database server and the ability to create and update the tables that you will store in the database. Glassfish already has a bunch of JDBC drivers pre-installed so its likely that you won’t need to add a JDBC driver for your specific setup, but you will still need to configure it.

If you don’t have database server software installed on your network, consider PostgreSQL. PostgreSQL is a free and open source RDBMS solution that is very good, has a solid JDBC driver which also happens to be already included in Glassfish. Another big plus for PostgreSQL is the quality of its on-line documentation and its friendly community of users especially those found on its mailing lists.

Here are the nuts and bolts of the database setup of this example:

• database static ip address: 192.168.0.6 (mssql.example.com entry in my LAN DNS)
• database name: dbwiki
• database user: dbuser
• database password: dbpassword

Again, your LAN/DB setup can be anything you like. But, you need will need to know what its configuration is as we will need to use these value sto configure our JDBC driver in Glassfish in a later step.

After you have setup your database system, created a database on that system and created a user (with password) that can create/update/modify/delete tables in the database you will need to login to the database and create a couple of tables to hold our list of wiki users and their role information.

On my MS-SQL server I issued the following commands to create my user tables:

create table auth(
id int NOT NULL IDENTITY PRIMARY KEY,
username varchar(20) NOT NULL UNIQUE,
password varchar(20) NOT NULL,
email varchar(64),
firstname varchar(20),
middlenam varchar(20),
lastname varchar(20),
wikiname varchar(40),
created datetime,
modified datetime,
dialupaccess bit,
disabled bit,
disableddate datetime
)

create table authrole(
id int NOT NULL IDENTITY PRIMARY KEY,
username varchar(20) REFERENCES auth(username) ON UPDATE CASCADE ON DELETE CASCADE,
role varchar(20) NOT NULL,
rolecode varchar(20)
)

Issue INSERT statements to populate your tables. Add a couple of users to the “auth” table. For now enter everything as plain text but later on you will want to store you password encoded.

Add the same users to the authrole tables but when you enter a value for “role” make this value equal “Authenticated”

When you add your self to the authrole table enter “Admin” as the value for “role.” As the administrator for the wiki this will grant you extra capabilites like being able to delete pages.