Re: Balancing security needs in ADO.NET applications

The simplest to prevent such kind of potential "evil" user is the only give permission of executing certain SPs, used by that application, to that user, no access to any other SQL Server objects. This way, the only thing he can do when connected to the SQL Serer database is to execute those SPs, which is the same when he uses the application. Say, userA is given permission to only execute SP1, which only does a "SELECT" on a table. So, userA can only do "SELECT.." from a table, nothing else. UserB is given permission SP1 and SP2 (doing "UPDATE..."). So, userB can both "SELECT..." from the table and "UPDATE..." to the table.... No matter userA and userB connect to the database from where, your app or their own "secret tool", they can only execute the given SPs, nothing else.



"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: Connecting to Sql Server 2005
    ... What "user" have you given permission to? ... executes the sp's I get the infamous EXECUTE permission denied on object ... I am using sql server authentication - I created a SQL server user in the ... sp's - I want to be able to pass these credentials via my application - so ...
    (microsoft.public.dotnet.general)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • RE: exec sp_help_job user account rights
    ... wrapper in which u can call original stored procedure sp_help_job using “WITH ... EXECUTE AS “ clause and then give execute permission of external stored ... Database Administrator, SQL Server 2005 ... the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns. ...
    (microsoft.public.sqlserver.security)
  • Re: SSIS - OLE DB Command - how to retrieve query results ???
    ... my side according to your process, and I managed to execute the task. ... Data Destination: <My SQL Server 2005 Instance>.TestDB ... OLE DB Command: ...
    (microsoft.public.sqlserver.dts)