September 16, 2009 at 3:39 pm
We are setting up a security plan for accessing SQL Server, using Windows domain groups and Windows authentication. Data access would be closed down to using stored procedures and views. Our in-house written applications and reports would typically be the direct consumers of these stored procedures and views. That would also be the way our users would primarily access SQL Server data (i.e., via those applications and reports.) However, it is my understanding (please correct me if I'm wrong), that anyone with a recognized Windows login, whose permissions grant him access to SQL Server stored procedures/views via using our in-house applications, could just as easily use some external tool such as Excel, Access, or Crystal Reports - if they knew what they were doing - to access the very same stored procedures and views.
A. Is this correct?
B. Is there a way to limit or control access from external tools, other than our applications, within the framework I've described above? Or, is there a preferred way of handling this?
Thanks,
Randy
September 16, 2009 at 4:13 pm
The ususal was to handle this is to use an n-tier application, and only grant database access to the account that the application server uses to connect to the database. Individual users would have no access to the database, only to the application.
September 16, 2009 at 4:23 pm
Using a single account for the application, how then does SQL Server distinguish the amount of access to grant the user? This is assuming that some users use the application to view small amounts of data, others have much greater view access, some have modify access to certain portions of data, others can modify lots of the data, etc. It would seem to me that a single application account wouldn't be able to provide this type of granularity. But, again, I may be missing something...
September 16, 2009 at 8:38 pm
Access to parts of the application is not controlled by granteing access to databases objects. Access is controlled by having tables in the database that define the access granted to user logins.
September 17, 2009 at 8:55 am
Michael has given you probably the only option because once you have given rights to database objects to a login then that login can access the those objects from any application.
Another way that I have seen suggested, which almost works, is to use a logon trigger which checks the application name and only allows non-admin users to access the database using application(s) in the approved list. The issue with this is that the application name is provided via the connection string, so a savvy user could create a connection string that spoofs your approved application name.
My personal opinion is that, if you have limited access to stored procedures and views, I'd be more inclined to audit than to deny access via other applications because you have already taken some control.
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
September 17, 2009 at 9:10 am
Thanks for the responses. Interesting that you mention the logon trigger idea, b/c since I wrote my original post, I had been tossing around an idea similar to that as well.
Maybe the audit route is the way to go, I'll need to think about that some more. SQL Server has all these capabilities - database roles, interconnecting with Active Directory and Windows Domain groups, etc., maybe I'm trying to make things more complicated than they need to be.
Thanks again,
Randy
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply