轻松掌握用户，角色，功能之间的关系视图

来源：赛迪    作者：36331

用户，角色，功能之间的关系视图：

假设你目前有下面的数据库结构：

create table userinfo(id varchar(20),name varchar(20));
create table roleinfo(id varchar(20),name varchar(20));
create table functioninfo(id varchar(20),name varchar(20));


create table userrole(userid varchar(20),roleid varchar(20));
create table rolefunction(roleid varchar(20),functionid varchar(20));


insert into userinfo values("1","gao1");
insert into userinfo values("2","gao2");
insert into userinfo values("3","gao3");

insert into roleinfo values("1","role1");
insert into roleinfo values("2","role2");
insert into roleinfo values("3","role3");


insert into functioninfo values("1","function1");
insert into functioninfo values("2","function2");
insert into functioninfo values("3","function3");


insert into userrole values("1","1");
insert into userrole values("1","2");
insert into userrole values("2","2");
insert into userrole values("3","1");
insert into userrole values("3","2");
insert into userrole values("3","3");

insert into role function values("1","1");
insert into role function values("1","2");
insert into role function values("2","2");
insert into role function values("3","1");
insert into role function values("3","2");
insert into role function values("3","3");

你可以根据三者连接的结果建立视图，得出整个权限模型的数据。

select u.name as username,r.name as rolename,f.name as functionname
from userrole ur inner join userinfo u on ur.userid=u.id
inner join roleinfo r on ur.roleid=r.id
inner join rolefunction rf on rf.roleid=r.id
inner join functioninfo f on rf.functionid=f.id
order by u.name;

结果如下：