用户对特定数据行的所有权？ [英] User ownership of a specific data row?

查看：67 发布时间：2019/6/15 16:39:06 SQL

本文介绍了用户对特定数据行的所有权？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我试图让我的用户拥有他们输入信息的特定数据行，但同时允许其他用户查看和选择。我的意思是数据库中的信息将能够在全球范围内看到，但也可以为该用户指定信息。我正在使用asp.net C＃和MS SQL管理工作室，任何人都可以帮忙，我相信这必须在SQL中..

谢谢Smile | :)

我有一个用户登录我的网站，用户填写表格并获得结果，我有两张桌子与aspnet_Users和aspnet_Genealogy相关的，我试图让aspname_Users中与Genealogy数据相关的UserName输入表aspnet_Genealogy，但同时aspnet_Genealogy表中的数据将被其他用户访问。这意味着数据将由用户拥有，并且能够被其他用户查看和选择，但只能编辑原始用户。

我的表格如下：

< pre lang =SQL> 
 CREATE TABLE [dbo]。[aspnet_Users]（
 [ApplicationId] UNIQUEIDENTIFIER NOT NULL，
 [UserId] UNIQUEIDENTIFIER DEFAULT（newid（））NOT NULL，
 [UserName] NVARCHAR （256）NOT NULL，
 [LoweredUserName] NVARCHAR（256）NOT NULL，
 [MobileAlias] NVARCHAR（16）DEFAULT（NULL）NULL，
 [IsAnonymous] BIT DEFAULT（（0） ）NOT NULL，
 [LastActivityDate] DATETIME NOT NULL，
 CONSTRAINT [PK__aspnet_U__1788CC4D0BC6C43E] PRIMARY KEY NONCLUSTERED（[UserId] ASC）
）; 
 
 
 GO 
 CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] 
 ON [dbo]。[aspnet_Users]（[ApplicationId] ASC，[LoweredUserName] ASC）; 
 
 
 GO 
 CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] 
 ON [dbo]。[aspnet_Users]（[ApplicationId] ASC，[LastActivityDate] ASC）;

  CREATE  表 [dbo]。[aspnet_Genealogy]（
 [GenealogyId]  INT   IDENTITY （ 1 ， 1 ） NOT   NULL ，
 [FamilyName]  CHAR （ 200 ） NOT   NULL ，
 [FirstName]  CHAR （ 200 ） NULL ，
 [MiddleName1] < span class =code-keyword> CHAR （ 200 ） NULL ，
 [MiddleName2]  CHAR （ 200 ） NULL ，
 [MiddleName3]  CHAR （ 200 ） NULL ，
 [性别]  CHAR （ 10 ） NULL ，
 [ DOB]  VARCHAR （ 20 ） NOT  < span class =code-keyword> NULL ，
 [COB]  CHAR （ 200 ） NULL ，
 [SOB]  CHAR （ 200 ） NULL ，
 [COOB]  CHAR （ 200 ）  NULL ，
 [新闻稿]  CHAR （ 10 ） NULL ，
 [DateTimeGenealogy]  DATETIME   NOT   NULL ，
 [UserId]  UNIQUEIDENTIFIER   NULL ，
  PRIMARY   KEY   CLUSTERED （[GenealogyId]  ASC ），
  CONSTRAINT  [FK_aspnet_Genealogy_aspnet_Users]  FOREIGN   KEY （[UserId]）参考 [dbo]。[aspnet_Users]（[UserId]）
）;

存储过程如下：

插入SP：

 CREATE PROCEDURE [dbo]。[aspnet_AddGenealogy] 
（
 @FamilyName char（200），
 @FirstName char（200），
 @ MiddleName1 char（200），
 @ MiddleName2 char（200），
 @ MiddleName3 char（200），
 @Gender char（10），
 @DOB varchar（20），
 @COB char（200）， 
 @SOB char（200），
 @COOB char（200），
 @Newletter char（200），
 @DateTimeGenealogy DateTime 
）
 
 AS 
 BEGIN 
 
 INSERT INTO aspnet_Genealogy（FamilyName，FirstName，MiddleName1，MiddleName2，MiddleName3，Gender，DOB，COB，SOB，COOB，Newsletter，DateTimeGenealogy）
 VALUES （@FamilyName，@ FirstName，@ MiddleName1，@ MiddleName2，@ MiddleName3，@ Gender，@ DOB，@ COB，@ SOB，@ COOB，@ Newsletter，@ DateTimeGenealogy）
 
 END

加入SP为：

CREATE PROCEDURE [dbo]。[aspnet_UserGenealogy]
AS
SELECT aspnet_Users.UserName，aspnet_Genealogy.GenealogyId
FROM aspnet_Users
INNER JOIN aspnet_Genealogy
ON aspnet_Users.UserId = aspnet_Genealogy.UserId
ORDER BY aspnet_Users.UserName;

返回 0

Default.aspx是用户/会员输入数据的页面。代码是：

< pre lang =c＃> 
<％@ Page Title =Language =C＃MasterPageFile =〜/ Main.masterAutoEventWireup =trueCodeFile =Default.aspx.csInherits =_ Default％> 
 
< asp：Content ID =Content1ContentPlaceHolderID =headRunat =Server> 
< / asp：Content> 
< asp：Content ID =Content2ContentPlaceHolderID =ContentPlaceHolder1Runat =Server> 
< link href =Styles / Content1.css =stylesheet/> 
< div id =content> 
< br /> 
< span style =font-family：Arial，Helvetica，sans-serif; font-weight：bold; font-size：large; text-transform：uppercase>< center> Genealogy Membership Profile< / center>< / span>< br /> 
< br /> 
请填写您的< span style =字体系列：Arial，Helvetica，sans-serif; font-weight：bold> Genealogy Membership Profile< / span>要充分利用您的免费会员资格，请填写以下信息及其免费：< br /> 
< br /> 
< span style =font-family：Arial，Helvetica，sans-serif; font-weight：bold; font-size：medium>姓氏：        ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：TextBox ID =FamilyNamerunat =serverWidth =200px>< / asp：TextBox> 
< br /> 
名字：&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：TextBox ID =FirstNamerunat =serverWidth =200px>< / asp：TextBox> 
< br /> 
中名：&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：TextBox ID =MiddleName1runat =serverWidth =200px>< / asp：TextBox> 
< br /> 
中名：&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：TextBox ID =MiddleName2runat =serverWidth =200px>< / asp：TextBox> 
< br /> 
中名：&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：TextBox ID =MiddleName3runat =serverWidth =200px>< / asp：TextBox> 
< br /> 
性别：&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP; 
< asp：DropDownList ID =Genderrunat =serverWidth =100px> 
< asp：ListItem>男< / asp：ListItem> 
< asp：ListItem>女性< / asp：ListItem> 
< / asp：DropDownList> 
< br /> 
出生日期：                      &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：TextBox ID =DOBrunat =serverWidth =200px>< / asp：TextBox> 
< br />出生
市：&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：TextBox ID =COBrunat =serverWidth =200px>< / asp：TextBox> 
< br /> 
 Prov。或国家诞生：&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; 
< asp：TextBox ID =SOBrunat =serverWidth =200px>< / asp：TextBox> 
< br />出生
国家：&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：TextBox ID =COOBrunat =serverWidth =200px>< / asp：TextBox> 
< br /> 
通讯：&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：DropDownList ID =Newsletterrunat =serverWidth =100px> 
< asp：ListItem>是< / asp：ListItem> 
< asp：ListItem>否< / asp：ListItem> 
< / asp：DropDownList> 
< br /> 
< br /> 
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 
< asp：Button ID =UpdateButtonrunat =serverOnClick =UpdateButton_ClickText =Update/> 
< br /> 
< br /> 
< / span>< / div> 
< div id =ad>< / div> 
< / asp：Content>

Default.aspx.cs的代码是：

 使用系统; 
 使用 System.Collections.Generic; 
 使用 System.Linq; 
 使用 System.Web; 
 使用 System.Web.UI; 
 使用 System.Web.UI.WebControls; 
 使用 System.Configuration; 
 使用 System.Data; 
 使用 System.Data.SqlClient; 
 使用 System.Data.SqlTypes; 
 使用 Microsoft.SqlServer.Server; 
 
  public   partial   class  _Default：System.Web.UI.Page 
 {
  protected   void  Page_Load（ object  sender，EventArgs e）
 {
 
} 
  protected   void  UpdateButton_Click（ object  sender，EventArgs e）
 {
 字符串 strConnString = ConfigurationManager.ConnectionStrings [  ApplicationServices ]的ConnectionString。 
 SqlConnection con =  new  SqlConnection（strConnString）; 
 SqlCommand cmd =  new  SqlCommand（）; 
 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.CommandText =   aspnet_AddGenealogy; 
 
 cmd.Parameters.Add（  @ FamilyName，SqlDbType.Char ）.Value = FamilyName.Text.Trim（）; 
 cmd.Parameters.Add（  @ FirstName，SqlDbType.Char）.Value = FirstName.Text.Trim（）; 
 cmd.Parameters.Add（  @ MiddleName1，SqlDbType.Char）.Value = MiddleName1.Text.Trim（）; 
 cmd.Parameters.Add（  @ MiddleName2，SqlDbType.Char）.Value = MiddleName2.Text.Trim（）; 
 cmd.Parameters.Add（  @ MiddleName3，SqlDbType.Char）.Value = MiddleName3.Text.Trim（）; 
 cmd.Parameters.Add（  @ Gender，SqlDbType.Char）.Value = Gender.Text.Trim（）; 
 cmd.Parameters.Add（  @ DOB，SqlDbType.VarChar）.Value = DOB.Text.Trim（）; 
 cmd.Parameters.Add（  @ COB，SqlDbType.Char）.Value = COB.Text.Trim（）; 
 cmd.Parameters.Add（  @ SOB，SqlDbType.Char）.Value = SOB.Text.Trim（）; 
 cmd.Parameters.Add（  @ COOB，SqlDbType.Char）.Value = COOB.Text.Trim（）; 
 cmd.Parameters.Add（  @ Newsletter，SqlDbType.Char）.Value = Newsletter.Text.Trim（）; 
 
 cmd.Parameters.Add（  @ DateTimeGenealogy，SqlDbType.DateTime ）.Value = DateTime.Now.ToString（）; 
 
 cmd.Connection = con; 
 
 尝试 
 {
 
 con.Open（）; 
 cmd.ExecuteNonQuery（）; 
 Server.Transfer（  ResultsMembers.aspx）; 
 
} 
 
  catch （例外情况）
 {
 
 < span class =code-keyword> throw  ex; 
 
} 
 
 最后 
 {
 
 con.Close（） ; 
 con.Dispose（）; 
 
} 
 
} 
}

我用的是代码对于结果页面，ResultsMembers.aspx，这是：

 <％@     Page    标题  =    语言  =  C＃     MasterPageFile   = < span class =code-keyword>〜/ Main.master    AutoEventWireup   =  true    CodeFile   =  ResultsMembers.aspx.cs   继承  =  ResultsMembers   ％>  
 
 <   asp：内容    ID   =  Content1    ContentPlaceHolderID   =  head    Runat   = 服务器 >  
 <   / asp：内容 >  
 <   asp：内容   < span class =code-attribute> ID   =  Content2    ContentPlaceHolderID   =  ContentPlaceHolder1    Runat   = 服务器 >  
 
 
 <   asp：GridView     ID   =  GridVie w1    runat   =  server    AutoGenerateColumns   =  False    CellPadding   =  4    DataSourceID   =  SqlDataSource1    ForeColor   = ＃333333    GridLines   = 无   <跨度类= 代码属性 > OnSelectedIndexChanged  <跨度类= 代码关键字 > = <跨度类= 代码关键字> GridView1_SelectedIndexChanged  >  
 <   AlternatingRowStyle     BackColor   = 白色    /  >  
 <  列 >  
 <   asp：CommandField  < span class =code-attribute>   ShowSelectButton   =  True    /  >  
 <   asp：BoundField     DataField   =  UserName    HeaderText   =  UserName    SortExpression   =  UserName    /  >  
 <   asp：BoundField     DataField   =  FamilyName    HeaderText   =  FamilyName    SortExpression   =  FamilyName    /  >  
 <   asp：BoundField     DataField   =  FirstName    HeaderText   =  FirstName    SortExpression   =  FirstName    /  >  
 <   asp：BoundField     DataField   =  MiddleName1   < span class =code-attribute> HeaderText   =  MiddleName1    SortExpression   =  MiddleName1    /  >  
 <   asp：BoundField     DataField   =  MiddleName2    HeaderText   =  MiddleName2    SortExpression   =  MiddleName2    /  >  
 <   asp：BoundField     DataField   =  MiddleName3    HeaderText   =  MiddleName3    SortExpression   =  MiddleName3    /  >  
 <   asp：BoundField     DataField   = 性别    HeaderText   = 性别 < span class =code-attribute>   SortExpression   = 性别    /  >  
 <  < span class =code-leadattribute> asp：BoundField     DataField   =  DOB    HeaderText   =  DOB    SortExpression   =  DOB < span class =code-attribute>   /  >  
 <   asp：BoundField     DataField   =  COB    HeaderText   =   COB    SortExpression  < span class =code-keyword> =  COB    /  >  
 <   asp：BoundField     DataField   =  SOB    HeaderText   =  SOB   < span class =code-attribute> SortExpression   =  SOB    /   >  
 <   asp：BoundField     DataField   =  COOB     HeaderText   = < span class =code-keyword> COOB    SortExpression   =  COOB    /  >  
 <   asp：BoundField     DataField   = 时事通讯    HeaderText   = 时事通讯    SortExpression   = 简报    /  >  
 <   asp：BoundField     DataField   =  DateTimeGenealogy    HeaderText   =  DateTimeGenealogy    SortExpression   =  DateTimeGenealogy    /  >  
     </Columns> 
     <FooterStyle BackColor=\"#990000\"  Font-Bold=\"True\" ForeColor=\"White\" /> 
     <HeaderStyle BackColor=\"#990000\" Font-Bold=\"True\" ForeColor=\"White\" /> 
     <PagerStyle BackColor=\"#FFCC66\" ForeColor=\"#333333\" HorizontalAlign=\"Center\" /> 
     <RowStyle BackColor=\"#FFFBD6\" ForeColor=\"#333333\" /> 
     <SelectedRowStyle BackColor=\"#FFCC66\" Font-Bold=\"True\" ForeColor=\"Navy\" /> 
     <SortedAscendingCellStyle BackColor=\"#FDF5AC\" /> 
     <SortedAscendingHeaderStyle BackColor=\"#4D0000\" /> 
     <SortedDescendingCellStyle BackColor=\"#FCF6C0\" /> 
     <SortedDescendingHeaderStyle BackColor=\"#820000\" /> 
 </asp:GridView> 
 <asp:SqlDataSource ID=\"SqlDataSource1\" runat=\"server\" ConnectionString=\"<%$ ConnectionStrings:ApplicationService s %>\" SelectCommand=\"SELECT vw_aspnet_Users.UserName, vw_aspnet_Genealogy.* FROM vw_aspnet_Genealogy CROSS JOIN vw_aspnet_Users\"></asp:SqlDataSource> 
  
  
 </asp:Content>

The code behind the resultsMembers.aspx.cs which is:

using System; 
 使用 System.Collections.Generic; 
 使用 System.Linq; 
 使用 System.Web; 
 使用 System.Web.UI; 
 使用 System.Web.UI.WebControls; 
  
 public partial class ResultsMembers : System.Web.UI.Page 
 { 
     protected void Page_Load(object sender, EventArgs e) 
     { 
  
     } 
      
 }

The resultsMembers.aspx page gives a result with all the UserName to one genealogy record because there is only one genealogy record entered. I want the genealogy record that is entered by the User/Member to be shown to that user/Member as well to be able to be access by other users such as in a search and so they can select from that record from a search, but the other Users/Members will not be able to edit or change the record.

I am fairly new to asp.net and sql, can anyone help?

Thanks

解决方案

ConnectionStrings:ApplicationServices %>\" SelectCommand=\"SELECT vw_aspnet_Users.UserName, vw_aspnet_Genealogy.* FROM vw_aspnet_Genealogy CROSS JOIN vw_aspnet_Users\"></asp:SqlDataSource>

</asp:Content>

The code behind the resultsMembers.aspx.cs which is:
using System; 
 使用 System.Collections.Generic; 
 使用 System.Linq; 
 使用 System.Web; 
 使用 System.Web.UI; 
 使用 System.Web.UI.WebControls; 
  
 public partial class ResultsMembers : System.Web.UI.Page 
 { 
     protected void Page_Load(object sender, EventArgs e) 
     { 
  
     } 
      
 }
The resultsMembers.aspx page gives a result with all the UserName to one genealogy record because there is only one genealogy record entered. I want the genealogy record that is entered by the User/Member to be shown to that user/Member as well to be able to be access by other users such as in a search and so they can select from that record from a search, but the other Users/Members will not be able to edit or change the record.

I am fairly new to asp.net and sql, can anyone help?

Thanks

There are several ways of achieving this. A quite common way is to store for example the user name or id of the person who created the row and then use this persistent information in your application in filters etc. You can use triggers to enforce the logic in the database in order to make it more robust. Filters can be included in a view so this technique would help you to prevent other users from seeing this data if needed.

A bit more comprehensive example can be found here Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005[^]

This is not a SQL Server functionnality ; you cannot grant a specific access to a specific user on a single row.

You will have to implement it in your code ; you will have to store in your row which user has created it, and allow the same user programmatically to edit this specific row.

I am trying to give my user ownership of specific data row that they enter information into, but as well as allowing other users to view and select from. I mean the information will in the database will be able to be seen globally but can also have the information designated to that user. I am using asp.net C# and MS SQL Management Studios, can anyone help, I believe this will have to be in SQL..

Thanks Smile | :)

I have a User logging into my website, the User fills out a form and gets a result, I have two tables that are related to a aspnet_Users and aspnet_Genealogy, I trying to have the UserName from aspnet_Users related to the Genealogy data enter in the table aspnet_Genealogy, but at the same time the data in the aspnet_Genealogy table being to be access by other Users. This means that the data will be own by the User and well as being able to be viewed and selected by other Users but be able to edited only the originated Users.

My tables are as follows:

<pre lang="SQL">
CREATE TABLE [dbo].[aspnet_Users] (
    [ApplicationId]    UNIQUEIDENTIFIER NOT NULL,
    [UserId]           UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [UserName]         NVARCHAR (256)   NOT NULL,
    [LoweredUserName]  NVARCHAR (256)   NOT NULL,
    [MobileAlias]      NVARCHAR (16)    DEFAULT (NULL) NULL,
    [IsAnonymous]      BIT              DEFAULT ((0)) NOT NULL,
    [LastActivityDate] DATETIME         NOT NULL,
    CONSTRAINT [PK__aspnet_U__1788CC4D0BC6C43E] PRIMARY KEY NONCLUSTERED ([UserId] ASC)
);


GO
CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index]
    ON [dbo].[aspnet_Users]([ApplicationId] ASC, [LoweredUserName] ASC);


GO
CREATE NONCLUSTERED INDEX [aspnet_Users_Index2]
    ON [dbo].[aspnet_Users]([ApplicationId] ASC, [LastActivityDate] ASC);

    CREATE TABLE [dbo].[aspnet_Genealogy] (
    [GenealogyId]       INT              IDENTITY (1, 1) NOT NULL,
    [FamilyName]        CHAR (200)       NOT NULL,
    [FirstName]         CHAR (200)       NULL,
    [MiddleName1]       CHAR (200)       NULL,
    [MiddleName2]       CHAR (200)       NULL,
    [MiddleName3]       CHAR (200)       NULL,
    [Gender]            CHAR (10)        NULL,
    [DOB]               VARCHAR (20)     NOT NULL,
    [COB]               CHAR (200)       NULL,
    [SOB]               CHAR (200)       NULL,
    [COOB]              CHAR (200)       NULL,
    [Newsletter]        CHAR (10)        NULL,
    [DateTimeGenealogy] DATETIME         NOT NULL,
    [UserId]            UNIQUEIDENTIFIER NULL,
    PRIMARY KEY CLUSTERED ([GenealogyId] ASC),
    CONSTRAINT [FK_aspnet_Genealogy_aspnet_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[aspnet_Users] ([UserId])
);

The Stored Procs are as follows:

The Insert SP:

CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
(
	@FamilyName char(200),
	@FirstName char(200),
	@MiddleName1 char(200),
	@MiddleName2 char(200),
	@MiddleName3 char(200),
	@Gender char(10),
	@DOB varchar(20),
	@COB char(200),
	@SOB char(200),
	@COOB char(200),
	@Newsletter char(200),
	@DateTimeGenealogy DateTime
)
	
AS
BEGIN

	INSERT INTO aspnet_Genealogy (FamilyName, FirstName, MiddleName1, MiddleName2, MiddleName3, Gender, DOB, COB, SOB, COOB, Newsletter, DateTimeGenealogy)
	VALUES (@FamilyName, @FirstName, @MiddleName1, @MiddleName2, @MiddleName3, @Gender, @DOB, @COB, @SOB, @COOB, @Newsletter, @DateTimeGenealogy)

END

The Join SP is:

CREATE PROCEDURE [dbo].[aspnet_UserGenealogy]
	AS
	SELECT aspnet_Users.UserName, aspnet_Genealogy.GenealogyId
	FROM aspnet_Users
	INNER JOIN aspnet_Genealogy
	ON aspnet_Users.UserId=aspnet_Genealogy.UserId
	ORDER BY aspnet_Users.UserName;

RETURN 0

The Default.aspx is the page that Users/Members enters there data. The code for this is:

<pre lang="c#">
<%@ Page Title="" Language="C#" MasterPageFile="~/Main.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <link href="Styles/Content1.css" rel="stylesheet" />
    <div id="content">
        <br />
        <span style="font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: large; text-transform: uppercase"><center>Genealogy Membership Profile</center></span><br />
        <br />
        Please complete your "<span style="font-family: Arial, Helvetica, sans-serif; font-weight: bold">Genealogy Membership Profile</span>" to take full advantage of your free membership, please fillout the following information below and its FREE:<br />
        <br />
        <span style="font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: medium">Family Name:                                       
        <asp:TextBox ID="FamilyName" runat="server" Width="200px"></asp:TextBox>
        <br />
        First Name:                                           
        <asp:TextBox ID="FirstName" runat="server" Width="200px"></asp:TextBox>
        <br />
        Middle Name:                                       
        <asp:TextBox ID="MiddleName1" runat="server" Width="200px"></asp:TextBox>
        <br />
        Middle Name:                                       
        <asp:TextBox ID="MiddleName2" runat="server" Width="200px"></asp:TextBox>
        <br />
        Middle Name:                                       
        <asp:TextBox ID="MiddleName3" runat="server" Width="200px"></asp:TextBox>
        <br />
        Gender:                                                 
        <asp:DropDownList ID="Gender" runat="server" Width="100px">
            <asp:ListItem>Male</asp:ListItem>
            <asp:ListItem>Female</asp:ListItem>
        </asp:DropDownList>
        <br />
        Date of Birth:                                       
        <asp:TextBox ID="DOB" runat="server" Width="200px"></asp:TextBox>
        <br />
        City of Birth:                                        
        <asp:TextBox ID="COB" runat="server" Width="200px"></asp:TextBox>
        <br />
        Prov. or State Birth:                          
        <asp:TextBox ID="SOB" runat="server" Width="200px"></asp:TextBox>
        <br />
        Country of Birth:                               
        <asp:TextBox ID="COOB" runat="server" Width="200px"></asp:TextBox>
        <br />
        Newsletter:                                          
        <asp:DropDownList ID="Newsletter" runat="server" Width="100px">
            <asp:ListItem>Yes</asp:ListItem>
            <asp:ListItem>No</asp:ListItem>
        </asp:DropDownList>
        <br />
        <br />
                                                                                                           
        <asp:Button ID="UpdateButton" runat="server" OnClick="UpdateButton_Click" Text="Update" />
        <br />
        <br />
        </span></div>
    <div id="ad"></div>
</asp:Content>

The code for Default.aspx.cs is:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void UpdateButton_Click(object sender, EventArgs e)
    {
        String strConnString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
        SqlConnection con = new SqlConnection(strConnString);
        SqlCommand cmd = new SqlCommand();

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "aspnet_AddGenealogy";

        cmd.Parameters.Add("@FamilyName", SqlDbType.Char).Value = FamilyName.Text.Trim();
        cmd.Parameters.Add("@FirstName", SqlDbType.Char).Value = FirstName.Text.Trim();
        cmd.Parameters.Add("@MiddleName1", SqlDbType.Char).Value = MiddleName1.Text.Trim();
        cmd.Parameters.Add("@MiddleName2", SqlDbType.Char).Value = MiddleName2.Text.Trim();
        cmd.Parameters.Add("@MiddleName3", SqlDbType.Char).Value = MiddleName3.Text.Trim();
        cmd.Parameters.Add("@Gender", SqlDbType.Char).Value = Gender.Text.Trim();
        cmd.Parameters.Add("@DOB", SqlDbType.VarChar).Value = DOB.Text.Trim();
        cmd.Parameters.Add("@COB", SqlDbType.Char).Value = COB.Text.Trim();
        cmd.Parameters.Add("@SOB", SqlDbType.Char).Value = SOB.Text.Trim();
        cmd.Parameters.Add("@COOB", SqlDbType.Char).Value = COOB.Text.Trim();
        cmd.Parameters.Add("@Newsletter", SqlDbType.Char).Value = Newsletter.Text.Trim();

        cmd.Parameters.Add("@DateTimeGenealogy", SqlDbType.DateTime).Value = DateTime.Now.ToString();

        cmd.Connection = con;

        try
        {

            con.Open();
            cmd.ExecuteNonQuery();
            Server.Transfer("ResultsMembers.aspx");

        }

        catch (Exception ex)
        {

            throw ex;

        }

        finally
        {

            con.Close();
            con.Dispose();

        }

    }
}

I used the code for the results page, ResultsMembers.aspx, which is:

<%@ Page Title="" Language="C#" MasterPageFile="~/Main.master" AutoEventWireup="true" CodeFile="ResultsMembers.aspx.cs" Inherits="ResultsMembers" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">


    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
    <AlternatingRowStyle BackColor="White" />
    <Columns>
        <asp:CommandField ShowSelectButton="True" />
        <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
        <asp:BoundField DataField="FamilyName" HeaderText="FamilyName" SortExpression="FamilyName" />
        <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
        <asp:BoundField DataField="MiddleName1" HeaderText="MiddleName1" SortExpression="MiddleName1" />
        <asp:BoundField DataField="MiddleName2" HeaderText="MiddleName2" SortExpression="MiddleName2" />
        <asp:BoundField DataField="MiddleName3" HeaderText="MiddleName3" SortExpression="MiddleName3" />
        <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
        <asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" />
        <asp:BoundField DataField="COB" HeaderText="COB" SortExpression="COB" />
        <asp:BoundField DataField="SOB" HeaderText="SOB" SortExpression="SOB" />
        <asp:BoundField DataField="COOB" HeaderText="COOB" SortExpression="COOB" />
        <asp:BoundField DataField="Newsletter" HeaderText="Newsletter" SortExpression="Newsletter" />
        <asp:BoundField DataField="DateTimeGenealogy" HeaderText="DateTimeGenealogy" SortExpression="DateTimeGenealogy" />
    </Columns>
    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
    <SortedAscendingCellStyle BackColor="#FDF5AC" />
    <SortedAscendingHeaderStyle BackColor="#4D0000" />
    <SortedDescendingCellStyle BackColor="#FCF6C0" />
    <SortedDescendingHeaderStyle BackColor="#820000" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" SelectCommand="SELECT vw_aspnet_Users.UserName, vw_aspnet_Genealogy.* FROM vw_aspnet_Genealogy CROSS JOIN vw_aspnet_Users"></asp:SqlDataSource>


</asp:Content>

The code behind the resultsMembers.aspx.cs which is:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ResultsMembers : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    
}

解决方案

ConnectionStrings:ApplicationServices %>" SelectCommand="SELECT vw_aspnet_Users.UserName, vw_aspnet_Genealogy.* FROM vw_aspnet_Genealogy CROSS JOIN vw_aspnet_Users"></asp:SqlDataSource> </asp:Content>

The code behind the resultsMembers.aspx.cs which is:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ResultsMembers : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    
}
The resultsMembers.aspx page gives a result with all the UserName to one genealogy record because there is only one genealogy record entered. I want the genealogy record that is entered by the User/Member to be shown to that user/Member as well to be able to be access by other users such as in a search and so they can select from that record from a search, but the other Users/Members will not be able to edit or change the record.

I am fairly new to asp.net and sql, can anyone help?

Thanks

There are several ways of achieving this. A quite common way is to store for example the user name or id of the person who created the row and then use this persistent information in your application in filters etc. You can use triggers to enforce the logic in the database in order to make it more robust. Filters can be included in a view so this technique would help you to prevent other users from seeing this data if needed.

A bit more comprehensive example can be found here Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005[^]

This is not a SQL Server functionnality ; you cannot grant a specific access to a specific user on a single row.

You will have to implement it in your code ; you will have to store in your row which user has created it, and allow the same user programmatically to edit this specific row.

这篇关于用户对特定数据行的所有权？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

用户对特定数据行的所有权？ [英] User ownership of a specific data row?

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

用户对特定数据行的所有权？ [英] User ownership of a specific data row?

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭