Control
In a project, you often define different project-level users and permissions, and, following the control of Windows role/user/access right, my implementation is as follows:
1, in the database to establish 5 tables: Tsvrole, Tsvuser, Tsvobject, Tsvroleuser and Tsvroleobject, respectively, storage role, User, Object, Role-user Correspondence Relation and Role-object correspondence relation. 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 ' 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] () COLLATE sql_latin1_general_cp1_ci_as not NULL,
[Fobjectname] [varchar] (m) COLLATE SQL_Latin1_General_CP1_CI_AS not NULL
) on [PRIMARY]
Go
CREATE TABLE [dbo]. [Tsvrole] (
[Froleid] [varchar] () COLLATE sql_latin1_general_cp1_ci_as not NULL,
[Frolename] [varchar] (m) COLLATE SQL_Latin1_General_CP1_CI_AS not NULL
) on [PRIMARY]
Go
CREATE TABLE [dbo]. [Tsvroleobject] (
[Froleid] [varchar] () COLLATE sql_latin1_general_cp1_ci_as not NULL,
[Fobjectid] [varchar] () 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] () COLLATE sql_latin1_general_cp1_ci_as not NULL,
[Fuserid] [varchar] () COLLATE SQL_Latin1_General_CP1_CI_AS not NULL
) on [PRIMARY]
Go
CREATE TABLE [dbo]. [Tsvuser] (
[Fuserid] [varchar] () COLLATE sql_latin1_general_cp1_ci_as not NULL,
[Fusername] [varchar] (m) COLLATE sql_latin1_general_cp1_ci_as not NULL,
[Fuserpwd] [nvarchar] (COLLATE) SQL_Latin1_General_CP1_CI_AS not NULL,
[Fuseremail] [varchar] () 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 the data in the program, the function is:
static public DataSet Getadmindata (String strdatabaseconnectionstring)
{
DataSet ds;
SqlConnection SqlConnection = New SqlConnection ();
SqlCommand SqlCommand = new SqlCommand ();
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 stored procedure that is invoked 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
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, Read permission to determine whether a user can access an object function is:
The rules here are:
A, if this object is not registered in the Role-object table, return allow;
B, if any role of this user is registered in the Role-object table to access this object, then this user can access this object
C, otherwise prohibited.
4, the use of examples
On the User admin page, use the DataGrid to list the user, and use the footer line of the DataGrid as the place to add user, and the program set only people with "Add user permissions" to see Footer rows. As follows:
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
