michael
9/21/2007 9:00:00 PM
Hope you're still watching this thread.
After thinking about this for a bit, it seems that the whole idea of using
TableAdapters, because of the direct access to the base tables, has a serious
security flaw. Since these objects don't operate through SP's (and therefore
you can't control security at this level) user access is determined by their
SQL Server credentials or the application's logon/pw. If the SQL credentials
allow DELETE, there's nothing to prevent a user to use SSMS and wipe out
data. If the user can extract the application's logon/pw from the code, the
same thing is possible.
If my assumption is correct, then I'm sort of surprised that MS would
promote this kind of architecture to end user developers.
Unless they put a whole lot of faith in the ability of obfuscation to
protect an application's logon/pw.
--
Michael
"William Vaughn" wrote:
> 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@discussions.microsoft.com> wrote in message news:8029BF55-5287-44FF-9158-D83C181CC6E3@microsoft.com...
> > 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