Udostępnij za pośrednictwem

Określanie skutecznych uprawnień silnika bazy danych

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w usłudze Microsoft Fabric

W tym artykule opisano sposób określania, kto ma uprawnienia do różnych obiektów w Aparacie bazy danych programu SQL Server. Program SQL Server implementuje dwa systemy uprawnień dla aparatu bazy danych. Starszy system stałych ról ma wstępnie skonfigurowane uprawnienia. Począwszy od programu SQL Server 2005 (9.x) dostępny jest bardziej elastyczny i precyzyjny system.

Notatka

Informacje zawarte w tym artykule dotyczą programu SQL Server 2005 (9.x) i nowszych wersji. Niektóre typy uprawnień nie są dostępne w niektórych wersjach programu SQL Server.

Należy zawsze pamiętać o następujących kwestiach:

  • Skuteczne uprawnienia to agregacja obu systemów uprawnień.
  • Odmowa uprawnień zastępuje przyznanie uprawnień.
  • Jeśli użytkownik jest członkiem stałej roli serwera sysadmin, dalsze sprawdzanie uprawnień nie ma miejsca, więc odmowy nie zostaną wymuszone.
  • Stary system i nowy system mają podobieństwa. Na przykład członkostwo w stałej roli serwera sysadmin jest podobne do posiadania uprawnień CONTROL SERVER. Ale systemy nie są identyczne. Jeśli na przykład identyfikator logowania ma tylko uprawnienia CONTROL SERVER, a procedury składowane sprawdzają członkostwo w sysadmin stałej roli serwera, sprawdzanie uprawnień zakończy się niepowodzeniem. Odwrotnie jest również prawdą.
  • W usłudze Fabric SQL Database identyfikator Entra firmy Microsoft dla użytkowników bazy danych jest jedyną obsługiwaną metodą uwierzytelniania. Role i uprawnienia na poziomie serwera nie są dostępne, tylko na poziomie bazy danych. Aby uzyskać więcej informacji, zobacz Autoryzacja w bazie danych SQL w Microsoft Fabric.

Streszczenie

  • Uprawnienia na poziomie serwera mogą pochodzić z członkostwa w stałych rolach serwera lub rolach serwera zdefiniowanych przez użytkownika. Wszyscy należą do public stałej roli serwera i otrzymują wszystkie uprawnienia przypisane do niej.
  • Uprawnienia na poziomie serwera mogą pochodzić z uprawnień udzielanych do logowania lub ról serwera zdefiniowanych przez użytkownika.
  • Uprawnienia na poziomie bazy danych mogą pochodzić z członkostwa w stałych rolach bazy danych lub rolach bazy danych zdefiniowanych przez użytkownika w każdej bazie danych. Wszyscy należą do stałej roli bazy danych public i otrzymują dowolne uprawnienia przypisane tam.
  • Uprawnienia na poziomie bazy danych mogą pochodzić z uprawnień udzielanych użytkownikom lub ról bazy danych zdefiniowanych przez użytkownika w każdej bazie danych.
  • Uprawnienia można uzyskać od użytkownika logowania guest lub użytkownika bazy danych guest, jeśli są włączone. Logowanie i użytkownicy guest są wyłączone domyślnie.
  • Użytkownicy systemu Windows mogą być członkami grup Windows, które mogą mieć loginy. Program SQL Server uczy się członkostwa w grupie systemu Windows, gdy użytkownik systemu Windows nawiązuje połączenie i przedstawia token systemu Windows z identyfikatorem zabezpieczeń grupy systemu Windows. Ponieważ program SQL Server nie zarządza ani nie otrzymuje automatycznych aktualizacji dotyczących członkostwa w grupach systemu Windows, program SQL Server nie może niezawodnie zgłaszać uprawnień użytkowników systemu Windows otrzymanych z członkostwa w grupie systemu Windows.
  • Uprawnienia można uzyskać, przełączając się na rolę aplikacji i podając hasło.
  • Uprawnienia można uzyskać, wykonując procedurę składowaną zawierającą klauzulę EXECUTE AS.
  • Uprawnienia można uzyskać przy użyciu identyfikatorów logowania lub użytkowników z uprawnieniami IMPERSONATE.
  • Członkowie lokalnej grupy administratorów komputerów zawsze mogą podnieść swoje uprawnienia do sysadmin. (Nie dotyczy usługi SQL Database).
  • Członkowie stałej roli serwera securityadmin mogą zwiększyć wiele swoich uprawnień, a w niektórych przypadkach mogą zwiększyć poziom uprawnień do sysadmin. (Nie dotyczy usługi SQL Database).
  • Administratorzy programu SQL Server mogą wyświetlać informacje o wszystkich nazwach logowania i użytkownikach. Mniej uprzywilejowani użytkownicy zwykle widzą informacje o swoich własnych tożsamościach.

Starszy ustalony system uprawnień ról

Stałe role serwera i stałe role bazy danych mają wstępnie skonfigurowane uprawnienia, których nie można zmienić. Aby określić, kto jest członkiem stałej roli serwera, wykonaj następujące zapytanie:

Notatka

Nie dotyczy usługi SQL Database ani Azure Synapse Analytics, gdzie uprawnienia na poziomie serwera nie są dostępne. Kolumna is_fixed_role z sys.server_principals została dodana w programie SQL Server 2012 (11.x). Nie jest to wymagane w przypadku starszych wersji programu SQL Server.

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;

Uwaga

Wszystkie loginy są członkami roli public i nie można ich usunąć. Zapytanie sprawdza tabele w bazie danych master, ale można je wykonać w dowolnej bazie danych dla produktu lokalnego.

Aby określić, kto jest członkiem stałej roli bazy danych, wykonaj następujące zapytanie w każdej bazie danych.

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;

Aby zrozumieć uprawnienia przyznane każdej roli, zobacz opisy ról na ilustracjach w witrynie Książki online (role na poziomie serwerai role na poziomie bazy danych).

Nowszy system uprawnień szczegółowych

Ten system jest elastyczny, co oznacza, że może być skomplikowany, jeśli osoby konfigurujące go chcą być precyzyjne. Aby uprościć kwestie, ułatwia tworzenie ról, przypisywanie uprawnień do ról, a następnie dodawanie grup osób do ról. I łatwiej jest, jeśli zespół deweloperów baz danych oddziela działanie według schematu, a następnie przyznaje uprawnienia roli do całego schematu zamiast do poszczególnych tabel lub procedur. Rzeczywiste scenariusze są złożone, a potrzeby biznesowe mogą tworzyć nieoczekiwane wymagania dotyczące zabezpieczeń.

Na poniższej ilustracji przedstawiono uprawnienia i ich relacje ze sobą. Niektóre z uprawnień wyższego poziomu (takich jak CONTROL SERVER) są wyświetlane wiele razy. Plakat w artykule jest zbyt mały, aby go przeczytać. Pełnowymiarowy plakat uprawnień silnika baz danych możesz pobrać w formacie PDF.

Zrzut ekranu z pliku PDF z uprawnieniami silnika bazy danych.

Klasy zabezpieczeń

Uprawnienia można przyznać na poziomie serwera, na poziomie bazy danych, na poziomie schematu lub na poziomie obiektu itp. Istnieją 26 poziomów (nazywanych klasami). Pełna lista klas w kolejności alfabetycznej to: 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, USERXML SCHEMA COLLECTION. (Niektóre klasy nie są dostępne w niektórych typach programu SQL Server). Aby podać pełne informacje o każdej klasie, wymagane jest inne zapytanie.

Dyrektorzy

Uprawnienia są przyznawane podmiotom . Podmioty zabezpieczeń mogą być rolami serwera, identyfikatorami logowania, rolami bazy danych lub użytkownikami. Identyfikatory logowania mogą reprezentować grupy systemu Windows, które obejmują wielu użytkowników systemu Windows. Ponieważ grupy systemu Windows nie są obsługiwane przez program SQL Server, program SQL Server nie zawsze wie, kto jest członkiem grupy systemu Windows. Gdy użytkownik systemu Windows nawiązuje połączenie z programem SQL Server, pakiet logowania zawiera tokeny członkostwa w grupie systemu Windows dla użytkownika.

Gdy użytkownik systemu Windows nawiązuje połączenie przy użyciu nazwy logowania opartej na grupie systemu Windows, niektóre działania mogą wymagać programu SQL Server utworzenia identyfikatora logowania lub użytkownika reprezentującego indywidualnego użytkownika systemu Windows. Na przykład grupa systemu Windows (inżynierowie) zawiera użytkowników (Mary, Todd, Pat), a grupa Inżynierowie ma konto użytkownika bazy danych. Jeśli Mary ma uprawnienia i tworzy tabelę, użytkownik (Mary) może zostać utworzony jako właściciel tabeli. Lub jeśli Toddowi odmówiono uprawnienia, które mają pozostali członkowie grupy Inżynierowie, należy utworzyć użytkownika Todd, aby śledzić odmowę uprawnień.

Pamiętaj, że użytkownik systemu Windows może być członkiem więcej niż jednej grupy systemu Windows (na przykład inżynierów i menedżerów). Uprawnienia przyznane lub odrzucone do logowania inżynierów, do logowania menedżerów, przyznane lub odrzucone dla użytkownika indywidualnie i przyznane lub odrzucone do ról, których użytkownik jest członkiem, zostaną zagregowane i ocenione pod kątem obowiązujących uprawnień. Funkcja HAS_PERMS_BY_NAME może ujawnić, czy użytkownik lub identyfikator logowania ma określone uprawnienia. Nie ma jednak oczywistego sposobu określania źródła udzielenia lub odmowy uprawnień. Zapoznaj się z listą uprawnień i być może poeksperymentuj metodą prób i błędów.

Przydatne zapytania

Uprawnienia serwera

Poniższe zapytanie zwraca listę uprawnień, które zostały przyznane lub odrzucone na poziomie serwera. To zapytanie powinno zostać wykonane w bazie danych master.

Notatka

Nie można udzielić uprawnień na poziomie serwera ani wykonywać zapytań dotyczących usługi SQL Database lub Azure Synapse Analytics.

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;

Uprawnienia bazy danych

Poniższe zapytanie zwraca listę uprawnień, które zostały przyznane lub odrzucone na poziomie bazy danych. To zapytanie powinno być wykonywane w każdej bazie danych.

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;

Każda klasa uprawnień w tabeli uprawnień może być połączona z innymi widokami systemowymi, które dostarczają powiązanych informacji na temat tej klasy zabezpieczeń. Na przykład następujące zapytanie zawiera nazwę obiektu bazy danych, którego dotyczy uprawnienie.

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';

Użyj funkcji HAS_PERMS_BY_NAME, aby określić, czy określony użytkownik (w tym przypadku TestUser) ma uprawnienie. Na przykład:

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

Aby uzyskać szczegóły dotyczące składni, zobacz HAS_PERMS_BY_NAME.

Następne kroki