Azure SQL Database-serverfuncties voor machtigingsbeheer

  • Artikel

Van toepassing op: Azure SQL Database

In dit artikel worden vaste serverfuncties in Azure SQL Database beschreven.

Notitie

De vaste serverfuncties in dit artikel zijn beschikbaar als openbare preview voor Azure SQL Database. Deze functies op serverniveau maken ook deel uit van de release voor SQL Server 2022.

Overzicht

In Azure SQL Database is de server een logisch concept en kunnen geen machtigingen worden verleend op serverniveau. Om het beheer van machtigingen te vereenvoudigen, biedt Azure SQL Database een set vaste functies op serverniveau om u te helpen de machtigingen op een logische server te beheren. Rollen zijn beveiligingsprinciplen waarmee groepsaanmelding wordt gebruikt.

Notitie

Het concept rollen in dit artikel is net als groepen in het Windows-besturingssysteem.

Deze speciale rollen op serverniveau gebruiken het voorvoegsel en het achtervoegsel ##MS_## om onderscheid te maken tussen andere reguliere door de gebruiker gemaakte principals.

Net als on-premises SQL Server worden servermachtigingen hiërarchisch georganiseerd. De machtigingen die worden bewaard door deze functies op serverniveau kunnen worden doorgegeven aan databasemachtigingen. Als u wilt dat de machtigingen effectief nuttig zijn op databaseniveau, moet een aanmelding lid zijn van de functie ##MS_DatabaseConnector##op serverniveau, die aan alle databases verleent CONNECT of een gebruikersaccount in afzonderlijke databases heeft. Dit geldt ook voor de virtuele master database.

De rol ##MS_ServerStateReader## op serverniveau bevat bijvoorbeeld de machtiging VIEW SERVER STATE. Als een aanmelding die lid is van deze rol een gebruikersaccount in de databases master heeft en WideWorldImportersdeze gebruiker de machtiging VIEW DATABASE STATE heeft in deze twee databases.

Notitie

Elke machtiging kan worden geweigerd binnen gebruikersdatabases, waardoor de serverbrede toekenning wordt overschreven via rollidmaatschap. In de hoofddatabase van het systeem kunnen machtigingen echter niet worden verleend of geweigerd.

Azure SQL Database biedt momenteel zeven vaste serverfuncties. De machtigingen die aan de vaste serverfuncties worden verleend, kunnen niet worden gewijzigd en deze rollen kunnen geen andere vaste rollen hebben als leden. U kunt aanmeldingen op serverniveau toevoegen als leden aan functies op serverniveau.

Belangrijk

Elk lid van een vaste serverfunctie kan andere aanmeldingen toevoegen aan dezelfde rol.

Zie Databasetoegang tot SQL Database, SQL Managed Instance en Azure Synapse Analytics autoriseren voor meer informatie over azure SQL Database-aanmeldingen en gebruikers.

Vaste functies op serverniveau

In de volgende tabel ziet u de functies op vaste serverniveau en hun mogelijkheden.

Rol op serverniveau opgelost Beschrijving
##MS_DatabaseConnector## Leden van de ##MS_DatabaseConnector## rol vaste server kunnen verbinding maken met elke database zonder dat een gebruikersaccount in de database verbinding moet maken.

Als u de CONNECT machtiging voor een specifieke database wilt weigeren, kunnen gebruikers een overeenkomend gebruikersaccount maken voor deze aanmelding in de database en vervolgens DENY de CONNECT machtiging voor de databasegebruiker. Deze DENY machtiging overschrijft de GRANT CONNECT machtiging die afkomstig is van deze rol.
##MS_DatabaseManager## Leden van de ##MS_DatabaseManager## vaste serverfunctie kunnen databases maken en verwijderen. Een lid van de ##MS_DatabaseManager## rol die een database maakt, wordt de eigenaar van die database, waardoor die gebruiker verbinding kan maken met die database als de dbo gebruiker. De dbo gebruiker heeft alle databasemachtigingen in de database. Leden van de ##MS_DatabaseManager## rol hebben niet per se toestemming om toegang te krijgen tot databases waarvan ze geen eigenaar zijn. U moet deze serverrol gebruiken ten opzichte van de databaseniveaurol dbmanager waarin zich bevindt master.
##MS_DefinitionReader## Leden van de ##MS_DefinitionReader## rol vaste server kunnen alle catalogusweergaven lezen die onder VIEW ANY DEFINITIONrespectievelijk VIEW DEFINITION een database vallen waarop het lid van deze rol een gebruikersaccount heeft.
##MS_LoginManager## Leden van de ##MS_LoginManager## vaste serverfunctie kunnen aanmeldingen maken en verwijderen. U moet deze serverrol gebruiken voor de rol op loginmanager-databaseniveau waarin zich bevindt master.
##MS_SecurityDefinitionReader## Leden van de ##MS_SecurityDefinitionReader## rol vaste server kunnen alle catalogusweergaven lezen die worden gedekt door VIEW ANY SECURITY DEFINITION, en hebben VIEW SECURITY DEFINITION respectievelijk machtigingen voor elke database waarop het lid van deze rol een gebruikersaccount heeft. Dit is een kleine subset waartoe de ##MS_DefinitionReader## serverfunctie toegang heeft.
##MS_ServerStateManager## Leden van de ##MS_ServerStateManager## vaste serverrol hebben dezelfde machtigingen als de ##MS_ServerStateReader## rol. Het bevat ook de ALTER SERVER STATE machtiging, die toegang tot verschillende beheerbewerkingen toestaat, zoals: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), ; DBCC SQLPERF()
##MS_ServerStateReader## Leden van de ##MS_ServerStateReader## vaste serverrol kunnen alle dynamische beheerweergaven (DMV's) en functies lezen die onder VIEW SERVER STATErespectievelijk VIEW DATABASE STATE een database vallen waarop het lid van deze rol een gebruikersaccount heeft.

Machtigingen van vaste serverrollen

Aan elke rol op serverniveau zijn bepaalde machtigingen toegewezen. In de volgende tabel ziet u de machtigingen die zijn toegewezen aan de rollen op serverniveau. Ook worden de machtigingen op databaseniveau weergegeven, die worden overgenomen zolang de gebruiker verbinding kan maken met afzonderlijke databases.

Rol op serverniveau opgelost Machtigingen op serverniveau Machtigingen op databaseniveau (als een databasegebruiker die overeenkomt met de aanmelding bestaat)
##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 N.v.t.
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateManager## ALTER SERVER STATE, , , VIEW SERVER STATEVIEW SERVER PERFORMANCE STATEVIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE, , VIEW SERVER PERFORMANCE STATEVIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE

Bevoegdheden

Alleen het beheerdersaccount van de server of het Microsoft Entra-beheerdersaccount (dat een Microsoft Entra-groep kan zijn) kan andere aanmeldingen toevoegen aan of verwijderen van serverfuncties. Dit is specifiek voor Azure SQL Database.

Notitie

Microsoft Entra-id is de nieuwe naam voor Azure Active Directory (Azure AD). Op dit moment wordt de documentatie bijgewerkt.

Werken met functies op serverniveau

In de volgende tabel worden de systeemweergaven en functies uitgelegd die u kunt gebruiken om te werken met serverfuncties in Azure SQL Database.

Functie Type Description
IS_SRVROLEMEMBER Metagegevens Geeft aan of een SQL-aanmelding lid is van de opgegeven serverrol.
sys.server_role_members Metagegevens Retourneert één rij voor elk lid van elke rol op serverniveau.
sys.sql_logins Metagegevens Retourneert één rij voor elke SQL-aanmelding.
ALTER SERVER ROLE Opdracht Hiermee wijzigt u het lidmaatschap van een serverfunctie.

Voorbeelden

In de voorbeelden in deze sectie ziet u hoe u kunt werken met serverfuncties in Azure SQL Database.

A. Een SQL-aanmelding toevoegen aan een functie op serverniveau

In het volgende voorbeeld wordt de SQL-aanmelding Jiao toegevoegd aan de rol ##MS_ServerStateReader##op serverniveau. Deze instructie moet worden uitgevoerd in de virtuele master database.

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

B. Een lijst weergeven van alle principals (SQL-verificatie) die lid zijn van een rol op serverniveau

De volgende instructie retourneert alle leden van een rol op vaste serverniveau met behulp van de sys.server_role_members en sys.sql_logins catalogusweergaven. Deze instructie moet worden uitgevoerd in de virtuele master database.

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. Volledig voorbeeld: Een aanmelding toevoegen aan een functie op serverniveau, metagegevens ophalen voor rollidmaatschap en machtigingen en een testquery uitvoeren

Deel 1: Rollidmaatschap en gebruikersaccount voorbereiden

Voer deze opdracht uit vanuit de virtuele master database.

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

Dit is de resultatenset.

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

Voer deze opdracht uit vanuit een gebruikersdatabase.

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

Deel 2: Rollidmaatschap testen

Meld u aan als aanmelding Jiao en maak verbinding met de gebruikersdatabase die in het voorbeeld wordt gebruikt.

-- 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. Controleer de functies op serverniveau voor Microsoft Entra-aanmeldingen

Voer deze opdracht uit in de virtuele master database om alle Microsoft Entra-aanmeldingen te zien die deel uitmaken van serverfuncties in SQL Database. Zie Microsoft Entra-server-principals voor meer informatie over aanmeldingen van Microsoft Entra-servers.

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. Controleer de virtuele master databaserollen op specifieke aanmeldingen

Voer deze opdracht uit in de virtuele master database om te controleren met rollen bob of wijzig de waarde zodat deze overeenkomt met uw principal.

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

Beperkingen van functies op serverniveau

  • Het kan tot vijf minuten duren voordat roltoewijzingen worden geïmplementeerd. Ook voor bestaande sessies worden wijzigingen in serverfunctietoewijzingen pas van kracht nadat de verbinding is gesloten en opnieuw is geopend. Dit komt door de gedistribueerde architectuur tussen de master database en andere databases op dezelfde logische server.

    • Gedeeltelijke tijdelijke oplossing: om de wachttijd te verminderen en ervoor te zorgen dat serverroltoewijzingen actueel zijn in een database, een serverbeheerder of een Microsoft Entra-beheerder, kunnen worden uitgevoerd DBCC FLUSHAUTHCACHE in de gebruikersdatabases waarop de aanmelding toegang heeft. Huidige aangemelde gebruikers moeten nog steeds opnieuw verbinding maken nadat DBCC FLUSHAUTHCACHE de lidmaatschapswijzigingen van kracht kunnen worden.

  • IS_SRVROLEMEMBER() wordt niet ondersteund in de master database.

Gerelateerde inhoud