Signature de modules

  • Article

Nouveau : 5 décembre 2005

Les applications de base de données exigent souvent que l'accès aux tables et objets sous-jacents dans le schéma d'application soit relayé par des procédures ou des vues de base. L'objectif est de permettre aux utilisateurs finals d'accéder aux objets de base qui, par la suite, accèdent aux objets sous-jacents pour le compte de l'utilisateur. Les utilisateurs finals n'ont ainsi pas besoin de bénéficier d'un accès à tous les objets au sein du schéma d'application. Cette approche sert à deux fins :

  • Elle simplifie la gestion des autorisations puisque cette gestion n'a besoin de porter que sur un sous-ensemble minime d'objets, par rapport à tous les objets du schéma d'application.
  • Il est possible de cacher la disposition du schéma sous-jacent à l'utilisateur final puisque seuls les points d'entrée sont exposés.

Microsoft SQL Server est doté de plusieurs fonctionnalités aidant à la réalisation de ces scénarios, notamment la fonction de chaînage des propriétés et l'emploi de l'instruction EXECUTE AS. SQL Server 2005 introduit également une nouvelle fonctionnalité permettant de signer des modules au sein de la base de données. La signature de modules offre des fonctions similaires mais ne modifie pas le contexte d'exécution. Un module dans ce contexte se rapporte à une procédure stockée, une fonction, un déclencheur ou un assembly. Pour plus d'informations, consultez Chaînes de propriétés, Utilisation d'EXECUTE AS pour créer des jeux d'autorisations personnalisés et le didacticiel sur la sécurité, Signature de procédures stockées à l'aide d'un certificat.

Signatures de modules

SQL Server 2005 présente la possibilité de signer des modules au sein de la base de données, notamment des procédures stockées, des fonctions, des déclencheurs ou des assemblys. Notez que les déclencheurs DDL (Data Definition Language) ne peuvent pas être signés. Une signature numérique est un condensé de données chiffrées avec la clé privée du signataire. La clé privée garantit que la signature numérique est la propriété unique de son détenteur ou propriétaire.

Pour signer des données, le signataire crée un condensé des données, les chiffre à l'aide d'une clé privée et joint la valeur du condensé chiffrée aux données. Pour vérifier la signature, le vérificateur utilise la clé publique du signataire pour déchiffrer la valeur du condensé chiffrée jointe aux données. Il compare ensuite cette valeur déchiffrée à la valeur calculée dans les données associées. Il est primordial que le signataire et le vérificateur utilisent la même fonction de hachage pour condenser les données.

ms345102.Caution(fr-fr,SQL.90).gifAttention :
La signature des modules ne doit avoir lieu que pour l'octroi d'autorisations, jamais pour leur refus ou leur révocation.

Scénario

Imaginons que l'accès à la vue sys.sysprocesses doit être relayé par la procédure stockée usp_sysprocesses. Les utilisateurs peuvent accéder aux informations de sys.sysprocesses uniquement par le biais de la procédure usp_sysprocesses. Du fait que les objets usp_sysprocesses et sys.sysprocesses appartiennent à différents propriétaires, le chaînage des propriétés ne s'applique pas.

Avant toute chose, un certificat doit être créé sur le serveur à partir d'une paire de clés utilisant l'instruction CREATE CERTIFICATE. Les autorisations à sélectionner dans la table sys.sysprocesses sont ensuite accordées au certificat. Néanmoins, du fait que SQL Server accorde uniquement des autorisations aux serveurs principaux, il est nécessaire de créer d'abord une connexion à partir du certificat à l'aide de l'instruction CREATE LOGIN. Cette connexion n'a pas besoin d'autorisations de connexion sur le serveur puisqu'elle constitue uniquement un espace réservé pour les autorisations et n'a pas pour but de se connecter à l'instance de serveur. Cette connexion mappée sur un certificat peut alors se voir octroyer des autorisations SELECT dans la table sys.sysprocesses avec l'instruction GRANT VIEW SERVER STATE TO. Une fois créée, la procédure stockée usp_sysprocesses peut être signée avec le certificat (ou plus précisément avec la clé privée correspondant au certificat) à l'aide de l'instruction ADD SIGNATURE. Un nouveau rôle est créé et reçoit l'autorisation d'exécuter la procédure stockée usp_sysprocesses. Tous les utilisateurs membres de ce rôle bénéficient donc de l'autorisation d'exécuter la procédure stockée usp_sysprocesses, et par conséquent de l'autorisation SELECT à partir de la vue sys.sysprocess. Lorsque vous exécutez un module signé, les autorisations octroyées au serveur principal (avec l'instruction GRANT) associé au certificat de signature sont provisoirement jointes par union dans votre jeton de sécurité d'exécution pendant la durée de l'appel. Dès le retour du contrôle d'exécution, ces autorisations sont supprimées de votre jeton de sécurité. Ainsi, pendant la durée d'exécution du module (et pendant cette durée uniquement), vous disposez effectivement d'un ensemble supplémentaire d'autorisations. Tout autre utilisateur ou rôle à qui des autorisations EXECUTE sont attribuées pour cette procédure bénéficie également des mêmes fonctions.

Exemple

Le script Transact-SQL suivant présente un exemple du scénario évoqué ci-avant. Un certificat est créé à partir d'une paire de clés, puis mappé sur une nouvelle connexion. Une paire de clés de test doit d'abord être créée à l'aide de l'outil MakeCert inclus dans le Kit de développement .NET Framework SDK. Les autorisations de sélection de la vue sys.sysprocesses sont ensuite accordées à la connexion associée au certificat. La procédure stockée managée usp_sysprocesses est créée dans la nouvelle base de données et signée à l'aide du certificat. Le rôle SysProcRole est créé et reçoit l'autorisation d'exécuter la procédure stockée usp_sysprocesses. Un utilisateur test est créé et ajouté au rôle SysProcRole. L'utilisateur test exécute une instruction SELECT sur sys.sysprocess, puis sur la procédure stockée usp_sysprocesses à titre de comparaison. Le script nettoie ensuite l'environnement de test.

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 = '!axLe1432ciosILD98'
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 = 'Iullsku409So!22BZo' 

-- 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= '0sJiy3569L!ns!ko12AeDDf', 
 ENCRYPTION BY PASSWORD='0Jiw43!!no92j1joksi')
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 = '0Jiw43!!no92j1joksi'
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

L'exemple ci-dessous décrit le code source de la procédure stockée usp_sysprocesses chargé d'exécuter une instruction SELECT * dans la vue sys.sysprocesses. L'assembly doit être signé lorsqu'il est créé.

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

Voir aussi

Autres ressources

CREATE CERTIFICATE (Transact-SQL)
CREATE LOGIN (Transact-SQL)
GRANT (Transact-SQL)
CREATE MASTER KEY (Transact-SQL)
ADD SIGNATURE (Transact-SQL)
CREATE ROLE (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005