Condividire...

Condividere ha il suo costo, ma alla fine finisce per arricchire tutti


Sharing has its cost, but at the end leads everyone to a better knowledge

mercoledì 10 febbraio 2016

SQL Server: Audit Security events


Few days ago a customer asked me to implement a security auditing solution for their production instances of SQL Server, to track users and logins management events. I immediately thought about the native SQL Audit function, but there was a problem, all users gain access to SQL Server with the same login, so to identify exactly who I had to capture the hostname field; unfortunately, the available fields in the SQL Server audits are predefined and not mdifiable (at least not easily) and among them the hostname is missing.
At that point I decided to use DDL triggers, which unlike the DML, have not a significant impact in terms of performances.
First of all we have to understand which events intercept, using this query you can have a hierarchical view of available events:

WITH EventsReports (name, PARENT_TYPE, type, level, sort) AS
(
    SELECT CONVERT (varchar (255), type_name), PARENT_TYPE, type, 1, CONVERT (varchar (255), type_name)
    FROM sys.trigger_event_types 
    WHERE PARENT_TYPE  IS NULL
    UNION ALL
    SELECT CONVERT (varchar (255), REPLICATE ( '|', level) + e.type_name),
        e.parent_type, e.type, level + 1,
    CONVERT (varchar (255), RTRIM (sort) + '|' + e.type_name)
    FROM sys.trigger_event_types  as e
        INNER JOIN EventsReports as d
        ON  e.parent_type =  d.type
  )
SELECT PARENT_TYPE, type, name
FROM EventsReports
-- where [name] like '% object%'
ORDER BY sort;

The event name is quite explicative, but in the dubt has a look here

So we decided to track these events: DDL_DATABASE_SECURITY_EVENTS, DDL_DATABASE_EVENTS, DDL_SERVER_SECURITY_EVENTS

Then I have built the DDL trigger.

First let's create a DB and the table that will store our events:

Create DataBase AdminDB;
go
use AdminDB;
go
CREATE TABLE dbo.DDL_Security_Audit_LOG  ( EventId int NOT NULL IDENTITY (1, 1) PRIMARY KEY, EventTime datetime , DbName nvarchar(100), EventType nvarchar(100) , UserName nvarchar(100) ,HostName nvarchar (100) , EventTSQL nvarchar(3000) , Data_XML xml);


Then our DDL Trigger:


Create TRIGGER [DDL_Security_Audit]
ON ALL SERVER
FOR DDL_DATABASE_SECURITY_EVENTS, DDL_DATABASE_EVENTS, DDL_SERVER_SECURITY_EVENTS
AS
BEGIN

Declare @ObjectName nvarchar(100) =  EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')

Declare @EventType nvarchar(100) =  EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

Declare @DbName  nvarchar(100) = isnull( EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(1000)'),'Master')

Declare @Command nvarchar(1000) =  EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(1000)')

INSERT INTO AdminDB.dbo.DDL_Security_Audit_LOG
 VALUES ( GETDATE() ,  @DbName, @EventType, suser_sname() , HOST_NAME(), @Command, EVENTDATA()  )

END
GO

Then remember to create a JOB to purge old data (if you want to)

Declare @CutOffDate datetime =  DATEDIFF(d, -30, getdate())
Delete from AdminDB.dbo.DDL_Audit_Security_TABLE_LOG where EventTime < @CutOffDate

Now you have all info you need to monitor your security events at Server and DataBase level.

Just notice in SQL Server there a security hole: no DDL events avalaible for disable Trigger action.

(Thanks to Alessandro, Matteo, Simone and "Rocco" for allowing me to publish this)






5 commenti:

  1. reasonable workaround for troubleshooting and general activity logging but not usable for legal or regulatory requirements. hostname is not a reliable, tamper evident data source. If someone who has access to the shared login, it takes just minutes and minimal skills to spoof the hostname presented to SQL Server. If the customer needs the audit for potential compliance requirements, legal investigations, etc... they'll need to rethink their current security model.

    RispondiElimina
  2. You're totally right. This solution just tracks not very skilled users potentially dangerous activities. I suggested my customer to review the user's access policy, but in the while this trigger does its work.

    RispondiElimina
  3. How to find users permission ?

    RispondiElimina
    Risposte
    1. I haven't understood exactly your question, which infos about users permissions are you willing to get?

      Elimina
  4. I have the following error: "SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations"

    Should I insert the settings on the trigger:
    SET CONCAT_NULL_YIELDS_NULL ON?

    RispondiElimina