July 12, 2012 at 12:07 am
Comments posted to this topic are about the item SQL Triggers in a Security Context
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
July 12, 2012 at 3:40 am
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
July 12, 2012 at 5:31 am
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:
SELECTdec.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:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
July 12, 2012 at 11:52 am
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.
July 12, 2012 at 12:03 pm
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.
July 12, 2012 at 2:31 pm
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:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
July 12, 2012 at 3:30 pm
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/
July 12, 2012 at 3:32 pm
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.
July 12, 2012 at 3:39 pm
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:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
July 12, 2012 at 4:25 pm
derek.colley (7/12/2012)
TimothyGood 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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply