Impossible de créer des procédures stockées avec EXECUTE AS en utilisant un principal basé sur un certificat.

Question

J'essaie de recréer un certificat expiré qui était utilisé sur nos serveurs pour créer des principes, puis ces principes étaient utilisés avec l'usurpation d'identité pour exécuter des procédures stockées.

Voici ce que je fais (bien sûr, c'est beaucoup plus complexe en production, mais ce test exact échoue également).

Use ReportingDb
GO

CREATE CERTIFICATE MyCertTest ENCRYPTION BY PASSWORD = 'acrazygoodpassword'
   WITH SUBJECT = 'Stored procedure signing for Reports'
      ,EXPIRY_DATE = '11/18/2019';
GO

BACKUP CERTIFICATE MyCertTest TO FILE = 'D:\MyCertTest.CER';
GO

CREATE USER TestReportUser
FROM CERTIFICATE MyCertTest;
GO

EXEC sp_addrolemember 'db_datareader','TestReportUser';

GRANT AUTHENTICATE
   TO TestReportUser;
GO

GRANT EXECUTE
   TO TestReportUser;
GO

USE Master;
GO

CREATE CERTIFICATE MyCertTest
FROM FILE = 'D:\MyCertTest.CER';
GO
CREATE USER TestReportUser
FROM CERTIFICATE MyCertTest;
GO
EXEC sp_addrolemember 'db_datareader','TestReportUser';

GRANT AUTHENTICATE
   TO TestReportUser;

GRANT EXECUTE
   TO TestReportUser;
GO

use ReportingDb
GO

CREATE PROCEDURE dbo.Reports_DC_Project_sp
WITH EXECUTE AS 'TestReportUser'
AS
SELECT 1
GO

Je ne sais pas si j'ai vraiment besoin de la pièce maîtresse. Tout se passe bien jusqu'à la création de la procédure stockée :

Msg 15517, Level 16, State 1, Procedure Reports_DC_Project_sp, Line 47
Impossible d'exécuter en tant que principal de la base de données car le principal "TestReportUser" n'existe pas, ce type de principal ne peut pas être personnalisé ou vous n'avez pas l'autorisation.

J'ai également essayé de créer la procédure stockée avec EXECUTE AS 'dbo'. Cela fonctionne bien... puis j'ai ajouté la signature à la procédure stockée et enfin j'ai modifié la procédure stockée pour qu'elle soit exécutée en tant qu'utilisateur de mon certificat. Même erreur à la dernière étape.

Y a-t-il un paramètre/une étape que j'ai manqué(e) ?

Answer 1

Utiliser [nom de la base de données]

GO

EXEC sp_changedbowner 'sa' (en anglais)

GO

Answer 2

J'essaie de recréer un certificat expiré qui était utilisé sur nos serveurs pour créer des principes...

Hum, non. Les dates d'expiration des certificats ne sont pas vérifiées / validées lors de la création des principaux à partir de ceux-ci, ni même lors de leur utilisation avec AJOUTER LA SIGNATURE pour signer un module.

et ensuite ces principes ont été utilisés avec l'usurpation d'identité pour exécuter des procédures stockées.

Définitivement non. Cela ne s'est pas produit parce que c'est techniquement impossible ; les principaux basés sur les certificats et les clés assymétriques ne peuvent pas être utilisés pour l'usurpation d'identité.

Je ne sais pas si j'ai vraiment besoin de la pièce maîtresse.

Non, ce n'est pas le cas. Il ne fait rien dans cet usage. Si vous vouliez associer des permissions au niveau du serveur (c'est-à-dire de l'instance) (même l'appartenance à la catégorie sysadmin ) au certificat et à tous les modules signés par celui-ci, puis vous restaurez le certificat dans le rôle de master comme vous l'avez montré, mais vous devez ensuite créer un Login à partir de ce certificat et lui accorder les permissions appropriées. Créer un utilisateur dans master ne fait rien, à moins qu'il n'y ait quelque chose de spécifique dans l'option master Base de données avec laquelle un utilisateur doit interagir. Mais en ce qui concerne votre test, cela n'est absolument pas pertinent.

En outre, l'octroi AUTHENTICATE à celui basé sur un certificat n'est absolument pas pertinent et inutile puisque les principaux basés sur un certificat et une clé asymétrique ne peuvent pas s'authentifier. Pas du tout. Jamais.

puis en ajoutant la signature à la procédure stockée et enfin en modifiant la procédure stockée pour qu'elle soit exécutée en tant qu'utilisateur de mon certificat.

Même s'il était possible d'EXECUTER EN TANT QUE principal basé sur un certificat ou une clé asymétrique, il serait inutile d'ajouter la signature antes de en modifiant la procédure stockée avec le EXECUTE AS puisque cette clause ALTER invaliderait automatiquement la signature. Les signatures sont automatiquement abandonnées lorsque soit la définition du module, soit l'instruction EXECUTE La clause AS a changé. Vous devrez resigner le module après la modification de la clause AS. ALTER il est donc inutile d'ajouter la signature au préalable.

Y a-t-il un paramètre/une étape que j'ai manqué(e) ?

Vous ne manquez pas une étape car il n'y a aucun moyen de faire fonctionner ce système. Cependant, vous comprenez mal les concepts ici. L'usurpation d'identité (via EXECUTE AS ) et la signature des modules (via ADD SIGNATURE ) s'excluent mutuellement. En fait, la signature de module remplace ou rend obsolète l'usurpation d'identité.

Les principaux (logins et utilisateurs) basés sur des certificats et des clés asymétriques ne peuvent pas être usurpés d'identité ou authentifiés. Vous obtenez donc l'erreur suivante (c'est nous qui soulignons) :

Impossible d'exécuter en tant que principal de la base de données car le principal "TestReportUser" n'existe pas, ce type de principal ne peut être usurpé d'identité ou vous n'avez pas la permission.

C'est en partie ce qui fait de la signature de module un cadre plus sûr que l'usurpation d'identité.

Débarrassez-vous donc de ce qui suit :

1. GRANT AUTHENTICATE
2. toutes les déclarations exécutées dans master
3. le site EXECUTE AS clause

et vous irez bien.