SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 BCP Security
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

purplex8
Starting Member

2 Posts

Posted - 04/05/2013 :  13:29:38  Show Profile  Reply with Quote
I perceive a security flaw with BCP. I am assuming that I am missing a simple setting that would answer my question but after searching on google I cannot find the answer.

-BCP (out) only requires the SELECT permission.

-If I grant a windows login/domain group access to the sql server instance (and the SELECT permission on the table), are they therefore able to use bcp to export data?

-If I were to only grant the EXECUTE permission (since our application uses stored procedures exclusively), they could still execute a stored procedure with BCP to get the data, correct?

I only want users to be able to view the data through our application. What topic do I need to look at to learn how to configure the server so that they can't use BCP to pull out the data on their own?

It seems that I can use BCP to export data from any SQL Server that my user account has a login to (and SELECT permissions on data) ...what am I missing?

Any insight would be appreciated.

Ryan

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 04/05/2013 :  13:56:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
Remove user accounts and use application roles instead: http://msdn.microsoft.com/en-us/library/ms190998.aspx
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/05/2013 :  14:55:19  Show Profile  Reply with Quote
application role is a good solution.

quote:
-If I were to only grant the EXECUTE permission (since our application uses stored procedures exclusively), they could still execute a stored procedure with BCP to get the data, correct?

Good that your app only uses SPs. So don't give them an SP with BCP and of course don't give them permission to create their own SPs. The only permissions they should have is exec on the SPs you provide.

And if you don't go application role you can still use either a database roles where the users are members or have your application connect as a designated application user. But the only way to avoid a user BCPing your data is don't give them SELECT on a specific table and certainly not something like db_datareader or greater.

Be One with the Optimizer
TG
Go to Top of Page

purplex8
Starting Member

2 Posts

Posted - 04/08/2013 :  11:48:11  Show Profile  Reply with Quote
Thank you very much for your advice.

I looked into Application Roles and at first glance it appeared to be exactly what I was looking for. In fact, the scenario described in this post (http://www.universalthread.com/ViewPageArticle.aspx?ID=607) was almost exactly what I was trying to prevent. Once Windows Authentication is used it allows any other program that knows how to connect to a database (Excel, Access, ODBC connections, etc) to login to that database.

Using an Application Role would allow me to use Windows Authentication for logging into the database (and therefore take advantage of the built-in password policies) but still only allow the target application access to the data; since only the target application knows the Application Role login information.

However, two aspects of Application Roles take it out of contention:

1) There is no password policy for an Application Role; the password will never expire

2) The Application Role must be set for each connection to a database. I developed the data layer so that it opens a connection as late as possible, does the work, and closes it right after. (This seemed to be the recommended approach to using SQL Connection resources) So I would have to modify the data layer so that it sets the Application Role for each connection...therefore doubling the traffic to SQL server. And when the articles started talking about connection pooling issues regarding Application Roles, they started talking about alternatives to using Application Roles. (EXECUTE AS, etc)

The initial requirement for this change was to have the "database password" expire every 60 days.
-Using Windows Authentication seemed like the best approach to this; but enabling that opens the Pandoras Box of any program used under that Windows Login can now get at the data.
-Application Roles won't fullfill our needs because the password doesn't expire and the additional overhead it occurs on the connection. (Maybe this overhead is neglible...)
-I think that staying with SQL Authentication is the "best" solution since I can apply a password policy to it and effectively only the target application will know the login information. The downside to this is that we have to manually change the password and update the target application every time the password changes. And of couse the fact that SQL Authentication isn't recommended by Microsoft.

Ryan
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000