Project-level permission Control

In a Project, users and permissions of different Project levels are often defined, which is similar to Role/User/Access Right Control of windows. My implementation is as follows:

1. Create five tables in the database: tSvRole, tSvUser, tSvObject, tSvRoleUser, and tSvRoleObject. Store the Mappings of Role, User, Object, Role-User, and Role-Object respectively. The tsql statement for table creation is as follows:

If exists (select * from dbo. sysobjects where id = object_id (N [dbo]. [tSvObject]) and OBJECTPROPERTY (id, NIsUserTable) = 1)
Drop table [dbo]. [tSvObject]
GO

If exists (select * from dbo. sysobjects where id = object_id (N [dbo]. [tSvRole]) and OBJECTPROPERTY (id, NIsUserTable) = 1)
Drop table [dbo]. [tSvRole]
GO

If exists (select * from dbo. sysobjects where id = object_id (N [dbo]. [tSvRoleObject]) and OBJECTPROPERTY (id, NIsUserTable) = 1)
Drop table [dbo]. [tSvRoleObject]
GO

If exists (select * from dbo. sysobjects where id = object_id (N [dbo]. [tSvRoleUser]) and OBJECTPROPERTY (id, NIsUserTable) = 1)
Drop table [dbo]. [tSvRoleUser]
GO

If exists (select * from dbo. sysobjects where id = object_id (N [dbo]. [tSvUser]) and OBJECTPROPERTY (id, NIsUserTable) = 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. Read data in the program. The function is:

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

The called stored procedure is:
If exists (select * from dbo. sysobjects where id = object_id (N [dbo]. [spSvAdminData]) and OBJECTPROPERTY (id, NIsProcedure) = 1)
Drop procedure [dbo]. [spSvAdminData]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

Create procedure dbo. spSvAdminData

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. Read Permission. The function used to determine whether a User can access an Object is:

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

The rule here is:
A. if the Object is not registered in the Role-Object table, the system returns the permission;
B. If any Role of this User registers a Role in the Role-Object table that can access this Object, this User can access this Object.
C. Otherwise, it is forbidden.

4. Example
On the User management page, use the DataGrid to list users and use the row Footer of the DataGrid as the place to add users. The Program sets that only the "add User permission" person can see the row Footer. As follows:

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

Summary: This method combines databases with programs to control Project-level User/Object access permissions.