Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Windows authenticated users SQL Server 2008R2 and restricted access Expand / Collapse
Author
Message
Posted Monday, July 7, 2014 1:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:43 AM
Points: 64, Visits: 496
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



Post #1590091
Posted Monday, July 7, 2014 2:55 PM This worked for the OP Answer marked as solution


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:19 PM
Points: 800, Visits: 713
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
Post #1590119
Posted Tuesday, July 8, 2014 7:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:43 AM
Points: 64, Visits: 496
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



Post #1590354
Posted Thursday, July 10, 2014 8:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 59, Visits: 221
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
Post #1591175
Posted Thursday, July 10, 2014 8:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:43 AM
Points: 64, Visits: 496
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

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



Post #1591187
Posted Thursday, July 10, 2014 2:28 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:19 PM
Points: 800, Visits: 713
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
Post #1591324
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse