My employee updated all records in a database table...

[KingGheedora]

Originally posted by: KLin
So what if someone needs to update 1001 records? Will they have to call a dba to temporarily disable the trigger that's throwing the error?

Thinking something like, the trigger detects > 1000 records trying to be updated it tries to insert to a table that only dbo role has permissions to insert on. If the user isn't dbo that step will fail. They can have me or a dba run it for them, which will force someone to review the script.

 

[mugs]

Originally posted by: drum

Originally posted by: kranky
To me, the big issue is that he didn't come forward after making the mistake.

again, assuming he realized he made a mistake.
we use EMC Avamar tapeless backup and it is marvelous

:thumbsup:

I forgot a where clause once. And I followed it up with a commit. :Q But I realized that the query was taking too long to update one record, so I hit ctrl-C to kill it before the query completed, so it got rolled back. The size of that table saved my ass.

 

[nakedfrog]

Originally posted by: KLin
So what if someone needs to update 1001 records? Will they have to call a dba to temporarily disable the trigger that's throwing the error?

Don't be ridiculous, why would you ever need to update more than 1,000 rows at once?

 

[torpid]

Originally posted by: KingGheedora

Originally posted by: torpid

Originally posted by: KingGheedora
As i stated in the original post, this is NOT A PRODUCTION DB, and we aren't losing any money with this data being lost. It's operational data that is useful to track but we can start from scratch if need be, without any of it, at any time.

If you are reporting off it then isn't it production data? It might not be customer data but it sounds like production data to me...??

In a way yes. But it is technically a development server where people started writing rogue apps and tools, and db's. Anything that is mission critical is put on the actual production servers, where we have robust backup procedures, and permissions are locked down, etc.

Sounds like Lord of the Flies or something. It's even less surprising that this would happen to me now...

 

[DomS]

Originally posted by: KingGheedora
I plan to put a trigger on the table that will throw an error if someone tries to update more than 1,000 records at once. Fuck i don't want to have to deal with managing permissions. I have to now deny everyone's dbo access to the db now, and they will have to submit scripts to me to be run on this server from now on to make changes. They can still update data but no more permissions to make modifications because that would mean they could bypass the restrictions on the table.

I used to work at a utility company and I got absolutely sick and tired of people snooping around in the guts of my DB I was creating for them, so I did something like this. It would pop up with a message telling them "get. out." when they tried to change things they shouldn't

 

[Evadman]

/* here, i wrote a trigger for you to make sure that you will be notified if this happens again */

Declare @table varchar (1024)
declare @email varchar (100)
SET @email = 'YoueEmailHere@bla.com' /* change this line to your email address */
Declare tbl_cur cursor for
select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tbl_cur

FETCH NEXT from tbl_cur INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('delete from '+ @table)
FETCH NEXT from tbl_cur INTO @table
END

CLOSE tbl_cur
DEALLOCATE tbl_Cur

 

[Crusty]

Originally posted by: KingGheedora

Originally posted by: torpid

Originally posted by: KingGheedora
As i stated in the original post, this is NOT A PRODUCTION DB, and we aren't losing any money with this data being lost. It's operational data that is useful to track but we can start from scratch if need be, without any of it, at any time.

If you are reporting off it then isn't it production data? It might not be customer data but it sounds like production data to me...??

In a way yes. But it is technically a development server where people started writing rogue apps and tools, and db's. Anything that is mission critical is put on the actual production servers, where we have robust backup procedures, and permissions are locked down, etc.

Whether or not it's mission critical doesn't matter. The problem is that with the current procedures someone can do exactly what they did and you will be in the exact same situation. If you can really start over with none of the data and nothing will be affected then do that and quit complaining. Otherwise you need to treat it as a production DB and implement the proper security and data integrity procedures.

 

[Descartes]

I haven't read all the replies, but a few points:

1) These things happen. Developers should be careful, but nevertheless mistakes will happen no matter what you do. If your backup plan failed, then blame the backup plan.

My strategy for preventing mistakes is to always write the where clause first. I then first write the select to verify the data before I write the delete/update. I also wrap all of my SQL statements between a conditional that's never true, so if I hit F5 (for SQL Server) I don't actually execute a batch. But again, this relies on individual discipline.

2) Singling a person out in an email is extremely unprofessional. Even if you don't refer to the person by name, they will know and so will others (not hard to figure it out).

3) You don't go to your boss with such petty mistakes. You should resolve the issues yourself, that's why you're the "boss" (personally, I hate that term as much as I do employee; they're your team and you're their leader... help them do their jobs effectively).

That's all I have. Empower them to do their jobs well, put procedures in place for when they don't and have fail-safe measures for when the inevitable happens.

 

[nakedfrog]

Originally posted by: Evadman
/* here, i wrote a trigger for you to make sure that you will be notified if this happens again */

Declare @table varchar (1024)
declare @email varchar (100)
SET @email = 'YoueEmailHere@bla.com' /* change this line to your email address */
Declare tbl_cur cursor for
select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tbl_cur

FETCH NEXT from tbl_cur INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('delete from '+ @table)
FETCH NEXT from tbl_cur INTO @table
END

CLOSE tbl_cur
DEALLOCATE tbl_Cur

That looks like it could boost performance quite a bit as well.

 

[MrPickins]

Originally posted by: DomS

Originally posted by: KingGheedora
I plan to put a trigger on the table that will throw an error if someone tries to update more than 1,000 records at once. Fuck i don't want to have to deal with managing permissions. I have to now deny everyone's dbo access to the db now, and they will have to submit scripts to me to be run on this server from now on to make changes. They can still update data but no more permissions to make modifications because that would mean they could bypass the restrictions on the table.

I used to work at a utility company and I got absolutely sick and tired of people snooping around in the guts of my DB I was creating for them, so I did something like this. It would pop up with a message telling them "get. out." when they tried to change things they shouldn't

"Nuh uh uh... You didn't say the magic word!"

 

[Cattlegod]

Does he even know he screwed up?

 

[KLin]

Originally posted by: nakedfrog

Originally posted by: Evadman
/* here, i wrote a trigger for you to make sure that you will be notified if this happens again */

Declare @table varchar (1024)
declare @email varchar (100)
SET @email = 'YoueEmailHere@bla.com' /* change this line to your email address */
Declare tbl_cur cursor for
select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tbl_cur

FETCH NEXT from tbl_cur INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('delete from '+ @table)
FETCH NEXT from tbl_cur INTO @table
END

CLOSE tbl_cur
DEALLOCATE tbl_Cur

That looks like it could boost performance quite a bit as well.

lol

 

[kyzen]

As has been said already:

1) It wasn't a production system. If it's not a production system, you should expect that horrible things can and will happen to it during testing/development. I could delete a dozen random tables from our development server, and the result would be a longer lunch while our systems team restored backups. And maybe some annoyed co-workers if I deleted some of their tables instead of just my own

2) Your backup system sucks.

If he knew what he did, he probably should have told you. However, it's entirely possible that he either didn't realize that he screwed up, or he was planning to fix it himself.

Hopefully you don't give him too hard of a time, I sincerely doubt he deserves it.

 

[Chryso]

Revoke his modify privileges.
Better revoke his delete privileges too.

 

[Kev]

I have an original thought which is as follows: I think the problem is with the backup system.