October 14, 2003 at 12:04 pm
I had a developer request DELETE permissions on a table on the Production server. I know, I know scary. I read the thread where most DBA's agree this is a major no, no. But as some of us can agree, there are some instances when the dev. needs particular rights.
Case in point, my dev. has an application that (needs permission)when a user access the dB they need the right to delete from this one table (via the application). Currently, the dev. has a generic user name and id to access the dB via the app. He wants me to grant delete permissions to that userid. Because multiple people use this app, I want to create a better way to do this.
My thought is to create a new role, db_AppDELETE, and add the necessary users to the role and grant DELETE permissions to the role on that table. How can my developer code this in his app? I don't really want to send them back to the drawing board but must protect my dB. Any advise, insight or recommendations? One last thing I don't particularly want to create a application role for this.
Aurora
October 14, 2003 at 12:10 pm
If he's sharing a login, there's no way to grant it exclusively to him at the database level. The only way to be able to change permissions would be through the use of app roles. However, if that's the case and you are using resource pooling (likely with a shared login), that connection will get thrown back into the pool and someone else might get said permissions.
Since the developer is an exception, is there the possibility of making a different connection with a different set of credentials (login)?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 14, 2003 at 12:33 pm
What about the standard role (user defined)? Here's my thought:
The developer instead of hard coding a generic userid (that multiple people would use)I create a role and assign users to that role. The developer in turn codes his app with a variable for username and id. The user has been added to the role (on my end). This should work right? Or am I naively over looking something?
Aurora
October 14, 2003 at 12:50 pm
If the application is capable of passing different username/passwords, then yes, a standard user-defined database role is the best option.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply