我认为比较通用的权限设计-CSDN博客

某网权限系统设计 ,自自己认为还可以...哈哈.

作者:小男

一．要求：

1. 用户分组 ... 组里有这些权限 ... 单个用户也能设置权限 ..

2. 时间段权限 .. 就是某个时间段某个组或某个人是什么权限 ..

3. 权限列表 .

[ 商业信息 ]

发布商业信息是 / 否

商业信息是否需要审核是 / 否

修改商业信息是 / 否

隐藏商业信息是 / 否

重发商业信息是 / 否

每天发布商业信息数数字

每天重发商业信息数数字

[ 产品信息 ]

发布产品信息是 / 否

修改产品信息是 / 否

{ 删除产品信息 } 是 / 否

[ 商业往来 ]

发送留言是 / 否

删除收到留言是 / 否

每天留言最大数数字

[ 收藏功能 ]

收藏供求信息是 / 否

供求最大收藏数数字

收藏公司信息是 / 否

公司最大收藏数数字

删除收藏信息是 / 否

[ 会员资料 ]

公司名称修改是 / 否

公司信息修改（激活企业）是 / 否

企业图片上传是 / 否

企业图片上传数数字

联系信息修改（激活个人）是 / 否

[ 企业新闻 ]

添加企业新闻是 / 否

企业新闻是否需要审核是 / 否

修改企业新闻是 / 否

[ 论坛权限 ]

管理权限再议（估计不会在此系统中）

修改论坛个人资料（激活论坛）是 / 否

发贴是 / 否

投票是 / 否

回复是 / 否

二．实现：

注 : 示例假设当前时间为 2007-2-1

[Sys_Member_ Popedom _Group]

Group_Id Group_Name Group_Description

100 普通用户

200 VIP 用户

300 认证用户

1000 常用限制 ( 惩罚 ) 限制发供求，限制访问论坛等

2000 限制发供应信息

3000 限制发求购信息

4000 变态限制组

5000 再变态限制组

20000 无权限用户

.........

此表为“用户权限组”。 Group_Id 非自动编号， Group_Id 越大权重越大 ( 用户同时属于多组时以当前最大 Group_Id 为准 )

[Sys_Member_Popedom_List]

Popedom_Id Popedom_Flag Popedom_Description

1 Info_Add_Sell 增加供应信息 ( 是 / 否 )

2 Info_Add_Buy 增加求购信息 ( 是 / 否 )

3 Info_Add_DayCount 用户每天能增加多少供求信息

.............

此表为每个“权限名定义”表。

[Member_Popedom_List]

Member_Id Group_Id Time_Start Time_End

61 100 20061202 99999999 ( 普通用户 . 无限制 )

61 1000 20070101 20070302 ( 用户 2007-1-1 到 2007-3-2 被进行过 " 常用限制 ")

........

此表为“用户时间段权限组”表。即某个时间段里，某个用户属于哪些用户组。

[Member_Popedom_Group]

( 详细记录每个用户组的权限 )

Group_Id Popedom_Flag Popedom_Value(int)

100 Info_Add_Sell 1

100 Info_Add_Buy 1

100 Info_Add_DayCount 10

1000 Info_Add_DayCount 0

........

1 ．每个“允许”操作的权限必须明确定义。存入此表 .

2 ．此表 Group_Id 与用户组表 [Sys_Member_Group] 的 Group_Id 对应

Popedom_Flag 与 [Sys_Member_Popedom] 的 Popedom_Flag 对应 .Popedom_Value 字段存入相应权限的逻辑或数值。

3 ．权限值 Popedom_Value 为 int 类型，保存某个权限（是 / 否）或数字值。“允许”为 1, “拒绝”为 0

4 ．用户多个组的权限 Popedom_Value 以最小值为准，

例：先运行附录里建表语句，查询用户 id=61 的当前权限“ Info_Add_DayCount ”的值

Select IsNull ( Min (value), 0 ) From ( Select TOP 1 Member_Popedom_Group.Popedom_Value value
FROM Member_Popedom_List INNER JOIN
      Member_Popedom_Group ON
      Member_Popedom_List.Group_Id = Member_Popedom_Group.Group_Id
Where (Member_Popedom_Group.Popedom_Flag = ' Info_Add_DayCount ' ) AND
      (Member_Popedom_List.Member_Id = 61 ) AND
      (Member_Popedom_List.Time_End >= 20070302 )
ORDER BY Member_Popedom_List.Group_Id DESC ) a

结果为 0

如日期至 20070303 （把红色字改成 20070303 ）

结果为 10 即惩罚结束。

用户权限列表

Select * ,( Select IsNull ( Min (value), 0 ) From ( Select TOP 1 Member_Popedom_Group.Popedom_Value value
FROM Member_Popedom_List INNER JOIN
      Member_Popedom_Group ON
      Member_Popedom_List.Group_Id = Member_Popedom_Group.Group_Id
Where (Member_Popedom_Group.Popedom_Flag = Sys_Member_Popedom_List.Popedom_Flag) AND
      (Member_Popedom_List.Member_Id = 61 ) AND
      (Member_Popedom_List.Time_End >= 20070308 )
ORDER BY Member_Popedom_List.Group_Id DESC ) a
) From Sys_Member_Popedom_List

附录：

-- 建表及数据

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[FK_Sys_Member_Data_Base_Sys_Member_Group] ' ) and OBJECTPROPERTY (id, N ' IsForeignKey ' ) = 1 )
Alter TABLE [ dbo ] . [ Member_Data_Base ] Drop CONSTRAINT FK_Sys_Member_Data_Base_Sys_Member_Group
GO

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Member_Popedom_List] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ Member_Popedom_List ]
GO

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Sys_Member_Popedom_Group] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ Sys_Member_Popedom_Group ]
GO

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Sys_Member_Popedom_List] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ Sys_Member_Popedom_List ]
GO

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Member_Popedom_Group] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ Member_Popedom_Group ]
GO

Create TABLE [ dbo ] . [ Member_Popedom_List ] (
          [ Member_Id ] [ int ] NOT NULL ,
          [ Group_Id ] [ int ] NOT NULL ,
          [ Time_Start ] [ int ] NOT NULL ,
          [ Time_End ] [ int ] NOT NULL
) ON [ PRIMARY ]
GO

Create TABLE [ dbo ] . [ Sys_Member_Popedom_Group ] (
          [ Group_Id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
          [ Group_Name ] [ varchar ] ( 200 ) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [ PRIMARY ]
GO

Create TABLE [ dbo ] . [ Sys_Member_Popedom_List ] (
          [ Popedom_Flag ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
          [ Popedom_Class ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
          [ Popedom_Description ] [ varchar ] ( 200 ) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [ PRIMARY ]
GO

Create TABLE [ dbo ] . [ Member_Popedom_Group ] (
          [ Group_Id ] [ int ] NOT NULL ,
          [ Popedom_Flag ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
          [ Popedom_Value ] [ int ] NOT NULL
) ON [ PRIMARY ]
GO

Alter TABLE [ dbo ] . [ Sys_Member_Popedom_Group ] WITH NOCHECK ADD
          CONSTRAINT [ PK_Sys_Member_Role_Group ] PRIMARY KEY CLUSTERED
         (
                    [ Group_Id ]
         ) WITH FILLFACTOR = 90 ON [ PRIMARY ]
GO

Alter TABLE [ dbo ] . [ Sys_Member_Popedom_List ] WITH NOCHECK ADD
          CONSTRAINT [ PK_Sys_Member_Popedom_List ] PRIMARY KEY CLUSTERED
         (
                    [ Popedom_Flag ]
         ) WITH FILLFACTOR = 90 ON [ PRIMARY ]
GO

Create UNIQUE CLUSTERED INDEX [ IX_Member_Group_List ] ON [ dbo ] . [ Member_Popedom_List ] ( [ Member_Id ] , [ Group_Id ] ) WITH FILLFACTOR = 90 ON [ PRIMARY ]
GO

Create UNIQUE CLUSTERED INDEX [ IX_Member_Group_Popedom ] ON [ dbo ] . [ Member_Popedom_Group ] ( [ Group_Id ] , [ Popedom_Flag ] ) WITH FILLFACTOR = 90 ON [ PRIMARY ]
GO

Alter TABLE [ dbo ] . [ Member_Popedom_List ] ADD
          CONSTRAINT [ DF_Member_Group_List_Time_Start ] DEFAULT ( 0 ) FOR [ Time_Start ] ,
          CONSTRAINT [ DF_Member_Group_List_Time_End ] DEFAULT ( 0 ) FOR [ Time_End ]
GO

exec sp_addextendedproperty N ' MS_Description ' , N ' 权限说明 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' Sys_Member_Popedom_List ' , N ' column ' , N ' Popedom_Description '
GO
exec sp_addextendedproperty N ' MS_Description ' , N ' 权限名 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' Sys_Member_Popedom_List ' , N ' column ' , N ' Popedom_Flag '

GO

Insert INTO [ Member_Popedom_List ] ( [ Member_Id ] , [ Group_Id ] , [ Time_Start ] , [ Time_End ] ) values ( 61 , 100 , 20061202 , 99999999 )
Insert INTO [ Member_Popedom_List ] ( [ Member_Id ] , [ Group_Id ] , [ Time_Start ] , [ Time_End ] ) values ( 61 , 1000 , 20070101 , 20070302 )

Insert INTO [ Member_Popedom_Group ] ( [ Group_Id ] , [ Popedom_Flag ] , [ Popedom_Value ] ) values ( 100 , ' Info_Add_Buy ' , 1 )
Insert INTO [ Member_Popedom_Group ] ( [ Group_Id ] , [ Popedom_Flag ] , [ Popedom_Value ] ) values ( 100 , ' Info_Add_DayCount ' , 10 )
Insert INTO [ Member_Popedom_Group ] ( [ Group_Id ] , [ Popedom_Flag ] , [ Popedom_Value ] ) values ( 100 , ' Info_Add_Sell ' , 1 )
Insert INTO [ Member_Popedom_Group ] ( [ Group_Id ] , [ Popedom_Flag ] , [ Popedom_Value ] ) values ( 1000 , ' Info_Add_DayCount ' , 0 )

Insert INTO [ Sys_Member_Popedom_Group ] ( [ Group_Name ] ) values ( ' 普通用户 ' )
Insert INTO [ Sys_Member_Popedom_Group ] ( [ Group_Name ] ) values ( ' VIP用户 ' )
Insert INTO [ Sys_Member_Popedom_Group ] ( [ Group_Name ] ) values ( ' 认证用户 ' )
Insert INTO [ Sys_Member_Popedom_Group ] ( [ Group_Name ] ) values ( ' 论坛管理员 ' )

Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Company_Contact_Edit ' , ' [会员资料] ' , ' 联系信息修改 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Company_Info_Edit ' , ' [会员资料] ' , ' 公司信息修改 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Company_Name_Edit ' , ' [会员资料] ' , ' 公司名称修改 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Company_Pic_Add ' , ' [会员资料] ' , ' 企业图片上传 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Company_Pic_Add_Count ' , ' [会员资料] ' , ' 企业图片上传数 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Favorite_Company ' , ' [收藏功能] ' , ' 收藏公司信息 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Favorite_Company_Count ' , ' [收藏功能] ' , ' 公司最大收藏数 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Favorite_Del ' , ' [收藏功能] ' , ' 删除收藏信息 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Favorite_Info ' , ' [收藏功能] ' , ' 收藏供求信息 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Favorite_Info_Count ' , ' [收藏功能] ' , ' 供求最大收藏数 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Info_Add_Buy ' , ' [商业信息] ' , ' 增加求购信息 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Info_Add_DayCount ' , ' [商业信息] ' , ' 每天发布商业信息数 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Info_Add_Sell ' , ' [商业信息] ' , ' 增加供应信息 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Info_Advance ' , ' [商业信息] ' , ' 重发(提前)商业信息 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Info_Advance_DayCount ' , ' [商业信息] ' , ' 每天重发商业信息数 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Info_Check ' , ' [商业信息] ' , ' 商业信息是否需要审核 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Info_Edit ' , ' [商业信息] ' , ' 修改商业信息 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Info_Hide ' , ' [商业信息] ' , ' 隐藏商业信息 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Message_Add ' , ' [商业往来] ' , ' 发送留言 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Message_Add_DayCount ' , ' [商业往来] ' , ' 每天留言最大数 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Message_Del ' , ' [商业往来] ' , ' 删除收到留言 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' News_Add ' , ' [企业新闻] ' , ' 添加企业新闻 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' News_Check ' , ' [企业新闻] ' , ' 企业新闻是否需要审核 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' News_Edit ' , ' [企业新闻] ' , ' 修改企业新闻 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Product_Add ' , ' [产品信息] ' , ' 发布产品信息 ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Product_Del ' , ' [产品信息] ' , ' {删除产品信息} ' )
Insert INTO [ Sys_Member_Popedom_List ] ( [ Popedom_Flag ] , [ Popedom_Class ] , [ Popedom_Description ] ) values ( ' Product_Edit ' , ' [产品信息] ' , ' 修改产品信息 ' )