Hi there, i am thinking of it is a good design of JDBCRealm implementation.
the table in HOW-TO looks like this: ------------------------------------------------------------------------------------------------------------------------------------------------------ create table users ( user_name varchar(15) not null primary key, user_pass varchar(15) not null ); create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, primary key (user_name, role_name) ); ------------------------------------------------------------------------------------------------------------------------------------------------------ obviously *it missed the id field*. it would be much better that each table have an id field. and make the table looks like this: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `username` varchar(225) NOT NULL, `password` varchar(225) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_username` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for roles -- ---------------------------- DROP TABLE IF EXISTS `roles`; CREATE TABLE `roles` ( `id` int(11) NOT NULL auto_increment, `role` varchar(255) NOT NULL, `description` text, PRIMARY KEY (`id`), UNIQUE KEY `unique_role` (`role`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for users_roles -- ---------------------------- DROP TABLE IF EXISTS `users_roles`; CREATE TABLE `users_roles` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL, `role_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_user_id_and_role_id` (`user_id`,`role_id`), KEY `fk_roles_by_role_id` (`role_id`), CONSTRAINT `fk_roles_by_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_users_by_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ i mean the bridge-table use id to associate users and roles -- Shanbo Li Master student Software Engineering of Distributed Systems, KTH
