November 17, 2011 at 9:58 am
Often we use "delete from xyz where name = 'mike'" to delete a rows.
But it is possible for a developer to forget the where clause and accidentally execute "delete from xyz".
As a DBA how can one prevent scenarios like these? I've searched for options but couldn't find any.
November 17, 2011 at 10:00 am
That's called a maintenance plan.
Backup, test the backups.
Keep the backups as long as necessary.
Shit happens, you can <mostly> prepare to repair the damage.
Change control management is an extra protection on the prod servers.
November 17, 2011 at 10:07 am
Or Deny delete permissions to the developers! 😉
November 17, 2011 at 10:09 am
Ninja's_RGR'us (11/17/2011)
That's called a maintenance plan.Backup, test the backups.
Keep the backups as long as necessary.
Shit happens, you can <mostly> prepare to repair the damage.
Change control management is an extra protection on the prod servers.
But before all this:
1) Reviews
2) ACLs / Permissions
3) NO R&D on PROD
4) ... (guys help me here)
November 17, 2011 at 10:11 am
Ninja's_RGR'us (11/17/2011)
That's called a maintenance plan.Backup, test the backups.
Keep the backups as long as necessary.
Shit happens, you can <mostly> prepare to repair the damage.
Change control management is an extra protection on the prod servers.
Well they shouldn't have access to prod for 1.
For 2 they should have application access in QA.
dbo for dev is fine so that they can have access to all the tools they want / need for new stuff.
They can destroy that system all they want. It should be rebuildable via scripts anyways ;-).
November 17, 2011 at 10:12 am
Dev (11/17/2011)
Ninja's_RGR'us (11/17/2011)
That's called a maintenance plan.Backup, test the backups.
Keep the backups as long as necessary.
Shit happens, you can <mostly> prepare to repair the damage.
Change control management is an extra protection on the prod servers.
But before all this:
1) Reviews
2) ACLs / Permissions
3) NO R&D on PROD
4) ... (guys help me here)
NO ACCESS to prod. End of problems.
See previous message for other points. 🙂
November 17, 2011 at 10:15 am
Gazareth (11/17/2011)
Or Deny delete permissions to the developers! 😉
Even qualified DBAs can do this messup...
DELETE FROM <TABLE1>
WHERE a='X' -- OOPS... Forgot to select before I hit Execute
November 17, 2011 at 10:20 am
Dev (11/17/2011)
Gazareth (11/17/2011)
Or Deny delete permissions to the developers! 😉Even qualified DBAs can do this messup...
DELETE FROM <TABLE1>WHERE a='X' -- OOPS... Forgot to select before I hit Execute
Hence, tested backup & DR.
November 17, 2011 at 11:05 am
Thanks for your valuable suggestions. Please excuse me if the following sounds stupid (I am not much of a sql expert).
I found something on this forum that I've tried and it worked.
I put a delete trigger on the table that I want to prevent accidental executions of delete from without a where clause.
I am not worried about other forms of delete that involve joins. My simple goal is not to allow the execution of delete without a where clause.
from the discussion and post by Lowell in http://www.sqlservercentral.com/Forums/Topic958337-146-1.aspx#bm958451
I created a delete trigger on the table.
Like
alter trigger [dbo].[trg_ID_PreventDelete] on [dbo].[PreventDelete] for delete --INSTEAD OF DELETE
as
begin
declare @DeleteCommand nvarchar(max)
declare @Buffer table
(
[EventType] nvarchar(30),
[Parameters] int,
[EventInfo] nvarchar(4000)
)
insert @Buffer
exec sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
select @DeleteCommand = EventInfo
from @Buffer
if(patindex('%where%', @DeleteCommand) = 0)
begin
raiserror( 'Deleting all the data in table is denied', 16, 1 )
rollback tran
end
end
go
It seemed to work if I just execute delete from PreventDelete without a where. If I specified a where it executed fine. Please let me know if this is safe and as always thanks for your help. 🙂
November 17, 2011 at 11:09 am
The problem with this is the high cost of getting that info.
I'd much rather use something else like the rowcount or something.
That being said it would still be option 197 on the list after correct permissions and disaster recovery.
November 17, 2011 at 11:18 am
I originally had the rowcount but I thought it would have concurrency issues. Thanks. I am considering this topic solved.
alter trigger [dbo].[trg_ID_PreventDelete] on [dbo].[PreventDelete] instead of delete
as
begin
declare @NumRowsDeleted bigint,
@TotalRows bigint
select @NumRowsDeleted = count(*) from deleted
select @TotalRows = count(*) from dbo.[PreventDelete]
if @NumRowsDeleted = @TotalRows
begin
raiserror( 'Deleting all the data in table is denied', 16, 1 )
rollback tran
end
else
begin
delete from pd
from [dbo].[PreventDelete] pd
inner join deleted on deleted.CategoryID = pd.CategoryID --CategoryID is the primary key
end
end
November 17, 2011 at 11:30 am
I had @@rowcount in mind! Not too sure if it holds the correct value in the trigger but it should.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply