Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
purplex8
Starting Member
2 Posts |
 Posted - 2013-04-05 : 13:29:38
|
| 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
15732 Posts |
Posted - 2013-04-05 : 13:56:07
|
| Remove user accounts and use application roles instead: http://msdn.microsoft.com/en-us/library/ms190998.aspx |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-05 : 14:55:19
|
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 OptimizerTG |
|
|
|
purplex8
Starting Member
2 Posts |
Posted - 2013-04-08 : 11:48:11
|
| 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 expire2) 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 |
|
|
|
|
|
|
|
|
BCP Security