Создание CRL Stored Procedure в SQL Server 2012

Создание проекта и процедуры

Создаем проект SQL Server DataBase Project

Add sql database project

Создаем хранимую процедуру CRL

Create clr stored procedure

Создаем класс и метод хранимой процедуры. На метод необходимо добавить атрибут [SqlProcedure]

[SqlProcedure]
public static int UpdateSphinxRun(int catalogId)
{
    WindowsImpersonationContext OriginalContext = null;
    try
    {
        WindowsIdentity CallerIdentity = SqlContext.WindowsIdentity;

    if (CallerIdentity != null)
    {
        OriginalContext = CallerIdentity.Impersonate();

        string ooutput = string.Empty;
        using (var p = new Process())
        {
            p.StartInfo.Verb = "runas";
            p.StartInfo.UseShellExecute = false;
            p.StartInfo.RedirectStandardOutput = true;
            p.StartInfo.FileName = @"С:\Index.bat";
            p.StartInfo.Arguments = catalogId.ToString();
            p.Start();
            ooutput = p.StandardOutput.ReadToEnd();
            p.WaitForExit();
        }

        OriginalContext.Undo();
        return 0;
    }

    return 2;


}
catch (Exception exception)
{
    // Log error
}

return 1;

}

Код хранимой процедуры не сложен. Стоит заметить, эта хранимая процедура вызывает cmd.exe и хочет выполнять bat файл. Для этого нужны определенные права (об этом дальше). Кроме того, bat файл запускает приложение, которое требует прав администратора. Это самое интересное. Для этого необходимо добавить информацию о контексте исполнения в коде процедуры WindowsIdentity CallerIdentity = SqlContext.WindowsIdentity;. Попробовать повысить себе права (если SQL User имеет на это права) и выполнить необходимые дейсвия. После этого обязательно нужно вернуть права OriginalContext.Undo();.

Безопасность CLR внутри SQL Server

Одним из основных преимуществ  .NET является Code Access Security (CAS). CAS обеспечивает безопасность на уровне кода, а не схему пользователь-роль. Как эта схема будет работать внутри SQL Server. Понятно, что SQL CLR не может сделать больше, чем эквивалетная схема на T-SQL, т.к. работает в том же контексте безопасности. Также, код CLR хранимой процедуры запускается в области памяти SQL Server и в некоторых случаях код, написанный на C# может вызывать Win32 API, а это совсем другие возможности.

Основные плюсы сборок CLR в использовании SQL Server

Microsoft уделила достаточно много внимания безопасности сервера и операционной системы при взаимодействии SQLCLR.

По умолчанию процедуры CRL отключены. Для влючения необходимо использовать sp_configure.

sp_configure ‘clr enabled’, 1
GO
RECONFIGURE
GO

Уровни доступа CRL Stored Procedure

Хранимая процедура может принимать одно из 3-х возможных значений уровня доступа.

SAFE

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

EXTERNAL_ACCESS

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

UNSAFE

UNSAFE - эквивалент полного доверия. Он получает единый, неограниченный SecurityPermission (на уровне CLR это полное доверие). Сборки UNSAFE могут вызывать COM компоненты и нативный Win32 API. Для создания сборок UNSAFE необходимы права администратора.

Trustworthy Databases

Еще одно отличие безопасного кода от кода с полным уровнем доступа: для создания сборки ENTERNAL_ACCESS или UNSAFE владелец базы данных (DataBaseOwner) должен иметь свойство Trustworthy, также сборка должна быть подписана сертификатом. 

Если необходимо включить такую настройку в базе данных, это делает следующий запрос:

ALTER DATABASE DATABASE_NAME
SET TRUSTWORTHY ON
GO

Настройка проекта в VisualStudio 2013

К счастью, много чего настраивается внутри VS. В свойствах проекта:

Project Setings > DataBase Settings… устанавливаем значение Trustworthy

SQL Server Trustworthy

 

Во второй вкладке настраиваем 3 пункта.

  1. Информацию о сборке (Assembly Information), здесь устанавливаем свое имя и версии. Так проще будет отлаживать сборки
  2. Устанавливаем необходимый уровень доступа. В моем случае это UNSAFE
  3. Создаем и подписываем сборку

Signing sql clr assembly

 

Публикация сборки SQL CLR 

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

publish

Если публикация успешна, VS сообщит об этом (в противном случае тоже).

publish step 2

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

Installed CLR SQL

Ссылки

Комментарии

comments powered by Disqus