Erste Schritte mit Datenbank-Engine-Berechtigungen

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 macht das gleiche System in jeder Datenbank verfügbar, aber die Berechtigungen auf Serverebene sind nicht verfügbar. In diesem Artikel werden einige grundlegende Sicherheitskonzepte erläutert und dann eine typische Implementierung der Berechtigungen beschrieben.

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. Es handelt sich in der Regel um Personen oder Gruppen von Personen, aber es können andere Entitäten sein, die vorgeben, Menschen zu sein. Die Sicherheitsprinzipale können mithilfe der aufgeführten Transact-SQL-Liste 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 auf Grundlage der Windows- und SQL Server -Authentifizierung. Informationen zu den beiden Anmeldungstypen finden Sie unter Auswählen eines Authentifizierungsmodus.

Feste Serverrollen

In SQL Serversind 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, verfügt aber über zwei Rollen in der master Datenbank (dbmanager und loginmanager), die sich wie Serverrollen verhalten.

Benutzerdefinierte Serverrollen

In SQL Serverkö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

Anmeldenamen erhalten Zugriff auf eine Datenbank, indem sie einen Datenbankbenutzer in einer Datenbank erstellen und diesem Datenbankbenutzer die Anmeldung zuordnen. In der Regel ist der Datenbankbenutzername mit dem Anmeldenamen identisch, muss aber nicht identisch sein. 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.

Datenbankbenutzer können auch erstellt werden, die nicht über eine entsprechende Anmeldung verfügen. Diese Benutzer werden als eigenständige Datenbankbenutzer bezeichnet. 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 den 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 Windows Active Directory oder Azure Active Directory

1. Erstellen Sie einen Windows-Benutzer für jede Person.

2. Erstellen Sie Windows-Gruppen, die die Arbeitseinheiten und Arbeitsfunktionen darstellen.

3. Fügen Sie Windows-Benutzer den Windows-Gruppen hinzu.

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

1. 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.)

2. Erstellen Sie in der Benutzerdatenbank einen Datenbankbenutzer für die Anmeldung, die die Windows-Gruppen darstellt.

3. Erstellen Sie in der Benutzerdatenbank eine oder mehrere benutzerdefinierte Datenbankrollen, die jeweils eine ähnliche Funktion darstellen. Beispiele: Finanzanalyst und Vertriebsanalyst.

4. Fügen Sie die Datenbankbenutzer einer oder mehreren benutzerdefinierten Datenbankrollen hinzu.

5. Erteilen Sie den benutzerdefinierten Datenbankrollen Berechtigungen.

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

1. 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 die Active Directory, und erstellen Sie hier die SQL Server -Authentifizierung für den eigenständigen Datenbankbenutzer.)

2. Erstellen Sie in der Benutzerdatenbank eine oder mehrere benutzerdefinierte Datenbankrollen, die jeweils eine ähnliche Funktion darstellen. Beispiele: Finanzanalyst und Vertriebsanalyst.

3. Fügen Sie die Datenbankbenutzer einer oder mehreren benutzerdefinierten Datenbankrollen hinzu.

4. 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 in SQL Server oder SQL-Datenbanküber eine Anmeldung. 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.

Zuweisen von Berechtigungen

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 diagramm aufgeführt, auf das unten verwiesen wird.

• ON SECURABLE::NAME ist der Typ des sicherungsfähigen Objektes (Server, Serverobjekt, Datenbank oder Datenbankobjekt) und sein Name. Einige Berechtigungen sind nicht erforderlich ON SECURABLE::NAME , da sie im Kontext eindeutig oder unangemessen sind. Für die CREATE TABLE Berechtigung ist beispielsweise die ON SECURABLE::NAME -Klausel nicht erforderlich (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 Beispielgenehmigung erteilt der Rolle namens PartsTeamdie UPDATE Berechtigung für Parts die Tabelle oder Sicht, die Production im Schema enthalten ist:

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

Die folgende Beispielzuweisungsanweisung gewährt die UPDATE Berechtigung für das Production Schema und erweitert für jede Tabelle oder Sicht in diesem Schema auf die Rolle namens ProductionTeam, was ein effektiverer und besserer Ansatz zum Zuweisen von Berechtigungen als auf einzelner Objektebene ist:

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 "Vertrieb". Jae wurde auch die Berechtigung für die OrderStatus-Tabelle unter ihrem eigenen Benutzernamen über die Anweisung GRANT SELECT ON OBJECT::OrderStatus TO Jae;erteiltSELECT. Angenommen, der Administrator möchte die Berechtigung GRANT aus der Rolle „Sales“ entfernen.

• Wenn der Administrator ordnungsgemäß ausführt REVOKE SELECT ON OBJECT::OrderStatus TO Sales;, behält SELECT Jae über seine individuelle GRANT Anweisung zugriff auf die Tabelle OrderStatus.

• Wenn der Administrator fälschlicherweise ausgeführt DENY SELECT ON OBJECT::OrderStatus TO Sales; wird, wird Jae als Mitglied der Rolle "Vertrieb" die SELECT Berechtigung verweigert, da die DENY für Sales ihre einzelperson GRANTüberschreibt.

Hinweis

Berechtigungen können mithilfe von Management Studiokonfiguriert werden. Suchen Sie das sicherungsfähige element in Objekt-Explorer, klicken Sie mit der rechten Maustaste auf das sicherungsfähige Element, und wählen Sie dann 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).

• CONTROLdie Berechtigung für die Tabelle Region umfasst alle anderen Berechtigungen für die Tabelle Region, einschließlich ALTER, SELECT, INSERT, DELETEUPDATE, und einige andere 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;

Gewähren der geringsten 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 (Sie können mehr über das Prinzip der geringsten Rechte lesen), aber gleichzeitig (im Widerspruch dazu) versuchen, auf höheren Ebenen zu gewähren, um das Gewährungssystem zu vereinfachen. Wenn Jae also Berechtigungen für das gesamte Schema benötigt, erteilen Sie SELECT einmal auf Schemaebene, anstatt mehrmals auf Tabellen- oder Ansichtsebene zu gewähren SELECT . 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

Die folgende Abbildung 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 Berechtigungen der Datenbank-Engine in voller Größe im PDF-Format herunterladen.

Eine Grafik, die die Beziehungen zwischen datenbank-Engine-Prinzipalen und Server- und Datenbankobjekten zeigt, finden Sie unter Berechtigungshierarchie (Datenbank-Engine).

Berechtigungen im Vergleich zu festen Server- und festen 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 Berechtigung CONTROL SERVER . Das Erteilen der CONTROL SERVER Berechtigung macht eine Anmeldung jedoch nicht zu einem Mitglied der festen Serverrolle sysadmin, und das Hinzufügen einer Anmeldung zur sysadmin festen Serverrolle gewährt der Anmeldung nicht explizit die CONTROL SERVER Berechtigung. Mitunter überprüft eine gespeicherte Prozedur Berechtigungen, indem die feste Rolle und nicht die präzise Berechtigung überprüft wird. Beispielsweise erfordert das Trennen einer Datenbank die Mitgliedschaft in der db_owner festen Datenbankrolle. Die entsprechende CONTROL DATABASE Berechtigung 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 ist in SQL-Datenbank nicht verfügbar.

• 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 ist in SQL-Datenbank nicht verfügbar.

• 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. sys.database_role_members untersucht werden.

• Die Mitgliedschaft in einer Serverrolle kann mithilfe der Sicht sys.server_role_members untersucht werden. Diese Ansicht ist in SQL-Datenbank nicht verfügbar.

• Weitere sicherheitsbezogene Sichten finden Sie unter Sicherheitskatalogsichten (Transact-SQL).

Beispiele

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

A. Liste der Datenbankberechtigungen für jeden Benutzer

Um die expliziten Berechtigungen zurückzugeben, die in einer Datenbank (SQL Server und SQL-Datenbank) gewährt oder verweigert wurden, führen Sie die folgende Anweisung in der Datenbank aus.

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 (nur SQL Server) zurückzugeben.

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 Mitglieder einer Rolle auf Datenbankebene sind

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

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;

Weitere Informationen

• Sicherheitscenter für SQL Server-Datenbank-Engine und Azure SQL-Datenbank
• Sicherheitsfunktionen (Transact-SQL)
• Sicherheitsbezogene dynamische Verwaltungssichten und -funktionen (Transact-SQL)
• Sicherheitskatalogsichten (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• Ermitteln effektiver Datenbank-Engine-Berechtigungen

Nächste Schritte

• Tutorial: Erste Schritte mit der Datenbank-Engine
• Erstellen einer Datenbank (Lernprogramm)
• Tutorial: SQL Server Management Studio
• Tutorial: Schreiben von Transact-SQL-Anweisungen