Delen via

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 bij het beheren van de machtigingen op een logische server. Rollen zijn beveiligingsprinciplen waarmee groepsaanmelding wordt gebruikt.

Notitie

Het rollenconcept in dit artikel is als groepen in het Windows-besturingssysteem.

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

Vergelijkbaar met SQL Server on-premises worden servermachtigingen hiërarchisch georganiseerd. De machtigingen die worden bewaard door deze functies op serverniveau kunnen worden doorgegeven aan databasemachtigingen. Als de machtigingen effectief zijn op databaseniveau, moet een login ofwel lid zijn van de serverniveau-rol ##MS_DatabaseConnector##, die CONNECT aan alle databases verleent, of een gebruikersaccount in afzonderlijke databases hebben. Dit geldt ook voor de virtuele master-database.

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

Notitie

Elke toestemming kan worden geweigerd binnen gebruikersgegevensbanken, waardoor de serverbrede toekenning wordt overschreven door lidmaatschap van rollen. In de systeemdatabase hoofddatabasekunnen 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.

Voor meer informatie over Azure SQL Database-aanmeldingen en gebruikers, raadpleeg Database-toegang verlenen tot SQL Database, SQL Managed Instance en Azure Synapse Analytics.

Vaste functies op serverniveau

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

Vaste rol op serverniveau Beschrijving
##MS_DatabaseConnector## Leden van de ##MS_DatabaseConnector## vaste serverfunctie kunnen verbinding maken met elke database zonder dat er een gebruikersaccount in de database nodig is om verbinding mee te 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 de CONNECT machtiging voor de databasegebruiker DENY. 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 rol ##MS_DatabaseManager## hebben niet per se machtigingen voor toegang tot databases waarvan ze geen eigenaar zijn. U moet deze serverfunctie gebruiken voor de dbmanager rol op databaseniveau die aanwezig is in master.
##MS_DefinitionReader## Leden van de ##MS_DefinitionReader## vaste serverrol kunnen alle catalogusweergaven lezen die onder VIEW ANY DEFINITIONvallen, respectievelijk VIEW DEFINITION op elke database 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 serverfunctie gebruiken voor de loginmanager rol op databaseniveau die aanwezig is in master.
##MS_SecurityDefinitionReader## Leden van de ##MS_SecurityDefinitionReader## vaste serverrol kunnen alle catalogusweergaven lezen die worden gedekt door VIEW ANY SECURITY DEFINITIONen hebben respectievelijk VIEW SECURITY DEFINITION machtigingen voor elke database waarop het lid van deze rol een gebruikersaccount heeft. Dit is een kleine subset van 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 biedt tot verschillende beheerbewerkingen, 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 STATEvallen, respectievelijk VIEW DATABASE STATE op elke database waarop het lid van deze rol een gebruikersaccount heeft.

Machtigingen van vaste serverfuncties

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.

Vaste rol op serverniveau 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 DEFINITION, VIEW 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 STATE, VIEW SERVER PERFORMANCE STATE, VIEW 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

Machtigingen

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 voorheen Azure Active Directory (Azure AD) werd genoemd.

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.

Kenmerk Type Beschrijving
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.
SERVERFUNCTIE WIJZIGEN Bevelen 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.

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

In het volgende voorbeeld wordt de SQL-aanmelding Jiao toegevoegd aan de serverniveaurol ##MS_ServerStateReader##. 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 vaste serverrol 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: Voorbereiding van roltoewijzing en gebruikersaccount

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 zijn de resultaten.

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: Testen van roltoewijzing

Meld u aan als 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. Voor meer informatie over Microsoft Entra-aanmeldingen op servers, zie Microsoft Entra server principals.

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 logins

Voer deze opdracht uit in de virtuele master-database om te controleren met rollen die bob heeft, 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

  • Roltoewijzingen kunnen tot vijf minuten duren voordat ze effectief worden. 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, kan een serverbeheerder of een Microsoft Entra-beheerder DBCC FLUSHAUTHCACHE uitvoeren in de gebruikersdatabases waartoe de aanmelding toegang heeft. Huidige aangemelde gebruikers moeten nog steeds opnieuw verbinding maken nadat ze DBCC FLUSHAUTHCACHE hebben uitgevoerd om de lidmaatschapswijzigingen van kracht te laten worden.

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

Verwante inhoud