Erteilen Sie entsprechende Berechtigungen zu verwenden Symmetrischen Schlüssel, der in stored proc

Ich erzeugt er einen symmetrischen Schlüssel in SQL2012 mit dem folgenden code (angemeldet als Windows Admin-Benutzer):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '34trg45trgf546t';
CREATE CERTIFICATE SSCert01 WITH SUBJECT = 'SS Certificate 01';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE SSCert01;

Sobald das erledigt war habe ich die Verschlüsselung auf bestimmte Datenbank-Spalten. Noch als Admin angemeldet, kann ich erfolgreich entschlüsseln Spalten mit der Taste:

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE SSCert01;
SELECT
    name,
    surname,
    CONVERT(nvarchar(50),DECRYPTBYKEY(PasswordEnc)) as DecryptedPassword
FROM
    [tbl_Users];
CLOSE SYMMETRIC KEY SSN_Key_01;

Dann stellte ich den oben genannten code in eine Gespeicherte Prozedur. Das problem ist, dass meine Anwendung den Zugriff auf SQL mit zwei Rollen, die den Zugriff auf die entsprechenden Prozeduren. Wenn diese beiden Rollen, die versucht ausführen, eine proc mit dem obigen code sehe ich diesen Fehler:

Nicht finden können das Zertifikat 'SSCert01", weil es
nicht vorhanden ist oder Sie nicht die Berechtigung haben. Der Schlüssel 'SSN_Key_01' ist
nicht öffnen. Bitte öffnen Sie den Schlüssel, bevor Sie es.

Wenn ich login entweder als Rolle, die Sie nicht sehen können die Taste oder die Cert. Also, kann jemand beraten, WELCHE Berechtigungen zu gewähren, um die Rollen, so dass Sie können verwenden Sie die key/cert innerhalb von gespeicherten Prozeduren (nur) zum verschlüsseln/entschlüsseln von Daten. Die Rollen sollten nicht erlaubt werden, führen Sie alle Funktionen mit dem key/cert abgesehen von der Verschlüsselung/Entschlüsselung. Ich habe mir bei MSDN/Google und bin nicht klüger.

UPDATE
Der folgende code ermöglicht der Rollen zu verwenden, der proc ist, aber ich bin besorgt, dass CONTROL ist zu viel Zugriff. Kann jemand bieten einige Klarheit bitte?

GRANT CONTROL ON CERTIFICATE :: SSCert01 TO Role001;
GRANT CONTROL ON SYMMETRIC KEY :: SSN_Key_01 TO Role001;
InformationsquelleAutor EvilDr | 2014-03-07
  1. 9

    Den Weg, ich bekomme in der Regel, um dieses ist, um das Verfahren zu führen als Besitzer und dann stellen Sie sicher, dass der Besitzer des Verfahrens hat die richtigen Berechtigungen für die Entschlüsselung, viel Zeit die Besitzer des proc DBO ist sowieso, so dass keine weitere Konfiguration gemacht werden muss, abgesehen von der änderung der Vorgehensweise etwa so:

    ALTER PROCEDURE proc_name
WITH EXECUTE AS OWNER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE SSCert01;
SELECT
    name,
    surname,
    CONVERT(nvarchar(50),DECRYPTBYKEY(PasswordEnc)) as DecryptedPassword
FROM
    [tbl_Users];
CLOSE SYMMETRIC KEY SSN_Key_01;

    Dies bedeutet, dass Sie nicht zu gewähren keine zusätzliche Berechtigungen auf alle zu Ihrer Anwendung, Rolle oder Benutzer.

    • Danke, ich war mir nicht bewusst EXECUTE AS. Nicht sicher, ob das mehr ein workaround, aber...?
    • nun, das ist, wie Sie es tun. Es scheint nicht, dass es einen besseren Weg gibt. Sie müssen die CONTROL-Berechtigung für das Zertifikat zum entschlüsseln der Schlüssel. Wenn Sie nicht wollen, zu gewähren, um die Rolle zu verwenden, eine gespeicherte Prozedur wie oben in dieser Antwort
    • Okay Super. Tut GRANT CONTROL erlauben, eine Rolle/Benutzer DROP oder ALTER Zertifikat/Schlüssel?
    • Ja, die STEUERUNG ermöglicht es dem Benutzer, um die drop-Taste.
    • Akzeptiert als Antwort plus ausgezeichnet bounty. Dieser Ansatz ist viel sauberer als das erteilen von Berechtigungen für Benutzer/Rollen. Danke @steoleary für Ihre Eingabe.
    • Das ist großartig und scheint sicherer als das erteilen von Berechtigungen +1

    InformationsquelleAutor steoleary
Schreibe einen Kommentar