Аудит схемы SQL сервера?
У нас есть база данных SQL Server 2008 Enterprise с двумя разными схемами: заблокированная, которую мы поддерживаем, и открытая, которую мы разрешаем сторонним разработчикам добавлять и изменять в соответствии со своими потребностями. Обычно это работает хорошо для нас, но одна конкретная команда любит действительно испортить это, и это влияет на всех остальных. Итак 2 вопроса:
- Оглядываясь назад, я хотел бы, чтобы с самого начала мы установили что-то надежное, но мы этого не сделали, только установку по умолчанию. Было бы неплохо увидеть, что было сделано со схемой до сих пор, даже если это так просто, как "Пользователь XYZ изменил процедуру ABC 12 июля 2012 года в 9:00". Есть ли что-то встроенное в SQL Server и включенное по умолчанию, которое отслеживает это, что мы могли бы использовать, и если да, то где и как?
- Что касается долгосрочного решения, что бы вы порекомендовали для этого? Я немного читал о триггерах DDL, и это кажется многообещающим вариантом. Если вы использовали этот подход, можете ли вы немного рассказать о том, как он работает и что вы можете с ним сделать?
благодарю вас
4 ответа
У меня есть система, которая использует триггер DDL именно для этого типа вещей. Это работает достаточно хорошо для моих нужд. Первоначально он был разработан на Sql Server 2005, а теперь живет в системе Sql Server 2008R2. Это похоже на тот, который описан по ссылке в комментарии Аарона Бертранда.
Создайте таблицу, похожую на эту.
CREATE TABLE [Audit].[SchemaLog](
[SchemaLogID] [int] IDENTITY(1,1) NOT NULL,
[PostTimeUtc] [datetime] NOT NULL,
[DatabaseUser] [nvarchar](128) NOT NULL,
[Event] [nvarchar](128) NOT NULL,
[Schema] [nvarchar](128) NULL,
[Object] [nvarchar](128) NULL,
[TSQL] [nvarchar](max) NOT NULL,
[XmlEvent] [xml] NOT NULL,
CONSTRAINT [PK_SchemaLog_1] PRIMARY KEY CLUSTERED
(
[SchemaLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Убедитесь, что у всех есть разрешения на вставку в таблицу, а затем создайте триггер ddl, подобный этому.
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
INSERT [Audit].[SchemaLog] (
[PostTimeUtc]
, [DatabaseUser]
, [Event]
, [Schema]
, [Object]
, [TSQL]
, [XmlEvent] )
VALUES (
GETUTCDATE()
, CONVERT(sysname, CURRENT_USER)
, @eventType
, CONVERT(sysname, @schema)
, CONVERT(sysname, @object)
, @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
, @data );
END;
Варианты Redgate следующие.
1) Самый простой способ начать аудит изменений схемы - это установить DLM Dashboard. Это бесплатный инструмент, который оповещает и регистрирует все изменения, используя триггеры DDL, и будет включать запрашиваемую информацию.
2) Как уже упоминал Энди Дэвис, правильный способ сделать это - начать управлять исходным кодом своей схемы так же, как вы делаете это для кода приложения. Сделав это, вы можете повысить уровень зрелости управления жизненным циклом своей базы данных, включив ее в непрерывную интеграцию и практику управления выпусками.
Вы можете посмотреть, как поставить свою базу данных под контроль исходного кода. Возможно, заставить каждую внешнюю команду взять ветку или ветвь вашей базы данных. Это дает вам возможность аудита изменений с помощью коммитов и возможность выбирать, какие изменения объединять, и / или просматривать / редактировать эти изменения перед объединением.
Посмотрите на http://www.red-gate.com/products/sql-development/sql-source-control/ для одного продукта, который доступен.
В сочетании с такими решениями, как Github или Bitbucket, которые открывают ваши репозитории управления версиями более напрямую для внешних участников через Интернет.
Для второго вопроса вы можете рассмотреть триггеры в качестве опции. В следующем примере информация о событии, которое активировало триггер, захватывается с помощью функции EVENTDATA() SQL Server. Сценарий SQL создает триггер DDL, который перехватывает события CREATE, ALTER и DROP на уровне базы данных (хотя на уровне сервера можно создавать триггеры для захвата событий для всех баз данных на сервере; вместо параметра ON ALL SERVER следует использовать вместо НА БАЗЕ ДАННЫХ)
CREATE TRIGGER Audit_DDL ON DATABASE
FOR CREATE_TABLE , ALTER_TABLE , DROP_TABLE
AS
DECLARE
@event xml;
SET @event = EVENTDATA(
);
INSERT INTO Audit_DDL_Events
VALUES( REPLACE( CONVERT( varchar( 50
) , @event.query( 'data(/EVENT_INSTANCE/PostTime)'
)
) , 'T' , ' '
) ,
CONVERT( varchar( 150
) , @event.query( 'data(/EVENT_INSTANCE/LoginName)'
)
) ,
CONVERT( varchar( 150
) , @event.query( 'data(/EVENT_INSTANCE/UserName)'
)
) ,
CONVERT( varchar( 150
) , @event.query( 'data(/EVENT_INSTANCE/DatabaseName)'
)
) ,
CONVERT( varchar( 150
) , @event.query( 'data(/EVENT_INSTANCE/SchemaName)'
)
) ,
CONVERT( varchar( 150
) , @event.query( 'data(/EVENT_INSTANCE/ObjectName)'
)
) ,
CONVERT( varchar( 150
) , @event.query( 'data(/EVENT_INSTANCE/ObjectType)'
)
) ,
CONVERT( varchar( max
) , @event.query( 'data(/EVENT_INSTANCE/TSQLCommand/CommandText)'
)
)
);
Также должна быть создана соответствующая таблица хранения данных аудита из EVENTDATA XML:
CREATE TABLE Audit_DDL_Events( DDL_Event_Time datetime ,
DDL_Login_Name varchar( 150
) ,
DDL_User_Name varchar( 150
) ,
DDL_Database_Name varchar( 150
) ,
DDL_Schema_Name varchar( 150
) ,
DDL_Object_Name varchar( 150
) ,
DDL_Object_Type varchar( 150
) ,
DDL_Command varchar( max
)
);