0 Replies Latest reply on Oct 4, 2007 7:09 AM by lition

    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