sqlserver权限设计

create database lcsy_setting


go
Use lcsy_setting
go
----用户
create table lcsy_settingAccount
(
id  int identity(1,1) primary key,--用户主建
SellerId  bigint,--卖家ID
SellerNick  nvarchar(300), --卖家昵称
LoginName nvarchar(50),--登录名
LoginPass nvarchar(200),--密码
--roleNumber int  not null,-- 给用户分角色role 
rid int,-- 给用户分角色role 
status int ,--状态
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(1,'tom',1)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(2,'mary',2)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(3,'yy',3)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(4,'tt',3)
--查询用户
select * from lcsy_settingAccount
------2.编写用户表，的增删改查接口。
--（1）根据用户名称和用户ID返回用户信息。以json格式返回。
select * from lcsy_settingAccount where SellerId=1 and SellerNick='tom'
 --   （2）根据用户id 和用户名称修改用户信息。
 update lcsy_settingAccount set SellerNick='tom1' where SellerId=1 and SellerNick='tom'
---角色role
create table lcsy_SettingRole
(
rid int identity(1,1) primary key, --角色 
--roleNumber int not null,--角色的编号
RoleName varchar(300),--角色的名称
pid int,--权限表
PType  as rid,----权限类型（不同的角色有不同的权限）
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
insert into lcsy_SettingRole(RoleName)  values('超级管理员')
insert into lcsy_SettingRole(RoleName)  values('管理员')
insert into lcsy_SettingRole(RoleName)  values('普通用户')
insert into lcsy_SettingRole(RoleName)  values('VIP')
select * from lcsy_SettingRole
--查询用户的角色
select * from lcsy_SettingRole sr,lcsy_settingAccount sa
where sa.rid=sr.rid
--权限
create table lcsy_SettingPrivilege
(
pid int identity(1,1) primary key, --权限的主键
PType int not null,--角色的编号 权限（不同的角色有不同的权限）
status varchar(200),--0/1 是否可用
SellerId  bigint,--卖家ID
SellerNick  nvarchar(300), --卖家昵称
mtype int ,--菜单（不同的权限有不同的菜单）famquanxian
modName varchar(300),--模块名称
picture  varchar(1000),--图片大
minipicture varchar(1000),--图片小
described nvarchar(50),--描述
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)




 
---给用户开权限
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,1)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,2)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,3)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,4)


---插入数据到权限表。
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(3,3, 1,4)
select * from lcsy_SettingPrivilege
--查询不同角所拥有的权限 (开权限)
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick from lcsy_SettingRole sr,lcsy_settingAccount sa,lcsy_SettingPrivilege sp
where sa.rid=sr.rid and sp.PType=sr.PType  and sa.SellerId=sp.SellerId
--=========
---根据用户ID查询该用户的所有应用Id
select SellerId,SellerNick,rid from lcsy_settingAccount
select rid,PType from lcsy_SettingRole --分配角色和设置类型
select pid,PType,status,mtype from lcsy_SettingPrivilege
select meid,mtype from lcsy_SettingMenu 


select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick,sp.mtype,sp.status
 from lcsy_SettingRole sr,lcsy_settingAccount sa,lcsy_SettingPrivilege sp
where sa.rid=sr.rid and sp.PType=sr.PType  and sa.SellerId=sp.SellerId and sa.SellerId=3




---根据用户Id更新用户的权限。
select * from lcsy_SettingPrivilege
update lcsy_SettingPrivilege set mtype=3  where SellerId=3  and pid=5
---插入数据到权限表。(开权限)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(4,3, 1,4)
--==========




---菜单表
create table lcsy_SettingMenu
(
meid int identity(1,1) primary key,--菜单的主键
mtype varchar(200),--菜单的权限设置
mename varchar(200),--菜单的名称
Url nvarchar(300),--url
status varchar(200),--0/1 是否可用
described nvarchar(50),--描述
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)


select * from lcsy_SettingMenu
  select 
  sa.SellerId,
sp.picture,
sp.minipicture,
  sp.modName,
  sm.mename ,
  sm.mtype,
  sm.Url,
sm.described
from 
lcsy_SettingRole sr,
lcsy_settingAccount sa,
lcsy_SettingPrivilege sp,
lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype  and sa.SellerId=1
insert into lcsy_SettingMenu(mtype,mename)values(1,'定时分析')
insert into lcsy_SettingMenu(mtype,mename)values(1,'定制报告')
insert into lcsy_SettingMenu(mtype,mename)values(2,'趋势分析')
insert into lcsy_SettingMenu(mtype,mename)values(2,'活跃用户')
insert into lcsy_SettingMenu(mtype,mename)values(2,'留存用户')
insert into lcsy_SettingMenu(mtype,mename)values(3,'实时抽样')
insert into lcsy_SettingMenu(mtype,mename)values(3,'用户属性')
insert into lcsy_SettingMenu(mtype,mename)values(3,'地域分布')
insert into lcsy_SettingMenu(mtype,mename)values(3,'终端分析')
insert into lcsy_SettingMenu(mtype,mename)values(4,'访问页面')
insert into lcsy_SettingMenu(mtype,mename)values(4,'访问路径')


--菜单查询


select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick,sm.mename from lcsy_SettingRole sr,lcsy_settingAccount sa,lcsy_SettingPrivilege sp,lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype  and sa.SellerId=1


----========
select * from lcsy_SettingPrivilege
select * from lcsy_SettingMenu 
--根据模块名称查询
select sp.modName,sm.mename from lcsy_SettingMenu sm,lcsy_SettingPrivilege sp
where sp.mtype=sm.mtype and sp.modName='定制分析'
--根据模块Id查询
select * from  lcsy_SettingMenu where mtype=1
---根据模块类型查询
select * from  lcsy_SettingMenu where mtype=1


--1.根据用户ID查询用户所具有的服务市场的菜单权限
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick,sm.mename from 
lcsy_SettingRole sr,
lcsy_settingAccount sa,
lcsy_SettingPrivilege sp,
lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype  and sa.SellerId=1


select sa.SellerId,sp.modName,sm.mename ,sm.mtype,sm.Url
from 
lcsy_SettingRole sr,
lcsy_settingAccount sa,
lcsy_SettingPrivilege sp,
lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype  and sa.SellerId=1