Re: Balancing security needs in ADO.NET applications

There are several approaches that can be taken--this is what I recommend for many applications:
a.. Do not expose base tables at all. This is the foundation of the remaining strategies.
b.. Create Views that return focused subsets of the tables as needed by the applications, but do not grant "write" access.
c.. Create stored procedures that manage the changes to the base tables. Since many operations involve more than a single table, these procedures can deal with the complexities of managing business rules and RI. Grant access to these SPs to specific accounts created specifically for the applications that invoke them.
d.. Create user accounts for the application--not the individual. That way the user need not know what credentials are used to gain access to the data. If the credentials are discovered, all they can do is run specific SPs that carefully guard the data and do not permit gross operations like dropping tables or changing rights.
e.. Manage user access to the applications through your own means using Windows authentication with login rights management that has no correlation to the rights granted to the application. For example Sam clerk signs in to Windows and runs the accounting application which asks him to log in. These credentials are validated by the application and grant Sam specific rights and enable portions of the application that only apply to him. When Sam is fired or moves on, you simply drop him from the list of valid accounts. This approach also permits you to log all of Sam's operations and grant him just the rights he needs--and no more.
I discuss this at length in my book.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"michael" <michael@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:8029BF55-5287-44FF-9158-D83C181CC6E3@xxxxxxxxxxxxxxxx
What's the best way of having a .NET application interact with SQL server
without compromising security? Here's my issue:

Let's say you want a user to be able to read, write, and delete records from
a SQL Server table. It's simple enough to give the user logon credentials on
SQL Server allowing them to do just that. Then, a .NET application can use
those credentials to consume the data. But let's say that same user has some
saavy and uses his/her credentials with SQL Server Management Studio for the
purposes of evil?

Alternatively, you could use your own credentials to logon to the SQL Server
from within the application preventing the user from using a tool like SSMS.
However, then you're storing your credentials within application code which
could be dissected and recovered (unless obfuscated).

Or maybe write a "middle tier" that alone interacts with the SQL Server. The
application would only have to leverage the middle tier's own security scheme
and not SQL Servers.


What's the best way?
--
Michael

Relevant Pages

  • Re: Balancing security needs in ADO.NET applications
    ... See my article http://www.developer.com/db/article.php/3693236 that shows how to put together a hierarchical TableAdapter using SPs. ... Hitchhiker's Guide to Visual Studio and SQL Server ... SQL Server credentials or the application's logon/pw. ... all they can do is run specific SPs that carefully guard the data and do not permit gross operations like dropping tables or changing rights. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Login failed for user . The user is not associated with a trusted SQL Server connection.
    ... he never mentioned he is impersonating in asp.net - so no delegation needed. ... Cassini runs with the credentials of the interactive user - which seems to have access to sql - in contrast to the local ASPNET account - which i am trying to tell him since 2 days.... ... yes - use explicit credentials and enable mixed mode auth in sql server to get this to work. ...
    (microsoft.public.dotnet.security)
  • Re: Balancing security needs in ADO.NET applications
    ... you can't control security at this level) user access is determined by their ... SQL Server credentials or the application's logon/pw. ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: IIS 6 and SQL Server - two seperate boxes
    ... make use of a risky solution. ... login to your SQL Server is that exploiting a "known vulnerability"? ... And the credentials could be stolen on ... the server (e.g. from a web.config file if you are using a static account), ...
    (microsoft.public.inetserver.iis.security)
  • RE: SQLXML Newbie Question...
    ... create SQL Server logins, but how do you pass the credentials to the service? ... Mark ... I want to plae my web service on the public internet but it needs ...
    (microsoft.public.sqlserver.xml)