Project級別的權限控制

在項目中常常要定義不同的Project級別的用戶和權限，仿照windows的Role/User/Access Right的控制，我的實現(xiàn)如下：

1、在數(shù)據(jù)庫中建立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、在程序中讀取數(shù)據(jù)，函數(shù)是：

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;
}

其中調(diào)用的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的函數(shù)是：

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;
}

這里的規(guī)則是：
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");

小結(jié)：本方法使用Database與程序結(jié)合的方式，實現(xiàn)了Project級別User/Object訪問權限的控制。