Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DDL Trigger to prevent dropping of a specific table? Expand / Collapse
Author
Message
Posted Thursday, July 14, 2011 9:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:56 AM
Points: 55, Visits: 327
Hi,

I know DDL Triggers can monitor changes on both the Server level and Database level, which could track/prevent changes for all Tables in a dB, but is there a way to craft a trigger such that it only prevents the dropping of 1, specific table?

And if not, is there another way to prevent a table from getting dropped?
Post #1141863
Posted Thursday, July 14, 2011 9:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 12,965, Visits: 32,541
yes.
i just created and tested this trigger as a prototype.
i've created 4 tables, and tried to delete them, only one is successful, the others raise the expected error.
CREATE TRIGGER [TR_ProtectCriticalTables]
ON DATABASE
FOR
DROP_TABLE

AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
IF @oname IN ('tblBananas','tblApples','tblOranges')
BEGIN
DECLARE @err varchar(100)
SET @err = 'Table ' + @oname + ' is super duper protected and cannot be dropped.'
RAISERROR (@err, 16, 1) ;
ROLLBACK;
END
GO
ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE

CREATE TABLE [dbo].[tblBananas] ( 
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
CREATE TABLE [dbo].[tblCherries] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
CREATE TABLE [dbo].[tblApples] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
CREATE TABLE [dbo].[tblOranges] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)

DROP TABLE tblBananas
DROP TABLE dbo.[tblCherries]
DROP TABLE [dbo].[tblApples]
DROP TABLE [tblOranges]



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1141875
Posted Thursday, July 14, 2011 10:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:56 AM
Points: 55, Visits: 327
Thanks for the quick response. This is exactly what I needed and it works perfectly.
Post #1141917
Posted Thursday, July 14, 2011 10:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 12,965, Visits: 32,541
don't know if you use other schemas than the default dbo, but this slight modification to the model prevents dbl.tblBananas from being dropped, but not TestSchema.tblBananas; probably a good enhancement just in case for the future:
ALTER TRIGGER [TR_ProtectCriticalTables]
ON DATABASE
FOR
DROP_TABLE

AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@sname NVARCHAR(100),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@sname=@eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
IF @oname IN ('tblBananas','tblApples','tblOranges') and @sname = 'dbo'
BEGIN
DECLARE @err varchar(100)
SET @err = 'Table ' + @sname + '.' + @oname + ' is super duper protected and cannot be dropped.'
RAISERROR (@err, 16, 1) ;
ROLLBACK;
END
GO
ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE



CREATE SCHEMA TestSchema
CREATE TABLE [TestSchema].[tblBananas] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)

DROP TABLE [TestSchema].[tblBananas]



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1141931
Posted Thursday, July 14, 2011 10:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
I have to ask: Why are you worried about a table being dropped? Why is it okay if other tables get dropped?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1141934
Posted Thursday, July 14, 2011 12:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:56 AM
Points: 55, Visits: 327
Well, the reason I am worrying about this one specific table being dropped is because it is used to store information from another DDL trigger we have setup on the database that monitors all database actions.

Last week, one of our developers dropped every table/proc/view from the dbo. schema on our development box and either covered their tracks really, really well or our default 'black box' trace for some reason did not capture anything regarding it. We only lost a day of coding since we had a full backup from 1am to restore from. Since then, we implemented 1 DDL trigger to put all database events into a table and want to prevent the developers from dropping it.

So, in essence, if someone decides to drop everything again, at least we will still have this table to tell us what happened. Hmmmm.....unless they delete everything from the table? I may have to think about how to handle that issue as well.

Edit: decided on putting a normal table trigger on this table to prevent deletions. Also, modified the original DDL trigger to not log anything to do with Updating Statistics since it creates about 400 records a night that really serve no purpose and added a weekly job to remove records older then 7 days.

....Auditing...fun for the whole family!
Post #1142018
Posted Thursday, July 14, 2011 12:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 12,965, Visits: 32,541
upstart (7/14/2011)
... either covered their tracks really, really well or our default 'black box' trace for some reason did not capture anything regarding it....


In my career I've seen mostly careless mistakes and bad assumptions that affect schemas/ data like that.
I haven't found an evil developer covering his tracks yet...but i've seen a few posts here on SSC for newbies wanting to hide their big errors so they don't get fired.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1142023
Posted Thursday, July 14, 2011 1:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:56 AM
Points: 55, Visits: 327
OK, so know I have a DDL trigger that logs all events, a DDL trigger to prevent this specific table from being dropped, and a normal table trigger to prevent the deletion or updates of records from this table.

I can't think of any other action a user can do to the table other then Truncating it. Is there a way to prevent Truncating a table? Won't truncating override any triggers on the table?
Post #1142058
Posted Thursday, July 14, 2011 1:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
How about simply denying them permission to access that database through normal SQL Server security practices. The DDL trigger to log database events will need to have permission to write to the table, but it can run under an account designed for that. Then you eliminate whole levels of complexity.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1142061
Posted Thursday, July 14, 2011 2:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:56 AM
Points: 55, Visits: 327
I thought of this as well, but I think because this is a Development box, we have a lot of people setup as sysadmins who need access to drop/create/alter objects at will.

Because of the natural sysadmin rights, wouldn't they basically be able to bypass all of the extra security or just undo it if they needed?

Maybe security is the answer and I am doing overkill with the Triggers and Logging...but I had some free-time to kill and it taught me a little something about DDL trigger I guess.
Post #1142072
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse