Ermitteln effektiver Datenbank-Engine-Berechtigungen

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric

In diesem Artikel wird beschrieben, wie Sie feststellen können, wer über Berechtigungen für verschiedene Objekte in der SQL Server-Datenbank-Engine verfügt. SQL Server implementiert zwei Berechtigungssysteme für die Datenbank-Engine. Ein älteres System fester Rollen hat vorkonfigurierte Berechtigungen. Ab SQL Server 2005 (9.x) ist ein flexibleres und präziseres System verfügbar.

Berücksichtigen Sie dabei Folgendes:

• Die effektiven Berechtigungen sind das Aggregat von beiden Berechtigungssystemen.
• Eine Verweigerung von Berechtigungen hat Vorrang vor der Erteilung von Berechtigungen.
• Wenn ein Benutzer ein Mitglied der festen Serverrolle „sysadmin“ ist, werden Berechtigungen nicht darüber hinaus überprüft, damit Verweigerungen nicht erzwungen werden.
• Die alten und neuen Systeme sind vergleichbar. Z.B. ist die Mitgliedschaft in der festen sysadmin-Serverrolle vergleichbar mit der CONTROL SERVER-Berechtigung. Die Systeme sind jedoch nicht identisch. Wenn eine Anmeldung beispielsweise nur über die CONTROL SERVER-Berechtigung verfügt und eine gespeicherte Prozedur die Mitgliedschaft in der festen sysadmin-Serverrolle überprüft, dann schlägt die Überprüfung der Berechtigung fehl. Das Gegenteil ist auch der Fall.
• In der Fabric SQL-Datenbank ist Die Microsoft Entra-ID für Datenbankbenutzer die einzige unterstützte Authentifizierungsmethode. Rollen und Berechtigungen auf Serverebene sind nicht verfügbar, nur auf Datenbankebene. Weitere Informationen finden Sie unter Autorisierung in der SQL-Datenbank in Microsoft Fabric.

Summary

• Berechtigungen auf Serverebene können aus der Mitgliedschaft in festen Serverrollen oder in benutzerdefinierten Serverrollen resultieren. Jeder Benutzer gehört zur festen public-Serverrolle und erhält jede dort zugewiesene Berechtigung.
• Berechtigungen auf Serverebene können von Berechtigungen für Anmeldungen oder benutzerdefinierten Serverrollen stammen.
• Berechtigungen auf Datenbankebene können aus der Mitgliedschaft in festen Datenbankrollen oder benutzerdefinierten Datenbankrollen in jeder Datenbank stammen. Jeder gehört zur festen public-Datenbankrolle und erhält jede Berechtigung, die ihr zugewiesen ist.
• Berechtigungen auf Datenbankebene können von Berechtigungen für Benutzer oder benutzerdefinierten Datenbankrollen in jeder Datenbank stammen.
• Berechtigungen können, falls aktiviert, vom guest login oder vom guest database user erteilt werden. Die guest-Anmeldenamen und -Benutzer sind standardmäßig deaktiviert.
• Windows-Benutzer können Mitglieder der Windows-Gruppen sein, die über Anmeldenamen verfügen können. SQL Server erfährt von der Windows-Gruppenmitgliedschaft, wenn ein Windows-Benutzer eine Verbindung herstellt und ein Windows-Token mit der Sicherheits-ID einer Windows-Gruppe darstellt. Da SQL Server keine automatischen Aktualisierungen zu Mitgliedschaften in Windows-Gruppen verwaltet oder empfängt, kann SQL Server die Berechtigungen von Windows-Benutzern, die sich aus der Mitgliedschaft in Windows-Gruppen ergeben, nicht zuverlässig ausweisen.
• Berechtigungen können erworben werden, indem zu einer Anwendungsrolle gewechselt und das Kennwort bereitgestellt wird.
• Berechtigungen können erworben werden, indem eine gespeicherte Prozedur ausgeführt wird, die die EXECUTE AS-Klausel einschließt.
• Berechtigungen können mit der IMPERSONATE-Berechtigung durch Anmeldenamen oder Benutzer erworben werden.
• Mitglieder der Administratorgruppe des lokalen Computers können immer ihre Berechtigungen auf sysadmin erhöhen. (Gilt nicht für die SQL-Datenbank.)
• Mitglieder der festen securityadmin-Serverrolle können viele ihrer Berechtigungen und in einigen Fällen auch die Berechtigungen auf sysadmin erhöhen. (Gilt nicht für die SQL-Datenbank.)
• SQL Server-Administratoren können Informationen zu allen Anmeldungen und Benutzern anzeigen. Weniger privilegierten Benutzern werden in der Regel nur Informationen zu ihren eigenen Identitäten angezeigt.

Ältere feste Rollenberechtigungssysteme

Feste Serverrollen und feste Datenbankrollen verfügen über vorkonfigurierte Berechtigungen, die nicht geändert werden können. Führen Sie die folgende Abfrage aus, um zu bestimmen, wer Mitglied der festen Serverrolle ist:

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

Um zu bestimmen, wer die Mitglieder einer festen Datenbankrolle sind, führen Sie die folgende Abfrage in jeder Datenbank aus.

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

Informationen zu den Berechtigungen, die jeder Rolle gewährt werden, finden Sie in den Rollenbeschreibungen in Rollen auf Serverebene und Rollen auf Datenbankebene.

Neuere präzise Berechtigungssysteme

Dieses System ist flexibel, was bedeutet, dass es kompliziert sein kann, wenn die Personen, die es einrichten, präzise sein möchten. Zur Vereinfachung könnten Sie Rollen erstellen, Rollen Berechtigungen zuweisen und dann Personengruppen zu den Rollen hinzufügen. Und es ist einfacher, wenn das Entwicklungsteam für die Datenbank die Aktivitäten nach Schema trennt und dann die Rollenberechtigungen für ein ganzes Schema statt für einzelne Tabellen oder Prozeduren erteilt. Reale Szenarien sind komplex und geschäftliche Anforderungen können unerwartete Sicherheitsanforderungen schaffen.

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 das Poster viel zu klein, um es lesen zu können. Sie können das Poster zu den Datenbank-Engine-Berechtigungen im PDF-Format in voller Größe herunterladen.

Sicherheitsklassen

Berechtigungen können auf der Serverebene, der Datenbankebene, der Schemaebene, der Objektebene usw. erteilt werden. Es gibt 26 Ebenen (Klassen genannt). Die vollständige Liste der Klassen in alphabetischer Reihenfolge lautet: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASESCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER und XML SCHEMA COLLECTION. (Einige Klassen sind für einige Sql Server-Typen nicht verfügbar.) Um vollständige Informationen zu den einzelnen Klassen bereitzustellen, ist eine andere Abfrage erforderlich.

Principals

Dateiberechtigungen werden an Prinzipale erteilt. Prinzipale können Serverrollen, Anmeldenamen, Datenbankrollen oder Benutzer sein. Anmeldungen können Windows-Gruppen darstellen, die viele Windows-Benutzer enthalten. Da Windows-Gruppen nicht von SQL Server verwaltet werden, weiß SQL Server nicht immer, wer Mitglied einer Windows-Gruppe ist. Wenn ein Windows-Benutzer mit SQL Server verbunden ist, enthält das Anmeldungspaket das Token der Windows-Gruppenmitgliedschaft für den Benutzer.

Wenn ein Windows-Benutzer eine Verbindung mit einer Anmeldung herstellt, die auf einer Windows-Gruppe basiert, könnten einige Aktivitäten erfordern, dass SQL Server eine Anmeldung oder einen Benutzer erstellt, um den einzelnen Windows-Benutzer darzustellen. Z.B. enthält eine Windows-Gruppe (Techniker) Benutzer (Mary, Todd, Pat) und die Techniker-Gruppe verfügt über ein Datenbankbenutzerkonto. Wenn Mary über eine Berechtigung verfügt und eine Tabelle erstellt kann ein Benutzer (nämlich Mary) als Besitzer der Tabelle erstellt werden. Wenn Todd eine Berechtigung verweigert wird, über die der Rest der Techniker-Gruppe verfügt, dann muss der Benutzer Todd die Möglichkeit erhalten, die Berechtigungsverweigerung nachverfolgen zu können.

Denken Sie daran, dass ein Windows-Benutzer Mitglied von mehr als einer Windows-Gruppe (z.B. sowohl Techniker und Manager) sein könnte. Berechtigungen, die für die Anmeldung der Ingenieure oder der Manager erteilt oder verweigert werden, die dem Benutzer individuell gewährt oder verweigert werden und die Rollen gewährt oder verweigert werden, denen der Benutzer angehört, werden alle zusammengefasst und ausgewertet, um die effektiven Berechtigungen zu bestimmen. Die HAS_PERMS_BY_NAME-Funktion kann anzeigen, ob ein Benutzer oder Anmeldename über eine bestimmte Berechtigung verfügt. Es gibt jedoch keine offensichtliche Möglichkeit zur Bestimmung der Quelle der Erteilung oder Verweigerung der Berechtigung. Studieren Sie die Liste der Berechtigungen und experimentieren Sie vielleicht durch Versuch und Irrtum.

Nützliche Abfragen

Serverberechtigungen

Die folgende Abfrage gibt eine Liste der Berechtigungen zurück, die auf Serverebene erteilt oder verweigert wurden. Führen Sie diese Abfrage in der master Datenbank aus.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

Datenbankberechtigungen

Die folgende Abfrage gibt eine Liste der Berechtigungen zurück, die auf Datenbankebene erteilt oder verweigert wurden. Führen Sie diese Abfrage in jeder Datenbank aus.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

Jede Berechtigungsklasse in der Berechtigungstabelle kann mit anderen Systemansichten verknüpft werden, die zu dieser Klasse von Sicherungsobjekten zugehörige Informationen bereitstellen. Die folgende Abfrage enthält z.B. den Namen des Datenbankobjekts, das von der Berechtigung betroffen ist.

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

Verwenden Sie die HAS_PERMS_BY_NAME-Funktion, um zu bestimmen, ob ein bestimmter Benutzer (in diesem Fall TestUser) über eine Berechtigung verfügt. Beispiel:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

Die Details der Syntax finden Sie unter HAS_PERMS_BY_NAME.

Verwandte Inhalte

• Erste Schritte mit Berechtigungen für die Datenbank-Engine
• Tutorial: Erste Schritte mit der Datenbank-Engine