Ermitteln effektiver Datenbankmodulberechtigungen
Gilt für:
SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
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 Datenbankrollen hat vorkonfigurierte Berechtigungen. Ab SQL Server 2005 (9.x) ist ein flexibleres und präzises System verfügbar.
Hinweis
Die Informationen in diesem Artikel gelten für SQL Server 2005 (9.x) und höhere Versionen. Einige Arten von Berechtigungen sind in einigen Versionen von SQL Server nicht verfügbar.
Beachten Sie immer die folgenden Punkte:
- Die effektiven Berechtigungen sind das Aggregat von beiden Berechtigungssystemen.
- Eine DOS-Berechtigung überschreibt eine Gewährung von Berechtigungen.
- Wenn ein Benutzer Mitglied der festen Serverrolle "sysadmin" ist, werden Berechtigungen nicht weiter überprüft, sodass Denials nicht erzwungen werden.
- Die alten und neuen Systeme sind vergleichbar. Z.B. ist die Mitgliedschaft in der festen
sysadmin-Serverrolle vergleichbar mit derCONTROL SERVER-Berechtigung. Aber die Systeme sind nicht identisch. Wenn eine Anmeldung beispielsweise nur über dieCONTROL SERVER-Berechtigung verfügt und eine gespeicherte Prozedur die Mitgliedschaft in der festensysadmin-Serverrolle überprüft, dann schlägt die Überprüfung der Berechtigung fehl. Das Gegenteil ist auch der Fall.
Zusammenfassung
- Eine Berechtigung auf Serverebene stammt von einer Mitgliedschaft in der festen Serverrolle oder von benutzerdefinierten Serverrollen. 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 zu den festen
public-Datenbankrollen und empfängt jede zugewiesene Berechtigung. - Berechtigungen auf Datenbankebene können von Berechtigungen für Benutzer oder benutzerdefinierten Datenbankrollen in jeder Datenbank stammen.
- Berechtigungen können von der
guest-Anmeldung oder dem aktiviertenguest-Datenbankbenutzer empfangen werden. Dieguest-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 Updates zu Windows-Gruppenmitgliedschaften verwaltet oder empfängt, kann SQL Server nicht zuverlässig die Berechtigungen von Windows-Benutzern melden, die von der Windows-Gruppenmitgliedschaft empfangen werden.
- 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
sysadminerhöhen. (Gilt nicht für SQL-Datenbank.) - Mitglieder der festen
securityadmin-Serverrolle können viele ihrer Berechtigungen und in einigen Fällen auch die Berechtigungen aufsysadminerhöhen. (Gilt nicht für 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.
Älteres Festes Rollenberechtigungssystem
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:
Hinweis
Dies gilt nicht für SQL-Datenbank oder Synapse Analytics, bei denen die Berechtigung auf Serverebene nicht verfügbar ist. Die is_fixed_role Spalte wurde sys.server_principals in SQL Server 2012 (11.x) hinzugefügt. Sie ist für ältere Versionen von SQL Server nicht erforderlich.
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;
Hinweis
Alle Anmeldenamen sind Mitglieder der öffentlichen Rollen und können nicht entfernt werden. Die Abfrage überprüft Tabellen in der master Datenbank, kann aber in jeder Datenbank für das lokale Produkt ausgeführt werden.
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 den einzelnen Rollen gewährt werden, finden Sie in den Rollenbeschreibungen in "Books Online" (Rollen auf Serverebene und Rollen auf Datenbankebene).
Neueres granulares Berechtigungssystem
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 Szenarios sind komplex, und Geschäftsanforderungen können unerwartete Sicherheitsanforderungen schaffen.
Die folgende Abbildung zeigt die Berechtigungen und ihre Beziehungen miteinander. 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 für Berechtigungen des Datenbankmoduls im PDF-Format herunterladen.
Sicherheitsklassen
Berechtigungen können auf Serverebene, auf Datenbankebene, auf Schemaebene oder auf Objektebene erteilt werden. Es gibt 26 Ebenen (als Klassen bezeichnet). 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
Berechtigungen werden 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 mithilfe einer Anmeldung auf Basis einer Windows-Gruppe herstellt, könnten einige Aktivitäten SQL Server benötigen. Dieser würde eine Anmeldung oder einen Benutzer erstellen, der die einzelnen Windows-Benutzer darstellt. 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 möglicherweise Mitglied mehrerer Windows-Gruppen ist (z. B. Techniker und Manager). Berechtigungen, die der Techniker- oder Manager-Anmeldung, den individuellen Benutzern oder Rollen, in denen der Benutzer Mitglied ist, gewährt oder verweigert werden, werden alle aggregiert und für die effektiven Berechtigungen ausgewertet. 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. Durchsuchen Sie die Liste der Berechtigungen, und probieren Sie sie vielleicht auch aus.
Nützliche Abfragen
Serverberechtigungen
Die folgende Abfrage gibt eine Liste der Berechtigungen zurück, die auf Serverebene erteilt oder verweigert wurden. Diese Abfrage sollte in der master Datenbank ausgeführt werden.
Hinweis
Berechtigungen auf Serverebene können nicht auf SQL-Datenbank oder Azure Synapse Analytics abgefragt oder erteilt werden.
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. Diese Abfrage sollte in jeder Datenbank ausgeführt werden.
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 Klasse der Berechtigungstabelle kann mit anderen Systemansichten verknüpft werden, die verwandte Informationen über die Klasse des sicherungsfähigen Elements 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.
Nächste Schritte
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für