SQL添加数据的权限以及如何验证？ [英] SQL Permissions to Add data and how to verify?

问题描述

我正在寻找一种维护有关谁可以在C＃应用程序和SQL Server 2005中向数据库添加数据的权限的方法。

I'm looking for a good way to maintain permissions on who can add data to a database in a C# app and SQL Server 2005.

我需要解释一下尽管很清楚。因此，让我们举个例子：

I need to explain though to make this clear. So let's take an example of this:

我有两个用户 Bob 和 Jim ，都已添加到SQL权限中，因此它们具有对数据库的写访问权。现在，所有访问都基于域用户帐户。所有其他用户仅具有读取权限。

I have two users Bob and Jim, both have been added to the SQL permissions so they have write access to the database. Now all access is based on Domain User Accounts. All other users only have read access.

现在我有几个表，例如：

Now I have a couple tables such as:

• 用户


• UserPermissions


• 图书


• BookPublishers

• Users
• UserPermissions
• Books
• BookPublishers

因此 UserPermissions 包含用户和BookPublishers的列表。因此，例如： Bob 有权为 MS Press 和 Jim 有权为 O'Reilly 添加图书。

So UserPermissions contains a list of Users and BookPublishers. So for example: Bob has permission to add books for MS Press and Jim has permission to add books for O'Reilly.

现在我需要验证此信息，并限制它们可以添加的内容。

Now I need to verify this information and limit what they can add.

所以说 Jim 从命令行使用我的应用程序，他写道：

So say Jim uses my application from a command line and he writes something like:

Addbook.exe坚果壳中的C＃3.0 O'Reilly

该工具应继续进行操作并将这本书添加到书桌中。

The tool should go ahead and add the book to the book table.

现在说 Bob 尝试相同的命令，该工具应该出错，因为他没有。

Now say Bob tries the same command, the tool should error as he does not have permission to add books by O'Reilly.

现在我需要知道如何做几件事。

Right now I need to know how to do a couple things.

• 验证用户首先具有对SQL Server的写权限


• 验证用户具有对添加 books 的写权限由特定发布者


• Als o在工具实际尝试添加数据之前，我需要验证上述情况是否正确，即在工具继续运行之前，我首先需要验证反馈。

• Verify that a user first has write permission to the SQL Server
• Verify that the user has write permission to add books by a specific publisher
• Also I need to verify that the above is true before the tool actually tries to add the data, i.e. I need verification feedback first before the tool continues

现在我不是100％担心用户注入恶意数据，虽然可以阻止这种情况很好，但这是一种内部工具，我想我可以信任用户...（可能）

Now I'm not 100% worried about the user from injecting malicious data, though it would be nice to stop that, but it's an internal tool and I guess I can trust the users... (possibly)

无论哪种方式，我都不知道从哪里开始，我的SQL技能非常缺乏。

Either way I don't know where to be begin, my SQL skills are very much lacking.

Akk，一个最后，我不想使用存储过程添加数据。

Akk, one last thing, I don't want to add data using store procedures.

推荐答案

好吧，让我们来分解一下：

Okay, let's break this down:

验证用户可以写入表（如果为true，则返回1，否则返回0）：

Verify that a user can write to the table (this will return 1 if true, 0 if not):

SELECT isnull(has_perms_by_name('MyDb.dbo.MyTable', 'OBJECT', 'INSERT'), 0)

验证用户可以写那个发布者：

Verify that a user can write that publisher:

SELECT count(*) FROM UserPermissions WHERE
UserName = 'username' AND Publisher = 'publisher'

现在，那是这些的SQL，而不是实际的C＃ 。要获得C＃中的值：

Now, that's the SQL for those, and not the actual C#. To get the values in C#:

SqlConnection SqlConn = new SqlConnection("connection_string_goes_here");
SqlCommand SqlCmd = new SqlCommand();

SqlConn.Open();
SqlCmd.Connection = SqlConn;
SqlCmd.CommandText = "SELECT isnull(has_perms_by_name('MyDb.dbo.MyTable', " +
    "'OBJECT', 'INSERT'), 0)"

if (SqlCmd.ExecuteScalar())
{
   SqlCmd.CommandText =
       "SELECT count(*) FROM UserPermissions WHERE " +
       "Username = " + System.Environment.UserDomainName + "\" + 
           System.Environment.UserName + " " +
       AND Publisher = @Publisher";
   SqlCmd.Parameters.Add("@Publisher", SqlDbType.NVarChar);
   SqlCmd.Parameters("@Publisher").Value = PublisherInput;

   if(SqlCmd.ExecuteScalar())
   {
       SqlCmd.Parameters.Clear();
       SqlCmd.CommandText = "INSERT INTO Books (Title, Publisher) VALUES " +
                            "(@Title, @Publisher)";
       SqlCmd.Parameters.Add("@Title", SqlDbType.NVarChar);
       SqlCmd.Parameters.Add("@Publisher", SqlDbType.NVarChar);
       SqlCmd.Parameters("@Title").Value = TitleInput;
       SqlCmd.Parameters("@Publisher").Value = PublisherInput;
       SqlCmd.ExecuteNonQuery();
   }
}

SqlCmd.Dispose();
SqlConn.Close();
SqlConn.Dispose();

最后，请清理输入内容。在应用程序中使用参数，并且不信任任何用户，甚至不信任内部用户。我不能强调足够。

As a final note, cleanse your input. Use parameters in your application, and do not trust any user, even internal ones. I can't stress that enough.

编辑：因为有多种方法可以给猫皮换皮，所以我不把LINQ to SQL包括在内对我来说是愚蠢的解决方案（至少要解决计数问题）：

Because there are more than one way to skin a cat, I felt it foolish of me to not include the LINQ to SQL solution (at least to the count issue):

int PermsAvailable = (from up in db.UserPermissions
                      where up.Username == 
                          System.Environment.UserDomainName + "\" + 
                          System.Environment.UserName
                      && up.Publisher == PublisherInput
                      select up).Count();
if(PermsAvailable)
{
    var NewBook = New Book with {.Title = TitleInput, .Publisher = PublisherInput};
    db.Books.Add(NewBook);
}

这篇关于SQL添加数据的权限以及如何验证？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！