[jboss-dev-forums] [Design of Security on JBoss] - 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

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4091451#4091451

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4091451