Project级别的权限控制

在项目中常常要定义不同的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();

  sqlConnection.Open();
  SqlDataAdapter adap = new SqlDataAdapter(sqlCommand);

  adap.Fill(ds);
  sqlConnection.Close();

  ds.Tables[0].TableName = "tRole";
  ds.Tables[1].TableName = "tUser";
  ds.Tables[2].TableName = "tObject";
  ds.Tables[3].TableName = "tRoleUser";
  ds.Tables[4].TableName = "tRoleObject";

  return ds;
}

其中调用的stored procedure是：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSvAdminData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSvAdminData]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.spSvAdminData AS

SELECT  fRoleId, fRoleName
FROM  tSvRole
ORDER BY fRoleId

SELECT  fUserId, fUserName, fUserEmail
FROM  tSvUser
ORDER BY fUserId

SELECT  fObjectId, fObjectName
FROM  tSvObject
ORDER BY fObjectId

SELECT  fRoleId, fUserId
FROM  tSvRoleUser
ORDER BY fRoleId, fUserId

SELECT  fRoleId, fObjectId, fVisible, fEnable, fExecutable
FROM  tSvRoleObject
ORDER BY fRoleId, fObjectId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

3、读取权限，判断某User是否可以访问某Object的函数是：

static public bool GetAccessRight(DataSet dsAdmin,
  String tablenameRole, String tablenameUser, String tablenameObject, 
  String tablenameRoleUser, String tablenameRoleObject,
  String fieldnameRole, String fieldnameUser, String fieldnameObject, String fieldnameAccessRight,
  String strUserId, String strObjectId)
{
  int i;
  DataRow[] datarowObjectRoleList;
  datarowObjectRoleList = dsAdmin.Tables[tablenameRoleObject].Select(fieldnameObject+"='"+strObjectId+"'");
  if(datarowObjectRoleList.GetLength(0) == 0)     
    return true;
  for(i=0;i<datarowObjectRoleList.GetLength(0);i++)
  {
    DataRow datarowObjectRole;
    datarowObjectRole = datarowObjectRoleList[i];
    bool boolObjectRoleAccessRight = Convert.ToBoolean(datarowObjectRole[fieldnameAccessRight].ToString());
    if(boolObjectRoleAccessRight == true)
    {
      String strRoleId = datarowObjectRole[fieldnameRole].ToString();
      DataRow[] datarowObjectRoleUa;
      datarowObjectRoleUa = dsAdmin.Tables[tablenameRoleUser].Select(fieldnameRole + "='" + strRoleId + "' AND " + fieldnameUser + "='" + strUserId + "'");
      if(datarowObjectRoleUa.GetLength(0)>0)
        return true;
    }
  }
  return false;
}

这里的规则是：
A、如果此Object没有在Role-Object表中注册，则返回允许；
B、如果此User的任意一个Role在Role-Object表中注册了可访问此Object，则此User可访问此Object
C、否则禁止。

4、使用举例
在User管理页面，使用DataGrid列出User，使用DataGrid的Footer行作为添加User的地方，程序设定只有有“添加User权限”的人才会看到Footer行。如下：

UserGrid.ShowFooter = clsCommon.GetAccessRight(
  dsAdmin, "tRole", "tUser", "tObject", "tRoleUser", "tRoleObject",
  "fRoleId", "fUserId", "fObjectId", "fVisible",
  Session["UserId"].ToString().Trim(), "ObjUserGridFooter");

小结：本方法使用Database与程序结合的方式，实现了Project级别User/Object访问权限的控制。