Aan de slag met database-enginemachtigingen

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-database in Microsoft Fabric

In dit artikel worden enkele basisconcepten voor beveiliging besproken en wordt vervolgens een typische implementatie van machtigingen beschreven. Machtigingen in de database-engine worden beheerd op serverniveau via aanmeldingen en serverfuncties en op databaseniveau via databasegebruikers en databaserollen.

SQL Database en SQL-database in Microsoft Fabric bieden dezelfde opties binnen elke database, maar de machtigingen op serverniveau zijn niet beschikbaar.

• Raadpleeg in SQL Database de zelfstudie: Een database beveiligen in Azure SQL Database. Microsoft Entra ID-verificatie wordt aanbevolen. Zie Zelfstudie: Microsoft Entra-gebruikers maken met behulp van Microsoft Entra-toepassingen voor meer informatie.

• In SQL Database in Microsoft Fabric is de enige ondersteunde verificatiemethode voor databasegebruikers Microsoft Entra-id. Functies en machtigingen op serverniveau zijn niet beschikbaar. Zie Autorisatie in SQL Database in Microsoft Fabricvoor meer informatie.

Opmerking

Microsoft Entra-id werd voorheen Azure Active Directory (Azure AD) genoemd.

Beveiligingsprinciplen

Een beveiligingsprincipaal is de identiteit die DOOR SQL Server wordt gebruikt, die kan worden toegewezen om acties uit te voeren. Beveiligingsprincipals zijn meestal personen of groepen personen, maar kunnen andere entiteiten zijn die doen alsof ze mensen zijn. Beveiligingsprinciplen kunnen worden gemaakt en beheerd met behulp van de Transact-SQL voorbeelden die in dit artikel worden weergegeven, of met behulp van SQL Server Management Studio.

Aanmeldingen

Aanmeldingen zijn afzonderlijke gebruikersaccounts voor aanmelding bij de SQL Server Database Engine. SQL Server en SQL Database ondersteunen aanmeldingen op basis van Windows-verificatie en aanmeldingen op basis van SQL Server-verificatie. Zie Een verificatiemodus kiezen voor meer informatie over de twee typen aanmeldingen.

Vaste serverrollen

In SQL Server zijn vaste serverfuncties een set vooraf geconfigureerde rollen die een handige groep machtigingen op serverniveau bieden. Logins kunnen worden toegevoegd aan de rollen met behulp van de ALTER SERVER ROLE ... ADD MEMBER statement. Zie ALTER SERVER ROLE voor meer informatie. SQL Database biedt geen ondersteuning voor de vaste serverfuncties, maar heeft twee rollen in de master database (dbmanager en loginmanager) die fungeren als serverfuncties.

Door de gebruiker gedefinieerde serverfuncties

In SQL Server kunt u uw eigen serverfuncties maken en machtigingen op serverniveau toewijzen. Logins kunnen worden toegevoegd aan de serverfuncties met behulp van de ALTER SERVER ROLE ... ADD MEMBER statement. Zie ALTER SERVER ROLE voor meer informatie. SQL Database biedt geen ondersteuning voor de door de gebruiker gedefinieerde serverfuncties.

Databasegebruikers

Als u toegang wilt verlenen voor een login tot een database, maakt u een databasegebruiker in die database en koppelt u de databasegebruiker aan een login. De gebruikersnaam van de database is meestal hetzelfde als de aanmeldingsnaam volgens conventie, maar hoeft niet hetzelfde te zijn. Elke databasegebruiker is gekoppeld aan een enkele login. Een aanmelding kan worden toegewezen aan slechts één gebruiker in een database, maar kan worden toegewezen als databasegebruiker in verschillende databases.

Databasegebruikers kunnen ook worden gemaakt die geen bijbehorende aanmelding hebben. Deze gebruikers worden ingesloten databasegebruikers genoemd. Microsoft moedigt het gebruik van ingesloten databasegebruikers aan, omdat het eenvoudiger is om uw database naar een andere server te verplaatsen. Net als bij een aanmelding kan een ingesloten databasegebruiker Gebruikmaken van Windows-verificatie of SQL Server-verificatie. Zie Uw database draagbaar maken met behulp van ingesloten databasesvoor meer informatie.

Er zijn 12 typen gebruikers met kleine verschillen in hoe ze zich verifiëren en wie ze vertegenwoordigen. Zie CREATE USER voor een lijst met gebruikers.

Vaste databaserollen

Vaste databaserollen zijn een set vooraf geconfigureerde rollen die een handige groep machtigingen op databaseniveau bieden. Databasegebruikers en door de gebruiker gedefinieerde databaserollen kunnen worden toegevoegd aan de vaste databaserollen met behulp van de ALTER ROLE ... ADD MEMBER instructie. Zie ALTER ROLE voor meer informatie.

Door de gebruiker gedefinieerde databaserollen

Gebruikers met de machtiging kunnen nieuwe door de CREATE ROLEgebruiker gedefinieerde databaserollen maken om groepen gebruikers met algemene machtigingen te vertegenwoordigen. Normaal gesproken worden machtigingen verleend aan of geweigerd voor de hele rol, waardoor machtigingenbeheer en bewaking worden vereenvoudigd. Databasegebruikers kunnen aan de databaserollen worden toegevoegd met behulp van de ALTER ROLE ... ADD MEMBER instructie. Zie ALTER ROLE voor meer informatie.

Andere belanghebbenden

Andere beveiligingsprinciplen die hier niet worden besproken, zijn toepassingsrollen en aanmeldingen en gebruikers op basis van certificaten of asymmetrische sleutels.

Zie Een databasegebruiker maken voor een afbeelding met de relaties tussen Windows-gebruikers, Windows-groepen, aanmeldingen en databasegebruikers.

Typisch scenario

Het volgende voorbeeld vertegenwoordigt een algemene en aanbevolen methode voor het configureren van machtigingen.

In Windows Active Directory of Microsoft Entra-id

1. Maak een gebruiker voor elke persoon.
2. Maak Windows-groepen die de werkeenheden en de werkfuncties vertegenwoordigen.
3. Voeg de Windows-gebruikers toe aan de Windows-groepen.

Als de gebruiker verbinding maakt met veel databases

1. Maak een aanmelding voor de Windows-groepen. (Als u SQL Server-verificatie gebruikt, slaat u de Active Directory-stappen over en maakt u hier aanmeldingen voor SQL Server-verificatie.)

2. Maak in de gebruikersdatabase een databasegebruiker voor de aanmelding die de Windows-groepen vertegenwoordigt.

3. Maak in de gebruikersdatabase een of meer door de gebruiker gedefinieerde databaserollen, die elk een vergelijkbare functie vertegenwoordigen. U hebt bijvoorbeeld een rol van financiële analist en een rol verkoopanalist.

4. Voeg de databasegebruikers toe aan een of meer door de gebruiker gedefinieerde databaserollen.

5. Machtigingen verlenen aan de door de gebruiker gedefinieerde databaserollen.

Als de gebruiker verbinding maakt met slechts één database

1. Maak in de gebruikersdatabase een ingesloten databasegebruiker voor de Windows-groep. (Als u SQL Server-verificatie gebruikt, slaat u de Active Directory-stappen over en maakt u hier SQL Server-verificatie van ingesloten databasegebruikers.)

2. Maak in de gebruikersdatabase een of meer door de gebruiker gedefinieerde databaserollen, die elk een vergelijkbare functie vertegenwoordigen. U hebt bijvoorbeeld een rol van financiële analist en een rol verkoopanalist.

3. Voeg de databasegebruikers toe aan een of meer door de gebruiker gedefinieerde databaserollen.

4. Machtigingen verlenen aan de door de gebruiker gedefinieerde databaserollen.

Het gebruikelijke resultaat is dat een Windows-gebruiker lid is van een Windows-groep. De Windows-groep heeft een aanmelding in SQL Server of SQL Database. De aanmelding wordt toegewezen aan een gebruikersidentiteit in de gebruikersdatabase. De gebruiker is lid van een databaserol. Nu moet u machtigingen toevoegen aan de rol.

Machtigingen toewijzen

De meeste machtigingsinstructies hebben de volgende indeling:

<authorization> <permission> ON <securable>::<name> TO <principal>;

• <authorization> moet zijn GRANT, REVOKEof DENY.

• Hiermee <permission> wordt de actie vastgesteld die u toestaat of verbiedt. Het exacte aantal machtigingen verschilt tussen SQL Server en Azure SQL Database. Zie Machtigingen (Database Engine) voor meer informatie over machtigingen en raadpleeg de grafiek verderop in dit artikel.

• ON <securable>::<name>: het type beveiligbaar (server, serverobject, database of databaseobject) en de naam ervan. Sommige machtigingen vereisen geen <securable>::<name> omdat het eenduidig of ongepast is in de context. De machtiging vereist bijvoorbeeld CREATE TABLE niet de <securable>::<name> component (GRANT CREATE TABLE TO Mary; staat Mary toe om tabellen te maken).

• <principal> is de beveiligingsprincipaal (aanmelding, gebruiker of rol) die de machtiging ontvangt of verliest. Verken waar mogelijk machtigingen aan rollen.

De volgende voorbeeldinstructie verleent de UPDATE toestemming voor de Parts tabel of weergave in het Production schema aan de rol genaamd PartsTeam.

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

De volgende voorbeeldinstructie verleent de UPDATE machtiging, voor het Production schema en daardoor ook voor elke tabel of weergave die binnen dit schema is opgenomen, aan de genoemde rol ProductionTeam, wat een effectievere en beter schaalbare benadering is voor het toewijzen van machtigingen dan op afzonderlijk objectniveau.

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Machtigingen worden verleend aan beveiligingsprinciplen (aanmeldingen, gebruikers en rollen) met behulp van de GRANT instructie. Machtigingen worden expliciet geweigerd met behulp van de DENY opdracht. Een eerder verleende of geweigerde machtiging wordt verwijderd met behulp van de REVOKE instructie. Machtigingen zijn cumulatief, waarbij de gebruiker alle machtigingen ontvangt die zijn verleend aan de gebruiker, aanmelding en eventuele groepslidmaatschappen; elke machtigingsweigering overschrijft echter alle verleende toestemming.

Waarschuwing

Een veelvoorkomende fout is om een GRANT te verwijderen met behulp van DENY in plaats van REVOKE. Dit kan problemen veroorzaken wanneer een gebruiker machtigingen van meerdere bronnen ontvangt. Dit kan een veelvoorkomend scenario zijn. In het volgende voorbeeld ziet u het principe.

De groep Verkoop ontvangt SELECT machtigingen voor de OrderStatus tabel via de instructie GRANT SELECT ON OBJECT::OrderStatus TO Sales;. Gebruiker Jae is lid van de Sales rol. Jae heeft ook SELECT toestemming tot de OrderStatus tabel onder hun eigen gebruikersnaam via de instructie GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Stel dat de beheerder de GRANT uit de Sales rol wil verwijderen.

• Als de beheerder REVOKE SELECT ON OBJECT::OrderStatus TO Sales; correct uitvoert, behoudt Jae via zijn afzonderlijke GRANT verklaring toegang tot de OrderStatus tabel.

• Als de beheerder DENY SELECT ON OBJECT::OrderStatus TO Sales; incorrect uitvoert, wordt Jae, als lid van de Sales rol, de SELECT machtiging geweigerd omdat de DENY naar Sales hun individuele GRANT overschrijft.

Opmerking

Machtigingen kunnen worden geconfigureerd met Management Studio. Zoek het beveiligingsobject in Object Verkenner, klik met de rechtermuisknop op het beveiligingsobject en selecteer Eigenschappen. Selecteer de pagina Machtigingen . Zie de pagina Machtigingen of Beveiligbare Objecten voor hulp bij het gebruik van de pagina met machtigingen.

Machtigingshiërarchie

Machtigingen hebben een ouder/kind hiërarchie. Als u machtigingen verleent SELECT voor een database, bevat SELECT die machtiging machtigingen voor alle (onderliggende) schema's in de database. Als u machtigingen verleent SELECT voor een schema, bevat SELECT het machtigingen voor alle (onderliggende) tabellen en weergaven in het schema. De machtigingen zijn transitief: als u machtigingen verleent SELECT voor een database, bevat SELECT deze machtigingen voor alle (onderliggende) schema's en alle (kleinkind)-tabellen en -weergaven.

Machtigingen hebben ook overkoepelende machtigingen. De CONTROL machtiging voor een object geeft u normaal gesproken alle andere machtigingen voor het object.

Omdat zowel de bovenliggende/onderliggende hiërarchie als de dekkingshiërarchie op dezelfde machtiging kunnen reageren, kan het machtigingssysteem ingewikkeld worden. Laten we bijvoorbeeld een tabel (Region), in een schema (Customers) in een database (SalesDB) nemen.

• CONTROL de machtiging voor de tabel Region bevat alle andere machtigingen voor de tabel Region, waaronder ALTER, SELECT, INSERT, UPDATE, en DELETEenkele andere machtigingen.

• SELECT in het Customers schema dat de eigenaar is van de Region tabel, omvat de SELECT machtiging voor de Region tabel.

De SELECT machtiging voor de Region tabel kan dus worden bereikt via een van deze zes instructies:

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

De minste machtiging verlenen

De eerste machtiging die eerder (GRANT SELECT ON OBJECT::Region TO Jae;) wordt vermeld, is de meest gedetailleerde machtiging. Deze verklaring is de minimaal mogelijke machtiging die de SELECT verleent. Er zijn geen machtigingen voor onderliggende objecten bij. Het is een goed principe om altijd zo min mogelijk machtigingen te verlenen, maar u moet overwegen om op een hoger niveau toe te kennen, om het toekenningssysteem te vereenvoudigen.

Dus als Jae toestemming nodig heeft voor het hele schema, verleen één keer toestemming op schemaniveau, in plaats van SELECTSELECT meerdere keren toe te kennen op tabel- of weergaveniveau. Het ontwerp van de database kan aanzienlijk van invloed zijn op hoe succesvol deze strategie kan zijn. Deze strategie werkt het beste wanneer uw database is ontworpen, zodat objecten die identieke machtigingen nodig hebben, in één schema worden opgenomen.

Aanbeveling

Wanneer u een database en de bijbehorende objecten ontwerpt, plant u vanaf het begin hoe toepassingen en gebruikers toegang hebben tot deze objecten. Gebruik deze informatie om de toegang tot tabellen, weergaven, functies en opgeslagen procedures te beheren met behulp van schema's. Met schema's kunt u eenvoudiger toegangstypen groeperen.

Diagram van machtigingen

In de volgende afbeelding ziet u de machtigingen en de bijbehorende relaties met elkaar. Sommige machtigingen op een hoger niveau (zoals CONTROL SERVER) worden vaak vermeld. In dit artikel is de poster veel te klein om te lezen. U kunt de Database Engine Permissions Poster in volledige grootte in PDF-formaat downloaden.

Zie Machtigingenhiërarchie (Database Engine) voor een afbeelding met de relaties tussen de database-engine-principals en server- en databaseobjecten.

Machtigingen versus vaste server- en vaste databaserollen

De machtigingen van de vaste serverfuncties en vaste databaserollen zijn vergelijkbaar met, maar niet precies hetzelfde als gedetailleerde machtigingen. Leden van de vaste serverrol sysadmin hebben bijvoorbeeld alle machtigingen voor het exemplaar van SQL Server, net als aanmeldingen met de CONTROL SERVER machtiging.

Het verlenen van de CONTROL SERVER machtiging maakt echter geen lid van de vaste serverrol sysadmin en het toevoegen van een aanmelding aan de vaste serverfunctie sysadmin verleent de aanmelding niet expliciet de CONTROL SERVER machtiging. Soms controleert een opgeslagen procedure machtigingen door de vaste rol te controleren en niet de gedetailleerde machtiging te controleren.

Het loskoppelen van een database vereist bijvoorbeeld lidmaatschap van de db_owner vaste databaserol. De equivalente CONTROL DATABASE machtiging is niet voldoende. Deze twee systemen werken parallel, maar werken zelden met elkaar. Microsoft raadt u aan om waar mogelijk het nieuwere, gedetailleerde machtigingssysteem te gebruiken in plaats van vaste rollen.

Machtigingen bewaken

De volgende weergaven geven beveiligingsinformatie. Voor alle beveiligingsgerelateerde weergaven, zie Beveiligingscatalogusweergaven (Transact-SQL).

Bekijk	Beschrijving
sys.server_principals 1	Aanmeldingen en door de gebruiker gedefinieerde serverfuncties op een server
sys.database_principals	Gebruikers en door de gebruiker gedefinieerde rollen in een database
sys.server_permissions 1	Machtigingen verleend aan aanmeldingen en door de gebruiker gedefinieerde vaste serverfuncties
sys.database_permissions	Machtigingen verleend aan gebruikers en door de gebruiker gedefinieerde vaste databaserollen
sys.database_role_members	Lidmaatschap van databaserol
sys.server_role_members 1	Lidmaatschap van serverfunctie

¹ Deze weergave is niet beschikbaar in SQL Database.

Voorbeelden

De volgende uitspraken leveren nuttige informatie over machtigingen.

Eén. Lijst met databasemachtigingen voor elke gebruiker

Als u de expliciete machtigingen wilt retourneren die zijn verleend of geweigerd in een database (SQL Server en SQL Database), voert u de volgende Transact-SQL instructie uit in de database.

SELECT perms.state_desc AS State,
       permission_name AS [Permission],
       obj.name AS [on Object],
       dp.name AS [to User Name]
FROM sys.database_permissions AS perms
     INNER JOIN sys.database_principals AS dp
         ON perms.grantee_principal_id = dp.principal_id
     INNER JOIN sys.objects AS obj
         ON perms.major_id = obj.object_id;

B. Lijst met serverrolleden

Voer de volgende instructie uit om de leden van de serverrollen (alleen SQL Server) te retourneren.

SELECT roles.principal_id AS RolePrincipalID,
       roles.name AS RolePrincipalName,
       server_role_members.member_principal_id AS MemberPrincipalID,
       members.name AS MemberPrincipalName
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
     LEFT OUTER JOIN sys.server_principals AS members
         ON server_role_members.member_principal_id = members.principal_id;

C. Een lijst weergeven van alle database-principals die lid zijn van een rol op databaseniveau

Als u de leden van de databaserollen (SQL Server en SQL Database) wilt retourneren, voert u de volgende instructie uit in de database.

SELECT dRole.name AS [Database Role Name],
       dp.name AS [Members]
FROM sys.database_role_members AS dRo
     INNER JOIN sys.database_principals AS dp
         ON dRo.member_principal_id = dp.principal_id
     INNER JOIN sys.database_principals AS dRole
         ON dRo.role_principal_id = dRole.principal_id;

Verwante inhoud

• Beveiliging voor SQL Server Database Engine en Azure SQL Database
• beveiligingsfuncties (Transact-SQL)
• Beveiligingsgerelateerde dynamische beheerweergaven en -functies (Transact-SQL)
• Beveiligingscatalogusweergaven (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• Effectieve database-enginemachtigingen bepalen
• Zelfstudie: Aan de slag met de database-engine
• Les 1: Databaseobjecten maken en er query's op uitvoeren
• Zelfstudie: SQL Server Management Studio
• Zelfstudie: Transact-SQL uitspraken schrijven