SQL Permissions to Add data and how to verify?

Question

Hi All,

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:

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:

• Users
• UserPermissions
• Books
• BookPublishers

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.

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

Addbook.exe "C# 3.0 in a Nutshell" "O'Reilly"

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

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.

• 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

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)

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

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

Answer 1

This article has an explanation of various methods of securing applications with specific permissions. It's worth reading the rest of the series and also the prior ASP.NET 2.0 series in order to understand the architecture being used.

Answer 2

Okay, let's break this down:

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'

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.

Edit: 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);
}

Answer 3

From a usability point of view, I wonder if it wouldn't be friendlier to manage editability in the UI. As a user, if I enter data and get slapped with a message that I don't have permission to make that entry, I am not going to be encouraged to continue participating in your website.

If a user is not able to add any entries to the database, you could display a read-only page (or DIV). Users with permissions to save new entries would get an editable page/DIV.

For a user who is permitted to save some categories of information, but not others, would it work to limit the entries in that category by using a drop-down list? For example, Bob's publisher drop-down list displays MSPress, and Jim's list includes O'Reilly. That way, they can see clearly from those lists what they are permitted to do before they attempt to add the data. Permissions aren't a secret, hidden from the user.