The first part, Securing J[2]EE applications, part 1, discusses a simple setup where a single database table, 2 views and a correctly configured jdbcRealm could handle the most basic authentication and authorization requirements.
This second article discusses a few enhancements to the first setup, enabling more complex user and group setup scenarios. This article discusses the possible difference between a user and a login and describes a setup where any user can be assigned to any group.
Last but not least, the 2 articles together show the strength of using views to encapsulate database infrastructure, as the jdbcRealm does not need any configuration changes at all, to accommodate the different setups.
User and Login
In the most basic setup, a User and Login is the same. User-related details are registered in the same record (row) as login details. This is not always a good design.
In my view, a User can have a Login. Some Users might have several Logins, some Users have none; and some Logins does not necessarily have a corresponding User (system accounts, super-administrators etc.).
This calls for a separation of the User entity and the Login entity. Consider the tables below (PostgreSQL dialect):
CREATE TABLE t_user ( id serial NOT NULL, user_name VARCHAR(80) NOT NULL, email VARCHAR(80), created TIMESTAMP(3) NOT NULL DEFAULT now(), created_by INTEGER NOT NULL, CONSTRAINT pk_user PRIMARY KEY (id) ); CREATE TABLE t_login ( id serial NOT NULL, login_id VARCHAR(30) NOT NULL, md5_password CHAR(32) NOT NULL, enabled bool NOT NULL DEFAULT TRUE, id_user INTEGER, created TIMESTAMP(3) NOT NULL DEFAULT now(), created_by INTEGER NOT NULL, CONSTRAINT pk_login PRIMARY KEY (id), CONSTRAINT fk_login_user FOREIGN KEY (id_user) REFERENCES t_user (id), CONSTRAINT fk_login_created_by FOREIGN KEY (created_by) REFERENCES t_login (id), CONSTRAINT uk_login_id UNIQUE (login_id) ); ALTER TABLE t_user ADD CONSTRAINT fk_user_created_by FOREIGN KEY (created_by) REFERENCES t_user(id); |
For the initial startup, we create 2 users:
- A ‘system’ account to be used for internal system registrations, with a hard coded ID of 1.
- An ‘admin’ account to be used for initial site administration, with a hard coded password.
To ensure that system generated ID’s does not collide with the others, we reseed the login-ID sequence to start counting from 1000.
-- set sequence to count from 1000 SELECT SETVAL('t_login_id_seq', 1000, TRUE); INSERT INTO t_login (id, login_id, md5_password, enabled, created_by) VALUES (1, 'system', '-', FALSE, 1); INSERT INTO t_login (login_id, md5_password, enabled, created_by) VALUES ('admin', md5('password'), TRUE, 1); |
Then we create a Test user with a corresponding ‘tst1’ login and a disabled ‘tstx” user:
INSERT INTO t_user (user_name, email, created_by) VALUES ('Test User #1', 'test@localhost', 1); INSERT INTO t_login (login_id, md5_password, enabled, created_by, id_user) VALUES ('tst1', md5('password'), TRUE, 1, currval('t_user_id_seq')); INSERT INTO t_user (user_name, email, created_by) VALUES ('Test User X', 'testx@localhost', 1); INSERT INTO t_login (login_id, md5_password, enabled, created_by, id_user) VALUES ('tstx', md5('password'), FALSE, 1, currval('t_user_id_seq')); |
Now we only need the v_user view to enable the jdbcRealm to lookup user-logins and passwords:
CREATE VIEW v_user AS SELECT l.id AS id_login, l.login_id AS user_id, l.md5_password, u.id AS id_user, u.user_name, u.email FROM t_login l LEFT OUTER JOIN t_user u ON l.id_user=u.id WHERE l.enabled=TRUE; |
This view has been expanded with columns from the user table, so the view can be used for other purposes than just user authentication.
Users and Groups
In the first article, a user is either a simple “user” or an “administrator”. User to Group mapping is hard coded in the v_group view, based on some setting in the corresponding rows in the t_user table.
This setup is not very flexible. If future demands more complex User to Group mappings, the UI related to defining users has to change, the v_group view must change and possibly the basic structure of the database tables.
In order to make the group-definitions as dynamic as possible, still enabling a simple UI, we create the following group table:
CREATE TABLE t_group ( id serial NOT NULL, group_name VARCHAR(30) NOT NULL, description VARCHAR(80) NOT NULL, enabled bool NOT NULL DEFAULT TRUE, created TIMESTAMP(3) NOT NULL DEFAULT now(), CONSTRAINT pk_group PRIMARY KEY (id), CONSTRAINT uk_group_name UNIQUE (group_name) ); |
| group_name | The name of the group; e.g. “user”, “admin” etc as required by the J2EE applications. Must be mapped to roles in respective deployment descriptors. |
| description | Some “human-readable” textual representation of the group to be used i UIs. |
| enabled | Fast flag to enable/disable an entire group. |
| created | Just for keeping track of when the group was created. Note that there are no created_by column, as these groups has to be connected to J2EE roles and has to change with deployment of applications; the created_by column would probably always contain 1… |
Create the two initial groups needed for the basic functionality:
INSERT INTO t_group (group_name, description) VALUES ('user', 'Default user group'); INSERT INTO t_group (group_name, description) VALUES ('admin', 'Administrators group'); |
We need a table that connects Logins with Groups – any login can be connect to any number of groups:
CREATE TABLE t_login_group ( id serial NOT NULL, id_login INTEGER NOT NULL, id_group INTEGER NOT NULL, valid_from DATE NOT NULL DEFAULT now(), created TIMESTAMP(3) NOT NULL DEFAULT now(), created_by INTEGER NOT NULL, CONSTRAINT pk_login_group PRIMARY KEY (id), CONSTRAINT fk_login_group_login FOREIGN KEY (id_login) REFERENCES t_login (id), CONSTRAINT fk_login_group_group FOREIGN KEY (id_group) REFERENCES t_group (id), CONSTRAINT fk_login_group_created_by FOREIGN KEY (created_by) REFERENCES t_login (id), CONSTRAINT uk_login_group_ids UNIQUE (id_login, id_group) ); |
The following SQL assigns logins for tst1 and tstx to the user group, and assigns the admin login to the admin group:
-- user 'tst1' group 'user' INSERT INTO t_login_group (id_login, id_group, created_by) SELECT l.id, g.id, 1 FROM t_login l, t_group g WHERE l.login_id='tst1' AND g.group_name='user'; -- user 'admin' group 'admin' INSERT INTO t_login_group (id_login, id_group, created_by) SELECT l.id, g.id, 1 FROM t_login l, t_group g WHERE l.login_id='admin' AND g.group_name='admin'; -- user 'tstx' group 'user' INSERT INTO t_login_group (id_login, id_group, created_by) SELECT l.id, g.id, 1 FROM t_login l, t_group g WHERE l.login_id='tstx' AND g.group_name='user'; |
Note that the setup enables any user to be assigned to any group defined in the t_group table.
Lets create the corresponding v_group view:
CREATE VIEW v_group AS SELECT l.login_id AS user_id, g.group_name FROM t_login_group lg, t_login l, t_group g WHERE lg.valid_from <= now() AND lg.id_group=g.id AND lg.id_login=l.id AND l.enabled=TRUE AND g.enabled=TRUE; |
The view relates users and groups, showing only enabled users, enabled groups and relations that are valid…
Note that the view column containing the logon ID of the user is called user_id maintaining backwards compatibility with the jdbcRealm configured in the first part of the article.
View Jesper Udby's profile