Подписание модулей (компонент Database Engine)

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

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

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

Microsoft SQL Server включает в себя множество функций, помогающих реализовать эти сценарии, например цепочки владения и инструкцию EXECUTE AS. Начиная с SQL Server 2005, в SQL Server появилась также возможность подписывать модули в базе данных. Подписание модулей предоставляет сходные возможности, но не изменяет контекст выполнения. Модуль в этом контексте ссылается на хранимую процедуру, функцию, триггер или сборку. Дополнительные сведения см. в разделах CREATE ROLE (Transact-SQL) и Использование инструкции EXECUTE AS для создания пользовательских наборов разрешений.

Подписи модулей

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

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

ПредупреждениеВнимание!

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

Сценарий

Предположим, доступ к представлению sys.sysprocesses должен осуществляться при помощи хранимой процедуры usp_sysprocesses. Пользователи могут обращаться к данным представления sys.sysprocesses только через процедуру usp_sysprocesses. Так как объекты usp_sysprocesses и sys.sysprocesses имеют разных владельцев, цепочки владения неприменимы.

Вначале на сервере из пары ключей должен быть создан сертификат при помощи инструкции CREATE CERTIFICATE. Далее сертификату предоставляются разрешения для выбора из таблицы sys.sysprocesses. Но, поскольку сервер SQL Server предоставляет разрешения только участникам, необходимо вначале при помощи инструкции CREATE LOGIN из сертификата создать имя входа. Этому имени входа не нужны на сервере разрешения на подключение, так как оно является только местозаполнителем разрешений и не предназначено для подключения к экземпляру сервера. Этому имени входа, связанному с сертификатом, затем можно предоставить разрешение SELECT для таблицы sys.sysprocesses при помощи инструкции GRANT VIEW SERVER STATE TO. После создания хранимой процедуры usp_sysprocesses эта хранимая процедура может быть подписана при помощи сертификата (в действительности — закрытым ключом, соответствующим этому сертификату) в инструкции ADD SIGNATURE. Создается новая роль, которой предоставляются разрешения на выполнение хранимой процедуры usp_sysprocesses. В результате пользователи, являющиеся членами этой роли, получают разрешение выполнять хранимую процедуру usp_sysprocesses и, следовательно, разрешение SELECT на выбор из представления sys.sysprocess. При выполнении подписанного модуля те разрешения, которые предоставлены участнику (при помощи инструкции GRANT) и связаны с сертификатом подписания, оператором UNION временно встраиваются в маркер безопасности на время выполнения вызова. После возврата управления выполнения эти разрешения удаляются из маркера безопасности. Таким образом, дополнительный набор разрешений существует только во время выполнения модуля. Любой другой пользователь или другая роль, которым предоставлены разрешения EXECUTE на эту процедуру, будут иметь такие же возможности.

Пример

Следующий сценарий на языке Transact-SQL является примером вышеуказанного сценария. Из пары ключей создается сертификат, который сопоставляется с новым именем входа. Вначале с помощью средства MakeCert, поставляемого с пакетом SDK платформы .NET Framework, необходимо создать тестовую пару ключей. Затем имени входа, связанному с сертификатом, предоставляются разрешения на выбор из представления sys.sysproceses. В новой базе данных создается управляемая хранимая процедура usp_sysprocesses, которая подписывается при помощи сертификата. Создается роль SysProcRole, и этой роли предоставляются разрешения на выполнение хранимой процедуры usp_sysprocesses. Создается и добавляется к роли SysProcRole тестовая учетная запись. Тестовая учетная запись выполняет инструкцию SELECT на представлении sys.sysprocess, затем для сравнения выполняет хранимую процедуру usp_sysprocesses. Затем сценарий очищает тестовую среду.

use master
go

-- Create a test database.
CREATE DATABASE db_Demo
go

-- Create a certificate on the server. A test key pair can be created
-- using the MakeCert tool that ships with the .NET Framework SDK.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
go

-- Create a login and map it to the certificate.
CREATE LOGIN login_SysProcCert FROM CERTIFICATE SysProcCert
Go

-- Revoke the connect permission.
REVOKE CONNECT SQL FROM login_SysProcCert ;
go 
 
-- Grant the certificate, through the login, permission to select from sys.sysprocesses view.
GRANT VIEW SERVER STATE TO login_SysProcCert
go

-- Create a test login.
CREATE LOGIN bob WITH PASSWORD = '<enterStrongPasswordHere>'
go

-- Connect to the test database.
use db_Demo
go

-- Create the master key for the test database (used to protect 
-- private keys and certificates in the database).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>' 

-- Create a certificate from a private key.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
WITH PRIVATE KEY
(FILE = 'e:\programming\testCert.pvk', 
 DECRYPTION BY PASSWORD= '<enterStrongPasswordHere>', 
 ENCRYPTION BY PASSWORD='<enterStrongPasswordHere>')
go 

-- Create the assembly on the server. The assembly DLL must be signed.
CREATE ASSEMBLY SysStoredProcedures
FROM 'E:\programming\SysStoredProcs.dll'
WITH PERMISSION_SET = SAFE
go 

-- Create the managed stored procedure on the server.
CREATE PROCEDURE usp_sysprocesses
AS EXTERNAL NAME SysStoredProcedures.StoredProcedures.usp_sysprocesses
go 

-- Add the signature to the stored procedure.
ADD SIGNATURE TO [dbo].[usp_sysprocesses] 
BY CERTIFICATE SysProcCert WITH PASSWORD = '<enterStrongPasswordHere>'
go 

-- Create a role.
CREATE ROLE SysProcRole
go

-- Create a test user
CREATE USER bob
go

-- Add the test user to the role.
EXEC sp_addrolemember 'SysProcRole', 'bob'
go

-- Grant execute permissions on the stored procedure to the new role.
GRANT EXECUTE ON [dbo].[usp_sysprocesses] TO SysProcRole
go
 
-- Connect as the test user.
EXECUTE AS LOGIN = 'bob'
use db_Demo
go
 
-- User only has permission to see their own processes.
SELECT * FROM sys.sysprocesses
go

-- Execute the stored procedure, which has been signed.
exec usp_sysprocesses
go

-- REVERT
REVERT
----------------------------------------------------------------------
-- Cleanup

use db_Demo
go

use master
go

DROP DATABASE db_Demo
go 

DROP login login_SysProcCert
DROP login bob
go

DROP CERTIFICATE SysProcCert
go

Далее представлен исходный код хранимой процедуры usp_sysprocesses, который выполняет инструкцию SELECT * на представлении sys.sysprocesses. Сборка должна подписываться в ходе ее построения.

C#

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_sysprocesses()
{
    using(SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT * FROM sys.sysprocesses", connection);
        SqlContext.Pipe.ExecuteAndSend(command);
    }
}
};

Visual Basic

Imports System
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub  usp_sysprocesses ()
    Using connection As New SqlConnection("context connection=true")
        connection.Open()

        Dim command As New SqlCommand("SELECT * FROM sys.sysprocesses", connection)
        SqlContext.Pipe.ExecuteAndSend(command)
    End Using
End Sub
End Class