Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Triggers in a Security Context


SQL Triggers in a Security Context

Author
Message
derek.colley
derek.colley
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 603
Comments posted to this topic are about the item SQL Triggers in a Security Context

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

kevaburg
kevaburg
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 Visits: 944
Hi! Nice article so, thanks!

I have an interest though in DDL triggers in a security context with regards to accountability.

I am interested in a trigger that allows me to see whether someone has attempted to drop a database (a trigger exists to prevent the DROP being successful) or create one and at the information I would like to see is the name of the Windows user logged on, the name of the SQL Server user that is logged on and the event date. What I would also like to see is the name of the database this person has attempted to drop or even better, the TSQL that this person has attempted to execute.

Do you have any ideas....?

Regards to all!

Kev
derek.colley
derek.colley
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 603
Hi Kev, one way of doing this would be to append some code to your existing trigger. So when a database drop attempt is detected, you can capture the details you want (full SQL code):

I've written this - feel free to add/remove cols as you see fit:



SELECT dec.session_id, dec.connect_time, dec.protocol_type, dec.client_net_address,
dec.local_net_address, TEXT,
des.[host_name], des.[program_name], des.client_interface_name
FROM sys.dm_exec_connections dec
INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST




You could write a sproc to drop this into a table, and/or configure with alerting (including the table contents), or similar, for automatic notification.

Derek.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

DaVinci007
DaVinci007
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 82
Nice article. I would like to point out however, that in your article, your suggestion to prevent Bob from disabling the trigger is invalid. First, you provide a way to detect if Bob DROPs the trigger, not DISABLEs it.

Therefore, solution #2 would not work if Bob simply disables the trigger either from code or from SSMS. Additionally, there is a known problem on SQL Server where the DISABLE TRIGGER command is not caught by DDL triggers. See here: http://connect.microsoft.com/SQLServer/feedback/details/509242/fire-a-ddl-trigger-when-the-new-syntax-disable-trigger-is-executed

So the correct trigger would be:

USE HR
GO
CREATE TRIGGER dbo.stopDroppingTriggers
ON DATABASE
FOR ALTER_TABLE
BEGIN
DECLARE @xmlEventData XML;
DECLARE @DDLStatement nvarchar(max);
SELECT @xmlEventData = EVENTDATA();
SET @DDLStatement = @xmlEventData.value( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)' );
IF @DDLStatement like '%disable%trigger%'
BEGIN
ROLLBACK;
PRINT 'Dropping triggers is not allowed.'
END
END

But even with this DDL trigger, it would only cover the use case when a user disables the trigger by using the ALTER TABLE syntax, it would not cover the DISABLE TRIGGER syntax and if the user uses SSMS's right-click disable trigger feature because of the defect in SQL Server.
Marcia J
Marcia J
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 1889
Derek,

I loved the way you used a problem you ran into as an opportunity to educate the rest of us.

Also, in regards to e-mailing the apology, I've found that most people don't get angry if you make an occasional mistake as long as you take ownership of it and resolve it as you did. It's the person who makes mistakes and then tries to blame everyone else that makes people angry. Quickly admitting to a problem and then resolving it smoothes over a lot of problems.
derek.colley
derek.colley
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 603
DaVinci007 - you're absolutely right, it is quite easy to get around this trigger. I suppose my point was really that this approach would supplement more powerful security measures (like setting up role-based access) rather than provide an end-to-end solution. Thanks for the heads up on the SQL Server defect with triggers - this is new to me, so made interesting reading.

Marcia - thank you for the kind comments! Everyone makes mistakes (who hasn't killed a server in their time?). The likelihood of repeating a mistake, I think, is inversely proportional to the severity of the mistake that was made (the obvious extreme of this of course is getting fired!)

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)

Group: General Forum Members
Points: 892 Visits: 920
Hello,

Thank you for providing the article, it was certainly interesting.

However, I have to caution that I would not really advise using triggers like this. For one thing, as the article itself alludes, triggers may not be the most effective mehtod. If you wanted to ensure someone did could not delete records, it would be much better to deny them delete permissions.

Additionally, triggers create "hidden code", code that can run when developers do not expect it to. This can cause unexpected errors as well as make it difficult to diagnose performance issues. For that reason, I generally recommend using triggers only where no other solution fully covers the need and documenting them carefully.

There is one use for triggers I have encountered that does justify the "hidden code" dangers and has security implications, at least tangentially. Triggers are very useful for creating a thorough change log regardless of how the change is made. Such change logs have often been useful for me when the end users need a history of the table. But they can also be useful in diagnosing problems, including problems that are caused by people with malicious intent. But of course even in that case someone with the right security permissions can disable the trigger, make their change, and reenable it, so logging should not provide a false sense of security.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
DaVinci007
DaVinci007
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 82
More than reading great articles, I like to talk with a true researcher who is open to different opinions.

Continue your great work! Your article is great to see different ways to implement sql security.
derek.colley
derek.colley
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 603
Timothy

Good points and a trigger shouldn't be considered the principal method for securing a database. Your comments about a change log - have you considered using Kimball tables? These are the Type 2, 3, 4 and 6 tables that enable you to log changes to tables in different ways (also called Slowly Changing Dimension (SCD) tables, used in SSAS too).

This provides a way of keeping a full record of all changes (should you desire) in a tabular format rather than looking at a wall of SQL code. Upsides: comprehensive, full audit trail. Downsides: extra resources required for IO, on a heavily used table you might start seeing page IO latches increasing, and correspondingly an increase in waits.

Plus the output is easily readable to a non-technician, in a Type 4 table a simple SELECT from the history table will give a readable, tabular output which you can port to Excel for a management summary.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)

Group: General Forum Members
Points: 892 Visits: 920
derek.colley (7/12/2012)
Timothy

Good points and a trigger shouldn't be considered the principal method for securing a database. Your comments about a change log - have you considered using Kimball tables? These are the Type 2, 3, 4 and 6 tables that enable you to log changes to tables in different ways (also called Slowly Changing Dimension (SCD) tables, used in SSAS too).



I haven't run into Kimball tables before, and a quick google search seems to lead me mostly to pages about wooden tables. Is there a reference you would recommend?

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search