Project级别的权限控制

【 tulaoshi.com - ASP.NET 】

在项目中常常要定义不同的Project级别的用户和权限，仿照windows的Role/User/Access Right的控制，我的实现如下：

1、在数据库中建立5个表：tSvRole, tSvUser, tSvObject, tSvRoleUser和tSvRoleObject，分别存储Role、User、Object、Role-User对应关系以及Role-Object对应关系。建表的tsql如下：

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

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

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

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

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

CREATE TABLE [dbo].[tSvObject] (
[fObjectId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fObjectName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tSvRole] (
[fRoleId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fRoleName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tSvRoleObject] (
[fRoleId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fObjectId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fVisible] [bit] NOT NULL ,
[fEnable] [bit] NOT NULL ,
[fExecutable] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tSvRoleUser] (
[fRoleId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fUserId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tSvUser] (
[fUserId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fUserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fUserPwd] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fUserEmail] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tSvObject] WITH NOCHECK ADD
CONSTRAINT [PK_tSvObject] PRIMARY KEY  CLUSTERED
(
[fObjectId]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tSvRole] WITH NOCHECK ADD
CONSTRAINT [PK_tSvPrjRole] PRIMARY KEY  CLUSTERED
(
[fRoleId]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tSvRoleObject] WITH NOCHECK ADD
CONSTRAINT [DF_tSvRoleObject_fVisible] DEFAULT (0) FOR [fVisible],
CONSTRAINT [DF_tSvRoleObject_fEnabled] DEFAULT (0) FOR [fEnable],
CONSTRAINT [DF_tSvRoleObject_fExecutable] DEFAULT (0) FOR [fExecutable],
CONSTRAINT [PK_tSvRoleObject] PRIMARY KEY  CLUSTERED
(
[fRoleId],
[fObjectId]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tSvRoleUser] WITH NOCHECK ADD
CONSTRAINT [PK_tSvRoleUser] PRIMARY KEY  CLUSTERED
(
[fRoleId],
[fUserId]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tSvUser] WITH NOCHECK ADD
CONSTRAINT [PK_tSvPrjUser] PRIMARY KEY  CLUSTERED
(
[fUserId]
)  ON [PRIMARY]
GO

2、在程序中读取数据，函数是：

static public DataSet GetAdminData(String strDatabaseConnectionString)
{
  DataSet ds;

  SqlConnection sqlConnection = new SqlConnection();
  SqlCommand sqlCommand = new SqlCommand();

  sqlConnection.ConnectionString = strDatabaseConnectionString;
  sqlCommand.CommandText = "[spSvAdminData]";
  sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
  sqlCommand.Connection = sqlConnection;
  ds = new DataSet();

  sqlCon