自己对权限表的一点理解

create table Users
(
 uid int identity primary key,
 name varchar(30) not null,
 password varchar(30) not null
)
insert into Users values('wish','wish')
insert into Users values('admin','admin')
--=========用户表============
create table Roles
(
 rid int identity primary key,
 Rname varchar(30) not null,
 Tid int default 1
)
insert into Roles(Rname) values('超级管理员')
insert into Roles(Rname) values('普通管理员')
--=========角色表============
create table Purview
(
 pid int identity primary key,
 Pname varchar(30) not null
)
insert into Purview values('修改')
insert into Purview values('查看')
insert into Purview values('删除')
insert into Purview values('添加')
--=========权限表============
create table UserRole
(
 Uid int references Users(uid),
 Rid int references Roles(rid)
)
--=========用户角色表============
create table RolePurview
(
 Rid int references Roles(rid),
 Pid int references Purview(pid)
)
insert into RolePurview values(1,1)
insert into RolePurview values(2,2)
--=========角色权限表============
select

select * from Users
select * from Roles
select * from Purview
select * from UserRole
select * from RolePurview

insert into UserRole values(1,1)

select u.uid as '用户ID',r.rid as '角色ID',p.pid as '权限ID',ur.uid as '用户角色用户ID',ur.rid as '用户角色角色ID',
rp.rid as '角色权限角色ID',rp.pid as '角色权限权限ID'
from Users u join UserRole ur on u.uid=ur.uid
join Roles r on ur.rid=r.rid
join RolePurview rp on r.rid=rp.rid
join Purview p on rp.pid=p.pid where u.name='wish' and u.password='wish' and r.tid=1
select * from UserRole

--权限添加角色==搜索角色然后隶属与某个权限
--角色添加用户==搜索用户然后隶属与某个角色
--禁用角色==把表示字段Tid修改为零,在对可用角色的查询语句里家条件Tid=1
--用户从该角色中退出=在用户角色表里删除对应用户ID的记录
--角色从该权限中退出=在角色权限表里删除对应角色ID的记录