SQL Permissions to Add data and how to verify?

Question

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

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