Udostępnij za pośrednictwem

Role serwera usługi Azure SQL Database na potrzeby zarządzania uprawnieniami

  • Artykuł

Dotyczy:Azure SQL Database

W tym artykule opisano stałe role na poziomie serwera w usłudze Azure SQL Database.

Uwaga

Stałe role na poziomie serwera w tym artykule są w publicznej wersji zapoznawczej usługi Azure SQL Database. Te role na poziomie serwera są również częścią wydania programu SQL Server 2022.

Omówienie

W usłudze Azure SQL Database serwer jest koncepcją logiczną i nie można udzielić uprawnień na poziomie serwera. Aby uprościć zarządzanie uprawnieniami, usługa Azure SQL Database udostępnia zestaw stałych ról na poziomie serwera, które ułatwiają zarządzanie uprawnieniami na serwerze logicznym. Role to podstawowe elementy zabezpieczeń, które grupują loginy.

Uwaga

Koncepcja ról w tym artykule jest podobna do grup w systemie operacyjnym Windows.

Te specjalne stałe role na poziomie serwera używają prefiksu ##MS_ i sufiksu ##, aby odróżnić je od innych zwykłych ról utworzonych przez użytkownika.

Podobnie jak w przypadku lokalnego programu SQL Server, uprawnienia serwera są zorganizowane hierarchicznie. Uprawnienia przechowywane przez te role na poziomie serwera mogą być propagowane do uprawnień bazy danych. Aby uprawnienia były skutecznie przydatne na poziomie bazy danych, identyfikator logowania musi być członkiem roli ##MS_DatabaseConnector##na poziomie serwera, która przyznaje CONNECT wszystkim bazom danych, lub mieć konto użytkownika w poszczególnych bazach danych. Dotyczy to również wirtualnej master bazy danych.

Na przykład rola ##MS_ServerStateReader## na poziomie serwera zawiera uprawnienie VIEW SERVER STATE. Jeśli identyfikator logowania, który jest członkiem tej roli, ma konto użytkownika w bazach danych master i WideWorldImporters, ten użytkownik ma uprawnienia VIEW DATABASE STATE w tych dwóch bazach danych.

Uwaga

Każde uprawnienie może zostać cofnięte w bazach danych użytkowników, w efekcie nadpisując uprawnienia nadane na poziomie serwera poprzez członkostwo w rolach. Jednak w systemie master bazy danych nie można udzielić ani odmówić uprawnień.

Usługa Azure SQL Database obecnie udostępnia siedem stałych ról serwera. Nie można zmienić uprawnień przyznanych stałym rolom serwera, a te role nie mogą mieć innych stałych ról jako członków. Identyfikatory logowania na poziomie serwera można dodawać jako elementy członkowskie do ról na poziomie serwera.

Ważne

Każdy członek stałej roli serwera może dodać inne loginy do tej samej roli.

Aby uzyskać więcej informacji na temat logowania i użytkowników usługi Azure SQL Database, zobacz Autoryzowanie dostępu do SQL Database, SQL Managed Instance i Azure Synapse Analytics.

Stałe role na poziomie serwera

W poniższej tabeli przedstawiono stałe role na poziomie serwera i ich możliwości.

Stała rola na poziomie serwera opis
##MS_DatabaseConnector## Członkowie stałej ##MS_DatabaseConnector## roli serwera mogą łączyć się z dowolną bazą danych bez konieczności nawiązywania połączenia z kontem użytkownika w bazie danych.

Aby odmówić CONNECT uprawnień do określonej bazy danych, użytkownicy mogą utworzyć zgodne konto użytkownika dla tego identyfikatora logowania w bazie danych, a następnie odmówić CONNECT uprawnień użytkownikowi bazy danych. To DENY uprawnienie przeważa nad GRANT CONNECT uprawnieniem pochodzącym z tej roli.
##MS_DatabaseManager## Członkowie stałej ##MS_DatabaseManager## roli serwera mogą tworzyć i usuwać bazy danych. Członek roli tworzącej ##MS_DatabaseManager## bazę danych staje się właścicielem tej bazy danych, co pozwala temu użytkownikowi na łączenie się z bazą dbo danych jako użytkownik. Użytkownik dbo ma wszystkie uprawnienia bazy danych w bazie danych. ##MS_DatabaseManager## Członkowie roli nie muszą mieć uprawnień dostępu do baz danych, których nie posiadają. Należy użyć tej roli serwera zamiast roli na poziomie bazy danych dbmanager, która istnieje w master.
##MS_DefinitionReader## Członkowie stałej roli serwera ##MS_DefinitionReader## mogą odczytywać wszystkie widoki katalogu, które są objęte odpowiednio przez VIEW ANY DEFINITION oraz VIEW DEFINITION w dowolnej bazie danych, w której członek tej roli ma konto użytkownika.
##MS_LoginManager## Członkowie stałej ##MS_LoginManager## roli serwera mogą tworzyć i usuwać loginy. Należy używać tej roli serwera zamiast roli poziomu bazy danych loginmanager, która istnieje w master.
##MS_SecurityDefinitionReader## Członkowie stałej roli serwera ##MS_SecurityDefinitionReader## mogą odczytywać wszystkie widoki katalogu objęte VIEW ANY SECURITY DEFINITION, i mają uprawnienie VIEW SECURITY DEFINITION do dowolnej bazy danych, w której członek tej roli ma konto użytkownika. Jest to niewielki podzbiór tego, do czego ma dostęp rola serwera ##MS_DefinitionReader##.
##MS_ServerStateManager## Członkowie stałej ##MS_ServerStateManager## roli serwera mają takie same uprawnienia jak ##MS_ServerStateReader## rola. Ponadto posiada ALTER SERVER STATE uprawnienie, które umożliwia dostęp do kilku operacji zarządzania, takich jak: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF()
##MS_ServerStateReader## Członkowie stałej roli serwera ##MS_ServerStateReader## mogą odczytywać wszystkie dynamiczne widoki zarządzania (DMV) i funkcje, które są objęte odpowiednio w VIEW SERVER STATE oraz VIEW DATABASE STATE, w dowolnej bazie danych, w której członek tej roli ma konto użytkownika.

Uprawnienia stałych ról serwera

Każda stała rola na poziomie serwera ma przypisane pewne uprawnienia. W poniższej tabeli przedstawiono uprawnienia przypisane do ról na poziomie serwera. Przedstawia również uprawnienia na poziomie bazy danych, które są dziedziczone, o ile użytkownik może łączyć się z poszczególnymi bazami danych.

Stała rola na poziomie serwera Uprawnienia na poziomie serwera Uprawnienia na poziomie bazy danych (jeśli istnieje użytkownik bazy danych odpowiadający identyfikatorowi logowania)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE, ALTER ANY DATABASE ALTER
##MS_DefinitionReader## VIEW ANY DATABASE, VIEW ANY DEFINITIONVIEW ANY SECURITY DEFINITION VIEW DEFINITION, VIEW SECURITY DEFINITION
##MS_LoginManager## CREATE LOGIN, ALTER ANY LOGIN Nie dotyczy
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateManager## ALTER SERVER STATE, , VIEW SERVER STATE, , VIEW SERVER PERFORMANCE STATEVIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

Uprawnienia

Tylko konto administratora serwera lub konto administratora firmy Microsoft Entra (które może być grupą Microsoft Entra) może dodawać lub usuwać inne identyfikatory logowania do ról serwera lub z nich. Jest to specyficzne dla usługi Azure SQL Database.

Uwaga

Microsoft Entra ID był wcześniej znany jako Azure Active Directory (Azure AD).

Praca z rolami na poziomie serwera

W poniższej tabeli opisano widoki systemowe i funkcje, których można użyć do pracy z rolami na poziomie serwera w usłudze Azure SQL Database.

Funkcja Typ opis
IS_SRVROLEMEMBER Metadane Wskazuje, czy identyfikator logowania SQL jest członkiem określonej roli na poziomie serwera.
sys.server_role_members Metadane Zwraca jeden wiersz dla każdego członka każdej roli serwera.
sys.sql_logins Metadane Zwraca jeden wiersz dla każdego logowania SQL.
ALTER SERVER ROLE Polecenie Można zmienić członkostwo roli serwera.

Przykłady

W przykładach w tej sekcji pokazano, jak pracować z rolami na poziomie serwera w usłudze Azure SQL Database.

Odp. Dodaj logowanie SQL do roli na poziomie serwera

Poniższy przykład dodaje login Jiao SQL do roli ##MS_ServerStateReader## na poziomie serwera. Ta instrukcja musi być uruchamiana w wirtualnej master bazie danych.

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B. Wyświetlanie listy wszystkich podmiotów zabezpieczeń (uwierzytelnianie SQL), które są członkami roli na poziomie serwera

Poniższa instrukcja zwraca wszystkich członków dowolnej stałej roli serwera przy użyciu widoków katalogu sys.server_role_members i sys.sql_logins. Ta instrukcja musi być uruchamiana w wirtualnej master bazie danych.

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
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
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

C. Kompletny przykład: Dodawanie identyfikatora logowania do roli na poziomie serwera, pobieranie metadanych dla członkostwa w rolach i uprawnień oraz uruchamianie zapytania testowego

Część 1. Przygotowywanie członkostwa w roli i konta użytkownika

Uruchom to polecenie z wirtualnej master bazy danych.

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
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
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

Oto zestaw wyników.

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

Uruchom to polecenie z bazy danych użytkownika.

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

Część 2: Testowanie członkostwa w roli

Zaloguj się jako login Jiao i połącz się z bazą danych użytkownika używaną w przykładzie.

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D. Sprawdź role na poziomie serwera dla logowań Microsoft Entra

Uruchom to polecenie w wirtualnej master bazie danych, aby wyświetlić wszystkie loginy Microsoft Entra, które są częścią ról na poziomie serwera w bazie danych SQL. Aby uzyskać więcej informacji na temat identyfikacji logowania na serwerze Microsoft Entra, zobacz Microsoft Entra server principals (Zasady główne serwera Microsoft Entra).

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
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
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. Sprawdź role wirtualnej master bazy danych dla konkretnych loginów

Uruchom to polecenie w wirtualnej master bazie danych, aby sprawdzić role, jakie posiada bob, lub zmienić wartość, aby była zgodna z użytkownikiem.

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

Ograniczenia ról na poziomie serwera

  • Zastosowanie przypisań ról może zająć do 5 minut. Ponadto w przypadku istniejących sesji zmiany przypisań ról serwera nie zostaną zastosowane, dopóki połączenie nie zostanie zamknięte i ponownie otwarte. Jest to spowodowane architekturą rozproszoną między bazą master danych a innymi bazami danych na tym samym serwerze logicznym.

    • Obejście częściowe: aby zmniejszyć czas oczekiwania i upewnić się, że przypisania ról serwera są aktualne w bazie danych, administrator serwera lub administrator Microsoft Entra może uruchomić DBCC FLUSHAUTHCACHE w bazach danych użytkowników, do których login ma dostęp. Bieżący zalogowani użytkownicy nadal muszą ponownie nawiązać połączenie po uruchomieniu DBCC FLUSHAUTHCACHE, aby zmiany w członkostwie zaczęły ich obowiązywać.

  • IS_SRVROLEMEMBER() nie jest obsługiwane w bazie danych master.

Powiązana zawartość