权限控制管理（用户与菜单项的关联）

GO

 

-- Description: <修改模块权限>
-- =============================================
ALTER PROCEDURE [dbo].[st_MES_ChgModuleAuth]--1.添加菜单的所具有的权限（对应程序清单的编辑/添加部分。）
( @ModuleCode varchar(50) = '',
@Auth varchar(2000)='',
@State varchar(2000) = '',
@user varchar(50)=''
)
AS
BEGIN
update s set [State] = f.Col,updateman=@user,updatetime=getdate()
from Sys_ModuleAuthList s 
join dbo.FN_SplitSTR(@Auth,',') t on s.AuthorityTag = t.Col 
join dbo.FN_SplitSTR(@State,',') f on t.ID = f.ID 
where s.ModuleCode = @ModuleCode

insert Sys_ModuleAuthList(ModuleCode, AuthorityTag, [State], CreateMan, CreateTime,UpdateMan,UpdateTime)
select ModuleCode = @ModuleCode, AuthorityTag = t.Col, [State] = f.Col, @User, getdate(), @User, getdate()
from dbo.FN_SplitSTR(@Auth,',') t
join dbo.FN_SplitSTR(@State,',') f on t.ID = f.ID
where not exists(select 1 from Sys_ModuleAuthList s 
where s.ModuleCode = @ModuleCode
and s.AuthorityTag = t.Col)

/*add by zhuss 2014-01-28*/
if exists(select 1 from master..sysdatabases where name='ChiefWMS')
exec ChiefWMS.dbo.st_Sys_ChgModuleAuth @ModuleCode,@Auth,@State,@user
END

----------------------------------------------------------------------------------------

 

2.对应组别权限页面
ALTER proc [dbo].[st_MES_EditGroupRight] (
@GroupCode varchar(50) = '',
@ModuleCode varchar(50) = '',
@Tag varchar(50) = '',
@Flag varchar(50) = '',
@User varchar(50)=''
)
/*************
Remark: 插入组别权限
**************/
as
BEGIN
DECLARE @TagInfo VARCHAR(50)

IF @Flag<>''
BEGIN
DECLARE tag CURSOR FOR
SELECT Col AS Tag FROM FN_SplitSTR(@Tag,',')
OPEN tag
FETCH NEXT FROM tag INTO @TagInfo
WHILE @@FETCH_STATUS=0 AND @TagInfo<>''
BEGIN
update s set Flag = @Flag, UpdateMan = @User, UpdateTime = getdate()
from Sys_GroupRight s
where s.GroupCode = @GroupCode and s.ModuleCode = @ModuleCode and s.AuthorityTag = @TagInfo

insert Sys_GroupRight(GroupCode, ModuleCode, AuthorityTag, Flag, CreateMan, CreateTime, UpdateMan, UpdateTime)
select @GroupCode, m.ModuleCode, m.AuthorityTag, @Flag, @User, getdate(), @User, getdate()
from Sys_ModuleAuthList m
left join Sys_GroupRight s on s.GroupCode = @GroupCode and s.ModuleCode = m.ModuleCode and s.AuthorityTag = m.AuthorityTag
where m.State = 1 and m.ModuleCode = @ModuleCode and m.AuthorityTag = @TagInfo and s.ModuleCode is NULL

FETCH NEXT FROM tag INTO @TagInfo

END
CLOSE tag
DEALLOCATE tag

 -----------------------------------------------------------------------------------------------------------------

3.个人组别权限页面

ALTER proc [dbo].[st_MES_EditPersonalRight] (
@UserCode varchar(50) = 'superadmin',
@ModuleCode nvarchar(50) = '150002',
@Tag varchar(50) = 'RGP_ADD',
@Flag varchar(50) = 'false',
@User varchar(50)='superadmin'
)
/*************
exec st_MES_EditPersonalRight ''
creator: Wuchun
create date: 2012.10.15
Remark: 插入个人权限
**************/
as
begin
update s set Flag = @Flag, UpdateMan = @User, UpdateTime = getdate()
from Sys_PersonalRight s
where s.UserCode = @UserCode and s.ModuleCode = @ModuleCode and (@Tag = '' or s.AuthorityTag = @Tag)

insert Sys_PersonalRight(UserCode, ModuleCode, AuthorityTag, Flag, CreateMan, CreateTime, UpdateMan, UpdateTime)
select @UserCode, m.ModuleCode, m.AuthorityTag, @Flag, @User, getdate(), @User, getdate()
from Sys_ModuleAuthList m
left join Sys_PersonalRight s on s.UserCode = @UserCode and s.ModuleCode = m.ModuleCode and s.AuthorityTag = m.AuthorityTag
where m.state = 1 and m.ModuleCode = @ModuleCode and (@Tag = '' or m.AuthorityTag = @Tag) and s.ModuleCode is null

/*add by zhuss 2014-01-28*/
exec ChangHongWMS612.dbo.[st_sys_EditPersonalRight] @UserCode,@ModuleCode,@Tag,@Flag,@User
end

 

--4.根据用户UserCode取出主菜单
ALTER proc [dbo].[st_MES_SelectAllMenu] (
@UserCode varchar(50) = 'superadmin'

)
as
begin
if object_ID('tempdb..#Menu') is not null drop table #Menu
declare @Ver varchar(20), @VerName varchar(50)
select @VerName = ''
select @VerName = VerName, @Ver = ver from Sys_Version where state = 1

SELECT distinct m.MenuCode,m.Name as MenuName, m.ParentCode as ParentMenuCode, m.Iconic, MenuURL = m.URL, m.Sort
, IsDisable = isnull(m.IsDisable,0)
into #Menu
from Sys_Module a, Sys_Menu m, Sys_PersonalRight b
where a.MenuCode=m.MenuCode and a.ModuleCode=b.ModuleCode and a.State=1 and b.Flag = 1
and b.UserCode = @UserCode
and (@VerName = '' or (m.VerName = @VerName and m.Ver = @Ver and m.IsShow = 1))

insert into #Menu(MenuCode, MenuName, ParentMenuCode ,Iconic, MenuURL,Sort, IsDisable)
select distinct m.MenuCode, m.Name as MenuName, m.ParentCode as ParentMenuCode, m.Iconic, MenuURL = m.URL, m.Sort, IsDisable = isnull(m.IsDisable,0)
from Sys_Module a,Sys_GroupRight b,Sys_User c, Sys_Menu m
where a.MenuCode = m.MenuCode and a.ModuleCode=b.ModuleCode and b.GroupCode=c.GroupCode and a.State=1 and b.Flag = 1
and not exists(select 1 from #Menu t where a.MenuCode = t.MenuCode)
and c.UserCode = @UserCode
and (@VerName = '' or (m.VerName = @VerName and m.Ver = @Ver and m.IsShow = 1))

insert into #Menu(MenuCode, MenuName, ParentMenuCode ,Iconic, MenuURL,Sort, IsDisable)
select a.MenuCode,a.Name as MenuName, a.ParentCode, a.Iconic, a.URL, a.Sort,0
from Sys_Menu a
where a.Name='系统管理' AND a.State=1
and not exists(select 1 from #Menu t where a.MenuCode = t.MenuCode)

select *, VerName = @VerName from #Menu order by case when @VerName <> '' then IsDisable else 1 end , sort
end

 -------------------------------------------------------------------

5.根据用户UserCode与MenuCode 去主菜单下的子菜单
ALTER proc [dbo].[st_MES_SelModuleInfo] (
@MenuCode varchar(50) = '100'
,@UserCode varchar(50) = 'superadmin'

)
/*************
exec [st_MES_SelModuleInfo] '130','superadmin'
creator: Wuchun
create date: 2012.10.15
Remark: 生成程序模块列表
**************/
as
begin
if object_ID('tempdb..#ModuleInfo') is not null drop table #ModuleInfo
declare @Ver varchar(20), @VerName varchar(50)
select @VerName = ''
select @VerName = VerName, @Ver = ver from Sys_Version where state = 1

SELECT DISTINCT a.ModuleCode,a.Name as ModuleName,a.URL,a.Sort,a.MenuCode,a.Iconic , IsDisable = isnull(a.IsDisable,0)
into #ModuleInfo
from Sys_Module a,Sys_PersonalRight b
where a.ModuleCode=b.ModuleCode and a.State=1 and b.Flag = 1
and b.UserCode = @UserCode and a.MenuCode=@MenuCode
and (@VerName = '' or (a.VerName = @VerName and a.Ver = @Ver and a.IsShow = 1))

insert into #ModuleInfo(ModuleCode, ModuleName, URL ,Sort, MenuCode, Iconic, IsDisable)
select Distinct a.ModuleCode,a.Name as ModuleName,a.URL,a.Sort,a.MenuCode,a.Iconic, IsDisable = isnull(a.IsDisable,0)
from Sys_Module a,Sys_GroupRight b,Sys_User c
where a.ModuleCode=b.ModuleCode and b.GroupCode=c.GroupCode and a.State=1 and b.Flag = 1
and not exists(select 1 from #ModuleInfo t where a.ModuleCode = t.ModuleCode)
and c.UserCode = @UserCode and a.MenuCode=@MenuCode
and (@VerName = '' or (a.VerName = @VerName and a.Ver = @Ver and a.IsShow = 1))

insert into #ModuleInfo(ModuleCode, ModuleName, URL ,Sort, MenuCode, Iconic, IsDisable)
select Distinct a.ModuleCode,a.Name as ModuleName,a.URL,a.Sort,a.MenuCode,a.Iconic , IsDisable = isnull(a.IsDisable,0)
from Sys_Module a
where a.Name='用户密码变更' AND a.State=1
and not exists(select 1 from #ModuleInfo t where a.ModuleCode = t.ModuleCode)
select * from #ModuleInfo where moduleCode not in('50005','70016') order by sort
end

----------------------------------------------------------------------------

select * from dbo.Sys_Menu--主菜单表
select * from dbo.Sys_Module order by MenuCode asc,Sort asc--菜单模板表(菜单对应的子菜单表)
select * from Sys_GroupRight--用户组与菜单与权限表
select * from Sys_PersonalRight--用户与菜单与权限表

select * from Sys_ModuleAuthList --每一个菜单应该有的权限表

 

 

 

SELECT * FROM Sys_Authority where auttype='Function'--菜单应该有的权限 程序清单下面的checkbox多选从这里显示的。

select * from Sys_User--用户表

select * from dbo.MES_Employee--员工表

(表结构在百度云MES上面)

---------------------------------------------------

1.菜单清单：  SystemManage/Module.aspx

2.组别权限在：SystemManage/GroupRight.aspx

3.个人权限在：SystemManage/PersonalRight.aspx

  1.1程序清单列表查询SQL：

select distinct sm.ModuleCode Id,sm.ModuleCode,sm.Name,sm.Sort,sm.CreateMan,sm.CreateTime,

sm.UpdateMan,sm.UpdateTime from Sys_Module sm
left join sys_GroupRight sgr on sgr.ModuleCode=sm.ModuleCode
left join Sys_User su on su.GroupCode=sgr.GroupCode
where 1=1

 

----------------------------------------------------------

 

USE [ChangHong_612]
GO
/****** Object: UserDefinedFunction [dbo].[FN_SplitSTR] Script Date: 04/11/2016 12:27:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FN_SplitSTR](
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)
RETURNS @re TABLE(ID int IDENTITY (1, 1), Col varchar(4000))
AS
BEGIN
--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
DECLARE @t TABLE(ID int IDENTITY,b bit)
INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
FROM @t
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(@split,@split+@s,ID)=ID
RETURN
END

 附注：每一个页面的里面的CRUD权限在MES：MGridPageBase类下面赋值的。

protected virtual void InitAuthority()
{
Dictionary<string, bool> AuthorityDic = CBF.DAL.RightCtrl.RightCtrl.PageRightCtrl(this.Page.User.Identity.Name.Trim(), this.CurrentModuleID);
foreach (KeyValuePair<string, bool> entry in AuthorityDic)
{
string propertyName = "Enable" + entry.Key.Replace("RGP", "").Trim();
if (this.GetType().GetProperty(propertyName) != null)
{
this.GetType().GetProperty(propertyName)
.SetValue(this, Convert.ChangeType(entry.Value, this.GetType()
.GetProperty(propertyName).PropertyType), null);
}
}
}