In the project, you often define different Project level users and permissions, which affects the control of Windows Role / User / Access Right, and my implementation is as follows:
1. Create 5 tables in the database: Tsvrole, TSVuser, Tsvobject, TsVroleuser, TsVroleObject, store Role, User, Object, Role-User correspondence, and Role-Object correspondence. The TSQL of the table is as follows:
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo). [tsvobject]') And ObjectProperty (id, n'susertable ') = 1) Drop Table [dbo]. [Tsvobject] Go
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]) And ObjectProperty (ID, n'susertable') = 1) Drop Table [dbo]. [TSVRole] Go
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [TsVroleObject]') And ObjectProperty (ID, n'susertable ') = 1) Drop Table [dbo]. [TsvroleObject] Go
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]) and Objectproperty (id, n'uSERTABLE') = 1) Drop Table [dbo]. [Tsvroleuser] Go
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]) and ObjectProperty (ID, n'uSERTABLE') = 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_GENER_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] GOCREATE TABLE [DBO]. [TSVROLEUSER] ([FroleID] [varchar] (30) Collate SQL_LATIN1_GENERAL_CP1_CI_AS NOT NULL, [FUSERID] [varchar] 30) Collate SQL_LATIN1_GENER_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 address [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 address [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] GO2, read data in the program, the function is:
Static Public DataSet GetMindata (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 = "tuse"; ds.tables [2] .tablename = "TOBJECT"; DS.TABLES [3] .tablename = "TROLEUSER "; DS.TABLES [4] .tablename =" TRoleObject ";
Return DS;
Wherein the stored procedure call is: 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 goset ANSI_NULLS OFF GO
Create Procedure DBo.spsvadmindata As
Select Froleid, FroleNameFrom TsvroleRder by Froleid
Select Fuserid, Fusername, FuseRemail from TsvuserRelf by FUSERID
SELECT FOBJECTID, FOBJECTNAMEFROM TSVOBJECTORDER by FOBJECTID
Select Froleid, FuseridFrom TsvroleUserORDER BY FROLEID, FUSERID
Select Froleid, FObjectID, Fvisible, Fenable, FexecutableFrom TsvroleObjectOrder by Froleid, FOBJECTIDGOSet Quoted_Identifier Off Goset Ansi_Nulls On Go
3, read permission, if the function of 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 Usergrid.showfooter = clscommon.GetAccessright (Dsadmin, "Trole", "Tuser", "Tobject", "Troleuser", "TroleObject", "FVILID", "FVisible", Session ["UserId "] .Tostring (). Trim ()," objusergridfooter "); Summary: This method uses Database and program combined to implement the control of the Project Level USER / Object Access.
