October 21, 2008 at 9:36 am
I'm sure this has been discussed before but...
We'd like to be able to restrict access to a database (tables, sps etc.) to users accessing the database through one of several applications. They should only have access rights through an application - through the business logic of the application.
What we don't want is to have someone have a username/password combination (whether SQL Server or passthrough) that allows access outside of the application, say through Query Analyzer or merely a cmd call.
We're familiar with application roles, which could work, but we also would like to be able to know 'who' is calling particular objects on the database by accessing the connection information. If we use application roles, we will lose this capability as well as all the other problems that come with application roles.
It would be ideal if as user could start up an application and connect to sql server indicating both the passthrough account attempting to connect and the application from which the connection is coming, and only through this combination access objects on sql server.
I've done much searching and I don't see that this exists. Does anyone know of third-party products or other solutions that could provide these capabilities?
October 21, 2008 at 9:46 am
I am not aware of any way to do exactly what you want. This is why I like to use stored procedures for all data access and manipulation. They may not enforce ALL the business rules, but do control access and would allow you to use the APP_NAME() function to "catch" other applications using the data. You would just need to be sure your application's connection string supplies the application name property. Your code in the sp would be something like this:
If APP_NAME() = 'Your Application Name' Then
Begin
-- do code
End
Else
Begin
RaisError 199999 'Access to this data is only allowed using [your application name]'
End
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 21, 2008 at 10:48 am
I suspected as much.
The App_Name() function could be potentially useful though.
thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply