July 7, 2014 at 1:52 pm
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
July 7, 2014 at 2:55 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 8, 2014 at 7:26 am
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
July 10, 2014 at 8:15 am
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
July 10, 2014 at 8:28 am
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
July 10, 2014 at 2:28 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply