|
在項目中常常要定義不同的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訪問權限的控制。
| 
