Accès SQL Server par procédure avec des droits limités

Une application demandant uniquement un récapitulatif de factures n'a pas besoin de modifier toutes les tables ou le schéma. Accorder db_owner peut faire disparaître des erreurs de déploiement tout en masquant les véritables dépendances. Une surface de droits restreinte est plus simple à examiner et à conserver dans le temps.

Les procédures stockées peuvent former cette interface lorsque leur contenu et leurs propriétaires sont maîtrisés. La question utile dépasse la connexion: quelles lectures et écritures l'application peut-elle effectuer, par quels points d'entrée autorisés?

Autoriser l'opération par un rôle

L'exemple crée une table de pratique avec des adresses et une procédure retournant seulement nombre et total. Utilisez une base jetable. GO est un séparateur de batches côté client; un pilote qui ne le reconnaît pas doit envoyer les parties séparément.

-- Run in a disposable practice database as its administrator.
CREATE TABLE dbo.PermissionInvoiceDemo (
    InvoiceId int PRIMARY KEY,
    CustomerEmail nvarchar(200) NOT NULL,
    Total decimal(12,2) NOT NULL
);
INSERT dbo.PermissionInvoiceDemo VALUES
(1, N'example1@example.invalid', 40.00),
(2, N'example2@example.invalid', 60.00);
GO
CREATE PROCEDURE dbo.GetInvoiceSummaryDemo
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT_BIG(*) AS InvoiceCount, SUM(Total) AS InvoiceTotal
    FROM dbo.PermissionInvoiceDemo;
END;
GO
CREATE ROLE InvoiceSummaryReaderDemo AUTHORIZATION dbo;
CREATE USER InvoiceReportUserDemo WITHOUT LOGIN;
ALTER ROLE InvoiceSummaryReaderDemo ADD MEMBER InvoiceReportUserDemo;
GRANT EXECUTE ON OBJECT::dbo.GetInvoiceSummaryDemo
TO InvoiceSummaryReaderDemo;

WITHOUT LOGIN crée volontairement un utilisateur de test local, pas un compte de connexion applicatif. Au déploiement, créez ou associez l'utilisateur réel selon l'authentification existante puis ajoutez-le au rôle restreint.

Le rôle reçoit EXECUTE sur une procédure, sans SELECT général sur la table. Les deux objets ont le même propriétaire via dbo et le SQL de la procédure est statique. Une chaîne de propriété intacte permet de vérifier l'appel sans demander séparément les droits de table de l'appelant.

Le résultat attendu est deux factures totalisant 100,00. Les adresses ne sont pas exposées. Il s'agit d'une frontière d'accès aux objets, pas d'une garantie universelle de confidentialité: des agrégats sur de petits groupes peuvent aussi révéler des informations.

Comprendre les limites de la frontière

L'absence de droit direct ne rend pas toute procédure sûre. Accepter des noms d'objets arbitraires, exécuter du SQL dynamique non contrôlé ou retourner tous les locataires peut offrir des capacités excessives. Examinez le corps de la procédure avec l'autorisation.

Le SQL dynamique ne bénéficie pas de la même hypothèse de chaîne statique. sp_executesql paramétré traite les valeurs et limite l'injection, mais n'accorde pas automatiquement les permissions du texte généré. Donner db_owner à l'application ne constitue pas une bonne correction.

Pour un accès dynamique ou traversant une frontière, envisagez une signature de module précisément limitée ou un contexte d'exécution choisi explicitement. Une signature par certificat peut ajouter des droits ciblés pendant l'exécution. Le déploiement doit toutefois re-signer après modification du module. Gestion du certificat et capacités accordées doivent rester examinables.

N'activez pas TRUSTWORTHY ni des chaînes interbases générales pour résoudre systématiquement une erreur. Ces réglages changent une frontière de confiance bien plus large qu'un appel. Concevez l'accès interbases spécifique.

Tester sous l'identité limitée

Un test réussi sous sysadmin ne prouve presque rien sur les droits réels de l'application. Cette usurpation contrôlée exécute le résumé puis restaure le contexte initial même en cas d'échec.

EXECUTE AS USER = N'InvoiceReportUserDemo';
BEGIN TRY
    EXEC dbo.GetInvoiceSummaryDemo;
END TRY
BEGIN CATCH
    REVERT;
    THROW;
END CATCH;
REVERT;

Vérifiez également qu'une lecture directe de CustomerEmail est refusée. Testez les opérations permises et interdites. Les GRANT du catalogue ne suffisent pas: rôles, droits directs, DENY, propriété et privilèges supérieurs influencent l'accès effectif.

Séparez identité de déploiement et identité d'exécution. Le déploiement peut avoir besoin de créer des procédures; les requêtes normales ne doivent pas hériter de cette capacité. Lorsqu'une version ajoute une opération, accordez-la explicitement au lieu d'ouvrir tous les objets présents et futurs.

Examinez aussi EXECUTE au niveau du schéma. C'est utile pour un schéma d'API volontairement gouverné, mais cela couvre automatiquement les futures procédures. C'est un choix de gestion, pas seulement une syntaxe plus courte.

Conservez une vérification de droits dans les contrôles de version utiles: opérations prévues autorisées, colonnes protégées inaccessibles directement et modification d'objets interdite. Une extension accidentelle des privilèges devient ainsi visible avant de s'installer durablement.

Références techniques: Microsoft Learn: Database Engine permissions · Microsoft Learn: GRANT object permissions · Microsoft Learn: Sign a procedure with a certificate.