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.value from rbac_user, rbac_role, rbac_oper, rbac_relation_user_role, rbac_relation_role_oper where rbac_relation_user_role.user_id = rbac_user.id and rbac_relation_user_role.role_id = rbac_role.id and rbac_relation_role_oper.role_id = rbac_role.id and rbac_relation_role_oper.oper_id = rbac_oper.id and rbac_user.id = 1 结果: name value foo 1 bar 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.id where rbac_user.id = 1 错误: #1066 - Not unique table/alias: 'rbac_role'