An ACL is very useful when you have users with different roles. For example, an administrator should have unlimited access, but an editor should have access only to the Articles section. We already have the database structure for the Acl, so we just need to create some relations. First, we will create a new intermediate table named user_roles
that will hold information about each user's role. A user can have many roles.
CREATE TABLE IF NOT EXISTS `user_role` ( `user_id` int(11) NOT NULL, `role` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, UNIQUE KEY `user_id_2` (`user_id`,`role`), KEY `role` (`role`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `user_role` ADD CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role`) REFERENCES `acl_roles` (`name`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
Another...