SQL Triggers in a Security Context

  • derek.colley

    SSCrazy Eights

    Points: 8040

    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 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.

  • kevaburg

    SSCoach

    Points: 17910

    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

    SSCrazy Eights

    Points: 8040

    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 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.

  • DaVinci007

    SSC Enthusiast

    Points: 152

    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

    SSCertifiable

    Points: 5649

    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

    SSCrazy Eights

    Points: 8040

    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 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.

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    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

    SSC Enthusiast

    Points: 152

    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

    SSCrazy Eights

    Points: 8040

    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 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.

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    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/

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply