SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Windows authenticated users SQL Server 2008R2 and restricted access


Windows authenticated users SQL Server 2008R2 and restricted access

Author
Message
John Abate
John Abate
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 694
I am in the beginning stages of updating an existing windows application to use SQL Server and windows authentication for user security.

I understand that I can create database users based on windows groups to control who has the required permissions in the database to be able to do CRUD operations. My question revolves around preventing the use of programs like Access or even SSMS by the users to mess with the data in the tables outside of the context of my program.

I have thought about creating stored procedures for doing the various operations which would allow me to not grant direct access to the tables, but would this complicate the programming which will be done in .net 4.0 using c# and DataSets.

This is the first program that I have done that is using windows authentication and AD to maintain the authorized users, and perhaps I am being paranoid about what the users will or won't do, but I am also trying to prevent any problems.

To add some more information to this, the .net program is going to be a windows form application that will be installed to the user's computers. I am a relative SQL Server newbie as until this past year I primarily used Oracle.

John
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5134 Visits: 875
It's fully clear what your question really is, but there is no way you can lock out users from doing things only from the application and never be able to get there through SSMS or Access. Not with a two-tier application installed on user machines.

What you can do is to make it difficult. You can use an application role which you grant all permissions. The application issues sp_setapprole to activate the application role. The application role requires a password. But the password must be stored somewhere, for instance in the executable. Which the users can access and tear apart as they like.

Thus, this is security by obscurity. You will keep the non-savvy out, and prevent them from doing silly things. But not the smart and evil-minded guy.

Beware that using application roles has some effects on connection pooling. Either don't use pooling, or set the role with a cookie and unset before you disconnect.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
John Abate
John Abate
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 694
Thank you for the reply. I had thought about using the application role like you suggested and will probably go that route. I will also consider adding a data tier.

John
Chris Wooding
Chris Wooding
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 1059
John,

all our C# applications use stored procedures to change data; it's the easiest way to set up the security so that users can do only what you want them to do. You can process datasets using table parameters.

Chris
John Abate
John Abate
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 694
Chris,

Thanks for the reply. I have also thought of going with the stored procedure route. I am both the DBA and one of the developers, so I guess I could argue with myself about how much trouble the programming is with stored procedures :-D

What I am really trying to protect against is what I call rogue systems (Some user connects using an access database and starts throwing together a "custom" data entry program) from cropping up and messing with the data. The thing that spooked me is the windows authentication and its double edged automatic connection to the database.

Perhaps I am being paranoid.

John
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5134 Visits: 875
Having the application using only stored procedures certainly gives somewhat better security, since you don't have to grant direct access to the tables. And if all you want to keep out are people who are too smart for their own good it works.

However, unless you encode all security checks into the stored procedures, and you only grant access to the top-level procedures, there may still be a lot that a rogue user could do with the stored procedures alone.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search