Modulsignierung (Datenbankmodul)

Das folgende Transact-SQL-Skript ist ein Beispiel für das oben aufgeführte Szenario. Aus einem Schlüsselpaar wird ein Zertifikat erstellt und einem neuen Anmeldenamen zugeordnet. Zuerst muss mit dem im .NET Framework SDK enthaltenen MakeCert-Tool ein Testschlüsselpaar erstellt werden. Anschließend werden dem mit dem Zertifikat verbundenen Anmeldenamen SELECT-Berechtigungen für die sys.sysproceses-Sicht erteilt. In der neuen Datenbank wird die verwaltete gespeicherte Prozedur usp_sysprocesses erstellt und mit dem Zertifikat signiert. Die SysProcRole-Rolle wird erstellt, und der Rolle wird die Ausführungsberechtigung für die gespeicherte Prozedur usp_sysprocesses erteilt. Ein Testbenutzer wird erstellt und der SysProcRole-Rolle hinzugefügt. Der Testbenutzer führt für sys.sysprocess eine SELECT-Anweisung aus und führt dann, um den Vergleich vornehmen zu können, die gespeicherte Prozedur usp_sysprocesses aus. Das Skript bereinigt danach die Testumgebung.

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

Nachstehend wird der Quellcode für die gespeicherte Prozedur usp_sysprocesses aufgeführt, mit dem eine SELECT *-Anweisung für die sys.sysprocesses-Sicht ausgeführt wird. Die Assembly muss signiert werden, wenn sie erstellt wird.

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