mercoledì 10 febbraio 2016
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)
WHERE PARENT_TYPE IS NULL
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
-- 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;
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
Declare @ObjectName nvarchar(100) = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)', 'nvarchar(100)')
Declare @EventType nvarchar(100) = EVENTDATA().value('(/EVENT_INSTANCE/EventType)', 'nvarchar(100)')
Declare @DbName nvarchar(100) = isnull( EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)', 'nvarchar(1000)'),'Master')
Declare @Command nvarchar(1000) = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)', 'nvarchar(1000)')
INSERT INTO AdminDB.dbo.DDL_Security_Audit_LOG
VALUES ( GETDATE() , @DbName, @EventType, suser_sname() , HOST_NAME(), @Command, EVENTDATA() )
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)