問題描述

RBAC(Role-Based Access Control,基于角色的訪問控制)用5張表實現:

用戶表 rbac_user(id, name)角色表 rbac_role(id, name)操作表 rbac_oper(id, name)用戶對應的角色表 rbac_relation_user_role(user_id, role_id)角色對應的操作表 rbac_relation_role_oper(role_id, oper_id, value)

建表并插入數據:

CREATE TABLE `rbac_user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;CREATE TABLE `rbac_role` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;CREATE TABLE `rbac_oper` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;CREATE TABLE `rbac_relation_user_role` ( `user_id` int(10) unsigned NOT NULL, `role_id` int(10) unsigned NOT NULL, UNIQUE KEY (`user_id`, `role_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `rbac_relation_role_oper` ( `role_id` int(10) unsigned NOT NULL, `oper_id` int(10) unsigned NOT NULL, `value` int(10) unsigned NOT NULL, UNIQUE KEY (`role_id`, `oper_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO rbac_user VALUES(1,’張三豐’);INSERT INTO rbac_role VALUES(1,’管理員’);INSERT INTO rbac_oper VALUES(1,’foo’);INSERT INTO rbac_oper VALUES(2,’bar’);INSERT INTO rbac_relation_user_role VALUES(1,1);INSERT INTO rbac_relation_role_oper VALUES(1,1,1);INSERT INTO rbac_relation_role_oper VALUES(1,2,0);

用where子句進行表連接獲取編號為1的用戶具有的操作權限:

select rbac_oper.name, rbac_relation_role_oper.valuefrom rbac_user, rbac_role, rbac_oper, rbac_relation_user_role, rbac_relation_role_operwhere rbac_relation_user_role.user_id = rbac_user.id and rbac_relation_user_role.role_id = rbac_role.idand rbac_relation_role_oper.role_id = rbac_role.idand rbac_relation_role_oper.oper_id = rbac_oper.idand rbac_user.id = 1結果:name valuefoo 1bar 0

現在的問題是用inner join獲得相同的結果該怎么寫?我這樣寫,會提示錯誤:

select * from rbac_relation_user_role inner join rbac_user on rbac_relation_user_role.user_id = rbac_user.id inner join rbac_role on rbac_relation_user_role.role_id = rbac_role.id inner join rbac_role on rbac_relation_role_oper.role_id = rbac_role.id inner join rbac_oper on rbac_relation_role_oper.oper_id = rbac_oper.idwhere rbac_user.id = 1錯誤: #1066 - Not unique table/alias: ’rbac_role’

問題解答

select rbac_oper.name, rbac_relation_role_oper.valuefrom rbac_user, rbac_role, rbac_oper, rbac_relation_user_role, rbac_relation_role_operwhere rbac_relation_user_role.user_id = rbac_user.id and rbac_relation_user_role.role_id = rbac_role.idand rbac_relation_role_oper.role_id = rbac_role.idand rbac_relation_role_oper.oper_id = rbac_oper.idand rbac_user.id = 1

等價于下面的inner join(縮寫join)寫法

select rbac_oper.name, rbac_relation_role_oper.value from rbac_relation_user_role join rbac_user on rbac_relation_user_role.user_id = rbac_user.idjoin rbac_role on rbac_relation_user_role.role_id = rbac_role.idjoin rbac_relation_role_oper on rbac_relation_role_oper.role_id = rbac_role.idjoin rbac_oper on rbac_relation_role_oper.oper_id = rbac_oper.idwhere rbac_user.id = 1

注意其中:join rbac_relation_role_oper on rbac_relation_role_oper.role_id = rbac_role.id不能寫作:join rbac_role on rbac_relation_role_oper.role_id = rbac_role.id因為要保證所有需要關聯的表都被join,同一個表不能join兩次.