This first part discusses a simple setup where only a single table is used for storing user-information. It includes information about how to configure the standard Glassfish jdbcRealm to use the information for authentication and using views to hide database implementaion details.
Basically a simple application is secured, enabling only registered users access.
I’ve created a small test database in PostgreSQL called testdb, and a datasource has been setup in the appserver named “jdbc/testdb” that accesses the database with the default user/owner as database user.
In order to secure the application in the Glassfish server, a security realm has to be configured. When storing users credentials in a database, the jdbcRealm (com.sun.enterprise.security.auth.realm.jdbc.JDBCRealm) could be used.
Then we need a table to store our users in. For each user we need at least a user-id (the ID to use when logging in) and a password.
If we are going to use this information elsewhere, it makes sense to also store the full name of the user and possibly other information (e.g. email etc.).
A simple draft table is defined below (PosgreSQL dialect):
CREATE TABLE t_user ( id serial NOT NULL, user_id VARCHAR(30) NOT NULL, user_name VARCHAR(80) NOT NULL, md5_password CHAR(32) NOT NULL, created TIMESTAMP(3) NOT NULL DEFAULT now(), CONSTRAINT pk_user PRIMARY KEY (id), CONSTRAINT uk_user_id UNIQUE (user_id) );
|id||The primary key column. I prefer to keep information out of the primary keys, and to have them in as simple datatypes as possible. “serial” is PostgreSQL short for INTEGER where a sequence is used for generating the values automatically.|
|user_id||The ID the user must specify when logging in. This must be unique among all users.|
|user_name||Some “human-readable” textual representation of the user; possibly full name.|
|md5_password||One should never store plain-text passwords in the database. In this sample we use MD5 hashed passwords, readily supported by the SUN JDBCRealm.|
|created||Just for keeping track of when the user was created. Should be followed by a similar “updated” column.|
Now, in order for us to be able to configure the JDBCRealm, we need to specify 2 tables: users and groups and we only have a table for the users.
Also, we do not really want to expose the inner workings of our user tables. If we should ever feel like chaning the table structures, we would have to reconfigure the JDBCRealm and possibly other applications as well.
So we encapsulate the required functionality behind 2 views; one for users and one for groups:
CREATE VIEW v_user AS SELECT user_id, md5_password FROM t_user; CREATE VIEW v_group AS SELECT user_id, 'user' AS group_name FROM t_user;
The v_user view returns only the user_id and md5_password from the t_user table, just enough information to satisfy the JDBCRealm user table configuration.
The v_group view returns the user_id and the hard-coded value “user” for each user defined. Again just enough information to be able to setup the JDBCRealm.
The required settings are repeated below:
|JAAS context||jdbcRealm (unchanged)|
|User Name Column||user_id|
|Group Name Column||group_name|
If you need a simple test-user, just insert the following row into the t_user table. This will create a user id ID=”tst1″ and password=”password” (note the PostgreSQL specific funtion “md5” that generates an MD5 digest as a 32-character HEX string from any string):
INSERT INTO t_user (user_id, user_name, md5_password) VALUES ('tst1', 'Test User #1', md5('password'));
Now, this setup might be just a little bit too simple. Maybe we want to be able to distinguish ordinary users from administrators, and we might need a simple way to enable/disable users access without deleting them from the system.
We add 2 columns to the t_user table, the full DDL given below:
CREATE TABLE t_user ( id serial NOT NULL, user_id VARCHAR(30) NOT NULL, user_name VARCHAR(80) NOT NULL, md5_password CHAR(32) NOT NULL, is_admin bool NOT NULL, enabled bool NOT NULL DEFAULT TRUE, created TIMESTAMP(3) NOT NULL DEFAULT now(), CONSTRAINT pk_user PRIMARY KEY (id), CONSTRAINT uk_user_id UNIQUE (user_id) );
The is_admin column is
true for administrators.
The enabled column is
true for all active users.
Now we modify the 2 views. First we don’t want disabled users to appear in the v_user view. Second, we want administrators to also have the “admin” group in the v_group view:
CREATE VIEW v_user AS SELECT user_id, md5_password, is_admin FROM t_user WHERE enabled=TRUE; CREATE VIEW v_group AS SELECT user_id, 'user' AS group_name FROM v_user UNION SELECT user_id, 'admin' AS group_name FROM v_user WHERE is_admin=TRUE;
For testing, we create 3 different users: tst1 who’s a standard user, tsta who’s an administrator and tstx that is a disabled user:
INSERT INTO t_user (user_id, user_name, md5_password, is_admin) VALUES ('tst1', 'Test User #1', md5('password'), FALSE); INSERT INTO t_user (user_id, user_name, md5_password, is_admin) VALUES ('tsta', 'Test User Administrator', md5('password'), TRUE); INSERT INTO t_user (user_id, user_name, md5_password, is_admin, enabled) VALUES ('tstx', 'Test User X', md5('password'), FALSE, FALSE);
If only a few roles are needed and the setup is never going to be more complex, the “is_admin” column could be changed into a “login_level” column defining different levels of access. The actual mapping between login-levels and groups must be implemented in the v_group view.
To test the sectest realm, install the sectest.ear application in your local Glassfish server and hit http://localhost:8080/sectest.
In part 2, the simple setup is expanded with logins, groups and relations…
Update 2010-01-26 20:45 CET: The sample application (sectest.ear) has been updated, now also contains jboss DD and allows “admins” only access 🙂