Home Dashboard Directory Help
Search

Fire a DDL TRIGGER when the new syntax "DISABLE TRIGGER" is executed by erbellico


Status: 

Closed
 as Won't Fix Help for as Won't Fix


17
0
Sign in
to vote
Type: Suggestion
ID: 509242
Opened: 11/9/2009 2:59:30 AM
Access Restriction: Public
0
Workaround(s)
view

Description

There is a big security hole in SQL Server 2008, that we discovered : there is this new syntax "DISABLE TRIGGER" (as opposed to legacy syntax ALTER TABLE DISABLE TRIGGER).

We discovered that this new syntax, contrary to any other DDL statement :
- does not fire any DDL TRIGGER (therefore we can't audit not forbid the usage)
- is not caught in the profiler (therefore we don't know who uses it)

This seems to be "as designed" in the product (see https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=434951&lc=1033&id=64416&wa=wsignin1.0 and affiliates )

This is unacceptable in a professional environment, and ruins any security model based on "Management by policy" (and not only for us : others on the internet are claiming this is a bug)

The proposed workarounds that we have read, are ranging from "discipline" of "not using this syntax" (easier said than done, since DbA cannot control nor enforce this discipline !). Even more asinine would be to upgrade to Enterprise Edition to be able to audit this... More constructive is to revoke the entire "ALTER" right for persons not supposed to do this. However this is _really_ overkilling, and is not acceptable either, when DbA want to delegate part of the Release Management to the Application department, and control them using DDL Triggers.
Details
Sign in to post a comment.
Posted by BrotherMiles on 8/27/2012 at 4:47 PM
Hi,

To Microsoft this might not be a problem but to those of us who would like to use what Microsoft produces it is a problem.

I would like some event in the Disable Trigger process where I can capture some information and notify a DBA or two. The reason is strictly security.

If I am trying to protect data by disabling both the update and delete of data to a table, such that all inserts are always kept as they were inserted, I need to know that the the triggers never are disabled unless the administrator says they can be disabled and gives permission to the DBA to do such. If I can not determine when a deny delete or deny update trigger is disabled I can not tell who disabled it nor when it was disabled.

I can however and do have an automated process that will run scheduled using the SMO to parse the database metadata and record the date of the creation of each trigger and the update of each trigger. This gives me an indication that something changed but does not give me the datetime that I could have if there was an event on the disable or enable trigger events.

If I am wrong and there is one other then what is being denied for user to use as a security precaution I would like to know it. But for now not having this is very disappointing.
Posted by ajhuddymsdn on 6/28/2012 at 1:11 PM
I'm astounded this has been closed and will not be addressed.

I just put in place a complete DML auditing mechanism through the use of triggers. Naturally, if Joe-Smart-Guy wants to disable the auditing, all he needs to do is disable the triggers. Thankfully, there are DDL triggers in SQL Server, and the DROP_TRIGGER and ALTER_TRIGGER events!

...whoa! hold on a minute, it seems that the DISABLE TRIGGER, and ENABLE TRIGGER commands do not fire DDL events!

Please fix this.
Posted by Microsoft on 3/18/2011 at 2:04 PM
Hello,

Thank you for submitting this suggestion, but we're trying to clean house and remove items we feel we will likely not address given their priority relative to other items in our queue. We believe it is unlikely that we will address this suggestion, and so we are closing it as “won’t fix”.

This cleaning will help us focus on the high-priority items that we feel need to get done, and we hope that it help provide better clarity to you about the issues we will (and won't) address.

While we've tried to look at each of these items individually, this was a large effort, and so we may have erred in assessing this particular suggestion's priority. If you feel that this is worth reconsidering, please feel free to re-file it and we will be happy to take another look.

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 2/1/2010 at 2:57 PM
Hi Phillipe & Erbellico,
The behavior change to make DAC handle the firing of DDL triggers is not something we will do in a service pack or cumulative update. These type of issues do not meet the bar. So unfotunately, this has to be done in a major version of SQL Server.

Thanks
Umachandar
Posted by Philippe_R on 12/11/2009 at 7:22 AM
Hello Umachandar,

Thanks for the answer. Is it possible to introduce such a change (allow DISABLE TRIGGER) only via DAC in a Service Pack or Cumulative Update? As erbellicao says it's really a breach that allows anyone who has the ALTER right to do such command.

The fact is that Dba's have no choice, as it is not a DDL event, except to implement a special trace (as it is not in the default trace) to follow up such a change by a user. That's an overhead on production system. Same question as before: is it possible to have a trace event so that a trigger or the brooker service can use it or include this kind of event in the default trace in a Service Pack or Cumulative Update?

Kind regards,
--
Philippe
DBA project Manager
Posted by erbellico on 12/10/2009 at 4:58 AM
Thanks Umachandar for the insight.
I understand the concern now and why this queer syntax was introduced.

Still seems surprising to me, that MS introduces a syntax that will compromise security, just to cover a foolish scenario of some DbA shooting themselves in the foot, or (worse) who leave their system wide open to untrustworthy users.

What I cannot understand from your explanation, is why not a dedicated right for this very-special-and-limited-backdoor ? Why letting anyone with ALTER rights run this "reserved" syntax ?

And a request : waiting for next revisions, wouldn't it be possible to add a configuration slot, or trace flag, to let DbA choose whether to enable or disable this syntax at configuration surface ? (taking therefore entire responsibility of shooting themselves in the foot if they like to).

Thanks again for your answers :-)
Posted by Microsoft on 12/8/2009 at 12:51 PM
Hi,
After some more analysis, we have determined that the current behavior is not a security issue. Here is the rationale for DISABLE|ENABLE TRIGGER statements not firing DDL triggers:

1. If someone creates a DDL rogue trigger that rolls back any DDL statement then there will be no mechanism to disable the rogue DDL trigger or DROP it. This statement provides someone with appropriate permissions to disable the rogue trigger and take action
2. The same applies to DML trigger also. Since DML trigger can also do something like the same and possibly create a DDL trigger that rolls back any DDL.

So the DISALBE|ENABLE TRIGGER statements provides a mechanism for DBAs / administrators to resolve such situations.
After looking at this closely though we feel that the current behavior is not in line with that of LOGON triggers behavior. For example, LOGON triggers are not fired when you connect to SQL Server using a Dedicated Admin Connection (DAC). This provides a backdoor for DBAs to disable/drop a logon trigger that prevents connections to SQL Server.
So for a future version of SQL Server, we will go with the same behavior for DDL triggers too i.e., connections via DAC will not fire DDL triggers and you can use it to disable rogue DDL/DML triggers or take other actions. At that point, we will fire DDL triggers for DISABLE|ENABLE TRIGGER statement too. You cna always use audit mechanisms to track these DDL statements.

Thanks
Umachandar, SQL Programmability Team


Posted by siener on 11/26/2009 at 7:53 AM
Our support department sometimes needs to temporarily disable some triggers in our clients' databases in order to fix data in the system.

The problem is that it has happened more than once that someone forgets to re-enable the triggers when they are done. DDL triggers seemed heaven sent. That way we could be notified when any triggers in the system are disabled.

That is until we realized that the DISABLE TRIGGER statement does not fire a DDL event. If this is not a bug, it is definitely an unfortunate oversight.
Posted by Microsoft on 11/20/2009 at 11:30 AM
Hi,

Thankyou for this report. I checked with our Security Team upstairs, just in case there was some rationale for this design which I was unaware of.

I have assigned this over to the Developer in charge of the area to repro and fix. (I've also raised its Severity and Priority so this gets to the top of the list)

Thankyou!

Jim Hogg
Posted by Philippe_R on 11/9/2009 at 4:00 AM
Totally agree on that.
By the way there's no way to trace it (even in the profiler, I tested it) and the 2008 SQL BOL is not verbose on the subject.
Why allowing this (DDL?) statement not to be audited?
Thanks for pointing out that annoying breach in the security.
I hope a solution or workaround will be find.
Sign in to post a workaround.