通用功能权限设计（算法超简单）

每个权限项用一个整型表示
如：插入权限用 1 ；
修改权限用 2 ；
删除权限用 4 ；
导入权限用 8 ；
导出权限用 16 ；更多权限…… 32，64，128 ……

给用户分配权限时，只需把用户获得的权限值累加就可以了。
应用时使用“与”操作来识别哪个用户是否拥有相应的权限。

例子
if 用户的权限总值 and 当前操作所需的权限 = 当前操作所需的权限 then
showmessage('有权限')
else
   showmessage('无权限');

运用此法，可简单的设计出复杂的权限系统。

道理很简单，and 是以二进制按位“与”操作的。其真值表是： 
1 and 1 ＝ 1 
1 and 0 ＝ 0 
0 and 1 ＝ 0 
0 and 0 ＝ 0 
比如十进位值 9的二进制是：01001 
8的二进制是：01000 
9 and 8的二进制结果是 01000＝ 8 
9 and 4的结果是 00000＝ 0 
所以，就表示8包含在9的组合方式内（8＋1＝9），而4不包含在内。

sql server按位“与”操作符是 &

权限项表设计脚本：

USE [TKPOS_MANAGER]
GO

/****** Object: Table [dbo].[Sys_Power] Script Date: 03/04/2014 17:38:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Sys_Power](
[powerId] [int] NOT NULL,
[powerName] [varchar](50) NULL,
[description] [varchar](50) NULL,
[powerValue] [int] NULL,
CONSTRAINT [PK_Sys_Power] PRIMARY KEY CLUSTERED
(
[powerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 权限表设计脚本：

USE [TKPOS_MANAGER]
GO

/****** Object: Table [dbo].[sys_power_master] Script Date: 03/04/2014 17:39:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[sys_power_master](
[masterId] [varchar](40) NOT NULL,
[powerType] [varchar](20) NULL,
[ownerId] [varchar](40) NULL,
[pluginId] [varchar](40) NULL,
[powerValueTotal] [int] NULL,
CONSTRAINT [PK_sys_power_master] PRIMARY KEY CLUSTERED
(
[masterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

USE [TKPOS_MANAGER]
GO
/****** Object: UserDefinedFunction [dbo].[f_HavePower] Script Date: 03/04/2014 17:30:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 陈新光
-- Create date: 2014-03-04
-- Description: 是否拥有某项权限
-- =============================================
ALTER FUNCTION [dbo].[f_HavePower]
(
@totalPowerValue int,
@currentPowerValue int
)
RETURNS bit
AS
BEGIN
declare @result bit
if @totalPowerValue & @currentPowerValue = @currentPowerValue begin
set @result=1
end else begin
set @result=0
end
RETURN @result

END

USE [TKPOS_MANAGER]
GO
/****** Object: StoredProcedure [dbo].[sp_power] Script Date: 03/04/2014 17:31:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 陈新光
-- Create date: 2014-01-22
-- Description: 获取指定角色或用户的权限列表
-- exec sp_power '{357ED3F1-B697-4A41-B399-E4F14608E0F5}',''
-- exec sp_power '','1'
-- =============================================
ALTER PROCEDURE [dbo].[sp_power]
@ruleId varchar(40),
@userId varchar(40)
AS
BEGIN
-- 变量定义
declare @pluginId varchar(40)
declare @p_query bit
declare @p_insert bit
declare @p_edit bit
declare @p_delete bit
declare @p_export bit
declare @p_import bit
declare @p_print bit
-- 创建临时表
create table #tmp_result(
menul1 varchar(20),
menul2 varchar(20),
menul3 varchar(20),
pluginId varchar(40),
pluginName varchar(40),
p_query bit,
p_insert bit,
p_edit bit,
p_delete bit,
p_export bit,
p_import bit,
p_print bit
)
-- 初始化临时表
insert into #tmp_result
select menuL1,menuL2,menuL3
,recId,[description],0,0,0,0,0,0,0
from sys_plugin
order by bplName,menuL1,menuL2,menuL3
-- 更新临时表
if @ruleId>'' begin -- 角色权限
DECLARE c1 CURSOR for
--select pluginid,p_query,p_insert,p_edit,p_delete,p_export,p_import,p_print
select pluginid,dbo.f_havePower(powerValueTotal,1),dbo.f_havePower(powerValueTotal,2)
,dbo.f_havePower(powerValueTotal,4),dbo.f_havePower(powerValueTotal,8)
,dbo.f_havePower(powerValueTotal,16),dbo.f_havePower(powerValueTotal,32)
,dbo.f_havePower(powerValueTotal,64)
from sys_power_master where powerType='rule' and ownerId=@ruleId
open c1
FETCH NEXT FROM c1 INTO @pluginid,@p_query,@p_insert,@p_edit,@p_delete,@p_export,@p_import,@p_print
WHILE @@FETCH_STATUS=0 begin
update #tmp_result
set p_query=@p_query,p_insert=@p_insert,p_edit=@p_edit,p_delete=@p_delete
,p_export=@p_export,p_import=@p_import,p_print=@p_print
where pluginId=@pluginId
FETCH NEXT FROM c1 INTO @pluginid,@p_query,@p_insert,@p_edit,@p_delete,@p_export,@p_import,@p_print
end
CLOSE c1
DEALLOCATE c1
end else if @userId>'' begin -- 用户权限
DECLARE c2 CURSOR for
--select pluginid,p_query,p_insert,p_edit,p_delete,p_export,p_import,p_print
select pluginid,dbo.f_havePower(powerValueTotal,1),dbo.f_havePower(powerValueTotal,2)
,dbo.f_havePower(powerValueTotal,4),dbo.f_havePower(powerValueTotal,8)
,dbo.f_havePower(powerValueTotal,16),dbo.f_havePower(powerValueTotal,32)
,dbo.f_havePower(powerValueTotal,64)
from sys_power_master where powerType='user' and ownerId=@userId
open c2
FETCH NEXT FROM c2 INTO @pluginid,@p_query,@p_insert,@p_edit,@p_delete,@p_export,@p_import,@p_print
WHILE @@FETCH_STATUS=0 begin
update #tmp_result
set p_query=@p_query,p_insert=@p_insert,p_edit=@p_edit,p_delete=@p_delete
,p_export=@p_export,p_import=@p_import,p_print=@p_print
where pluginId=@pluginId
FETCH NEXT FROM c2 INTO @pluginid,@p_query,@p_insert,@p_edit,@p_delete,@p_export,@p_import,@p_print
end
CLOSE c2
DEALLOCATE c2
end
-- 返回结果
select * from #tmp_result
-- 删除临时表
drop table #tmp_result
END

USE [TKPOS_MANAGER]
GO
/****** Object: StoredProcedure [dbo].[sp_GetPower] Script Date: 03/04/2014 17:32:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 陈新光
-- Create date: 2014-01-23
-- Description: 获取指定用户的所有的所属角色的权限合计值
-- exec sp_GetPower '1001'
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetPower]
@userId varchar(40)
AS
BEGIN
-- 定义变量
declare @ruleid varchar(40)
declare @ruleidList varchar(500)
declare @sql varchar(500)
-- 定义临时表
create table #tmp_power(
pluginId varchar(40),
powerValueTotal int
)
-- 获取用户所有的角色 一个用户可同时属于多个角色
DECLARE c1 CURSOR for
select ruleId from sys_userRule where userId=@userId
open c1
FETCH NEXT FROM c1 INTO @ruleid
WHILE @@FETCH_STATUS=0 begin
if @ruleidList<>'' begin
set @ruleidList=@ruleidList+','+''''+@ruleid+''''
end else begin
set @ruleidList=''''+@ruleid+''''
end
FETCH NEXT FROM c1 INTO @ruleid
end
close c1
deallocate c1
-- 返回结果
set @sql='select pluginid,powervaluetotal from sys_power_master where powerType=''user'' and ownerId='+@userId
+ ' union ' +
'select pluginid,powervaluetotal from sys_power_master where powerType=''rule'' and ownerId in ('+@ruleidList+')'
insert into #tmp_power exec(@sql)
select pluginId,MAX(powerValueTotal) as powerValue from #tmp_power group by pluginId
-- 删除临时表
drop table #tmp_power
END