Руководство по обеспечению соответствия требованиям в SQL Server

 Авторы:  Джей-Си Кэннон (JC Cannon), Денни Ли (Denny Lee)

Соавторы: Энди Робертс (Andy Roberts), Айяд Шаммут (Ayad Shammout)

Технические редакторы:    Дэн Джонс (Dan Jones), Крэйг Гик (Craig Gick), Джек Ричинс (Jack Richins), Рауль Гарсия (Raul Garcia), Девендра Тивари (Devendra Tiwari), Стивен Готт (Steven Gott), Эл Комо (Al Comeau), Лара Руббелке (Lara Rubbelke)

Публикация: ноябрь 2008 г.

Область применения: SQL Server 2008 и новее.

Сводка.   В этом документе представлены общие сведения о подходах к обеспечению соответствия требованиям при управлении базами данных SQL Server. В нем описаны компоненты SQL Server 2008, имеющие отношение к обеспечению соответствия требованиям и их применению к средствам управления ИТ. В документ включены советы и сценарии, помогающие быстро начать разработку решений для обеспечения соответствия требованиям.

Примечание. Можно загрузить образцы файлов для этого документа.

Авторские права

Сведения, содержащиеся в данном документе, отражают текущую позицию корпорации Майкрософт по рассматриваемым вопросам на момент публикации документа. Поскольку корпорации Майкрософт приходится реагировать на изменение рыночных условий, текст документа не может рассматриваться как обязательство со стороны корпорации Майкрософт. Корпорация Майкрософт не гарантирует достоверности предоставленной информации после даты публикации.

Данный технический документ предоставляется исключительно в ознакомительных целях. КОРПОРАЦИЯ МАЙКРОСОФТ НЕ ДАЕТ В НЕМ НИКАКИХ ЯВНЫХ ИЛИ ПОДРАЗУМЕВАЕМЫХ ГАРАНТИЙ.

Ответственность за соблюдение всех авторских прав целиком и полностью несет пользователь. Без ограничения авторских прав ни одна из частей этого документа не может быть воспроизведена, сохранена или использована в системах поиска либо передана в любой форме, любыми способами (электронными, механическими, в виде фотокопии, в виде записи или любыми другими) и в любых целях без письменного разрешения корпорации Майкрософт.

Корпорация Майкрософт может иметь патенты, патентные заявки, охраняемые товарные знаки, авторские или другие права на интеллектуальную собственность применительно к содержимому этого документа. Без письменного разрешения корпорации Майкрософт данный документ не дает лицензии на эти патенты, товарные знаки, авторские права и другую интеллектуальную собственность.

За исключением специально отмеченных случаев описанные здесь компании, организации, товары, имена доменов, адреса электронной почты, эмблемы, люди, места и события являются вымышленными; любые ассоциации с какими-либо реальными компаниями, организациями, товарами, именами доменов, адресами электронной почты, эмблемами, лицами, местами и событиями непреднамеренны и случайны.

© Корпорация Майкрософт (Microsoft Corporation), 2008. Все права защищены.

Microsoft, Active Directory, ActiveX, BitLocker, Excel, Internet Explorer, PivotTable, PowerShell, SQL Server, Vista, Visual Basic, Visual Studio, Windows, Windows Server и Windows Vista являются товарными знаками группы компаний Майкрософт.

Все прочие товарные знаки являются собственностью их владельцев.

Введение

В любой стране мира организациям приходится обеспечивать соответствие требованиям различных нормативов. Кроме того, им приходится совершенствовать управление своими ИТ-системами для поддержания необходимого уровня безопасности и эффективности их эксплуатации. Клиенты часто обращаются к Майкрософт за советами и технологиями, которые могли бы помочь организациям в этом. Технический документ «Руководство по обеспечению соответствия требованиям в SQL Server 2008» специально предназначен в помощь компаниям и лицам, которые хотели бы научиться использовать программные компоненты баз данных Microsoft® SQL Server® 2008 для решения своих задач по обеспечению соответствия требованиям. Этот документ служит дополнением к пакету средств разработки программного обеспечения (SDK) по обеспечению соответствия требованиям SQL Server 2008, который содержит образцы кода и основные сведения о компонентах SQL Server 2008 по обеспечению соответствия требованиям и их применению при разработке решений. 

Как пользоваться этим документом

Этот документ содержит руководство по обеспечению соответствия требованиям на различных уровнях. В его первой части описаны основные понятия о соответствии требованиям, где для сведения рисков к минимуму могут использоваться средства управления ИТ, а также технологии, которые могут быть для этого применены. Вторая часть посвящена конкретным сценариям обеспечения соответствия требованиям, в частности защите платформы и данных. Третья часть документа содержит образцы кода, примеры и советы, которые могут быть использованы для повышения темпов создания специализированных решений на основе SQL Server 2008. При создании средств управления ИТ также может быть использован сам по себе пакет SDK. В этом вам поможет поставляемый вместе с ним файл Readme.

Основные вопросы

Основная цель этого документа — дать общие навыки применения SQL Server 2008 для целей обеспечения соответствия требованиям. Он также содержит руководство верхнего уровня от Solution Accelerator Team, подразделения Майкрософт, и руководство по обеспечению безопасности операционной системы Windows Server®.

Предварительные требования

Для понимания основных понятий, представленных в этом документе, требуется знание основных сведений о работе SQL Server. Практическая работа с пакетом SDK поможет получить основные сведения о компонентах SQL Server 2008, связанных с обеспечением соответствия требованиям.

Основные сведения о соответствии требованиям

Соответствие требованиям — это задача, которую решают предприятия любых размеров. Основную нагрузку по обеспечению соответствия требованиям создают разнообразные нормативы, такие как Sarbanes-Oxley и PCI-DSS. Но даже тем компаниям, которым не требуется сертификация по каким-либо нормативам, все равно приходится заботиться о соответствии собственным организационным политикам. Часто с проблемой сталкиваются компании, начинающие работу над программой обеспечения соответствия требованиям. Как правило, они не знают, с чего начать работу и как автоматизировать программу с использованием различных технологий, в частности SQL Server.

Обеспечение соответствия требованиям — это последний из трех этапов процесса под названием GRC (governance, risk management, compliance — руководство, управление рисками, соответствие требованиям). Организации, начинающей разработку программы GRC, лучше обратиться за помощью к консультанту по обеспечению соответствия требованиям. В этом может оказаться полезным отчет по компаниям, предоставляющим консалтинговые услуги по GRC, подготовленный компанией Forrester, которая занимается отраслевым мониторингом GRC. В следующем разделе обсуждается понятие GRC и способы интеграции SQL Server в программу GRC.

Сущность программы GRC

Программа GRC помогает компаниям выявить риски, снизить связанные с ними угрозы и убедиться в том, что это сокращение сохранится в долгосрочной перспективе. На иллюстрации Рис. 1 приведены общие сведения о каждом из этих положений.

Рис. 1. Сущность программы GRC

Управление рисками

Управление рисками — это программа, направленная на снижение или устранение рисков. Ее выполнение начинается с выявления источников и оценки рисков в каждой из областей деятельности организации. Каждый отдел — безопасности, эксплуатации, продаж и разработок — может выполнить собственную оценку рисков. Для начала компания должна выполнить оценку своих рисков с участием аудитора соответствия требованиям. После того как все риски выявлены, им необходимо присвоить приоритеты и исходя из этого разработать план действий. В плане должен быть определен метод устранения каждого из рисков — пропустить его, снизить или устранить?

Пути решения для риска, связанного с хранением данных кредитных карт

·        Пропустить — не заниматься усилением защиты данных о кредитных картах.

·        Снизить — улучшить защиту данных о кредитных картах.

·        Устранить — не хранить данные о кредитных картах.

Рискам, которые предполагается снизить, должен быть назначен приоритет, и для снижения каждого из этих рисков должен быть разработан план. Поскольку каждый отдел выявляет свои риски и назначает им приоритеты, необходимо убедиться в том, чтобы они не противоречили рискам всей организации в целом. Приоритеты отдела не должны противоречить приоритетам компании.

Руководство

Руководство представляет действия, предпринимаемые для снижения рисков, выявленных на этапе оценки. На этом этапе применяются политики, средства управления ИТ, рекомендации системы и учебные программы, ведущие к снижению рисков. Средства снижения рисков, обсуждаемые ниже, могут использоваться в тех случаях, когда нельзя полностью избавиться от рисков или применять жесткие правила. Например, SQL Server не может запретить просматривать конфиденциальные данные пользователю sysadmin. Однако саму учетную запись sysadmin можно либо отключить, либо отслеживать любой выполняемый ею доступ, чтобы запретить несанкционированный доступ к конфиденциальным данным.

Способы применения руководства к защищаемым конфиденциальным данным

·        Создание политик, которые описывают надлежащую обработку конфиденциальных данных.

·        Обучение сотрудников правилам работы с данными.

·        Применение политик к системам, в которых хранятся конфиденциальные данные.

·        Мониторинг и ведение журнала выполнения политик при обработке конфиденциальных данных.

Соответствие требованиям

Соответствие требованиям — это проверка снижения выявленных рисков. Следующие примеры показывают, как можно проверить риски. Во-первых, для каждого ли выявленного риска имеется политика по его снижению или устранению? Во-вторых, поставлены ли соответствующие сотрудники в известность об этих политиках? В-третьих, развернуты ли эти политики на все процессы, программы и средства управления ИТ? В-четвертых, отслеживаются ли политики на соответствие требованиям и насколько быстро устраняются вновь обнаруженные дефекты защиты? Чтобы достичь истинного соответствия требованиям, каждый этап должен быть проверен аудитором. Здесь могут помочь отчеты аудита, журналы событий, видеозаписи и история версий.

Способы проверки соответствия требованиям

·        Показать, что разработаны политики для устранения выявленных рисков.

·        Показать, что эти политики развернуты в нужных местах.

·        Доказать, что политики разработаны и выполняются в течение периода их применения.

Обеспечение соответствия требованиям организации и нормативам обычно выполняется группой внутреннего аудита и профессиональным аудитором. Подготовьте системы к проверке соответствия требованиям, которую будут проводить аудиторы. Централизация систем аудита способствует повышению эффективности аудита соответствия. При этом снижается стоимость аудита и сводится к минимуму нарушение текущего обслуживания.

Пример GRC

Пользуясь рекомендациями GRC из предыдущего раздела, попробуем провести оценку необходимости построения защиты, как показано на иллюстрации Рис. 2.

Рис. 2. Пример GRC

Управление рисками

Проведение оценки рисков для зданий позволяет выработать политику безопасности, которая позволит снизить риски потерь от хищения компьютерного и офисного оборудования, личных вещей и интеллектуальной собственности. Для сотрудников также существует риск нападения или ограбления. Само по себе здание может также пострадать от вандализма. Каждый из этих рисков может отрицательно отразиться на итогах работы предприятия, если выразить их в материальных затратах.

Руководство

Результатом оценки является определенный набор политик и приоритетов их реализации. Создаются политики для доступа в здание и защиты входных путей. Проводится обучение сотрудников этим политикам. Двери защищаются электронными замками, которые открываются только беджами сотрудников. Сотрудникам выдаются беджи, ограничивающие их доступ внутри здания в соответствии с соображениями целесообразности. Чтобы обеспечить принудительное применение политик в уязвимых местах, там размещаются камеры и посты охраны.

Соответствие требованиям

Чтобы аудитор мог проверить соответствие требованиям корпоративных политик доступа, должна существовать возможность извлечения записей из журналов регистрации, показывающих, что в них правильно производится сбор данных, а сотрудники, указанные в журнале, имеют законное право доступа в здание. Должна существовать возможность просмотра записей с камер наблюдения, чтобы убедиться в том, что сотрудники соблюдают политики доступа в здание, а посторонние лица не могут в него проникнуть. Кроме того, аудитор может опросить охрану и ознакомиться с последними сводками новостей, чтобы проверить, что в здании не было никаких происшествий.

Средства сокращения рисков

Средства сокращения рисков, также называемые средствами компенсации, помогают снижать риски от средств управления, которые не могут быть в полной мере применены. В приведенном выше примере GRC камера наблюдения служит средством снижения рисков, позволяя отслеживать попытки проникновения в здание без беджа. Несмотря на то что электронный замок на двери открывается только с помощью беджа его владельца, это не остановит того, кто входит в дверь, когда она уже открыта.

Аудит часто используется в качестве средства сокращения рисков в компьютерных системах, позволяя отслеживать операции, которые невозможно заблокировать. Например, системные администраторы часто имеют доступ ко всем данным, хотя и не всегда авторизованы для ознакомления с этими данными. Наблюдение за действиями администраторов позволит определить, выполняются ли политики безопасности.

В SQL Server 2008 появились два компонента, которые могут быть использованы при разработке средств снижения рисков. Подсистема аудита SQL Server позволяет наблюдать за конфиденциальными операциями и привилегированными учетными записями, такими как sa. Управление на основе политик помогает следить за тем, чтобы важные параметры конфигурации в любой момент времени содержали соответствующие значения. В тех случаях, когда какие-либо из операций, учетных записей и параметров конфигурации невозможно заблокировать, создаваемые ими риски можно снизить с помощью этих двух компонентов.

Ключевые индикаторы производительности и ключевые индикаторы рисков

Ключевые индикаторы производительности (KPI) — это параметры или замеры, которые показывают, работает ли компания, отдел или система в соответствии с вашими ожиданиями. Как правило, значения и диапазоны для KPI задаются ответственными лицами компании. Некоторые средства управления ИТ также могут выступать в качестве KPI. Например, производительность обработки запросов к базам данных, загрузка ЦП, объем доступной памяти и количество свободных соединений могут служить индикаторами работоспособности системы.

Ключевые индикаторы рисков (KRI) представляют собой параметры или значения, которые могут показывать потенциальные угрозы для компании, отдела или системы. Как правило, значения и диапазоны для KRI задаются лицами, отвечающими за технические вопросы. Средства управления ИТ часто используются для реализации KRI. В качестве примеров KRI можно привести регулярность применения исправлений безопасности или защиты данных шифрованием, количество пользователей, имеющих роль администратора, или частоту выполнения резервного копирования.

О том, как использовать управление на основе политик для реализации KPI и KRI, см. в разделе Использование управления на основе политик для реализации KPI и KRI далее в этом документе.

Сопоставление нормативов базовым наборам средств управления ИТ

Определение политик обеспечения соответствия нормативам часто влечет за собой разработку средств управления ИТ для их реализации. Например, политика, применяемая для ограничения доступа к номерам кредитных карт, может потребовать реализации средств управления ИТ для строгой проверки подлинности, рекомендаций по выбору надежного пароля и использования шифрования. Некоторые из этих средств реализуются как бизнес-процессы, но многие требуют применения технологий.

 

На иллюстрации Рис. 3 показаны примеры средств управления ИТ, которые могут потребоваться для обеспечения соответствия ряду нормативов, распространенных в США. Разработанное в Майкрософт Руководство по планированию соответствия требованиям ИТ содержит рекомендации по сопоставлению нормативов базовому набору средств управления ИТ и технологий Майкрософт, которые могут оказаться полезными при их создании.

Рис. 3.  Закон Сарбейнса-Оксли (SOX) PCI: Отрасль платежных карт HIPAA: Закон о перемещении данных о страховании здоровья и ответственности за них GLBA: Закон Грэма - Лича - Блили

В следующих разделах этого документа все указанные средства управления ИТ описаны подробно.

Значок	Описание
	Управление идентификаторами
	Разграничение обязанностей
	Шифрование
	Управление ключами
	Аудит
	Управление на основе политик

Реализация средств управления ИТ с помощью SQL Server 2008

SQL Server 2008 содержит ряд компонентов, обеспечивающих возможность реализации средств управления ИТ, связанных с базами данных. Помимо этого, некоторые средства управления ИТ могут быть реализованы с помощью платформы, на которой установлен SQL Server. Группа обеспечения соответствия требованиям вашей компании должна определить средства управления ИТ, которые нужно реализовать, и выделить те из них, которые будут относиться к базе данных. В следующих разделах рассматриваются компоненты, которые могут быть использованы для реализации средств управления ИТ. В число рассматриваемых областей входит следующее.

·        Защита платформы

В этом разделе описаны действия, которые нужно выполнить перед установкой программного обеспечения и настройкой базы данных. Эти действия помогут снизить риски, связанные с операционной системой и приложением базы данных.

·        Управление удостоверениями и разделение обязанностей

В этом разделе приведены рекомендации по определению участников базы данных и разграничению обязанностей между ними.

·        Шифрование данных в базе данных

В этом разделе рассматриваются компоненты SQL Server 2008 для шифрования данных. Раздел также включает обсуждение часто упускаемых вопросов, связанных с шифрованием.

·        Аудит конфиденциальных данных

В этом разделе рассматривается новый компонент аудита SQL Server 2008 и его применение для централизованного сбора журналов и создания отчетов.

·        Использование управления на основе политик для определения, развертывания и проверки политик

В этих разделах рассматривается компонент SQL Server 2008 для управления на основе политик и его применение для управления несколькими базами данных с помощью единой политики.

Защита платформы

Прежде чем применять политики или реализовывать средства управления ИТ с помощью SQL Server, убедитесь в том, что подготовлена защищенная база данных для установки SQL Server:

·        Используйте последнюю версию операционной системы с текущими пакетами обновлений.

·        Установите все последние исправления безопасности.

·        Установите ПО для защиты от вредоносных программ.

·        Сведите к минимуму контактную зону операционной системы, доступную для атак.

o   Ограничьте число запущенных служб.

o   Устанавливайте только то ПО, которое необходимо для поддержки роли сервера.

o   Отключите ненужные порты.

o   Настройте брандмауэр.

·        Ограничьте число пользователей, имеющих доступ к серверу, и число их ролей.

Дополнительные сведения, в том числе директивные рекомендации об обеспечении безопасности Windows Server 2008, загрузите из раздела Руководство по безопасности Windows Server 2008.

Обеспечение безопасности SQL Server 2008

После защиты платформы, но перед применением политик и разработкой средств управления ИТ нужно установить и защитить SQL Server. Дополнительные сведения, включая рекомендации по защите SQL Server 2008, см. в техническом документе Обеспечение безопасности SQL Server.

Настройка контактной зоны SQL Server 2008

Начиная с SQL Server 2008 средство настройки контактной зоны удалено. Функции этой программы по управлению работой SQL Server перенесены в компонент управления на основе политик и значительно расширены. На иллюстрации Рис. 4 показан экран аспектов настройки контактной области. Кроме того, имеются аспекты настройки контактной зоны для служб Analysis Services и Reporting Services. Дополнительные сведения о настройке контактной зоны см. в разделе Основные сведения о настройке контактной зоны.

Рис. 4. Аспект настройки контактной зоны управления на основе политик

Функции управления компонентами связи в средстве настройки контактной зоны, показанные на Рис. 4, находятся в диспетчере конфигураций SQL Server. Поле Рис. 5 представляет узлы диспетчера конфигурации SQL Server для настройки служб, сети и протоколов.

Рис. 5. Диспетчер конфигурации SQL Server

Управление конфигурацией SQL Server можно выполнять с помощью хранимой процедуры sp_configure. Она обеспечивает управление следующими параметрами.

·        Сжатие резервной копии.

·        Включение стандарта Common Criteria.

·        Включение среды CLR.

·        Удаленный доступ.

·        Соединения пользователей.

Управление удостоверениями и разделение обязанностей

Первый этап ограничения доступа к данным в базе данных — это определение круга лиц, имеющих к ним доступ. SQL Server 2008 поддерживает различные способы, позволяющие ограничивать как круг лиц, так и набор операций, доступных для выполнения пользователям базы данных. На иллюстрации Рис. 6 показано несколько уровней управления идентификаторами, реализованных в SQL Server 2008. Помимо этого, для пользователей могут предоставляться или запрещаться специальные разрешения. Прежде чем предоставлять пользователям доступ, подготовьте план, в котором укажите, кто будет иметь доступ к базе данных, на каком уровне и к каким операциям. Затем следуйте приведенным далее рекомендациям.

·        Используйте для входа в базу данных проверку подлинности Windows.

·        Предоставляйте пользователям доступ к базам данных только по мере необходимости.

·        Предоставляйте пользователям необходимые разрешения и отзывайте их.

·        Включите пользователей в роли, которые им необходимы для выполнения их работы.

·        С помощью управления на основе политик проверьте политики удостоверений.

Рис. 6. Развитие определения пользователя базы данных

Использование проверки подлинности Windows

Управление идентификаторами — самый важный этап защиты базы данных после проверки защищенности платформы. С одной стороны, он дает возможность определить каждое лицо, получающее доступ к базе данных.

Использование проверки подлинности Windows привязывает идентификатор SQL Server к учетной записи Active Directory®, обеспечивая строгость идентификации. Интеграция управления доступом со службой каталогов Active Directory (AD DS) дает SQL Server несколько преимуществ.

·        Согласованность удостоверений для всех серверов

·        Централизованное применение политики паролей

·        Централизованное блокирование учетных записей

Дополнительные сведения о провизионировании учетных записей см. в разделе Настройка компонента Database Engine — назначение учетных записей.

Проверку подлинности Windows можно включить на странице Безопасность в диалоговом окне Свойства сервера среды SQL Server Management Studio, как показано на иллюстрации Рис. 7.

Рис. 7. Свойства безопасности сервера

Управление на основе политик может быть использовано для создания политики, обеспечивающей наличие и неизменность параметров безопасности. Создать политику для этого параметра можно с помощью аспекта управления на основе политик свойствами безопасности сервера. Дополнительные сведения о проверке параметров с помощью управления на основе политик см. в разделе Использование управления на основе политик для определения, развертывания и проверки политик.

Создание имен входа

Для доступа к базе данных пользователю сначала нужно назначить имя входа. Имя входа может быть назначено пользователю по его учетной записи Windows® или по группе безопасности Windows, к которой он принадлежит. Дополнительные сведения о создании имен входа см. в разделе Как создать имя входа SQL Server. Использование вместо групп безопасности конкретных учетных записей пользователей обеспечивает более детальный контроль имен входа, поскольку в противном случае администратор Windows может включить нового пользователя в группу безопасности без ведома администраторов базы данных. Но группы безопасности, конечно, значительно проще администрировать. Ниже приведен пример сценария для создания имени входа по учетной записи Windows.

CREATE LOGIN [SQLVM03-18158EA\Pat] FROM WINDOWS

Для создания нового имени входа в базе данных может быть использовано диалоговое окно Создание имени входа, показанное на иллюстрации Рис. 8. Чтобы его открыть, щелкните правой кнопкой мыши папку «Имена входа» в экземпляре сервера и выберите пункт Создать имя входа.

Рис. 8. Диалоговое окно Создание имени входа

Назначение пользователей для ролей сервера

Имена входа, определенные в SQL Server, должны быть назначены соответствующим ролям сервера. Назначение имени входа некоторой роли, кроме sysadmin, помогает ограничить доступ этого имени к определенному набору операций на сервере. Например, назначение имени входа роли dbcreator дает разрешение на создание, изменение, удаление и восстановление базы данных. Однако все остальные операции будут по-прежнему недоступны. Список предопределенных ролей базы данных вместе с операциями, на выполнение которых они дают право, см. в разделе Роли уровня сервера. Ниже приведен сценарий, который показывает, как назначить пользователю роль сервера.

sp_addsrvrolemember N'SQLVM03-18158EA\Pat', N'dbcreator'

На странице свойств, показанной на иллюстрации Рис. 9, можно назначить роль сервера имени входа. Чтобы ее открыть, щелкните правой кнопкой мыши имя входа, выберите Свойства, а затем откройте вкладку Роли сервера.

Рис. 9. Роли сервера для имени входа

Предоставление доступа к базе данных

После создания имени входа ему необходимо предоставить доступ к базе данных, чтобы пользователь мог обращаться к данным. Предоставляйте доступ к базе данных только тем пользователям, которым он необходим. После того как доступ предоставлен, нужно позаботиться об ограничении этого доступа. Дополнительные сведения о предоставлении имени входа доступа к базе данных см. в разделе Как создать пользователя базы данных. Ниже приведен сценарий, который показывает, как предоставить имени входа доступ к базе данных Test1.

USE Test1

CREATE USER [Pat] FOR LOGIN [SQLVM03-18158EA\Pat]

 

Назначить базу данных для имени входа можно в диалоговом окне Пользователь базы данных — создание, показанном на иллюстрации Рис. 10. В этом диалоговом окне можно также назначить пользователю схему и роли базы данных. Чтобы его открыть, щелкните правой кнопкой мыши папку Пользователи в базе данных и выберите Создать пользователя.

Рис. 10. Диалоговое окно Пользователь базы данных — создание

Назначение пользователям ролей базы данных

Пользователям, которым предоставлен доступ к базе данных SQL Server, нужно назначить соответствующую роль базы данных. При назначении пользователю роли, кроме db_owner, ему будет предоставлен доступ к определенному набору операций базы данных. Например, роль db_datareader разрешает пользователю считывать данные из всех таблиц базы данных. Однако все остальные операции будут недоступны этому пользователю. Список предопределенных ролей базы данных вместе с операциями, на выполнение которых они дают право, см. в разделе Роли уровня базы данных. Ниже приведен сценарий, который показывает, как назначить пользователю роль базы данных, позволяющую считывать и записывать данные в базе данных.

sp_addrolemember N'db_datareader', N'Pat'

sp_addrolemember N'db_datawriter', N'Pat'

Роли базы данных также можно назначить во время создания пользователя в диалоговом окне Пользователь базы данных — создание, как описано в разделе Предоставление доступа к базе данных.

Управление разрешениями

Разрешения — это права доступа или операции, которые предоставляются или запрещаются для пользователя базы данных или имени входа. Если имени входа или пользователю назначена роль, то он наследует набор разрешений, соответствующих этой роли. Разрешения можно также предоставить непосредственно пользователю или имени входа. Дополнительные сведения, в том числе список разрешений, см. в разделе Разрешения (компонент Database Engine).

Разрешения служат для предоставления гранулярных прав пользователям и ограничивают права, которые наследуются при включении в роль. Например, для пользователя, которому назначены роли базы данных db_datareader и db_datawriter, может быть запрещена возможность удаления записей с помощью команды DENY. Таким же способом можно разрешить пользователю создавать и обновлять записи о ценах, но не удалять их. В следующем сценарии показано, как запретить пользователю удалять строки из таблицы CCTable1.

DENY DELETE ON OBJECT::CCTable1 TO [Pat]

Примечание. Некоторые системные хранимые процедуры и команды DBCC при выполнении проверяют членство в ролях, но не разрешения на выполнение операций. Всегда проверяйте, работают ли разрешения ожидаемым образом, прежде чем переносить их в рабочую среду.

Разрешения ролей сервера

Каждая роль сервера содержит соответствующий ей набор разрешений. Эти роли позволяют упростить назначение пользователю набора разрешений. Дополнительные сведения, в том числе наборы разрешений, назначенные предопределенным ролям сервера, см. в разделе Разрешения предопределенных ролей сервера (компонент Database Engine). Ниже приведен пример сценария, применяющего разрешение сервера.

GRANT ALTER ANY LOGIN TO [SQLVM03-18158EA\Pat]

На странице Свойства имени входа, показанной на иллюстрации Рис. 11, можно предоставить или запретить имени входа разрешения сервера. На вкладке Явные перечислены все разрешения и есть возможность предоставить, запретить или разрешить имени входа предоставлять каждое из них. На вкладке Действующие перечислены разрешения, предоставленные имени входа на основании членства в роли. В примере на иллюстрации Рис. 11 пользователю Pat запрещено удаление из таблицы CCTable1.

Рис. 11. Разрешения сервера для имени входа

Разрешения базы данных

Каждая роль базы данных содержит назначенный ей набор разрешений. Набор разрешений, назначенных предопределенной роли базы данных, можно найти в разделе Разрешения предопределенных ролей базы данных (компонент Database Engine). Ниже приведен пример сценария, применяющего разрешение базы данных.

USE Test1

GRANT CREATE TABLE TO [SQLVM03-18158EA\Pat]

На странице Пользователь базы данных, показанной на иллюстрации Рис. 12, можно предоставить или отозвать разрешения базы данных для пользователя. На вкладке Явные перечислены все разрешения и есть возможность предоставить, запретить или разрешить пользователю предоставлять разрешение. На вкладке Действующие перечислены разрешения, предоставленные пользователю на основании членства в роли.

Рис. 12. Разрешения пользователя базы данных

Разрешения уровня столбца

Безопасность на уровне столбца позволяет предотвратить доступ пользователя к определенному столбцу таблицы. Для предотвращения доступа к столбцу служит разрешение DENY для объекта. Ниже приведен пример сценария для запрещения доступа к столбцу.

DENY SELECT ON [CCTable1] ([CCNumber]) TO [SQLVM03-18158EA\Pat]

Примечание. Запрос завершится ошибкой, если он содержит столбец, к которому пользователь не имеет доступа. Например, инструкция SELECT * не будет работать для таблицы, содержащей столбец, доступ к которому пользователю запрещен. Рекомендуется указывать в приложениях конкретные столбцы, а не использовать инструкцию SELECT *.

Показанная ниже страница свойств таблицы базы данных позволяет блокировать доступ к определенным столбцам. Пример на иллюстрации Рис. 13 показывает, что пользователю Pat запрещен доступ к столбцу, содержащему номер кредитной карты. Чтобы кнопка Разрешения столбца была активной, пользователю должно быть предоставлено разрешение SELECT для таблицы.

Рис. 13. Предоставление и запрещение разрешений для столбца базы данных

Разграничение обязанностей

Разграничение обязанностей (SOD), также называемое разграничением ответственности или разделением ролей, является важным условием при управлении разрешениями и удостоверениями. Это помогает снизить угрозы, возникающие при работе конкретных работников, и обеспечивает контроль за правильностью выполнения задач. Например, если задаче назначены автор, рецензент и контролер, то каждый из этих сотрудников будет смотреть за тем, чтобы остальные не выходили за рамки отведенных им полномочий и не делали ошибок во время выполнения своих задач. Разграничение обязанностей преследует две основные цели.

·        Обеспечение отсутствия конфликтов ролей пользователей (например, для рецензента и контролера).

·        Недопущение выхода пользователя, выполняющего определенную роль, за рамки своих полномочий.

Многим компаниям, занимающимся обработкой данных, хотелось бы исключить доступ привилегированных пользователей к конфиденциальным данным. На текущий момент SQL Server не в состоянии запретить членам роли sysadmin выполнение определенных операций, которые могут вызвать конфликт с правилами разграничения обязанностей. Однако можно создать средства снижения рисков, которые в состоянии сократить риски, связанные с возможностью выполнения пользователем sysadmin недопустимых операций. Во-первых, можно отключить учетную запись sa. Во-вторых, совершенно необходимо ограничить круг лиц, входящих в роль sysadmin. В третьих, каждая операция, выполняемая пользователем sysadmin, подлежит аудиту и контролю. Помимо этого, для проверки действия этих средств должно использоваться управление на основе политик. Примеры управления разграничением обязанностей с помощью управления на основе политик см. в разделе Управление разграничением обязанностей.

Ограничение использования роли sysadmin

Члены роли сервера sysadmin в SQL Server имеют возможность выполнить любую функцию в базе данных. Поэтому решение о включении пользователей в эту роль должно рассматриваться с особой тщательностью. Права доступа должны быть выданы минимально возможному числу пользователей. Неплохо иметь несколько администраторов для выполнения резервного копирования, но распределение широких полномочий — тоже не выход.

Отключение учетной записи sa

В реальных условиях учетная запись sa не нужна для управления SQL Server и поэтому не должна использоваться. Учетная запись sa не сопоставлена определенному лицу, поэтому достаточно определить, кто же должен пользоваться учетной записью sa. Можно переименовать учетную запись в среде SQL Server Management Studio, чтобы приложения не могли ей пользоваться. Кроме того, чтобы пользователи вообще не могли ей пользоваться, эту учетную запись можно отключить.

Использование подписанных процедур для работы в качестве sysadmin (отключение всех учетных записей sysadmin)

Отключение всех учетных записей sysadmin может оказаться проблематичным, поскольку только sysadmin может повторно включить учетную запись sa или запускать определенные команды, например DBCC PINTABLE. Однако это ограничение можно обойти за счет использования подписанных процедур. Подписанная процедура может запускаться от имени роли sysadmin пользователем, который не включен в роль sysadmin. Такая процедура может быть использована для включения и отключения всех учетных записей sysadmin. Дополнительные сведения см. в разделе Отключение всех учетных записей sysadmin. Они также служат для выполнения операций, обычно зарезервированных для роли sysadmin. Это позволяет непривилегированным пользователям выполнять такие действия, но не иметь всех разрешений, доступных роли sysadmin.

Примечание. Не предоставляйте разрешение CONTROL SERVER именам входа. Это разрешение дает пользователю возможность выполнять многие из операций sysadmin. Помимо этого, пользователь сможет олицетворять учетную запись sa даже в том случае, если она отключена. Если предоставление пользователю разрешения CONTROL SERVER необходимо, то это лучше делать совместно с DENY IMPERSONATE для каждой учетной записи sysadmin. Далее приведен пример.

USE master

DENY IMPERSONATE ON LOGIN::Yukonsa TO [SQLVM03-18158EA\Pat];

 

Учетные записи sysadmin, определяемые во время установки

В следующей таблице описаны участники, которые во время каждой новой установки определяются для роли sysadmin. При обновлении версии текущее членство в роли sysadmin не изменяется.

Назначение роли sysadmin во время установки SQL Server 2008

Службам SQL Server во время установки назначаются учетные записи службы. Однако рекомендуется переключить их на учетные записи домена или локальных пользователей, чтобы нельзя было запустить от имени учетных записей служб другие приложения и получить доступ к ресурсам SQL Server. Дополнительные сведения о том, как переключить учетные записи служб, см. в разделе Настройка учетных записей службы Windows.

В новых установках SQL Server 2008 в роль sysadmin по умолчанию включается базовый набор из трех участников.

Учетная запись	Цель
sa	Обязательная учетная запись пользователя для учетной записи sysadmin при работе в смешанном режиме. Эта учетная запись по умолчанию отключена, если установка SQL Server производится в режиме проверки подлинности Windows.
Участник Windows	Это обязательная учетная запись Windows, которая должна быть указана во время установки. Это лицо действует в качестве sysadmin для экземпляра SQL Server.
NT AUTHORITY\SYSTEM	Используется такими службами Windows, как Центр обновления Майкрософт, служба обновления Windows, диспетчер конфигурации System Center и сервер кластеров Windows.

   Таблица 1. Участники роли sysadmin, определяемые во время установки

 

При установке SQL Server на Windows Vista® или Windows Server 2008 в роль sysadmin добавляются следующие участники.

Таблица 2. Участники роли sysadmin, назначаемые в операционных системах Windows Vista и Windows Server 2008

При установке SQL Server на любых операционных системах, кроме Windows Vista и Windows Server 2008, в роль sysadmin добавляются следующие учетные записи.

Таблица 3. Участники роли sysadmin, назначаемые в предыдущих версиях WindoПримечание. В этой таблице localhost представляет имя компьютера.

Назначение SID служб в операционных системах Windows Vista и Windows Server 2008

Windows Vista и Windows Server 2008 реализуют возможность назначения службам скрытых идентификаторов безопасности. Во время установки SQL Server 2008 службам SQL Server и агента SQL Server назначаются отдельные идентификаторы безопасности. Учетная запись, назначенная службам, по-прежнему требует запуска службы и доступа к внешним ресурсам. Но когда службы производят доступ к SQL Server и локальным ресурсам, например к файлам или параметрам реестра, используются соответствующие SID.

Использование группы BUILTIN\Administrators

До версии SQL Server 2008 члены группы BUILTIN\Administrators были также и членами роли sysadmin в SQL Server. Но при установке на Windows Vista или Windows Server 2008 члены группы Administrators запускаются как стандартные пользователи, поэтому для них доступ к SQL Server блокируется. Разумеется, можно повысить их маркеры безопасности до административных, чтобы получить доступ к SQL Server, но так делать не рекомендуется. SQL Server 2005 с пакетом обновления 2 (SP2) поставляется со средством провизионирования учетных записей, которое позволяет администраторам добавить их учетные записи Windows в роль sysadmin.

SQL Server 2008 не добавляет группу BUILTIN\Administrators в роль sysadmin. Средство провизионирования учетных записей также недоступно. Однако во время установки учетные записи Windows можно добавить в роль sysadmin. Если необходимо, после установки дополнительные учетные записи Windows можно добавить в учетную запись sysadmin. Рекомендуется не добавлять группы Windows в роль sysadmin, так как это может привести к тому, что любой пользователь сможет получить доступ на чтение и запись для этой группы и таким образом получит доступ к SQL Server.

Управление идентификаторами в многоуровневых приложениях

Многие приложения реализуют доступ пользователей к базе данных через учетную запись службы, специально созданную для этого приложения. Это дает возможность предотвратить прямой доступ пользователя к базе данных и оптимизировать администрирование, однако лишает базу данных возможности отслеживать, кто из пользователей выполняет ту или иную операцию. Использование учетной записи службы с правом доступа IMPERSONATE может дать приложению возможность выполнить команду EXECUTE AS, чтобы назначить пользователю определенную роль, ограничивая таким образом набор операций, доступных для выполнения. Можно отключить олицетворяемую учетную запись, чтобы лишить пользователей возможности входа в систему, тем не менее оставив им определенные возможности на более гранулярном уровне.

Шифрование данных в базе данных

Защита конфиденциальных данных — важный аспект работы с базами данных. SQL Server 2008 обеспечивает несколько способов защиты таких данных с помощью шифрования. В этом разделе рассматривается использование шифрования в SQL Server и связанные с этим соображения. Дополнительные сведения, в том числе сценарии для управления шифрованием на основе политик, см. в разделе Управление ключами шифрования.

Шифрование в базе данных SQL Server

Начиная с SQL Server 2005 для приложений баз данных доступно собственное шифрование. Ранее приложения должны были самостоятельно реализовывать возможности шифрования с помощью CAPICOM. Дополнительные сведения об использовании шифрования в SQL Server см. в разделе Шифрование в SQL Server.

Выбор алгоритма шифрования

Обычно выбор алгоритма шифрования представляет собой компромисс между степенью защищенности и уровнем производительности. В общем случае чем длиннее ключ шифрования, тем большую степень защиты он обеспечивает, но тем больше времени требует для выполнения. Асимметричные ключи работают медленнее, чем симметричные, однако обеспечивают более надежную защиту, при этом ключ для расшифровки остается в секрете. При использовании сертификатов возможен их отзыв, однако SQL Server не поддерживает эту функцию (кроме локального удаления сертификата). Дополнительные сведения о выборе алгоритма см. в разделе Выбор алгоритма шифрования.

Использование прозрачного шифрования данных

В SQL Server 2008 было реализовано прозрачное шифрование данных (TDE), которое позволяет шифровать данные в базе данных без необходимости внесения изменений в приложение. TDE выполняет шифрование файлов данных, журналов и резервных копий.

 

На иллюстрации Рис. 14 показаны операции, которые необходимо рассмотреть при реализации TDE.

·        Обязательно создайте резервную копию закрытого ключа сертификата.

·        Сертификаты и ключи должны периодически меняться в соответствии с требованиями нормативов.

·        Пользуйтесь расширенным управлением ключами (EKM) для обеспечения более строгой защиты ключа и разграничения обязанностей.

·        Наблюдайте за доступом к ключам и шифрованию.

Рис. 14. Управление ключами для прозрачного шифрования данных

Создание резервной копии закрытого ключа сертификата

Ключ шифрования для шифрования TDE в базе данных хранится в базе данных и защищен сертификатом. После создания сертификата необходимо создать резервную копию закрытого ключа этого сертификата, прежде чем использовать ключ совместно с TDE. Если по некоторым причинам сертификат был уничтожен до создания резервной копии, то все резервные копии, защищенные с помощью TDE, будет непригодны для восстановления. Ниже приведен сценарий, который может быть использован для создания резервной копии сертификата.

USE master

BACKUP CERTIFICATE [MyServerCert] TO FILE = 'c:\certificates\MyServerCert.crt'

 WITH PRIVATE KEY (FILE = 'c:\certificates\MyServerCert.pvk',

  ENCRYPTION BY PASSWORD = 'MyPass7779311#');

 

Резервная копия сертификата должна храниться в защищенном месте. Запишите ее на диск и храните в сейфе или импортируйте в базу данных-получатель с ограниченным доступом.

Смена сертификатов в сравнении со сменой ключей

Многие нормативы требуют от организаций регулярной смены ключей шифрования. Для смены сертификатов и ключей шифрования TDE в базе данных можно пользоваться сценариями. Вам может показаться, что лучше будет сменить ключ шифрования базы данных, однако на самом деле для этой цели лучше подходит смена сертификата. Это даст возможность обновить защиту без перешифрования базы данных. Кроме того, при смене сертификата проще отменить доступ к зашифрованным данным. При смене ключа шифрования базы данных выполняется перешифрование данных, однако это не повышает уровень защиты.

Например, если сто лент с резервными копиями базы данных созданы с использованием ста различных ключей шифрования, то все они будут доступны по одному и тому же сертификату. Если между созданием резервных копий провести смену сертификата, то для получения данных с ленты потребуется доступ к ста сертификатам.

Каждый раз при создании сертификата необходимо выполнить его избыточное резервирование. Для создания резервной копии можно воспользоваться сценарием. Резервные копии сертификатов нужно немедленно импортировать в защищенную базу данных и скопировать уничтожаемые или сохраняемые ключи в надежное место. Для регулярной смены ключей можно создать задание. Управление на основе политик позволит гарантировать, что сертификаты или ключи меняются по регулярному расписанию.

Примечание. При восстановлении зашифрованной резервной копии, моментального снимка или записи журнала для расшифровки данных используется сертификат. Поэтому нужно создать копию сертификата и защитить ее для предотвращения несанкционированного доступа к конфиденциальным данным.

 

Наблюдение за доступом к ключу

Доступ к ключу шифрования является конфиденциальной операцией. Его наблюдение может служить средством снижения рисков в соответствии с требованиями PCI и других нормативов. Ключи и сертификаты для TDE хранятся в базах данных user и master соответственно. Доступ к этим объектам может отслеживаться по указанию сервера типами действий аудита DATABASE_OBJECT_ACCESS_GROUP и DATABASE_OBJECT_CHANGE_GROUP (первый наблюдает за доступом к ключу, а второй — за его изменением). Объем журнала по этим действиям для рабочих баз данных невелик, поскольку отслеживание выполняется по совсем небольшому числу объектов, которые в рабочих условиях изменяются очень редко. Ниже приведен список объектов баз данных.

•       Сборка

•       Асимметричный ключ (только закрытая часть ключа)

•       Сертификат (только закрытая часть ключа)

•       Спецификации аудита базы данных (только изменения)

•       Полнотекстовый каталог

•       Список полнотекстовых стоп-слов (только SQL Server 2008)

•       Функции секционирования (только изменения)

•       Схема (только изменения)

•       Объекты компонента Service Broker

o   Контракт

o   Тип сообщений

o   Привязка удаленной службы

o   Маршрут

o   Служба

•       Симметричный ключ

•       Триггеры (только изменения)

Наблюдение за флажком шифрования в базе данных

После назначения ключа шифрования для включения TDE в базе данных флажок шифрования также должен быть установлен. Чтобы обеспечить постоянную защиту базы данных, необходимо его отслеживание. Чтобы включить наблюдение за флажком шифрования для базы данных, в спецификацию аудита сервера нужно добавить тип действий аудита DATABASE_CHANGE_GROUP.

Работа в конкретных сценариях

SQL Server 2008 реализует ряд возможностей для защиты конфиденциальных данных. Выбор конкретного варианта зависит от требуемой степени защиты, возможности изменения приложения, уровня производительности и других нюансов. При подборе механизма защиты следует исходить из того, от каких угроз вы собираетесь защищаться.

Защита от хищения компьютера

Доступны следующие методы защиты: шифрование файловой системы Windows (EFS), шифрование дисков BitLocker и TDE. Каждый из них может быть использован независимо или в сочетании с другими методами и обеспечивает защиту файлов базы данных, хранящихся в файловой системе, в случае похищения компьютера, на котором находится база данных. EFS и BitLocker™ позволяют любому пользователю с административными правами производить доступ к компьютеру и просматривать файлы базы данных без использования SQL Server. EFS и BitLocker защищают файлы только тогда, когда они находятся на защищенной системе. Использование EFS и BitLocker может привести к снижению производительности базы данных. Если скопировать файл на незащищенный том, то данные перестанут быть зашифрованными. TDE защищает файл базы данных при копировании в другую систему и резервном копировании на ленту. Дополнительные сведения об использовании TDE в сравнении с BitLocker или EFS см. в разделе Шифрование базы данных в выпуске SQL Server 2008 Enterprise Edition.

Предотвращение доступа sysadmin и dbo

Для пользователя sysadmin и владельца базы данных (dbo) просмотр данных в таблицах не может быть заблокирован. Однако сами роли sysadmin и db_owner можно отключить, устранив таким образом эту уязвимость. Выполнение операций, доступных только пользователю sysadmin, можно реализовать через вызов хранимых процедур. Дополнительные сведения см. в разделе Ограничение использования пользователя sysadmin. Когда доступ пользователей sysadmin или dbo необходим, то можно реализовать отслеживание их действий через подсистему аудита SQL Server. Дополнительные сведения см. в разделе Отслеживание доступа sysadmin и db_owner.

Защита столбца в таблице базы данных

Часто необходимо защитить в таблице всего один столбец, который содержит конфиденциальные данные, такие как номер кредитной карты. SQL Server 2008 предусматривает несколько способов добиться этого. Столбец можно зашифровать с помощью криптографической функции. Дополнительные сведения см. в разделе Как зашифровать столбец данных. Этот механизм требует поддержки приложения, для него будут недоступны поиск и индексирование. Кроме того, это может отрицательно сказаться на производительности выполнения запросов. Столбец данных можно также защитить средствами безопасности уровня столбца, которые обсуждались выше в этом документе, в разделе Разрешения уровня столбца.

Аудит конфиденциальных операций

В этом разделе рассматривается наблюдение за конфиденциальными операциями с помощью подсистемы аудита SQL Server. Подсистема аудита SQL Server может использоваться для отслеживания событий базы данных на верхнем уровне с помощью групп действий аудита либо с помощью групп действий аудита с более низкой гранулярностью. Список групп и действий аудита, доступных для отслеживания, см. здесь. Сценарии, иллюстрирующие управление аудитом и создание различных элементов аудита, можно найти ниже в разделе Управление аудитом.

Пример показывает, как централизовать сбор журналов аудита в общей сетевой папке с помощью System Center Operations Manager. Приведенный сценарий демонстрирует разработку повторяющихся отчетов с помощью служб SQL Server Integration Services (SSIS) и SQL Server Reporting Services (SSRS), как показано на иллюстрации Рис. 15.

     Рис. 15. Централизация журналов аудита и подготовки отчетов

 

С точки зрения высокого уровня аудит конфиденциальных операций сводится к аудиту различных баз данных и серверов и размещению файлов журналов аудита в централизованном местоположении («Папка журналов» на Рис. 15). На основе данных из этого места можно разрабатывать собственные процессы (например, с помощью служб SQL Server Integration Services) для упорядочения и преобразования журналов аудита в отчеты, пригодные для использования.

Сбор журналов

Объект подсистемы аудита SQL Server можно настроить для сохранения журнала в файле. Рекомендуется отправка журналов для сохранения в удаленной общей папке. Таким образом они выводятся из-под контроля администратора сервера, и обеспечивается возможность их консолидации с журналами других серверов. К папке, в которой сохраняются журналы, должен быть предоставлен доступ на запись службе SQL Server. По умолчанию эта служба запускается от имени учетной записи NETWORK SERVICE. Риск использования этой учетной записи заключается в том, что любая служба, которая запускается от ее имени, имеет доступ к папке, где хранятся журналы, и может их перезаписать. Для снижения этого риска можно использовать учетную запись пользователя. Однако если эту учетную запись отключить или сменить пароль для нее, то для каждой из служб, в которой она используется, нужно будет обновить пароль, в противном случае служба SQL Server не сможет запуститься.

Передача журналов

Чтобы снизить риск перезаписи файлов журналов, их можно перемещать в закрытую базу данных. После того как файл журнала оказался в базе данных, к нему будут иметь доступ только пользователи, которые имеют доступ к этой базе данных. Имейте в виду, что аудиторы могут быть заинтересованы в возможности изменения журналов после их загрузки в таблицу базы данных. Можно создать пакет служб SSIS, который будет перемещать файлы журналов в таблицу базы данных. Пакет можно загрузить в задание, созданное с помощью агента SQL Server Agent, для его запуска по расписанию, что позволит сократить риск перезаписи файлов журналов.

Использование сценариев и средств для управления аудитом

После того как будет включен аудит в базе данных, вы получите массу информации в виде журналов аудита. Эти журналы быстро заполнят отведенное для них место, поэтому нет смысла вечно хранить все эти журналы аудита. Проще всего загружать данные аудита в базу данных SQL Server, где можно выполнять запросы, которые позволят быстро разобраться в том, что происходит в SQL Server.

Основной поток данных по обработке журналов аудита показан на иллюстрации Рис. 16.

          Рис. 16. Пример GRC

1)     Чтение журналов: необходим механизм считывания файлов журналов аудита, фильтрации и преобразования содержащихся в них данных.

2)     Добавление идентификатора импорта: чтобы отметить обработанные журналы аудита, определите в своем процессе ETL файлы, которые прошли обработку, и присвойте им идентификатор импорта. Таким образом все импортируемые события аудита будут связаны с идентификатором импорта. Это позволит впоследствии определить источник аудита.

3)     Управление и загрузка измерений: существуют текстовые описания для различных событий аудита и связанных с ними кодов доменов (например, категория аудита и класс безопасности). Чтобы сократить размер базы данных аудита, свяжите идентификаторы измерений с таблицей фактов события аудита и введите отдельную таблицу измерения, по которой можно будет перевести идентификатор измерения в имя измерения.

4)     Разбиение и загрузка фактов: здесь основной целью является преобразование всех данных таким образом, чтобы таблицы фактов событий аудита содержали идентификаторы, а не текстовые значения, что позволит сократить занимаемый объем данных. Дополнительным преимуществом выполнения шагов 3 и 4 является тот факт, что при построении отчетов аудита можно будет просматривать их по указанным категориям.

5)     Подсчет строк и сохранение информации о файле: чтобы обеспечить аудит механизма обработки журналов аудита, нам нужно проверить, какие файлы аудита будут обрабатываться (сохранение информации о файле). Шаг подсчета строк представляет собой дополнительное средство проверки, которое сверяет число строк в базе данных и число строк в файле журнала аудита.

Дополнительные сведения см. в разделе Проект аудита.

Аудит определенных пользователей и таблиц

Подсистема аудита SQL Server позволяет выполнять аудит на нескольких уровнях гранулярности. Аудит вообще всех операций в базе данных выполнить практически невозможно, так как это породило бы такой объем журнала, собрать который чрезвычайно трудно. Это также потребовало бы огромного места на диске и значительного расширения полосы пропускания сети. Вместо этого подсистема аудита SQL Server предоставляет возможность наблюдения за определенными ресурсами — пользователями, группами, таблицами и определенными действиями, такими как SELECT, UPDATE и EXECUTE. В следующих разделах эти возможности описаны более подробно.

Аудит отдельных пользователей

Если вы можете гарантировать, что ваша среда безопасна (например, когда изменение схемы и данных могут выполнять только члены роли sysadmin, а все остальные пользователи могут только считывать данные), можно просто организовать аудит пользователя sysadmin. Однако при этом придется исходить из предположения, что авторизованные пользователи не совершают ошибок и что имеются политики безопасности, покрывающие абсолютно все проблемы. Такой подход часто применяется в рабочих средах, и если эти предположения близки к истине, то можно включить аудит только для пользователя sysadmin, чтобы отслеживались все выполняемые им действия.

Но во многих случаях потребуется включить его и для многих других пользователей, поскольку им также необходимо выполнять вставку, обновление и изменение данных в системе. К счастью, в вашей среде только пользователь sysadmin может изменять схемы и объекты, но это не избавляет от необходимости аудита пользователей, имеющих возможность изменения данных. 

Наконец, можно просто включить аудит для всех пользователей, если вы считаете, что это может оказаться для компании перспективным подходом. Но в этом случае журналы аудита будут очень быстро расти, поскольку в них регистрируются все действия, включая те, которые пользователь выполняет на законных основаниях. Это может быть оправданным, например, в здравоохранении, где необходим контроль любого доступа пользователей к базе данных, даже если они просто запрашивают данные в соответствии с нормативами HIPAA. Если вы считаете, что политики безопасности не покрывают всех возможных случаев, можно включить аудит для всех пользователей на случай вторжения хакеров (например, атаки путем внедрения кода SQL). Но если такой уровень детализации не требуется, можно сократить нагрузку на журналы и выполнять аудит только для пользователей, выполняющих изменение схемы (т. е. sysadmin), и пользователей, которые могут изменять данные в базе данных. 

Аудит отдельных таблиц

Точно так же, как есть пользователи, для которых отслеживание действий не требуется, существуют и таблицы, для которых не нужно вести аудит. Например, если задать права доступа таким образом, чтобы данные могли изменяться только в небольшом наборе таблиц, то имеет смысл ограничить и аудит изменением данных в этих таблицах. 

В то же время, существует ряд сценариев, в которых нужно выполнять аудит любых действий, но включать в отчеты нужно только те из таблиц, которые считаются конфиденциальными. Таким образом, на сей раз необходимо включить нужные данные в отчет, а не сохранить все данные аудита в целях обеспечения соответствия требованиям. Чтобы добиться этого, конвейер, обрабатывающий данные аудита, будет считывать из таблицы конфигурации или файла список таблиц, включенных в обработку (или исключенных из нее). 

Отслеживание доступа sysadmin и db_owner

Часто отслеживание действий пользователей с высокими привилегиями необходимо в большей мере, чем для других пользователей, имеющих ограниченные разрешения. Подсистема аудита SQL Server обеспечивает простоту аудита ролей sysadmin и db_owner на любом уровне гранулярности. При определении действий для создаваемой базы данных назначение db_owner в качестве имени участника приводит к тому, что члены роли базы данных db_owner и роли сервера sysadmin также будут отслеживаться, поскольку члены роли sysadmin сопоставлены dbo, который всегда является членом роли db_owner. Следующий сценарий показывает, как это сделать.

USE [Test1]

ALTER DATABASE AUDIT SPECIFICATION [AuditDBO]

  ADD (SELECT ON [dbo].[CCTable1] BY [dbo])

Централизация журналов

Если вы собираетесь выполнять аудит только для одного сервера, то в централизации файлов журналов особого смысла нет. Но в большинстве случаев приходится работать с большим количеством серверов и большим количеством журналов аудита. Поэтому оптимальным подходом будет создание журналов аудита и отправка их в централизованное местоположение. С точки зрения управляемости, если необходимо просмотреть журналы аудита, проще сделать это в одном месте, чем просматривать множество отдельных серверов. Если из каких-либо соображений бизнес-логики нужно вести раздельные хранилища (папки) аудита, то централизация никак не мешает этому. Помимо прочего, с папками в централизованном местоположении проще работать.

В результате централизации хранения журналов аудита централизуется также и обработка. Таким образом, группа выделенных серверов сбрасывает файлы журналов в централизованное местоположение, где они обрабатываются, и при этом действия источников данных и действия аудита (обработка и запросы) никак не мешают друг другу. Например, часто размеры файлов журналов разбиваются так, чтобы каждый из них занимал около 100 МБ. Можно немного увеличить этот размер, если количество файлов слишком велико, но в то же время файлы журналов не должны быть слишком велики, поскольку так на их обработку будет уходить слишком много времени. 

Помимо этого, централизованная обработка позволит создавать отчеты для просмотра аудита всей среды из единого места.

Примечание. Единственным нежелательным эффектом централизации является снижение производительности. Если нужно выполнять аудит множества событий (например, сценарии для обеспечения соответствия требованиям HIPPA требуют аудита каждого пользователя и каждой таблицы), то будет порождаться огромное количество данных, и журнал аудита будет мало отличаться от журнала транзакций. Обычный файловый сервер с сетевым адаптером 100 МБ может не справиться с такой нагрузкой и снизить пропускную способность обработки запросов.

Создание отчетов из файлов журналов

Целью этого раздела является получение отчетов аудита, которые дают представление о событиях, возникающих во всей среде SQL Server. В разделе Полезные советы и сценарии показано несколько способов (в порядке возрастания сложности) анализа файлов журналов. В нем имеются примеры отчетов высокого уровня, которые можно создать для получения представления о работе системы. Отчеты разбиты по типу действий: сервер, база данных, DDL и DML.

Общие сведения — действия сервера

Этот отчет содержит общие сведения обо всех произошедших за день событиях с разбивкой по экземплярам сервера, участнику базы данных, классу и действию. 

      Рис. 17. Отчет о действиях сервера

Анализ трендов — действия сервера

Если к этому добавить диапазон дат, то можно увидеть не только огромное количество успешно выполненных операций входа в систему, но и множество операций администрирования и ошибок входа. Дальнейший анализ показывает интересные закономерности, относящиеся к ошибкам входа. Как видно на Рис. 18, мы просмотрели отчет «Общие сведения — действия сервера» за три дня, с 19.08.2008 по 21.08.2008, и выделили только действие Ошибка входа.

      Рис. 18. Отчет «Анализ трендов — действия сервера»

В данном случае 21.08.2008 ошибок входа было больше, чем во все остальные дни. Вот пример статистического отчета, содержащего полезную логику, — в данном случае надо более подробно разобраться в причинах, вызвавших ошибки входа в эти дни.

Детализация действий сервера

Если вернуться к Рис. 17, то можно также выполнить детализацию этого отчета, чтобы просмотреть более подробную информацию об этих ошибках входа. В данном примере было 83 ошибки. Щелкнув это число, можно просмотреть подробности по ошибкам входа.

      Рис. 19. Отчет «Действия сервера — подробности»

Общие сведения — действия базы данных

Точно так же, как и по действиям сервера, можно просмотреть события аудита по числу выполненных действий базы данных. Как показано на Рис. 20, имеется два экземпляра сервера, в которых 21.08.2008 было выполнено три и четыре действия базы данных.   

      Рис. 20. Отчет «Действия базы данных»

Детализация действий базы данных

Этот отчет показывает, что были выполнены три действия GRANT в базе данных DataCollectionSPW и четыре действия CREATE в базе данных master. Детализация (по щелчку этого числа) немедленно показывает, что четыре действия CREATE были всего лишь инструкциями RESTORE LABELONLY, выполненными от имени входа MYDOMAIN\Sql.

      Рис. 21. Отчет «Действия базы данных — подробности»

Примечание. При создании резервной копии базы данных user для поиска резервного носителя необходимы разрешения CREATE для базы данных master. Разрешение CREATE не совсем верно отражает суть, поскольку в результате операции ничего не создается, но тем не менее оно необходимо для выполнения резервного копирования, раз инструкция RESTORE LABELONLY присутствует в приведенном аудите.

Общие сведения — действия DDL

Чтобы разобраться, какие определения данных выполняются в вашей среде, откройте отчет Общие сведения — действия DDL, показанный на иллюстрации Рис. 22. В данном случае 20.08.2008 было выполнено 44 инструкции DROP TABLE для базы данных SQLDBADMIN в указанном экземпляре.

      Рис. 22. Отчет «DDL базы данных»

Детализация действий DDL

Чтобы убедиться, что инструкции DROP TABLE являются верными, можно выполнить детализацию до отдельных действий DDL. Здесь показаны время, действие, имя объекта и инструкция, выполненная для его удаления, что позволяет увидеть выполненную операцию.

      Рис. 23. Отчет «DDL базы данных — подробности»

Общие сведения — действия DML

Можно также просмотреть типы инструкций DML, выполненных в системе. В отчете Общие сведения — действия DML показано, что 21.08.2008 было выполнено четыре инструкции DELETE в базе данных SQLDBADMIN.

      Рис. 24. Отчет «DML базы данных»

Детализация действий DML

Этот отчет содержит подробные сведения, которые помогут определить правильность каждого из действий DML. Щелкнув цифру «4», можно увидеть полные инструкции DELETE вместе с именем участника-службы сервера пользователя, которым они были выполнены.

      Рис. 25. Отчет «DML базы данных — подробности»

Использование управления на основе политик для определения, развертывания и проверки политик

До сих пор в этом пакете SDK показывалось, как управлять безопасностью, идентификаторами, шифрованием и аудитом в целях обеспечения соответствия требованиям. В данном разделе приведены примеры использования управления на основе политик для проверки правильности настройки каждой из этих областей в соответствии с политикой.

Структура управления на основе политик

Управление на основе политик моделирует компьютер, базу данных и объекты в ней в виде иерархической структуры управляемых целей со вложенными целевыми типами. Управление на основе политик дает возможность определять категории, указывающие на группу политик. Каждая политика состоит из единственного условия, используемого для ее проверки. Каждое условие содержит одно выражение, которое состоит из предложений. Каждая политика может относиться к базе данных или объекту базы данных, такому как пользователь, таблица или хранимая процедура. Эти понятия изображены на схеме Рис. 26. Дополнительные сведения о категориях, выражениях, аспектах, политиках и целях управления на основе политик см. в разделе Администрирование серверов с использованием управления на основе политик.

При необходимости можно определить собственный набор политик. Для начала можно воспользоваться набором политик, поставляемым в составе SQL Server 2008. Их описание см. здесь.

           Рис. 26. Объекты управления на основе политик и их связи

Пример управления на основе политик

Чтобы соотнести управление на основе политик (PBM) с набором политик базы данных, начните с создания категории, представляющей норматив или область политик, управлять которыми необходимо вашей компании. Например, создадим категорию под названием PCI, если это стандарт, которому нам нужно соответствовать. Затем создадим набор политик PBM, соответствующих политикам норматива. Это те политики, которые мы хотим и можем автоматизировать. Определим условия, необходимые для проверки каждой из политик, и свяжем их с базой данных, подпадающей под действие стандарта PCI. На иллюстрации Рис. 27 показано, как можно настроить управление на основе политик в соответствии с PCI.

           Рис. 27. Пример управления на основе политик

Наблюдение за управлением на основе политик

Каждая из политик PBM может отслеживаться, что обеспечивает ее соответствие требованиям. Проверка может выполняться либо по расписанию, либо при возникновении каких-либо изменений в целевом объекте, для которого политика была создана (но не то и другое одновременно). Однако оба способа необходимы для того, чтобы гарантировать применение политики в течение определенного периода времени и неизменность условий между проверками по расписанию. Для этого создайте для каждой политики норматива два набора политик PBM, которые должны быть представлены в управлении на основе политик.

Использование управления на основе политик для обеспечения соответствия требованиям

С точки зрения соответствия требованиям возможности управления на основе политик «По расписанию» и «По запросу» дают возможность регулярно проверять работоспособность и выявлять пользователей и причины нарушений и рекомендаций. Параметр При изменении — запретить препятствует внесению изменений пользователями, а При изменении — записать в журнал позволяет вносить изменения, но регистрирует эти изменения. 

Создание плана

Способ планирования политик целиком и полностью определяется продуктом, правилами компании, рекомендациями и стандартами ИТ. В качестве примеров политик можно привести следующие.

Ø  Соглашения об именах: использование определенных правил именования для объектов базы данных (например, хранимые процедуры, связанные с пользовательским интерфейсом, должны иметь префикс usp).

 

Ø  Отсутствие пользовательских объектов в системных базах данных: гарантирует, что пользовательские объекты даже случайно не смогут быть помещены в базы данных master, msdb и tempDB.

 

Ø  Резервная копия базы данных является текущей: можно определить эксплуатационные соглашения, предусматривающие, что резервная копия базы данных является текущей (например, в пределах последних 24 часов).

 

Ø  Стандартные свойства базы данных: содержит свойства базы данных, обеспечивающие отключение автоматического сжатия и автоматического закрытия, либо правильную настройку автоматического увеличения размера, что предотвращает бесконтрольный рост базы данных, при котором она может занять все свободное место на диске.

 

Ø  Проверка учетных записей служб: гарантирует, что службы баз данных, такие как агент SQL Server, запускаются от имени соответствующей учетной записи службы.

 

Ø  Стандарты имени входа: многие производители в своих средах создают имена входа (например, двухсимвольные) для приложений. Можно создать политики, которые будут проверять этот тип создания.

 

Ø  Проверка sysadmin: гарантирует, что в роль сервера sysadmin назначены соответствующие элементы и отключена учетная запись sa.

 

Вот несколько примеров типов политик, которые можно создать. Как видите, возможности весьма широки. Но чтобы сделать это правильно, нужно сначала разобраться, в чем заключаются ваши ИТ-стандарты и рекомендации. После этого можно будет перейти к классификации и ранжированию типов политик, которые вы собираетесь развернуть.

Категория	Ранг	Политика	Режим выполнения
Действия	1	Резервная копия базы данных является текущей	По расписанию
	2	Гарантирует, что резервная копия сертификатов является текущей	По расписанию
	…	…	…
	28	Обеспечивает, что папка данных находится на диске H:	По запросу
Соглашения об именах	1	Гарантирует, что имя базы данных не превышает 50 символов	По расписанию
	2	Гарантирует, что имя базы данных не содержит пробелов	По расписанию
	3	Гарантирует, что роли базы данных…	При изменении — запретить
	…	…	…
	40	Гарантирует, что имена хранимых процедур не содержат подстроку "foo"	По запросу

    Таблица 4. Ранжирование политик по категориям

   

Начинать планирование стратегии в отношении политик лучше всего с разработки таблицы всех существующих и возможных политик. По этой таблице можно проверить, какие из политик уже работают. Кроме того, она поможет избавиться от ненужных политик, конфликтов и пересечений. В таблицу также можно внести новые политики и их приоритеты. 

Исходя из плана, какие политики нужно создать?

Как говорилось выше, одна из целей создания плана политик — определиться с политиками и назначить им приоритеты. На основе этого плана можно создать свой план и провести последовательное развертывание политик.

Смысл последовательного подхода заключается в том, что так будет сразу видно, какое влияние политики оказывают на систему. Если вы начали работу с небольшого числа политик, то влияние на систему будет незначительным. Но после того как будут созданы сотни политик, одновременно запускаемых по расписанию, могут начаться конфликты из-за ресурсов при попытке найти политики, нарушающие рекомендации и ИТ-стандарты. Важную роль играет ранжирование политик — оно определяет, какие из них должны быть развернуты первыми.

Определение режима выполнения для политик

Для каждой из политик в определенном выше плане нужно определить тип применения. Многие из политик будут периодически запускаться по расписанию и проверять систему на наличие нарушений. Не забывайте, что запуск проверки политик создает дополнительную нагрузку на серверы. При каждом изменении политики необходимо проверять, каким образом это изменение отразится на производительности системы.

Таким образом, нужно разделить политики, которые действительно требуют проверки по расписанию, и политики, которые можно выполнять по запросу. Например, имеет смысл периодически проверять, что резервные копии базы данных и ключа сертификата являются свежими. А вот проверять, расположена ли папка данных SQL Server на диске H:, вероятно, можно и по требованию. В то же время пользователь может попытаться создать недопустимую роль в базе данных — в этом случае можно создать политику, которая не даст ему даже произвести это изменение. Либо, если нужно позволить ему сделать это изменение, можно потребовать внесения этого изменения в журнал (т. е. выбрать режим При изменении — записать в журнал), чтобы можно было хотя бы проконтролировать это. 

На иллюстрации Таблица 5 показаны различные режимы выполнения, которые зависят от аспекта политики. В таблице перечислены следующие режимы выполнения: проверка «При изменении — запретить» (CoC: Prevent), проверка «При изменении — записать в журнал» (CoC: Log) и проверка «По расписанию» (CoS). Дополнительные сведения о том, какие из режимов выполнения применимы к каждому из аспектов, см. в разделе Управление на основе политик SQL Server: аспекты. 

Таблица 5. Поддержка аспектов для типов выполнения

Использование управления на основе политик для реализации KPI и KRI

Ключевые индикаторы производительности (KPI) и рисков (KRI) используются многими бизнесменами для отслеживания показателей работы предприятия. Управление на основе политик может быть использовано для создания KPI и KRI для базы данных SQL Server. Сначала создается категория PBM, представляющая набор KPI и KRI, которые нужно создать. Затем создается набор политик, которые могут быть использованы для проверки, находятся ли KPI и KRI в диапазоне приемлемых значений. Справку по созданию политик PBM см. в этом руководстве.

Управление на основе политик предусматривает аспекты для создания KPI, такие как «Производительность базы данных», «Производительность сервера», «Пул ресурсов» и «Структура плана». После того как политики будут созданы, убедитесь в том, что за ними правильно ведется наблюдение, и вы получите предупреждение, если значения выйдут за пределы нормального рабочего диапазона.

Использование управления на основе политик для проверки конфигурации аудита

Управление на основе политик может быть использовано для проверки правильности параметров аудита на серверах. Политика может проверить, существует ли спецификация аудита базы данных и сервера, и включена ли она. 

Примечание. Как указано в разделе Аудит конфиденциальных операций выше, существуют разные уровни конфиденциальности, и при этом выполнение аудита нужно не для всех объектов и пользователей. Поэтому в плане политик нужно учесть, что некоторые политики будут применимы только к некоторым системам и совершенно неприменимы к другим. Например, для серверов, где хранятся закрытые личные данные, аудит должен быть включен для того, чтобы можно было отследить любое действие. Но для серверов, где хранится только общая информация, аудит может потребоваться только для действий пользователя sysadmin. В последнем случае можно обойтись и без политики, которая отслеживает все действия, поскольку такая информация вряд ли когда-нибудь окажется полезной. 

Использование управления на основе политик для проверки конфигурации шифрования

Создание политик для проверки активности шифрования — еще один хороший способ слежения за работоспособностью системы. Как и в случае с аудитом, политики могут оказаться применимыми не ко всем имеющимся серверам. Серверы, содержащие личные данные, требуют шифрования и наличия политик для проверки их активности, но для серверов, где хранится общая несекретная информация, не нужно ни шифрование, ни политики. Некоторые области применения политик перечислены далее.

·        Проверка активности прозрачного шифрования данных (TDE) для баз данных, где оно требуется (эту проверку следует выполнять только для баз данных, содержащих личные данные).

·        Проверка шифрования резервных копий базы данных.

·        Проверка периодической смены ключей шифрования (например, раз в месяц).

·        Проверка резервного копирования ключей.

·        Проверка хранения резервных копий ключей в некотором централизованном месте.

·        Ограничение доступа к ключам шифрования.

 

Развертывание политик

Существуют различные способы развертывания политик на нескольких серверах. Один из методов — центральный сервер управления, который содержит все политики и экспортирует их в общую папку в виде файлов. 

    Рис. 28. Экспорт политики

Когда нужно импортировать политику, в среде SQL Server Management Studio можно в средстве просмотра «Зарегистрированные серверы» импортировать политики для всей группы, как показано на иллюстрации Рис. 29. 

        Рис. 29. Импорт политики

Можно также программным способом запустить сценарий Windows PowerShell™, выполняющий развертывание политик, как показано в разделе Развертывание политик PBM с помощью PowerShell.

Создание отчетов управления на основе политик

Если используется инфраструктура создания отчетов аудита, то рекомендуется также организовать единую центральную точку, в которой можно следить за состоянием работоспособности и выполнением политик на всех серверах. Таким образом вы сможете выполнять аудит и наблюдать за работоспособностью своей серверной среды из одного центрального местоположения.

Рис. 30. Централизованный сбор информации о политиках

Как показано на иллюстрации Рис. 30, центральный сервер SQLAudit можно использовать в качестве маршрутизатора для сбора информации о политиках. Процесс (показан сценарий PowerShell) сначала получает список серверов, которые производят доступ к данным о политиках. Затем производится извлечение данных из нужных таблиц и помещение их в общую папку. Затем эти данные можно загрузить на центральный сервер SQLAudit, чтобы можно было строить отчеты.

Просмотр всех политик

Как видно из отчета на иллюстрации Рис. 31, можно просмотреть информацию обо всех политиках, существующих на всех экземплярах сервера, включая режим выполнения, аспекты, политики и условия.

      Рис. 31. Отчет о политиках PBM

Здесь можно выполнить детализацию углублением, например если нужно просмотреть данные по параметру «Выполненные политики» из отчета «Проверочная политика с большим количеством нарушений» для экземпляра сервера с именем Caregroup за 18.06.2008.

      Рис. 32. Отчет «Управление на основе политик — подробности»

 

 

Обратите внимание, что на иллюстрации Рис. 32 видно, что отчет о выполнении политик содержит только ошибки. Причина этого заключается в том, что по умолчанию управление на основе политик регистрирует только ошибочные события. Чтобы регистрировались и ошибочные, и успешные события, в свойствах управления на основе политик установите для параметра LogOnSuccess значение True, как показано на иллюстрации Рис. 33. Параметр LogOnSuccess определяет, включаются ли в журнал управления на основе политик успешно выполненные политики.

Рис. 33. Свойства управления политиками —

Полезные советы и сценарии

В этом разделе содержится ряд полезных советов и сценариев, которые могут оказать неоценимую помощь в разработке решений по обеспечению соответствия требованиям в составе коммерческих или внутренних приложений компании. Все эти сценарии включены в состав пакета SDK соответствия стандартам. Там, где это применимо, имена файлов, в которых содержатся сценарии, указаны в заголовке.

Программные интерфейсы SQL Server

SQL Server предусматривает несколько программных интерфейсов. В этом разделе содержатся общие сведения о трех из них, которые используются в разделе: управляющие объекты SQL Server (SMO), Transact-SQL и Windows PowerShell.

SMO

Управляющие объекты SQL Server (SMO) представляют собой набор объектов, предназначенных для программирования всех аспектов управления Microsoft SQL Server. Объекты SMO можно использовать для создания специализированных приложений SQL Server. Объектная модель SMO расширяет и заменяет объектную модель DMO. По сравнению с моделью объектов SQL-DMO модель SMO повышает производительность, управляемость и облегчает использование. Почти все функциональные возможности SQL-DMO включены в модель объектов SMO, и, кроме того, в этой модели реализованы различные новые классы, поддерживающие новые возможности в SQL Server.

Transact-SQL

Transact-SQL — это традиционный язык взаимодействия с SQL Server. Все приложения, взаимодействующие с экземпляром SQL Server, независимо от их пользовательского интерфейса отправляют серверу инструкции Transact-SQL. Если разработчик собирается создавать приложение базы данных, то он должен хорошо владеть этим языком.

Windows PowerShell

Windows PowerShell — это оболочка командной строки и язык сценариев, который значительно повышает производительность труда ИТ-специалиста. Этот язык сценариев, рассчитанный специально на администраторов, предоставляет в их распоряжение согласованный синтаксис, более 130 стандартных программ командной строки и служебных программ. Windows PowerShell позволяет ИТ-специалистам с большей эффективностью администрировать системы и автоматизировать повторяющиеся задачи. Теперь Windows PowerShell включен в состав Windows Server 2008. Чтобы быстро начать создавать сценарии, см. Краткий справочник по Windows PowerShell.

Некоторые из примеров в этом разделе написаны в виде сценариев PowerShell. Сведения об использовании PowerShell совместно с SQL Server см. здесь. Можно также просмотреть видео, содержащее советы по использованию PowerShell в SQL Server.

Примечание. На первый взгляд запуск сценариев PowerShell может показаться непростым. Во-первых, для выхода в PowerShell нужно запускать SQLPS, а не POWERSHELL. Если нужна дополнительная справка, ознакомьтесь с разделом Запуск сценариев Windows PowerShell.

VBScript

Microsoft Visual Basic® Scripting Edition (VBScript) реализует активные сценарии на множестве платформ, в том числе на веб-клиенте в обозревателе Microsoft Internet Explorer® и веб-сервере на службах Microsoft IIS. VBScript позволяет создавать приложения для работы с SQL Server. Репозиторий сценариев, содержащий десятки примеров для SQL Server, см. по адресу Хранилище сценариев: SQL Server.

Компоненты доступа к данным Windows DAC

Компоненты Windows DAC представляют собой набор технологий, реализующих доступ к информации в масштабах всего предприятия. В число этих технологий входят объекты данных ActiveX® (ADO), OLE DB и интерфейс ODBC. Управляемые данными клиент-серверные приложения развертываются в Интернете или в локальной сети и могут использовать эти компоненты для интеграции информации из базы данных SQL Server.

Проверка параметров безопасности (Server Security Policy.xml)

В разделе Использование проверки подлинности Windows обсуждается важность использования режима проверки подлинности Windows при проверке подлинности сервера. Это можно сделать вручную в среде SQL Server Management Studio, выбрав нужный режим на странице Безопасность в окне Свойства сервера, как показано на иллюстрации Рис. 34. Для наблюдения за этими параметрами может быть использовано управление на основе политик. Пример того, как это сделать, см. в файле Server Security Policy.xml.

          Рис. 34. Свойства безопасности сервера

Проверка подлинности Windows — один из примеров параметров безопасности, которые служат для ограничения доступа к конфиденциальным данным. Управление на основе политик позволяет администраторам баз данных создавать политики, обеспечивающие наличие и неизменность параметров безопасности (и других параметров). В общем случае создать политику для параметров объекта не сложнее, чем щелнуть правой кнопкой мыши этот объект и выбрать пункт Аспект. На иллюстрации Рис. 35 показан пример доступа к меню Аспекты по щелчку объекта правой кнопкой мыши.

Рис. 35. Выбор аспектов для объекта сервера

 

В диалоговом окне Просмотр аспектов, показанном на иллюстрации Рис. 36, представлены все доступные аспекты для выбранного объекта вместе с их значениями.

          Рис. 36. Диалоговое окно аспектов сервера управления на основе политик

Управление разграничением обязанностей (SOD Policy.xml)

Файл политик SOD Policy.xml обеспечивает переименование и отключение учетной записи sa. Он следит за тем, чтобы только определенные учетные записи служб и пользователей можно было добавить в роль sysadmin. Он гарантирует, что пользователю может быть назначена только одна роль. В оставшейся части этого раздела показано, как выполнять эти функции вручную. Эта политика содержит два сценария, которые показывают гибкость выражений управления на основе политик.

Примечание. Когда сценарий входит в условие политики, в диалоговом окне Вычисление политик рядом с политикой появляется значок предупреждения и сообщение в верхней части окна, как показано на иллюстрации Рис. 37. Включение этих сценариев также препятствует автоматическому вычислению политик из среды SQL Server Management Studio. Однако после нажатия кнопки Вычислить вычисление будет разрешено.

          Рис. 37. Политики со сценариями, выделенные в диалоговом окне
          Вычисление политик

Использование сценариев в условиях

Одним из наиболее эффективных способов задания условий для управления на основе политик являются сценарии. Ниже приведен один из таких сценариев, используемый в условии «Проверка ролей» для политики разграничения обязанностей.

IsNull(ExecuteSql('Numeric',

                  'SELECT COUNT(DISTINCT name)

                   FROM sys.server_role_members,

                        sys.server_principals

                   WHERE principal_id = member_principal_id

                     AND role_principal_id

                      IN (SUSER_ID (''sysadmin''),

                          SUSER_ID (''bulkadmin''),

                          SUSER_ID (''securityadmin''))   

                   GROUP BY name

                   HAVING COUNT(member_principal_id)> 1 '), 0)

 

Функция ExecuteSQL позволяет внедрить инструкцию SELECT в выражение управления на основе политик. Эта инструкция SELECT определяет, является ли пользователь членом более чем одной роли безопасности из заданного списка. Если да, то возвращается значение больше нуля, что приводит к невыполнению условия. Список ролей может быть изменен при необходимости. Имена ролей заключаются в двойные апострофы, поскольку инструкция SELECT сама по себе внедряется в апострофы. Функция IsNull возвращает числовой результат в том случае, если в результирующем наборе попадется значение NULL.

 

Отключение всех учетных записей sysadmin (ManageSA.sql)

Отключение всех учетных записей роли sysadmin — это надежный способ заблокировать доступ к данным привилегированным пользователям, которые не должны их видеть. В обычных условиях отключение всех учетных записей sysadmin не подходит, поскольку только sysadmin может повторно включить эту учетную запись. В этом случае нужно будет остановить базу данных, перевести ее в однопользовательский режим и повторно включить учетную запись sysadmin. Следующий код показывает, как отключить все учетные записи sysadmin.

USE master

GO

 

CREATE PROCEDURE sp_DisableSA AS

IF (DB_ID() = 1)

BEGIN

  DECLARE @cmd nvarchar(max)

 

○  -- SID 0x01 всегда равен "sa"

  SET @cmd = N'ALTER LOGIN ' + QUOTENAME(SUSER_NAME(0x01)) +

             N' DISABLE'

  EXEC ( @cmd )

END

ELSE

BEGIN

    RAISERROR ('sp_DisableSA is only valid when hosted in master DB', -- Текст сообщения.

               16, -- Серьезность.

               1 -- Состояние.

               );

END

GO

 

CREATE PROCEDURE sp_EnableSA AS

IF (DB_ID() = 1)

BEGIN

  DECLARE @cmd nvarchar(max)

 

○  -- SID 0x01 всегда равен "sa"

  SET @cmd = N'ALTER LOGIN ' + QUOTENAME(SUSER_NAME(0x01)) +

             N' ENABLE'

  EXEC ( @cmd )

END

ELSE

BEGIN

    RAISERROR ('sp_EnableSA is only valid when hosted in master DB', -- Текст сообщения.

               16, -- Серьезность.

               1 -- Состояние.

               );

END

GO

 

-- Создать сертификат для подписи хранимой процедуры

CREATE CERTIFICATE SACert WITH SUBJECT = 'For signing stored procedures'

GO

 

-- Разрешить вход для выполнения хранимой процедуры

GRANT EXECUTE ON sp_DisableSA TO [SQLVM03-18158EA\Pat];

 

-- Подписать хранимую процедуру

ADD SIGNATURE TO sp_DisableSA BY CERTIFICATE SACert;

 

-- Создать имя входа на основе сертификата

CREATE LOGIN [CertLogin] FROM CERTIFICATE SACert;

 

-- Добавить имя входа в роль sysadmin, чтобы хранимая процедура

-- могла отключить учетную запись sa

EXEC sp_addsrvrolemember [CertLogin], N'sysadmin';

 

-- Удалить закрытый ключ, чтобы хранимую процедуру нельзя было повторно подписать

ALTER CERTIFICATE [SACert] REMOVE PRIVATE KEY;

 

-- Изменить спецификацию аудита сервера для отслеживания использования

-- хранимой процедуры. Обеспечивает отключение спецификации аудита

-- перед запуском команды ALTER

ALTER SERVER AUDIT SPECIFICATION [Audit Login Changes]

  WITH (STATE = OFF)

GO

 

ALTER SERVER AUDIT SPECIFICATION [Audit Login Changes]

ADD (SERVER_PRINCIPAL_CHANGE_GROUP)

GO

 

ALTER SERVER AUDIT SPECIFICATION [Audit Login Changes]

  WITH (STATE = ON)

GO

Проверка учетной записи sa (ValidateSA.sql)

Условие «Проверка ролей» может служить для проверки переименования или отключения учетной записи sa. В следующем сценарии показано, как это сделать вручную.

IF (SELECT COUNT(*)

    FROM sys.server_principals

    WHERE = principal_id 1

      AND is_disabled = 1

      AND name != 'sa') = 1

  PRINT 'Compliant'

ELSE

  PRINT 'Non-compliant'

Проверка членства в роли sysadmin (ValidateSysadmins.sql)

Условие «Проверка ролей» в файле SOD Policy.xml может служить для проверки того, что только определенные пользователи являются членами роли sysadmin. В следующем сценарии показано, как это сделать вручную.

DECLARE @Admin1   sysname

DECLARE @Admin2   sysname

DECLARE @Admin3   sysname

DECLARE @Admin4   sysname

 

SET @Admin1 = @@SERVERNAME + '\Pat'

SET @Admin2 = 'NT AUTHORITY\SYSTEM'

SET @Admin3 = 'NT AUTHORITY\NETWORK SERVICE'

SET @Admin4 = 'sa'

 

IF EXISTS (SELECT name

           FROM sys.server_role_members A,

                sys.server_principals B

           WHERE A.member_principal_id = B.principal_id

             AND role_principal_id = SUSER_ID('sysadmin')

             AND name NOT IN (@Admin1, @Admin2,

                              @Admin3, @Admin4))

  PRINT 'Non-compliant'

ELSE

  PRINT 'Compliant'

Применение разграничения ролей (SOD Policy.xml)

Эти сценарии проверяют наличие пользователя в нескольких ролях и выдают их список вместе с именами ролей. Один набор просматривает роли сервера, второй — роли базы данных.

Важно отметить, что в SQL Server отсутствует простой способ наблюдения за членством в группах безопасности Windows. Каждая организация должна создать политику для управления членством в группах безопасности. Если используются группы, то SQL Server не сможет определить, является ли пользователь членом нескольких ролей SQL Server через членство в группах.

Наблюдение за ролями сервера (ValidateServerRoles.sql)

В диалоговом окне Свойства имени входа можно просмотреть роли сервера для пользователя и определить, является ли он членом более чем одной критической роли, как показано на иллюстрации Рис. 38.

    Рис. 38. Диалоговое окно Свойства имени входа

Файл политик управления на основе политик SOD Policy.xml обеспечивает отслеживание ролей сервера и следит за тем, чтобы пользователю нельзя было назначить более одной роли без просмотра всех его ролей вручную. Показанный ниже сценарий выполняет ту же функцию. Если возвращается число больше нуля, то пользователь является членом нескольких ролей из заданного списка.

SELECT COUNT(*)Count

FROM sys.server_role_members, sys.server_principals

WHERE principal_id = member_principal_id

  AND role_principal_id

   IN (SUSER_ID('sysadmin'), SUSER_ID ('bulkadmin'),

       SUSER_ID ('securityadmin'))   

GROUP BY member_principal_id

HAVING COUNT(member_principal_id)> 1

Получение списка пользователей, входящих в несколько ролей (ValidateServerRoles.sql)

В этом сценарии приведены имена пользователей, входящих в несколько ролей сервера, вместе с ролями, в которые они входят. В данном примере в сценарий включено всего три роли сервера. Число ролей и их выбор можно изменить, поскольку они не изменяются в процессе работы сценария.

SELECT A.Name, B.NAME Role

FROM sys.server_principals A,

     sys.server_principals B,

     sys.server_role_members C

WHERE A.name IN (SELECT Name

                 FROM sys.server_role_members,    

                      sys.server_principals

                 WHERE principal_id = member_principal_id

                   AND role_principal_id

                    IN (SUSER_ID('sysadmin'),

                        SUSER_ID ('bulkadmin'),

                        SUSER_ID ('securityadmin'))

                 GROUP BY member_principal_id, name

                 HAVING COUNT(member_principal_id)> 1

                )

   AND A.principal_id = C.member_principal_id

   AND B.principal_id = C.role_principal_id

ORDER BY Name

 

Наблюдение за ролями базы данных (ValidateDatabaseRoles.sql)

В диалоговом окне Пользователь базы данных можно просмотреть роли базы данных для пользователя и определить, является ли он членом более чем одной критической роли, как показано на иллюстрации Рис. 39.

  Рис. 39. Членство пользователя в ролях базы данных

Этот сценарий получает имена пользователей вместе с числом ролей базы данных, к которым они принадлежат. Этот сценарий возвращает пустой результирующий набор, если нет пользователей, принадлежащих нескольким ролям. В данном примере в сценарий включено всего три роли базы данных. Число ролей и их выбор можно изменить, поскольку они не изменяются в процессе работы сценария.

SELECT COUNT(member_principal_id) Count, Name

FROM sys.database_role_members,

     sys.database_principals

WHERE principal_id = member_principal_id

  AND role_principal_id

   IN (DATABASE_PRINCIPAL_ID('db_securityadmin'),

       DATABASE_PRINCIPAL_ID('db_backupoperator'),

       DATABASE_PRINCIPAL_ID('db_datawriter'))   

GROUP BY member_principal_id, Name

HAVING COUNT(member_principal_id)> 1

ORDER BY Name

 

В этом сценарии приведены имена пользователей, входящих в несколько ролей базы данных, вместе с ролями, в которые они входят. В данном примере в сценарий включено всего три роли базы данных. Число ролей и их выбор можно изменить, поскольку они не изменяются в процессе работы сценария.

SELECT A.Name, B.Name Role

FROM sys.database_principals A,

     sys.database_principals B,

     sys.database_role_members C

WHERE A.name IN

        (SELECT Name

         FROM sys.database_role_members,

              sys.database_principals

         WHERE principal_id = member_principal_id

           AND role_principal_id

           IN (DATABASE_PRINCIPAL_ID('db_securityadmin'),

               DATABASE_PRINCIPAL_ID('db_backupoperator'),

               DATABASE_PRINCIPAL_ID('db_datawriter'))   

         GROUP BY member_principal_id, name

         HAVING COUNT(member_principal_id)> 1

        )

  AND A.principal_id = C.member_principal_id

  AND B.principal_id = C.role_principal_id

ORDER BY Name

Управление ключами шифрования

Часто администраторы баз данных пользуются шифрованием для защиты конфиденциальных данных, а о правильности управления ключами шифрования думают в последнюю очередь. Помимо этого, в SQL Server отсутствуют развитые средства управления ключами шифрования. Этот раздел рассказывает об управлении ключами и содержит сценарии, облегчающие этот процесс.

Наблюдение за доступом к ключам шифрования (AuditCryptoActions.sql)

Подсистема аудита SQL Server обеспечивает наблюдение за доступом к ключам шифрования, а также к параметрам шифрования в базе данных. Наблюдение за событиями DATABASE_OBJECT_ACCESS_GROUP и DATABASE_OBJECT_CHANGE_GROUP в спецификации аудита сервера позволит обеспечить регистрацию в журнале любого доступа к ключам шифрования базы данных. Наблюдение за событиями DATABASE_CHANGE_GROUP в спецификации аудита базы данных позволит регистрировать в журнале любые изменения флажка шифрования базы данных.

Изменение спецификации аудита сервера для наблюдения за изменениями и доступом ко всем объектам базы данных не приведет к регистрации большого числа событий, поскольку таких объектов немного, а их просмотр и изменение в стабильно работающих базах данных выполняется нечасто. Список объектов см. в разделе Отслеживание доступа к ключам.

Просмотр сертификатов и ключей (ViewKeys.sql)

Сертификаты для прозрачного шифрования данных (TDE) должны создаваться в базе данных master с помощью сценария, подобного приведенному ниже. Создание главного ключа необходимо только в том случае, если оно еще не производилось. Главный ключ необходим для защиты сертификата.

USE master;

CREATE MASTER KEY ENCRYPTION

BY PASSWORD = 'UseStrongPassword1!';

GO

 

CREATE CERTIFICATE MyServerCert

WITH SUBJECT = 'My DEK Certificate for Sensitive Data'

 

После создания сертификата его можно просмотреть, выполнив запрос к таблице certificates в базе данных master, как показано ниже.

USE master

SELECT name, certificate_id, start_date, thumbprint, pvt_key_last_backup_date

FROM sys.certificates

 

На иллюстрации Таблица 6 показаны некоторые важные столбцы из таблицы сертификатов. Столбец start_date служит для определения необходимости повторного создания сертификата. Столбец thumbprint служит для определения баз данных, связанных с сертификатом. Он соответствует столбцу encryptor_thumbprint, описанному ниже в этом разделе. Столбец pvt_key_last_backup_date указывает, когда в последний раз производилось создание резервной копии сертификата. Если значение равно NULL, то резервная копия сертификата еще не создавалась. Резервное копирование играет весьма важную роль, поскольку при уничтожении сертификата все защищенные им резервные копии, например на лентах, становятся непригодными к использованию.

name	start_date	thumbprint	pvt_key_last_backup_date
NewServerCert	2008-07-20 19:43:04.000	0xBF372D91C333B1E…	NULL
DEKCert_258	2008-07-23 04:21:40.000	0x99CF8887C56CEC9…	2008-07-23 04:50:36.553
DEKCert_260	2008-07-23 04:51:55.000	0x8BFD5885501314B…	2008-07-23 04:51:56.490
DEKCert_261	2008-07-25 05:11:26.000	0xC1B737DAFDCFAC…	2008-07-25 05:11:28.800

 Таблица 6. Столбцы в таблице сертификатов

Ключи для прозрачного шифрования данных (TDE) должны создаваться с помощью сценария, подобного показанному ниже, прежде чем TDE можно будет включить для базы данных. Для успешного создания ключа шифрования необходимо наличие ранее созданного сертификата.

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE DEKCert_258

GO

После создания ключа его можно просмотреть, выполнив запрос к динамическому административному представлению, как показано ниже.

SELECT database_id, create_date, regenerate_date,

       encryptor_thumbprint

FROM sys.dm_database_encryption_keys

 

На иллюстрации Таблица 7 показаны некоторые важные столбцы из представления. Столбец database_id определяет базу данных, в которой создан ключ. Идентификатор 2 указывает на tempdb. Столбец regenerate_date указывает время последнего формирования ключа. Он будет равен значению в столбце create_date, если ключ только что создан. Служит для определения необходимости повторного создания ключа. Как говорится в разделе Смена сертификатов в сравнении со сменой ключей, лучше менять сертификат, а не ключ шифрования. Столбец encryptor_thumbprint может быть использован для определения сертификата, используемого для защиты ключа.

 

database_id	create_date	regenerate_date	encryptor_thumbprint
2	2008-08-20 17:46:28.110	2008-08-20 17:46:28.110	0
7	2008-07-01 20:27:03.983	2008-08-07 16:14:36.013	0xC1B737DAFDCFAC9C…
8	2008-07-01 20:27:04.137	2008-08-07 16:14:36.103	0xC1B737DAFDCFAC9C…
9	2008-07-01 20:27:32.667	2008-08-07 16:14:36.213	0xC1B737DAFDCFAC9C…

  Таблица 7. Столбцы представления sys.dm_database_encryption_keys   

Смена сертификатов (RotateCerts.sql)

Показанный ниже сценарий заново формирует все сертификаты в текущей базе данных. Его можно запускать только в базе данных master. Повторное формирование выполняется только для тех сертификатов, которые действуют более месяца и использовались для защиты ключа шифрования базы данных. Чтобы изменить частоту повторного создания, внесите изменения в вызов функции DATEDIFF. Идентификатор заменяемого сертификата используется в имени нового, что позволяет избежать конфликтов. После формирования нового сертификата он будет использоваться для защиты всех ключей шифрования базы данных, которые защищал предыдущий сертификат. Этот сценарий может запускаться в задании по расписанию с помощью агента SQL Server.

Примечание. Необходимо как можно скорее провести резервное копирование закрытого ключа для каждого вновь создаваемого сертификата и сохранить ее в безопасном месте. Если этого не сделать, то при утере исходного закрытого ключа это может привести к невозможности восстановления зашифрованных данных.

DECLARE @Thumbprint varbinary(32)

DECLARE @CertID     int

DECLARE @CertName   sysname

DECLARE @DB_ID      int

DECLARE @cmd        nvarchar(max)

 

-- Ключевое слово INSENSITIVE предотвращает

-- обработку сертификатов, созданных этим сценарием

DECLARE Certificate_Cursor INSENSITIVE CURSOR FOR

  SELECT [thumbprint], [certificate_id]

  FROM sys.certificates

  WHERE (DATEDIFF(MONTH, [start_date], GETDATE()) > 0 )

    AND [thumbprint]

     IN (SELECT DISTINCT encryptor_thumbprint

         FROM sys.dm_database_encryption_keys)

 

OPEN Certificate_Cursor;

FETCH NEXT FROM Certificate_Cursor INTO @Thumbprint, @CertID;

 

WHILE @@FETCH_STATUS = 0

BEGIN

  SET @CertName = 'DEKCert' + '_' + LTRIM(STR(@CertID));

  SET @cmd = N'CREATE CERTIFICATE ' + QUOTENAME(@CertName) +

               N' WITH SUBJECT = ''DEK Certificate'''

  EXEC( @cmd )

 

  DECLARE Database_Cursor CURSOR FOR

    SELECT [database_id]

    FROM sys.dm_database_encryption_keys

    WHERE [encryptor_thumbprint] = @Thumbprint

   

  OPEN Database_Cursor;

  FETCH NEXT FROM Database_Cursor INTO @DB_ID;

       

  WHILE @@FETCH_STATUS = 0

  BEGIN

    SET @cmd = N'USE ' + QUOTENAME(DB_NAME(@DB_ID)) + ';' +

               N'ALTER DATABASE ENCRYPTION KEY ' +

               N'ENCRYPTION BY SERVER CERTIFICATE ' +

               QUOTENAME(@CertName)

    EXEC (@cmd);

    FETCH NEXT FROM Database_Cursor INTO @DB_ID;

  END

  CLOSE Database_Cursor;

  DEALLOCATE Database_Cursor;

     

  FETCH NEXT FROM Certificate_Cursor INTO @Thumbprint, @CertID;

END

CLOSE Certificate_Cursor;

DEALLOCATE Certificate_Cursor;

Примечание.            Этот сценарий завершится ошибкой, если его запустить более чем дважды без создания резервной копии журнала.

Создание резервной копии закрытого ключа сертификата (BackupCerts.sql)

Этот сценарий просматривает все сертификаты, используемые для защиты ключа шифрования базы данных, для которых не создана резервная копия (столбец pvt_key_last_backup_date равен NULL), и проводит их резервное копирование в файл в каталоге C:\certificates, используя сертификат в качестве имени файла с расширением CRT. Резервная копия закрытого ключа помещается в тот же каталог с расширением PVT. После создания резервной копии сертификата закрытый ключ необходимо немедленно сохранить в защищенном месте или в базе данных, а файл удалить.

Примечание. Нельзя проводить резервное копирование сертификата автоматически с помощью сценария вроде этого, если только создание пароля не производится безопасным способом. Раскрытие пароля в сценарии делает закрытый ключ уязвимым для доступа. Файл должен быть сохранен в безопасном месте или удален сразу же после импорта в базу данных-получатель. Не используйте один и тот же пароль более одного раза.

DECLARE @CertName  sysname

DECLARE @cmd       nvarchar(max)

 

DECLARE Cert_Cursor  CURSOR FOR

  SELECT [name]

  FROM sys.certificates

  WHERE [pvt_key_last_backup_date] IS NULL AND [thumbprint] IN

  (SELECT DISTINCT [encryptor_thumbprint]

   FROM sys.dm_database_encryption_keys)

       

  OPEN Cert_Cursor;

  FETCH NEXT FROM Cert_Cursor INTO @CertName;

       

  WHILE @@FETCH_STATUS = 0

  BEGIN

    SET @cmd =

             N'BACKUP CERTIFICATE ' + QUOTENAME(@CertName) +

             N'  TO FILE = ''c:\certificates\' + @CertName +

             N'.crt'' WITH PRIVATE KEY ( FILE = ''c:\certificates\' +

             @CertName +

             N'.pvk'', ENCRYPTION BY PASSWORD = ''MyPass7779311#'');'

    EXEC ( @cmd )

    FETCH NEXT FROM Cert_Cursor INTO @CertName;

  END

  CLOSE Cert_Cursor;

DEALLOCATE Cert_Cursor;

Обеспечение смены и резервного копирования пароля (CertRotationPolicy.xml)

Управление на основе политик может быть использовано для контроля смены сертификатов и ключей в соответствии с политиками. Кроме того, оно позволяет обеспечить резервное копирование закрытых ключей сертификатов. Политика PBM в файле CetRotationPolicy.xml содержит условие с тремя сценарными выражениями, которые могут быть использованы для контроля ежемесячной смены сертификатов и ключей, а также резервного копирования закрытых ключей сертификатов.

Управление аудитом

После того как журналы аудита собраны с помощью подсистемы аудита SQL Server, может потребоваться их обработка для выполнения анализа трендов и использования в суде. В этом разделе рассматриваются способы получения большей отдачи от журналов после их получения.

Передача журналов подсистемы аудита SQL Server в таблицу (StoreAuditLogs.sql)

Журналы, полученные из нескольких систем, можно собрать в центральной общей папке, как показано на иллюстрации Рис. 40. Чтобы сделать проще создание отчетов на основе журналов, их можно переместить в таблицу базы данных. Можно с помощью служб SQL Server Integration Services (SSIS) создать пакет, запускаемый по расписанию. Журналы можно перемещать как вручную, так и с помощью сценария. После перемещения журналов в таблицу ее можно открыть с помощью Microsoft Excel® для создания графиков и выполнения анализа трендов. Все эти методики описаны в этом разделе.

           Рис. 40. Передача журналов аудита в таблицу базы данных

Этот сценарий перемещает события из файла журнала подсистемы аудита SQL Server в таблицу базы данных, чтобы сделать проще построение отчетов. Это также позволит исключить возможность перезаписи этого файла системным администратором одного из серверов, которые отправляют события. Для этого необходимо учитывать два момента.

·        Подсистема аудита SQL Server сохраняет в файле журнала только коды операций и типов объектов. Поэтому сценарий выполняет соединение по таблицам действий и объектов, чтобы включить в результат их описание, а затем сохраняет его в таблице базы данных.

·        Имя последнего считанного файла вместе со смещением в этом файле загружается в локальные переменные и используется при считывании данных, чтобы исключить повторную загрузку одних и тех же файлов.

USE [Test1]

GO

 

DECLARE  @data_path nvarchar(256),

         @offset    int

 

SET @data_path = NULL

SET @offset = NULL

 

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[AuditLog]')

                 AND type in (N'U'))

  CREATE TABLE [dbo].[AuditLog](

   [event_time] [datetime2](7) NULL,

   [sequence_number] [int] NULL,

   [action_id] [varchar](4) NULL,

   [action_name] [nvarchar](128) NULL,

   [succeeded] [bit] NULL,

   [permission_bitmask] [bigint] NULL,

   [is_column_permission] [bit] NULL,

   [session_id] [smallint] NULL,

   [server_principal_id] [int] NULL,

   [database_principal_id] [int] NULL,

   [target_server_principal_id] [int] NULL,

   [target_database_principal_id] [int] NULL,

   [object_id] [int] NULL,

   [class_type] [varchar](2) NULL,

   [class_type_desc] [nvarchar](35) NULL,

   [session_server_principal_name] [nvarchar](128) NULL,

   [server_principal_name] [nvarchar](128) NULL,

   [server_principal_sid] [binary](85) NULL,

   [database_principal_name] [nvarchar](128) NULL,

   [target_server_principal_name] [nvarchar](128) NULL,

   [target_server_principal_sid] [binary](85) NULL,

   [target_database_principal_name] [nvarchar](128) NULL,

   [server_instance_name] [nvarchar](128) NULL,

   [database_name] [nvarchar](128) NULL,

   [schema_name] [nvarchar](128) NULL,

   [object_name] [nvarchar](128) NULL,

   [statement] [nvarchar](2000) NULL,

   [additional_information] [nvarchar](2000) NULL,

   [file_name] [nvarchar](260) NULL,

   [audit_file_offset] [bigint] NULL

) ON [PRIMARY]

 

-- Получить время и смещение последней записи журнала

SELECT @data_path = file_name, @offset = audit_file_offset

FROM AUDITLOG

WHERE event_time = (select MAX(event_time)FROM AUDITLOG)

 

INSERT INTO [Test1].[dbo].[AuditLog]

           ([action_name]

           ,[class_type_desc]

           ,[event_time]

           ,[sequence_number]

           ,[action_id]

           ,[succeeded]

           ,[permission_bitmask]

           ,[is_column_permission]

           ,[session_id]

           ,[server_principal_id]

           ,[database_principal_id]

           ,[target_server_principal_id]

           ,[target_database_principal_id]

           ,[object_id]

           ,[class_type]

           ,[session_server_principal_name]

           ,[server_principal_name]

           ,[server_principal_sid]

           ,[database_principal_name]

           ,[target_server_principal_name]

           ,[target_server_principal_sid]

           ,[target_database_principal_name]

           ,[server_instance_name]

           ,[database_name]

           ,[schema_name]

           ,[object_name]

           ,[statement]

           ,[additional_information]

           ,[file_name]

           ,[audit_file_offset])

 

SELECT name, class_type_desc, C.*

FROM sys.dm_audit_actions A, sys.dm_audit_class_type_map B,

     sys.fn_get_audit_file('C:\logs\*', @data_path, @offset) C

WHERE A.action_id = C.action_id

  AND B.class_type = C.class_type

 

Использование служб SSIS для переноса журналов в таблицу базы данных (LoadLogsPackage.dtsx)

Как говорилось в предыдущем разделе и было показано на иллюстрации Рис. 40, для преобразования и передачи данных можно воспользоваться службами SQL Server Integration Services (SSIS). На иллюстрации Рис. 41 показаны задачи из проекта SSIS для перемещения данных из файла аудита SQL Server в таблицу базы данных. Первая задача создает таблицу аудита, если она не существует. Вторая задача выполняет передачу данных.

                                  Рис. 41. Две задачи служб SSIS для сбора журналов аудита

На иллюстрации Рис. 42 подробно показана задача для передачи данных. Необработанные данные журнала считываются из файла подсистемы аудита SQL Server. Из таблицы действий производится получение названия действия. Затем поток данных объединяется с таблицей класса, чтобы получить описание типа класса. Полученные в результате объединения данные помещаются в таблицу аудита.

Рис. 42. Задачи для объединения данных аудита и их сохранения в таблице

Службы SSIS сохраняют выходные данные в пакете, который можно загрузить в шаг задания с помощью агента SQL Server, как показано на иллюстрации Рис. 43. Это позволяет автоматизировать передачу данных. Перемещение данных из файла журнала в локальную базу данных ограничивает круг лиц, имеющих к ним доступ, до администраторов. Этот вопрос необходимо обсудить с аудиторами, поскольку некоторые из них озабочены тем, что в журналы легко внести изменения. Файл CreateAuditJob.sql создает задание аудита, которое запускается каждые пять минут из файла пакета LoadLogsPackage.dtsx, который должен находиться в каталоге C:\.

   Рис. 43. Пакет служб SSIS, загруженный в агент SQL Server

Доступ к событиям подсистемы аудита SQL Server с помощью Excel (AuditReport.xlsx)

После того как данные сохранены в таблице базы данных, можно импортировать их в книгу Excel. То же относится и к журналам подсистемы аудита SQL Server. Excel реализует привычные механизмы анализа данных, представленных в виде таблиц. На иллюстрации Рис. 44 показано, как выглядит таблица подсистемы аудита SQL Server, описанная в предыдущем разделе, после импорта в Excel через ленту «Данные». Чтобы ограничить результат по датам, действиям, объектам и пользователям, можно воспользоваться фильтрами.

          Рис. 44. Журналы подсистемы аудита SQL Server после загрузки в Excel

На основе данных в Excel можно создать динамическое представление PivotTable® с помощью ленты Вставка. На иллюстрации Рис. 45 показан пример представления PivotTable, созданного на основе табличных данных аудита. В PivotTable включаются следующие поля: действие, тип класса и имя участника-службы сервера. Созданная для PivotTable линейчатая диаграмма показывает по типам количество действий, выполненных для определенных объектов пользователем с указанным идентификатором. Список полей в PivotTable определяет поля, включаемые в диаграмму, и способ их использования.

          Рис. 45. Представление PivotTable, сформированное на основе
          журналов подсистемы аудита SQL Server

Получение IP-адреса из журнала аудита

При просмотре журналов аудита поле server_principal_name может служить для определения идентификатора пользователя, выполнившего зарегистрированную операцию, там где это возможно. Однако если для соединения с базой данных используется учетная запись SQL Server, а не учетная запись Windows, то поле server_principal_name не содержит полезной информации, которая позволила бы просто определить лицо, выполнявшее операцию. Однако в журнале имеются данные, которые могут помочь в определении идентификатора пользователя. Каждая запись журнала содержит поле session_id. Это поле поставлено в соответствие идентификатору, назначенному пользователю при входе в систему. В самой первой записи журнала (с идентификатором действия LGIS), имеющей этот идентификатор ID, поле additional_information будет содержать IP-адрес компьютера, который подключается к базе данных. По журналам на этом компьютере можно выяснить, кто работал на нем в это время. Ниже приведен сценарий для получения IP-адреса из поля server_principal_name.

SELECT event_time, statement,

   CAST(additional_information AS XML).value('declare namespace z="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data";

      (//z:address)[1]', 'nvarchar(300)') 

FROM sys.fn_get_audit_file('C:\logs\*',Null, Null)

WHERE action_id = 'LGIS'

ORDER BY event_time DESC

 

Проект аудита

Этот проект является совместной работой Айяда Шаммута (Ayad Shammout) (Caregroup Healthcare), Энди Робертса (Andy Roberts) (Microsoft Consulting Services) и Денни Ли (Denny Lee) (SQL Customer Advisory Team). Целью этого проекта было создание работающего шаблона для комплексного проекта аудита, способного получать журналы, преобразовывать и загружать данные, выполнять их статистическую обработку и строить отчеты. Дополнительные сведения см. в разделе Технический обзор проекта аудита (готовится к публикации).

 

Рис. 46. Рабочий поток для решения с проектом аудита

 

Загрузите решение

Распакуйте файл SQLAudit.zip. Он должен содержать следующие файлы:

·        SQLAuditRepositoryDatabase.sql — SQL-файл, который создает базу данных SQLAudit

 

·        LoadLogsPackage.dtsx — пакет служб SSIS, который выполняет задачу по получению, извлечению, преобразованию и загрузке данных аудита, а затем архивирует файл журнала

 

·        SQLAuditReports — решение служб SQL Server Reporting Services (SSRS), которое можно развернуть на своем сервере для просмотра отчетов аудита

Создайте базу данных SQLAudit

Эта база данных будет служить хранилищем данных аудита.

1.     Откройте файл SQLAuditRepositoryDatabase.sql в среде SQL Server Management Studio и включите режим SQLCMD (в меню Запрос выберите режим SQLCMD). На то, что включен режим SQLCMD, указывают закрашенные серым строки :setvar.

 

2.     Определите следующие переменные.

Переменная	Описание	Комментарии
DataDirectory	Папка данных базы данных SQL Server (например, H:\sqldata\)	Обязательно завершайте путь обратной косой чертой «\».
LogDirectory	Папка журналов базы данных SQL Server (например, H:\sqllog\)	Обязательно завершайте путь обратной косой чертой «\».
DatabaseName	Имя базы данных (например, SQLAudit)	Нет.

 

3.     Выполните сценарий. Создается база данных.

 

 

Установите пакет LoadLogsPackageSSIS

Будут созданы файлы конфигурации, необходимые для выполнения пакета служб SSIS.

1.     Дважды щелкните SQLAuditLoader.SSISDeploymentManifest. Если с этим файлом не связана никакая программа, запустите файл C:\program files\Microsoft SQL Server\100\DTS\Binn\dtsinstall.exe, чтобы открыть мастер установки пакета. Дополнительные сведения об установке пакетов см. в разделе http://msdn.microsoft.com/ru-ru/library/ms365321(SQL.100).aspx.

 

2.     Нажмите кнопку Далее, щелкните Установить в файловую систему и снова нажмите Далее. Выберите папку установки, например C:\Program Files\Microsoft SQL Server\100\DTS\Packages\SQLAuditLoader, нажмите кнопку Далее, а затем снова кнопку Далее.

 

3.     Настройте следующие свойства пакета.

 

Параметр	Пример значения
Регистратор служб SSIS для SQL Server	SqlAuditLogRepository
SqlAuditLogRepository Укажите имя базы данных SQLAudit (например, SQLAudit)	Data Source=.;Initial Catalog=$DBName$;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package-{21C9032A-E45A-41F2-BA67-9EF35FCD18C3}SqlAuditLogRepository;
User:auditLogArchivePath Местоположение, в которое будет производиться архивирование после обработки	D:\audit\logs\archive
User:LogFilePath Расположение журналов аудита	D:\Audit\logs

 

Эти значения будут сохранены в файле LoadLogsPackageConfig.dtsConfig. Если их необходимо изменить после установки пакета служб SSIS, то это можно сделать вручную в этом файле.

 

Примечание. Эта версия проекта аудита подразумевает, что каталог, из которого производится получение файлов аудита, является локальным.

 

4.     Нажмите кнопку Далее, а затем кнопку Завершить.

 

Выполните пакет служб SSIS

При выполнении пакета подразумевается, что в папке D:\audit\logs находятся файлы аудита (как указано выше) и после обработки они будут перемещены в папку D:\audit\logs\archive.

 

1.     В командной строке перейдите в каталог SQLAuditLoader (например, C:\Program Files\Microsoft SQL Server\100\DTS\Packages\SQLAuditLoader).

 

2.     Выполните команду:

dtexec /ConfigFile LoadLogsPackageConfig.dtsConfig /File LoadLogsPackage.dtsx

Примечание. Убедитесь в том, что используется версия DTExec из поставки SQL Server 2008. На сервере, где установлено несколько экземпляров SQL Server 2005 и SQL Server 2008, может запуститься версия DTExec для SQL Server 2005 (9.00.xxxx), а не для SQL Server 2008 (10.00.xxxx). Чтобы избежать этого, нужно указать полный путь к программе DTExec (например, C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe).

Поскольку файлы журналов аудита формируются постоянно, рекомендуется запускать этот пакет служб SSIS периодически (например, раз в 15 минут), чтобы обеспечить своевременную загрузку данных.

 

 

Формирование статистических отчетов

Пакет служб SSIS обрабатывает журналы аудита и загружает их в таблицы aud.AuditLog_[тип события] в базе данных SQLAudit. Но поскольку в них содержится множество событий, удобнее создавать статистические (т. е. сводные) отчеты, которые содержат обобщенную картину по аудиту. 

 

Ø  Для этого в среде SQL Server Management Studio подключитесь к базе данных SQLAudit и выполните следующие команды.

exec aud.rspAggServerActions @EventDate = '08/22/2008'

exec aud.rspAggDatabaseActions @EventDate = '08/22/2008'

exec aud.rspAggDMLActions @EventDate = '08/22/2008'

exec aud.rspAggDDLActions @EventDate = '08/22/2008'

           

В результате этого в таблицы aud.rptAgg[событие аудита]Actions будет загружена сводная статистика.

 

Ø  Рекомендуется вызывать эти хранимые процедуры (например, с помощью агента SQL Server) по ночам, чтобы обеспечить актуальность дневной информации. Пример сценария для запуска задания по ночам выглядит следующим образом.

-- Объявить переменную @LastDay, в которой будет храниться последняя дата,

-- задание будет запущено после полуночи для статистической обработки данных за последний день

Declare @LastDay char(11)

select @LastDay = Convert(char(11), getdate()-1 , 1)

Select @LastDay

 

Exec aud.rspAggServerActions @LastDay

Exec aud.rspAggDatabaseActions @LastDay

Exec aud.rspAggDDLActions @LastDay

Exec aud.rspAggDMLActions @LastDay

 

Как секционированы ваши данные?

SQL-сценарий для создания базы данных аудита (SQLAudit) автоматически создаст 12 файловых групп по одной на каждый месяц, начиная от месяца, в котором выполняется сценарий. Кроме того, схема и функции секционирования обеспечат секционирование по месяцам таблиц aud.AuditLog_% в соответствии с этими файловыми группами. Следующий сценарий покажет, в какие секции таблицы попали те или иные строки данных.

select partition_id, OBJECT_NAME(object_id), object_id, index_id, partition_number, partition_id, rows as [RowCount], x.value

  from sys.partitions

    left outer join (

            select boundary_id, value

              from sys.partition_range_values

             where function_id = (

                  select function_id

                    from sys.partition_functions

                   where [name] = 'monthly_partition_function'

                  )

            ) x

            on x.boundary_id = partition_number - 1

 where OBJECT_NAME(object_id) like 'AuditLog%' and index_id = 1

 order by OBJECT_NAME(object_id), partition_number

Просмотр отчетов

Как отмечалось в разделе «Аудит конфиденциальных операций», существует ряд отчетов, которые можно сформировать на основе журналов аудита. Для просмотра этих отчетов необходимо выполнить развертывание решения SQLAuditReports служб Reporting Services.

 

1.     Откройте решение SQLAuditReports служб Reporting Services в среде разработки Microsoft Visual Studio®.

 

2.     Укажите в качестве TargetServerURL свой сервер, т. е. замените http://campschurmann/ на имя своего сервера.

 

    Рис. 47. Страница свойств SQL AuditingReports

3.     Убедитесь в том, что общий источник данных SQLAudit.rds указывает на вашу базу данных SQLAudit (а не на версию базы данных SQLAudit на другом сервере).

 

4.     Выполните развертывание отчетов (Построить > Развернуть). После этого можно просматривать отчеты по адресу: http://[имя сервера] /Reports/Pages/Folder.aspx?ItemPath=%2fSQL+Auditing+Reports&ViewMode=List

 

Известные проблемы

·        Бывают случаи, когда в журналы аудита не попадает имя экземпляра сервера, т. е. поле имени экземпляра сервера остается пустым. Чтобы обойти эту проблему, пакет служб SSIS проходит по всему журналу и выясняет имя экземпляра сервера, а затем присваивает его всем записям журнала (поскольку все они поступили с одного сервера). Но в некоторых случаях такой подход не работает, поскольку во всем журнале имя экземпляра пусто. Чтобы устранить эту проблему, файлы журналов должны называться в соответствии с соглашением об именах:

SQLAudit$%Server$InstanceName%_%GUID%.sqlaudit

[aud].[fn_GetServerInstanceName] — таким образом, экземпляр можно будет извлечь из имени файла аудита (%Server$InstanceName%) и использовать в том случае, если оно отсутствует в журнале. Чтобы называть файлы журналов в соответствии с соглашением об именах при их создании на сервере, измените имя аудита, указав его в формате SQLAudit$Server$InstanceName.

Управление политиками PBM

Управление на основе политик — отличное средство автоматизации управления базами данных. Многосерверное управление и управление на основе сценариев с применением управления на основе политик — не такая уж простая задача. В этом разделе приведены рекомендации и сценарии, расширяющие возможности управления на основе политик.

Создание политики с помощью PowerShell

Отличный ресурс, где можно научиться создавать политики с помощью PowerShell (а не вручную в среде SQL Server Management Studio), — блог Sethu по ссылке ниже.

http://blogs.msdn.com/sethus/archive/2008/06/16/sql-2008-powershell-script-for-creating-a-policy-and-saving-to-file.aspx

Пространство имен Microsoft.SqlServer.Management.Dmf содержит классы, представляющие управляющие объекты на основе политик SQL Server.

Дополнительные сведения см. в разделе http://msdn.microsoft.com/ru-ru/library/microsoft.sqlserver.management.dmf.aspx.

Развертывание политик PBM с помощью PowerShell (DeployPBMPolicies.ps1)

Следующий сценарий PowerShell может быть использован для развертывания сценариев управления на основе политик на другом экземпляре базы данных.

# Экспорт политик с сервера

$policydir = "C:\Policies\"

del C:\Policies\*

$sourceserver = "<Компьютер>\<Экземпляр>"

$conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$sourceserver;Trusted_Connection=true");

$polstore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);

$fileprefix = "ExportedPolicy_"

$policycount = 0

#Временное решение, пока не будет исправлена ошибка.

#$sourcepolicycount = $polstore.Policies.Count;

foreach ($policy in $polstore.Policies)

{

      $policycount++;

      $StringWriter = New-Object System.IO.StringWriter;

      $XmlWriter = New-Object System.XMl.XmlTextWriter $StringWriter;

      #$polstore.ExportPolicy($polstore.Policies[$policy.Key], $XmlWriter);

      $policy.serialize($XmlWriter);

      $XmlWriter.Flush();

      $StringWriter.Flush();

      $outputfile = $policydir + ("{0}.xml" -f (Encode-SqlName $policy.Name));

      $StringWriter.ToString() | out-file $outputfile;

}

if ($policycount -gt 0)

{

      Write-Host $policycount " политик из "$sourcepolicycount " экспортировано в " $policydir -foregroundcolor "green"

}

else

{

      write-host "Политики не экспортированы" -foregroundcolor "red"

}

 

# Импорт политик на несколько серверов

$policylocation = "C:\Policies"

$serversfile = "C:\Servers.txt"

$servercount = 0

$servers = Get-Content $serversfile

foreach ($server in $servers) {

      $servercount++;

      $conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server='$server';Trusted_Connection=true");

      $polstore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);

      foreach ($fileobject in get-childitem $policylocation){

            $file = $fileobject.FullName

            $reader = [System.Xml.XmlReader]::Create((convert-path $file));

            $output = $polstore.ImportPolicy($reader, 0, $true, $true);

      }

}

if ($servercount -gt 0)

{

      Write-Host "Политики импортированы на" $servercount "серверов." -foregroundcolor "green"

}

else

{

      write-host "Политики не импортированы" -foregroundcolor "red"

}

 

Перенос политик проверки работоспособности в таблицу базы данных

Ниже приведен набор сценариев PowerShell и SQL для извлечения и загрузки данных о политиках с нескольких серверов и помещения их в централизованное местоположение для просмотра.

 

Примечание. При переносе политик проверки работоспособности выполняется прямая передача таблицы базы данных с данными о политиках из базы данных msdb в центральную базу данных. Возможно, лучший способ сделать это — образец PBMTalk Дэна Джонса (Dan Jones), который также включен в этот пакет SDK. Дополнительные сведения см. в папке PMTalk, где содержится презентация PowerPoint, схема политик и код PBM для экспорта данных управления на основе политик.

 

Загрузите решение

Распакуйте файл Policy.zip в папку по своему выбору (например, D:\audit\code\Policy)

 

Обновите базу данных SQLAudit

Эта база данных будет использоваться в качестве центрального сервера. Чтобы добавить данные о политиках, установите PolicyLoad.sql на центральном сервере, где нужно просматривать все отчеты, например на том сервере, где находится база данных аудита SQLAudit. Таким образом, центральная база данных SQLAudit будет содержать данные всех политик и данные аудита.

 

В центральной базе данных SQLAudit заполните таблицу pol.ServerList данными со всех серверов, по которым нужно получать информацию. Пример приведен ниже.

insert into pol.ServerList values ('campschurmann', 1)

insert into pol.ServerList values ('emmonsroute', 1)

insert into pol.ServerList values ('emmonsglacier', 0)

 

где 1 — включено, а 0 — отключено.

 

Выполните сценарий PowerShell

Как отмечалось в разделе Использование управления на основе политик для определения, развертывания и проверки политик, этот сценарий считывает данные из таблицы pol.ServerList, проходит по всем серверам, перечисленным в ней, извлекает данные политик и загружает их на центральный сервер. Скопируйте сценарий PolicyExLoad.ps1 на центральный сервер, например в каталог D:\audit\code\Policy 

 

Убедитесь, что эта папка также содержит архив (например, D:\audit\code\Policy\archive)

 

Этот сценарий PowerShell требует наличия оснасток SQL Server, чтобы иметь возможность запуска sqlps перед открытием обычного окна командной строки (а не окна командной строки PowerShell).

 

В командной строке можно выполнить следующую команду.

.\PolicyExLoad.ps1 "[SQLCentral]" "[Database]" ”[Date]”  “[Folder]”

 

SQLCentral: экземпляр сервера, содержащий центральную базу данных (т. е. SQLAudit).

 

Database: в большинстве случаев это база данных SQLAudit.

 

Date: последняя дата (т. е. один день), в который была выполнена политика. Если необходимы все данные о политике, укажите пустые кавычки "", чтобы получить все данные.

 

Folder: центральный репозиторий файлов журналов политик. Не забывайте, что в этой папке также должна быть папка для архива.

 

Здесь сценарий подключается к каждому из серверов, извлекает данные политик и записывает в общую папку CSV-файл. Обратите внимание, что существует два типа данных о политиках.

Ø  Таблицы измерений политик: содержат основные конструкции политик (имя, условие, категория и т. д.). Если политика изменяется или удаляется, то эта информация обновится в соответствующих таблицах msdb на удаленных серверах. В случае приведенного ниже сценария будет получено имя, условие и категория политики в следующих таблицах.

o   syspolicy_policies

o   syspolicy_conditions

o   syspolicy_policy_categories

 

Ø  Таблицы фактов политик: поскольку эти таблицы содержат историю выполнения и подробные сведения о состоянии работоспособности, эти данные не обновляются (только вставляются новые события). Приведенный ниже сценарий получает историю выполнения, подробные сведения об истории выполнения и состояние работоспособности для следующих таблиц.

o   syspolicy_policy_execution_history

o   syspolicy_policy_execution_history_details

o   syspolicy_system_health_state

 

Затем сценарий извлечения создает CSV-файлы для каждой из таблиц, используя следующее соглашение об именах.

            ИмяСервера_[таблицаПолитики]_ггггММдд_ччммсс.csv

 

Затем сценарий PowerShell вызывает хранимую процедуру [pol].[uspImportPolicyData], которая выполняет некоторую дополнительную бизнес-логику для импорта каждого из CSV-файлов в соответствующую таблицу.

·        Если это файл для таблицы измерения политик, то данные будут сначала помещены в промежуточную таблицу. Затем все данные, связанные с экземплярами серверов в этой промежуточной таблице, будут удалены из таблицы политик. Смысл этого заключается в том, что нам необходимы актуальные данные о политиках (кем созданы, какие аспекты и условия используют, обновленный текст и т. п.). Затем промежуточная таблица используется для заполнения таблицы политик.

 

·        Если это файл для таблицы фактов политик, то этот файл просто загружается прямо в соответствующую таблицу фактов. Нас не волнуют повторы, поскольку таблицы фактов политик содержат уникальные индексы, которые обеспечивают отсутствие дубликатов в системе. Возможно даже многократное выполнение множества файлов фактов политик. База данных позаботится о том, чтобы отсеять все дубликаты.

 

·        После импорта этого файла он будет перемещен в каталог Folder\archive, чтобы исключить его повторную обработку.

 

 

Просмотр некоторых отчетов о политиках

Установите сценарий PolicyReports.sql на центральный сервер и базу данных (куда был установлен файл PolicyLoad.sql). 

Как отмечалось в разделе Использование управления на основе политик для определения, развертывания и проверки политик, по журналам аудита можно сформировать несколько отчетов. Для просмотра этих отчетов необходимо выполнить развертывание решения [Policy Reports - PBM] служб Reporting Services.

 

Ø  Откройте решение [Policy Reports PBM] служб Reporting Services в среде Visual Studio.

 

Ø  Укажите в качестве TargetServerURL свой сервер, т. е. замените http://campschurmann/ на имя своего сервера.

 

    Рис. 48. Страница свойств решения [Policy Reports — PBM] служб RS

Ø  Убедитесь в том, что общий источник данных SQLAudit.rds указывает на вашу базу данных SQLAudit (а не на версию базы данных SQLAudit на другом сервере).

 

Ø  Выполните развертывание отчетов (Построить > Развернуть). После этого можно просматривать отчеты по адресу: http://[имя сервера] /Reports/Pages/Folder.aspx?ItemPath=%2fSQL+Auditing+Reports&ViewMode=List

Заключение

Разработчики Майкрософт и SQL Server прилагают все усилия, чтобы сделать доступной технологию, которая поможет заказчикам и партнерам в решении их задач по обеспечению соответствия требованиям. SQL Server 2008 реализует технологии и возможности, которые позволяют организациям справляться со все возрастающими трудностями в управлении данными и поставке заказчикам полезных решений. Демонстрируя значительные достижения в ключевых областях, SQL Server 2008 сегодня предлагает платформу для быстрой разработки решений по обеспечению соответствия требованиям.

В этом техническом документе содержатся только общие сведения о компонентах и функциях, связанных с обеспечением соответствия требованиям, реализованных в SQL Server 2008. В следующих версиях эти возможности планируется значительно расширить. Дополнительные сведения об обеспечении соответствия требованиям см. по адресу http://www.microsoft.com/sql.

Дополнительные сведения см. в разделах:

http://www.microsoft.com/sqlserver/: веб-узел SQL Server

http://technet.microsoft.com/ru-ru/sqlserver/: технический центр SQL Server

http://msdn.microsoft.com/ru-ru/sqlserver/: центр разработки SQL Server

http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-security.aspx: технический документ по безопасности SQL Server

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1626&SiteID=1: форум по безопасности SQL Server

http://blogs.msdn.com/sqlsecurity/: блог по безопасности SQL Server

http://blogs.msdn.com/sqlpbm/: блог по управлению на основе политик SQL Server