June 27, 2011 at 6:05 am
Hi,
I have a requirment where i want a particular user to update only N number of rows at a time (in one single command).
can you guys guide me how can i do this?
thanks
puneet
June 27, 2011 at 6:09 am
only thing i can think of off the top of my head is a trigger on every table...you could count the rows in the INSERTED/DELETED tables, and compare the ORIGINAL_LOGIN() to teh suspected suer.
otherwise, i'd seriously consider removong direct access tot he tables formt he user and make them use stored procedures..
it this some attempt to keep a developer from repeating a mistake like forgetting to use a WHERE statement?
Lowell
June 27, 2011 at 6:13 am
yes thats correct, we are planning to give access to some database programmer to update production data but still want to be safe (if they forget to put where or didnt select the where clause while running the query).
do you have any other idea to implement this.
June 27, 2011 at 6:16 am
Yes have HIM write the code in dev. Test it in test server, prove it again in QA.
Then YOU execute it in prod after making sure you have a working backup (that means full restore + checkdb() which includes the latest log backups you took).
June 27, 2011 at 6:19 am
edit: i'm parroting what Ninja said in a different way...he's too fast!
pujain (6/27/2011)
do you have any other idea to implement this.
yes. don't give access to production.
give the develoepr access to a copy of production.
If a real issue is identified, where production needs to be updated, then after careful testing, peeer review and QA testing, the DBA takes the suggested changes from the developer and implements them on production.
you've already stated you have doubts about the ability of this develoepr because of the quesiton you asked. the answer is clearly he should not ahve access to production.
a developer can do just as much damage updating a single row with incorrect data as he can witht eh whole table.
do you really need him to screw up production data to confirm
he shouldn't have access?
Lowell
June 27, 2011 at 6:19 am
pujain (6/27/2011)
yes thats correct, we are planning to give access to some database programmer to update production data but still want to be safe (if they forget to put where or didnt select the where clause while running the query).do you have any other idea to implement this.
In general, I'd vote against the idea to give developers access to production.
Why don't they use a snapshot of prod db or a recent backup?
I find the issues you need to cover when letting dev access a prod db much more overweight the benefit.
What would be the purpose of letting a developer change data in production? Let's assume it has been made bulletproof that ther's always only one row that can be modified: How would you deal with a production lot going to scrap just because the status has changed? Or mark a production lot as "completed" even if it's not? Change the number of goods to produce for one single order from 10 to 10000?
June 27, 2011 at 6:21 am
thats the problem, we cannot run it in test enviorment as the code we are planning to give them are just one liner like
"update tablename set columnname=1 where columnname=2"
this will update only one row
but i am a bit worried if incidently while running the query they didnt select where clause, in that case it will update around 200 million rows which will take time to recover.
although they all are experienced programmer but as a DBA its my responsibility to restrict them from doing a blunder.
June 27, 2011 at 6:27 am
pujain (6/27/2011)
thats the problem, we cannot run it in test enviorment as the code we are planning to give them are just one liner like"update tablename set columnname=1 where columnname=2"
this will update only one row
but i am a bit worried if incidently while running the query they didnt select where clause, in that case it will update around 200 million rows which will take time to recover.
although they all are experienced programmer but as a DBA its my responsibility to restrict them from doing a blunder.
It's your job to make sure the app run, to make sure the dev doesn't screw up and that the application change doesn't screw up production. With that in mind, why do you want him to do you job for you?
June 27, 2011 at 6:32 am
thats because we (DBADMINS) coverage in not 24*7 and we are receiving request for these one liner update in off hours and needs to be done ASAP, it cannot wait for more than 8 hour( when we come and run that update). thats why what we are thinking is to give them update permission on that particular column only and as a precaution we want to restrict them to update 1 row at a time.
June 27, 2011 at 6:34 am
Then put that code in a proc and test the front end app to death...
Plan A would actually be to fix the application in the first place so that this error doesn't find it's way in the database.
You could possibly add a check constraint to prevent the bad data... then check the application's log to know when and where to code breaks that rule.
June 27, 2011 at 6:39 am
Ninja's_RGR'us (6/27/2011)
Then put that code in a proc and test the front end app to death...Plan A would actually be to fix the application in the first place so that this error doesn't find it's way in the database.
You could possibly add a check constraint to prevent the bad data... then check the application's log to know when and where to code breaks that rule.
ditto. have the develoeprs spend the time to fix the application so that the data doesn't need to be directly updated due to an overside or coding mistake.
If I were the DBA, you hav enot given me a reason to give the developers access at all, from what you've stated so far.
Especially since you've stated your mistrust of their abilities, in addition to identifying that the app has a coding mistake that requires a direct update to fix/work around, you've given even more reason to prevent them from having access at all.
Lowell
June 27, 2011 at 6:44 am
thanks all for your kind suggestions , i will rethink what we can do.
June 27, 2011 at 6:56 am
Additionally, we can have a stored proc with parameters ( where clause conditions) and handle the other restriction like "no of rows need to be updated" or we can handle the UPDATE on user_name basis( who will update what/how much).
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 27, 2011 at 9:35 am
Just to make sure I understand it correctly:
DBADMINS (like you) are not available 24/7, but developers are????
In that case there seems be a lot more to be fixed than the way a value can be updated in production immediately....
Who will do a point in time recovery if needed during DBADMINS off-hours? A developer? I'm scared...
June 27, 2011 at 10:00 am
thanks for the concern Lutz, i will rethink all again how to go ahead on this or to drop the idea.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply