db_datawriter role and application sec.
-
We currently have a corporate intranet that uses SQL Server for the back end. Unfortunately, we have a few applications that don't use stored procedures. Therefore we put users in the db_datawriter roll. Now that we rolled out Office XP, MS has made it very easy for users to access our SQL Server tables from Office applications. Is there an easy way to prevent users from accessing our databases outside of the application without updating all the front end code to use stored procedures?
-
Are the apps all accessed via a browser? Could you put a block on all access attempts that don't originate from the web server or IT computers?
If the apps connect via ODBC and use standard SQL authentication, do the users know the password to get them through the ODBC connection?
I would also take the political route and have amending data outside of an application a disciplinary offence for unauthorised staff.
-
Actually, most of the apps are through a web server. Blocking other traffic would probably work.
-
the db_datawriter role should come with a public health warning - hope you relasie it also allows users to update system tables ?
I believe you could set up application roles which may well limit access, I've not made much use of them but I'm sure they would help.
If you then removed the datawriter and probably datareader and set proper user groups with rights.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Yes, Application Roles will get around most of this behavior but are not perfect. The best way is to go with Stored Procedures.
The drawback to Application Roles is that the password must be stored somewhere--the registry, an xml or ini file,...some place that the application has access to read. Most overcome this by encrypting the password somehow.
As well, if you do any connection pooling there is no way to make the app role jump between connections.
Finally, the App role isn't perfect becuase of the Guest and Public accounts and their respective permissions and interaction to the database. The app role permissions do not override the permissions that user may have in the the public role.
If you attempt app roles, make sure you test it thoroughly before going live.
The short and long of it...App Roles will help eliminate some of the issues but the best way is to convert it to Stored procedures if at all possible.
SJ
-
For production databases you should have set the option to block updates on system tables
exec sp_configure 'Allow updates',0
RECONFIGURE WITH OVERRIDE
Accepting all invitesViewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply