Project級(jí)別的權(quán)限控制

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

1、在數(shù)據(jù)庫(kù)中建立5個(gè)表：tSvRole, tSvUser, tSvObject, tSvRoleUser和tSvRoleObject，分別存儲(chǔ)Role、User、Object、Role-User對(duì)應(yīng)關(guān)系以及Role-Object對(duì)應(yīng)關(guān)系。建表的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、讀取權(quán)限，判斷某User是否可以訪問(wèn)某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沒(méi)有在Role-Object表中注冊(cè)，則返回允許；
B、如果此User的任意一個(gè)Role在Role-Object表中注冊(cè)了可訪問(wèn)此Object，則此User可訪問(wèn)此Object
C、否則禁止。

4、使用舉例
在User管理頁(yè)面，使用DataGrid列出User，使用DataGrid的Footer行作為添加User的地方，程序設(shè)定只有有“添加User權(quán)限”的人才會(huì)看到Footer行。如下：

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

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