Erste Schritte mit Berechtigungen für Datenbank-Engine

Artikel
03/16/2024

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Berechtigungen im Datenbank-Engine werden auf Serverebene über Anmeldungen und Serverrollen und auf Datenbankebene über Datenbankbenutzer und Datenbankrollen verwaltet. Das Modell für SQL-Datenbank stellt innerhalb jeder Datenbank dasselbe System zur Verfügung, doch die Berechtigungen auf Serverebene sind nicht verfügbar. In diesem Artikel werden verschiedene grundlegende Sicherheitskonzepte untersucht. Anschließend wird eine typische Implementierung der Berechtigungen beschrieben.

Hinweis

Microsoft Entra ID war bisher unter Azure Active Directory (Azure AD) bekannt.

Sicherheitsprinzipale

Sicherheitsprinzipal ist der offizielle Name der Identitäten, die SQL Server nutzen und denen Berechtigungen für die Ausführung von Aktionen zugewiesen werden können. Dies sind in der Regel Personen oder Personengruppen, doch es kann sich auch um andere Entitäten handeln, die vorgeben, Personen zu sein. Die Sicherheitsprinzipale können mithilfe der aufgelisteten Transact-SQL oder mithilfe von SQL Server Management Studio erstellt und verwaltet werden.

Anmeldungen

Anmeldungen sind einzelne Benutzerkonten für die Anmeldung beim SQL Server Datenbank-Engine. SQL Server und SQL-Datenbank unterstützen Anmeldungen basierend auf Windows-Authentifizierung und Anmeldungen basierend auf der SQL Server-Authentifizierung. Informationen zu den beiden Anmeldungstypen finden Sie unter Auswählen eines Authentifizierungsmodus.

Feste Serverrollen

In SQL Server sind feste Serverrollen eine Gruppe vorkonfigurierter Rollen, die das Erteilen von Berechtigungen für eine Gruppe von Servern erleichtern. Anmeldungen können Rollen mit der ALTER SERVER ROLE ... ADD MEMBER-Anweisung hinzugefügt werden. Weitere Informationen finden Sie unter ALTER SERVER ROLE (Transact-SQL). SQL-Datenbank unterstützt nicht die festen Serverrollen, bietet jedoch zwei Rollen in der master-Datenbank (dbmanager und loginmanager), die wie Serverrollen fungieren.

Benutzerdefinierte Serverrollen

In SQL Server können Sie eigene Serverrollen erstellen und ihnen Berechtigungen auf Serverebene zuweisen. Anmeldungen können Serverrollen mit der ALTER SERVER ROLE ... ADD MEMBER-Anweisung hinzugefügt werden. Weitere Informationen finden Sie unter ALTER SERVER ROLE (Transact-SQL). SQL-Datenbank unterstützt die benutzerdefinierten Serverrollen nicht.

Datenbankbenutzer

Anmeldungen wird Zugriff auf eine Datenbank gewährt, indem in einer Datenbank ein Datenbankbenutzer erstellt und dieser Datenbankbenutzer einer Anmeldung zugeordnet wird. Der Datenbank-Benutzername ist in der Regel identisch mit dem Anmeldenamen, obwohl dies nicht so sein muss. Jeder Datenbankbenutzer ist einer einzelnen Anmeldung zugeordnet. Eine Anmeldung kann nur einem Benutzer in einer Datenbank zugeordnet werden, kann aber als Datenbankbenutzer in mehreren unterschiedlichen Datenbanken zugeordnet werden.

Es können auch Datenbankbenutzer erstellt werden, die keine entsprechende Anmeldung haben. Diese Benutzer werden als eigenständige Datenbankbenutzerbezeichnet. Microsoft empfiehlt die Verwendung eigenständiger Datenbankbenutzer, da dadurch das Verschieben Ihrer Datenbank auf einen anderen Server erleichtert wird. Wie eine Anmeldung kann ein eigenständiger Datenbankbenutzer entweder die Windows- oder SQL Server-Authentifizierung verwenden. Weitere Informationen finden Sie unter Eigenständige Datenbankbenutzer – machen Sie Ihre Datenbank portabel.

Es gibt 12 Typen von Benutzern mit geringfügigen Unterschieden dahingehend, wie sie sich authentifizieren und wen sie darstellen. Eine Liste von Benutzern finden Sie unter CREATE USER (Transact-SQL).

Feste Datenbankrollen

Feste Datenbankrollen sind eine Gruppe vorkonfigurierter Rollen, die das Erteilen von Berechtigungen für eine Gruppe von Datenbanken erleichtern. Datenbankbenutzer und benutzerdefinierte Datenbankrollen können festen Datenbankrollen mithilfe der ALTER ROLE ... ADD MEMBER-Anweisung hinzugefügt werden. Weitere Informationen finden Sie unter ALTER ROLE (Transact-SQL).

Benutzerdefinierte Datenbankrollen

Benutzer mit der Berechtigung CREATE ROLE können neue benutzerdefinierte Datenbankrollen erstellen, um Gruppen von Benutzern mit gemeinsamen Berechtigungen abzubilden. In der Regel werden Berechtigungen der gesamten Rolle erteilt oder verweigert, was die Verwaltung und Überwachung von Berechtigungen vereinfacht. Mithilfe der ALTER ROLE ... ADD MEMBER-Anweisung können Datenbankbenutzer den Datenbankrollen hinzugefügt werden. Weitere Informationen finden Sie unter ALTER ROLE (Transact-SQL).

Andere Prinzipale

Weitere Sicherheitsprinzipale, die hier nicht behandelt werden, sind u. a. Anwendungsrollen sowie Anmeldungen und Benutzer auf Grundlage von Zertifikaten oder asymmetrischen Schlüsseln.

Eine Grafik mit den Beziehungen zwischen Windows-Benutzern, Windows-Gruppen, Anmeldungen und Datenbankbenutzern finden Sie unter Erstellen eines Datenbankbenutzers.

Typisches Szenario

Das folgende Beispiel zeigt eine allgemeine und empfohlene Methode zum Konfigurieren von Berechtigungen.

In Microsoft Active Directory oder Microsoft Entra ID.

Erstellen Sie einen Benutzer für jede Person.
Erstellen Sie Windows-Gruppen, die die Arbeitseinheiten und Arbeitsfunktionen darstellen.
Fügen Sie Windows-Benutzer den Windows-Gruppen hinzu.

Wenn die Person eine Verbindung mit mehreren Datenbanken herstellen möchte

Erstellen Sie für die Windows-Gruppen eine Anmeldung. (Wenn Sie die SQL Server-Authentifizierung verwenden, überspringen Sie die Schritte für Active Directory, und erstellen Sie hier SQL Server-Authentifizierungsanmeldungen.)
Erstellen Sie in der Benutzerdatenbank einen Datenbankbenutzer für die Anmeldung, die die Windows-Gruppen darstellt.
Erstellen Sie in der Benutzerdatenbank eine oder mehrere benutzerdefinierte Datenbankrollen, die jeweils eine ähnliche Funktion darstellen. Beispiele: Finanzanalyst und Vertriebsanalyst.
Fügen Sie die Datenbankbenutzer einer oder mehreren benutzerdefinierten Datenbankrollen hinzu.
Erteilen Sie den benutzerdefinierten Datenbankrollen Berechtigungen.

Wenn die Person eine Verbindung mit nur einer Datenbank herstellen möchte

Erstellen Sie in der Benutzerdatenbank für die Windows-Gruppen einen eigenständigen Datenbankbenutzer. (Wenn Sie die SQL Server-Authentifizierung verwenden, überspringen Sie die Schritte für Active Directory, und erstellen Sie hier die SQL Server-Authentifizierung für den eigenständigen Datenbankbenutzer.)
Erstellen Sie in der Benutzerdatenbank eine oder mehrere benutzerdefinierte Datenbankrollen, die jeweils eine ähnliche Funktion darstellen. Beispiele: Finanzanalyst und Vertriebsanalyst.
Fügen Sie die Datenbankbenutzer einer oder mehreren benutzerdefinierten Datenbankrollen hinzu.
Erteilen Sie den benutzerdefinierten Datenbankrollen Berechtigungen.

Das typische Ergebnis an diesem Punkt ist, dass ein Windows-Benutzer Mitglied einer Windows-Gruppe ist. Die Windows-Gruppe verfügt über eine Anmeldung in SQL Server oder SQL-Datenbank. Die Anmeldung ist einer Benutzeridentität in der Benutzerdatenbank zugeordnet. Der Benutzer ist Mitglied einer Datenbankrolle. Nun müssen Sie der Rolle Berechtigungen hinzufügen.

Berechtigungen zuweisen

Die meisten Berechtigungsanweisungen haben das folgende Format:

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

AUTHORIZATION muss GRANT, REVOKE , DENYoder sein.
PERMISSION legt fest, welche Aktion zulässig oder unzulässig ist. Die genaue Anzahl von Berechtigungen ist bei SQL Server und SQL-Datenbank unterschiedlich. Die Berechtigungen sind im Artikel Berechtigungen (Datenbank-Engine) und im nachstehenden Diagramm aufgeführt.
ON SECURABLE::NAME ist der Typ des sicherungsfähigen Objektes (Server, Serverobjekt, Datenbank oder Datenbankobjekt) und sein Name. Einige Berechtigungen erfordern ON SECURABLE::NAME nicht, da diese Angabe unmissverständlich oder im Kontext nicht zulässig ist. Die CREATE TABLE-Berechtigung erfordert z.B. nicht die ON SECURABLE::NAME-Klausel (GRANT CREATE TABLE TO Mary; ermöglicht Mary das Erstellen von Tabellen).
PRINCIPAL ist der Sicherheitsprinzipal (Anmeldung, Benutzer oder Rolle), der die Berechtigung empfängt oder verliert. Erteilen Sie Berechtigungen nach Möglichkeit stets Rollen.

Die folgende beispielhafte „Grant“-Anweisung erteilt die Berechtigung UPDATE für die Tabelle oder Sicht Parts, die im Schema Production für die Rolle mit dem Namen PartsTeamenthalten ist:

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

Die folgende beispielhafte „Grant“-Anweisung erteilt die Berechtigung UPDATE für das Schema Production und durch Erweiterung für jede Tabelle oder Sicht, die in diesem Schema für die Rolle ProductionTeam enthalten ist. Dies ist ein effektiverer und verkaufsfähigerer Ansatz zum Zuweisen von Berechtigungen als auf einzelner Objektebene:

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Berechtigungen werden Sicherheitsprinzipalen (Anmeldungen, Benutzer und Rollen) mithilfe der GRANT -Anweisung erteilt. Berechtigungen werden mithilfe des DENY -Befehls explizit verweigert. Eine zuvor erteilte oder verweigerte Berechtigung wird mithilfe der REVOKE -Anweisung entfernt. Berechtigungen sind kumulativ, was heißt, dass der Benutzer alle Berechtigungen erhält, die dem Benutzer, der Anmeldung oder den Gruppe erteilt wurden, denen der Benutzer angehört. Durch jedwede Berechtigungsverweigerung werden alle erteilten Berechtigungen außer Kraft gesetzt.

Tipp

Ein häufiger Fehler ist der Versuch, GRANT über DENY anstatt über REVOKEzu entfernen. Dies kann Probleme verursachen, wenn ein Benutzer Berechtigungen aus mehreren Quellen empfängt, was recht häufig vorkommt. Das folgende Beispiel veranschaulicht das Prinzip.

Die Gruppe Sales erhält SELECT -Berechtigungen für die Tabelle „OrderStatus“ über die Anweisung GRANT SELECT ON OBJECT::OrderStatus TO Sales;. Benutzer Jae ist Mitglied der Rolle Sales. Über die Anweisung SELECT wurde Jae auch die Berechtigung GRANT SELECT ON OBJECT::OrderStatus TO Jae;für die Tabelle OrderStatus unter ihrem/seinem eigenen Benutzernamen erteilt. Angenommen, der Administrator möchte die Berechtigung GRANT aus der Rolle Sales entfernen.

Wenn der Administrator REVOKE SELECT ON OBJECT::OrderStatus TO Sales;ordnungsgemäß ausführt, behält Jae über ihre/seine individuelle SELECT -Anweisung GRANT -Zugriff auf die Tabelle OrderStatus.
Wenn der Administrator DENY SELECT ON OBJECT::OrderStatus TO Sales; nicht ordnungsgemäß ausführt, wird Jae als Mitglied der Rolle Sales die SELECT -Berechtigung verweigert, da DENY für die Rolle sales ihre/seine individuelle GRANT-Einstellung überschreibt.

Hinweis

Berechtigungen können mithilfe von Management Studio konfiguriert werden. Suchen Sie das sicherungsfähige Objekt im Objekt-Explorer, klicken Sie mit der rechten Maustaste darauf, und wählen Sie Eigenschaften aus. Wählen Sie die Seite Berechtigungen aus. Hilfe zum Verwenden der Seite „Berechtigungen“, finden Sie unter Seite 'Berechtigungen' oder 'Sicherungsfähige Elemente'.

Berechtigungshierarchie

Berechtigungen einer Hierarchie aus über- und untergeordneten Elementen. Wenn Sie also die Berechtigung SELECT für eine Datenbank erteilen, enthält diese Berechtigung die Berechtigung SELECT für alle (untergeordneten) Schemas in der Datenbank. Wenn Sie die Berechtigung SELECT für ein Schema erteilen, enthält sie die Berechtigung SELECT für alle (untergeordneten) Tabellen und Sichten im Schema. Die Berechtigungen sind transitiv. Das heißt, wenn Sie die Berechtigung SELECT für eine Datenbank erteilen, schließt dies die Berechtigung SELECT für alle (untergeordneten) Schemas sowie alle (zwei Ebenen untergeordneten) Tabellen und Sichten ein.

Berechtigungen können auch abdeckende Berechtigungen aufweisen. Die Berechtigung CONTROL für ein Objekt erteilt Ihnen normalerweise alle anderen Berechtigungen für das Objekt.

Da sowohl die Hierarchie über- und untergeordneter Berechtigungen als auch die abdeckende Berechtigungshierarchie für dieselbe Berechtigung gelten können, kann das Berechtigungssystem kompliziert sein. Angenommen, es gibt eine Tabelle (Region) in einem Schema (Customers) in einer Datenbank (SalesDB).

CONTROL Die Berechtigung für die Tabelle „Region“ umfasst alle anderen Berechtigungen für die Tabelle „Region“, einschließlich ALTER, SELECT, INSERT, UPDATE, DELETEund einiger anderer Berechtigungen.
SELECT für das Schema „Customers“, das Besitzer der Tabelle „Region“ ist, schließt die Berechtigung SELECT für die Tabelle „Region“ ein.

Die Berechtigung SELECT für die Tabelle „Region“ kann also mithilfe einer dieser sechs Anweisungen erteilt werden:

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

Erteilen der geringstmöglichen Berechtigung

Die erste oben aufgeführte Berechtigung (GRANT SELECT ON OBJECT::Region TO Jae;) ist die präziseste, d.h., diese Anweisung ist die geringstmögliche Berechtigung, die die Berechtigung SELECTerteilt. Zu ihr gehören keine Berechtigungen für untergeordnete Objekte. Es ist ein gutes Prinzip, immer die geringstmögliche Berechtigung zu erteilen (hier können Sie mehr über das Prinzip der geringsten Rechte erfahren), andererseits aber gleichzeitig zu versuchen, eine Berechtigung auf höheren Ebenen zu erteilen, um das Gewährungssystem zu vereinfachen. Wenn also Jae Berechtigungen für das gesamte Schema braucht, erteilen Sie die Berechtigung SELECT einmal auf Schemaebene, anstatt SELECT mehrfach auf Tabellen- oder Sichtebene zu erteilen. Der Entwurf der Datenbank kann erheblich beeinflussen, wie erfolgreich diese Strategie sein kann. Diese Strategie funktioniert am besten, wenn Ihre Datenbank so konzipiert ist, dass Objekte, die identische Berechtigungen benötigen, in einem einzigen Schema enthalten sind.

Tipp

Planen Sie beim Entwerfen einer Datenbank und deren Objekten von Anfang an, wer oder welche Anwendungen auf welche Objekte zugreifen werden. Fügen Sie dann Objekte, insbesondere Tabellen, aber auch Sichten, Funktionen und gespeicherte Prozeduren aufgrund dieser Festlegung möglichst in Schemas entsprechend den Buckets des Zugriffstyps ein.

Diagramm der Berechtigungen

Das folgende Bild zeigt die Berechtigungen und ihre Beziehungen zueinander. Einige der Berechtigungen auf höherer Ebene (z.B. CONTROL SERVER) sind mehrmals aufgeführt. In diesem Artikel ist nicht ausreichend Platz, um das Poster entsprechend darzustellen. Sie können das Poster zu den Datenbank-Engine-Berechtigungen im PDF-Format in voller Größe herunterladen.

A screenshot from the Database Engine permissions PDF.

Eine Grafik mit den Beziehungen zwischen den Datenbank-Engine-Prinzipalen- und Serverdatenbank-Objekten finden Sie unter Berechtigungshierarchie (Datenbank-Engine).

Berechtigungen im Vergleich mit festen Server- und Datenbankrollen

Die Berechtigungen der festen Serverrollen und festen Datenbankrollen sind ähnlich, jedoch nicht genau identisch mit präzisen Berechtigungen. Mitglieder der festen Serverrolle sysadmin haben z.B. alle Berechtigungen für die Instanz von SQL Server. Gleiches gilt für Anmeldungen mit der CONTROL SERVERBerechtigung. Durch Erteilen der Berechtigung CONTROL SERVER wird eine Anmeldung jedoch nicht Mitglied der festen Serverrolle sysadmin. Und durch Hinzufügen einer Anmeldung zur festen Serverrolle sysadmin wird der Anmeldung nicht explizit die Berechtigung CONTROL SERVER erteilt. Mitunter überprüft eine gespeicherte Prozedur Berechtigungen, indem die feste Rolle und nicht die präzise Berechtigung überprüft wird. Das Trennen einer Datenbank erfordert z.B. die Mitgliedschaft in der festen Datenbankrolle db_owner. Die entsprechende Berechtigung CONTROL DATABASE reicht nicht aus. Diese beiden Systeme arbeiten parallel, interagieren allerdings nur selten. Microsoft empfiehlt, nach Möglichkeit das neuere, präzise Berechtigungssystem anstelle der festen Rollen zu verwenden.

Überwachen von Berechtigungen

Die folgenden Sichten geben Sicherheitsinformationen zurück.

Die Anmeldungen und benutzerdefinierten Serverrollen auf einem Server können mithilfe der Sicht sys.server_principals untersucht werden. Diese Ansicht steht in SQL-Datenbank nicht zur Verfügung.
Die Benutzer und benutzerdefinierten Rollen in einer Datenbank können mithilfe der Sicht sys.database_principals untersucht werden.
Die Anmeldungen erteilten Berechtigungen und benutzerdefinierten festen Serverrollen können mithilfe der Sicht sys.server_permissions untersucht werden. Diese Ansicht steht in SQL-Datenbank nicht zur Verfügung.
Die Benutzern erteilten Berechtigungen und benutzerdefinierten festen Datenbankrollen können mithilfe der Sicht sys.database_permissions untersucht werden.
Die Mitgliedschaft in einer Datenbankrolle kann mithilfe der Sicht sys.database_role_members untersucht werden.
Die Mitgliedschaft in einer Serverrolle kann mithilfe der Sicht sys.server_role_members untersucht werden. Diese Ansicht steht in SQL-Datenbank nicht zur Verfügung.
Weitere sicherheitsbezogene Sichten finden Sie unter Sicherheitskatalogsichten (Transact-SQL) erstellt und verwaltet werden.

Beispiele

Die folgenden Anweisungen geben nützliche Informationen zu Berechtigungen zurück.

A. Liste der Datenbankberechtigungen für jeden Benutzer

Führen Sie die folgende Anweisung in einer Datenbank aus, um die expliziten Berechtigungen zurückzugeben, die in einer Datenbank (SQL Server und SQL-Datenbank) erteilt oder verweigert wurden.

SELECT
    perms.state_desc AS State,
    permission_name AS [Permission],
    obj.name AS [on Object],
    dp.name AS [to User Name]
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
JOIN sys.objects AS obj
    ON perms.major_id = obj.object_id;

B. Auflisten von Mitgliedern der Serverrolle

Führen Sie die folgende Anweisung aus, um die Mitglieder der Serverrollen zurückgegeben (nur SQL Server).

SELECT roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName,
    server_role_members.member_principal_id AS MemberPrincipalID,
    members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
LEFT JOIN sys.server_principals AS members
    ON server_role_members.member_principal_id = members.principal_id;

C. Auflisten aller Datenbankprinzipale, die Mitglied einer Rolle auf Datenbankebene sind

Führen Sie die folgende Anweisung in der Datenbank aus, um die Mitglieder der Datenbankrollen zurückzugeben (SQL Server und SQL-Datenbank).

SELECT dRole.name AS [Database Role Name], dp.name AS [Members]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;