SQL Server-Berechtigungen für Gespeicherte Prozeduren mit dynamic SQL

Ich habe eine Datenbank mit einer Anwendung Rolle. Die Rolle, die Mitglieder gehören alle zu einer Gruppe in Active Directory. Anstatt die Rolle Berechtigungen wählen Sie aus den Tabellen habe ich wegen der Rolle, die Ausführungsberechtigung für alle gespeicherten Prozeduren, die es braucht, um zu rufen.

Dies funktioniert gut, außer für einen meiner gespeicherten Prozeduren, die Gebäude bis einige dynamische SQL-und Aufruf von sp_executesql.

Den dynamischen sql-sieht irgendwie wie diese:

SET @SQL = N'
SELECT * 
FROM dbo.uvView1 
INNER JOIN uvView2 ON uvView1.Id = uvView2.Id'

EXEC sp_executesql @SQL

Den Benutzer in dieser Rolle Versagen, die zum aufrufen der gespeicherten Prozedur. Es gibt den folgenden Fehler, das ist so in der Art erwartet, nehme ich an:

Die SELECT-Berechtigung verweigert wurde, auf das Objekt 'uvView1', Datenbank 'Foobar', schema 'dbo'.

Gibt es eine Möglichkeit, ich kann meine Benutzer erfolgreich ausführen dieser proc, ohne dass die Rolle, die Berechtigungen für alle Ansichten in der dynamischen SQL?

Der Benutzer ist auf die Sicht, die richtige? Ich glaube nicht, dass die Sicherheit kümmert sich, wie Sie darauf zugreifen.
Dies ist die einzige proc, der wird scheitern und es ist nicht zu Versagen, die mit execute-Berechtigungen auf dem proc...es ist speziell, sagte mir, dass Sie können WÄHLEN Sie aus der Sicht...also ich vermute, es ist nicht egal irgendwie.
Warum das passiert, ich fand den Dynamische SQL-und besitzverkettung in SQL Server @ mssqltips.com nützlich: "Wenn entweder der sp_executesql oder die EXECUTE-Anweisung führt einen string, der string wird ausgeführt, als seine eigene Selbstversorger-batch. ... Berechtigungen sind erforderlich, auf die sicherungsfähigen Elemente, auf die verwiesen wird in der EXECUTE-Zeichenfolge"

InformationsquelleAutor Dismissile | 2010-11-02

  1. 10

    Ja.

    Hinzufügen einer EXECUTE AS CALLER-Klausel der Prozedur, dann melden Sie die gespeicherte Prozedur, und geben Sie die erforderliche Berechtigung, um die Signatur. Das ist 100% sicher und bullet proof. Sehen Unterzeichnung Verfahren mit Zertifikaten.

    Wie wäre eine Lösung mit einem Datenbank-trigger zur überwachung der Sql-Benutzer-und verwerfen es, wenn die SQL-starten Sie mit select?
    Wie der verlinkte Artikel sagt, WITH EXECUTE AS CALLER ist die Standardeinstellung. Und die Signierung, während unangenehm, ist aber nicht allzu kompliziert, erfordert nur ein paar SQL-Befehle (CREATE CERTIFICATE+CREATE USER cert$user FROM CERTIFICATE .., gefolgt von ADD SIGNATURE TO @spname BY CERTIFICATE .. und GRANT ... TO cert$user)

    InformationsquelleAutor Remus Rusanu

  2. 2

    Können Sie Identitätswechsel auf eine andere ID mit den erforderlichen Berechtigungen?

    SET @SQL = N'
EXECUTE AS USER = ''TrustedUser'';
SELECT * 
FROM dbo.uvView1 
INNER JOIN uvView2 ON uvView1.Id = uvView2.Id'

EXEC sp_executesql @SQL
    Gute Anregung, ich werde sehen, ob ich dies tun kann.
    -1: benötigt die IMPERSONATE-Berechtigung für TrustedUser was im Effekt bedeutet, dass der Benutzer hat alle Berechtigungen des trusted user, da kann es imitieren ihn.

    InformationsquelleAutor Joe Stefanelli

  3. 0

    Nicht. Gibt es eine Möglichkeit, ändern kann man es nicht verwenden dynamisches SQL?

    NÖ...nicht dass ich wüsste. Die Abfrage ist eigentlich schwenkbare einige Daten und die Spalten, die er schwenkt auf, sind dynamisch. Mein Beispiel zeigen nicht wirklich die Komplexität der Abfrage.
    Sie vielleicht in Erwägung ziehen, alle diese Ansichten in einem schema und geben Sie datareader-Zugriff auf das schema dann.

    InformationsquelleAutor Vidar Nordnes

Schreibe einen Kommentar