分组模式来管理更新日期为 2010.6.29

通过对用户分组，每个组的资源指定权限

用户组

USE [fangqm]
GO
/****** 对象: Table [fangqm].[MD_workgroup] 脚本日期: 06/29/2010 17:37:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [fangqm].[MD_workgroup](
[ID] [int] IDENTITY(1,1) NOT NULL,
[groupName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[description] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[Actived] [bit] NOT NULL CONSTRAINT [DF_MD_workgroup_Actived] DEFAULT ((1)),
CONSTRAINT [PK__MD_workgroup__5DA0D232] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

用户群

USE [fangqm]
GO
/****** 对象: Table [fangqm].[MD_user] 脚本日期: 06/29/2010 17:39:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [fangqm].[MD_user](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_MD_user_CreateDate] DEFAULT (getdate()),
[userCode] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[userName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Passoword] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[description] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[Actived] [bit] NOT NULL CONSTRAINT [DF_MD_user_Actived] DEFAULT ((1)),
[workgroupID] [int] NOT NULL,
CONSTRAINT [PK__MD_user__5BB889C0] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [fangqm].[MD_user] WITH CHECK ADD CONSTRAINT [FK_MD_user_MD_workgroup] FOREIGN KEY([workgroupID])
REFERENCES [fangqm].[MD_workgroup] ([ID])
GO
ALTER TABLE [fangqm].[MD_user] CHECK CONSTRAINT [FK_MD_user_MD_workgroup]

所有资源

USE [fangqm]
GO
/****** 对象: Table [fangqm].[MD_Resource] 脚本日期: 06/29/2010 17:40:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [fangqm].[MD_Resource](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ResourceName] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NOT NULL,
[WorkgroupID] [int] NOT NULL,
[PermissionID] [int] NOT NULL,
CONSTRAINT [PK__MD_Resource__6541F3FA] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [fangqm].[MD_Resource] WITH CHECK ADD CONSTRAINT [FK_MD_Resource_MD_Resource] FOREIGN KEY([ID])
REFERENCES [fangqm].[MD_Resource] ([ID])
GO
ALTER TABLE [fangqm].[MD_Resource] CHECK CONSTRAINT [FK_MD_Resource_MD_Resource]
GO
ALTER TABLE [fangqm].[MD_Resource] WITH CHECK ADD CONSTRAINT [FK_MD_Resource_MD_workgroup] FOREIGN KEY([WorkgroupID])
REFERENCES [fangqm].[MD_workgroup] ([ID])
GO
ALTER TABLE [fangqm].[MD_Resource] CHECK CONSTRAINT [FK_MD_Resource_MD_workgroup]

权限

USE [fangqm]
GO
/****** 对象: Table [fangqm].[MD_Permission] 脚本日期: 06/29/2010 17:42:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [fangqm].[MD_Permission](
[ID] [int] IDENTITY(1,1) NOT NULL,
[look] [bit] NOT NULL CONSTRAINT [DF_MD_Permission_look] DEFAULT ((1)),
[append] [bit] NOT NULL,
[del] [bit] NOT NULL,
[edit] [bit] NOT NULL,
[audit] [bit] NOT NULL,
[WorkgroupID] [int] NOT NULL,
CONSTRAINT [PK__MD_Permission__73901351] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [fangqm].[MD_Permission] WITH CHECK ADD CONSTRAINT [FK_MD_Permission_MD_workgroup] FOREIGN KEY([WorkgroupID])
REFERENCES [fangqm].[MD_workgroup] ([ID])
GO
ALTER TABLE [fangqm].[MD_Permission] CHECK CONSTRAINT [FK_MD_Permission_MD_workgroup]

ITPub博客

自创的一种Web权限管理方法

最新文章