piątek, 28 czerwca 2013

Implement GOD MODE for blocking triggers with CONTEXT_INFO [MS SQL]

Sometime you have to implement blocking trigger (for DELETE, INSERT or UPDATE) saving you and your users from changing some important data in database. Blocking trigger - I mean - doing a ROLLBACK when some rules are broken.

But also sometimes you have to DISABLE TRIGGER for some maitenance tasks when you have to fix some data.

But disabling trigger has global effect. At this time no user is protected by entering wrong data into DB.

You may implement some exceptions into blocking trigger.

First (obvious) idea is to make an exception for user logged into SQL Server:

IF SUSER_SNAME() IN ('superuser1', 'DOMAIN\superuser2') RETURN

And RETURN escapes from blocking trigger at its beginning.

But..
If you are "superuser1" you are not protected by trigger at all. And you want to do maintenance only ad-hoc.

Let me introduce CONTEXT_INFO solution.

CONTEXT_INFO is a "key" set to current SQL connection - so it's ad-hoc setting. It gets VARBINARY(128) as value.

Simplest example (from MSDN) is
SET CONTEXT_INFO 0x1256698456
GO
SELECT CONTEXT_INFO()
GO

But who's gonna to remember 0x1256698456 as his very special "key"?

My way - use varchar "key" this way:

DECLARE @my_god_mode VARBINARY(128) = CAST('MY_GOD_MODE' AS VARBINARY(128))
SET CONTEXT_INFO @my_god_mode

If you put (above) into your maintenance script and (following) into blocking trigger- you have a complete solution:

IF CONTEXT_INFO() = CAST('MY_GOD_MODE' AS VARBINARY(128)) RETURN

or in more secret way.. :p

IF CONTEXT_INFO() = 0x4D595F474F445F4D4F4445 RETURN

Last tip - conversions both ways to play:

SELECT CAST('MY_GOD_MODE' AS VARBINARY(128))
SELECT CAST(0x4D595F474F445F4D4F4445 AS VARCHAR)

Have a nice god modding, guys! :)

Brak komentarzy:

Prześlij komentarz