Authentication and role mapping on more than two Db tables

[lition]

Hi all,

I'm trying to implement a customized impelentation of LoginModule similar to the DatabaseServerLoginModule in JBoss AS 4.2.1 GA. It must be based on 5 tables in order to add users to groups and associate a role policy to every group. Here follows the sql code for tables:

CREATE TABLE `groups` (
`id` int(10) unsigned NOT NULL auto_increment,
`group_name` varchar(32) NOT NULL,
`group_description` varchar(255) default NULL,
`parent_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

CREATE TABLE `groups_has_roles` (
`group_id` int(10) unsigned NOT NULL,
`role_id` int(10) unsigned NOT NULL,
`assignment_date` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`group_id`,`role_id`),
KEY `groups_has_roles_FKIndex1` (`group_id`),
KEY `groups_has_roles_FKIndex2` (`role_id`),
CONSTRAINT `groups_has_roles_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `groups_has_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `groups_has_users` (
`data_inizio` date NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`group_id` int(10) unsigned NOT NULL,
`data_fine` date default NULL,
`note` text,
PRIMARY KEY (`data_inizio`,`user_id`,`group_id`),
KEY `groups_has_users_FKIndex1` (`group_id`),
KEY `groups_has_users_FKIndex2` (`user_id`),
CONSTRAINT `groups_has_users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `groups_has_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `roles` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`is_valid` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL auto_increment,
`codice_fiscale` char(16) NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(32) NOT NULL,
`email` varchar(255) NOT NULL,
`reg_time` datetime NOT NULL,
`last_visit` timestamp NOT NULL default CURRENT_TIMESTAMP,
`attivo` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`id`),
KEY `utenti_FKIndex1` (`codice_fiscale`),
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`codice_fiscale`) REFERENCES `persona` (`codice_fiscale`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table implementation is similar to the one required to use the default DatabaseServerLoginModule, but I cant understand if it's needed simply a modified rolesQuery or a more complez LoginModule implementation.

Thanks for answering