Kom igång med behörigheter för databasmotorn

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalysplattformssystem (PDW)SQL-databas i Förhandsversion av Microsoft Fabric

Den här artikeln går igenom några grundläggande säkerhetsbegrepp och beskriver sedan en typisk implementering av behörigheter. Behörigheter i databasmotorn hanteras på servernivå via inloggningar och serverroller och på databasnivå via databasanvändare och databasroller.

SQL Database och SQL Database i Microsoft Fabric Preview innehåller samma alternativ i varje databas, men behörigheterna på servernivå är inte tillgängliga.

• I SQL Database, se Självstudie: Skydda en databas i Azure SQL Database. Microsoft Entra ID-autentisering rekommenderas. Mer information finns i Självstudie: Skapa Microsoft Entra-användare med hjälp av Microsoft Entra-program.

• I SQL Database i Microsoft Fabric Preview är Microsoft Entra-ID den enda autentiseringsmetod som stöds för databasanvändare. Roller och behörigheter på servernivå är inte tillgängliga. Mer information finns i Auktorisering i SQL-databasen i Microsoft Fabric.

Security principals

Ett säkerhetsobjekt är den identitet som SQL Server använder, som kan tilldelas behörighet att vidta åtgärder. Säkerhetsobjekt är vanligtvis personer eller grupper av personer, men kan vara andra entiteter som låtsas vara människor. Säkerhetsobjekt kan skapas och hanteras med hjälp av de Transact-SQL exempel som visas i den här artikeln eller med hjälp av SQL Server Management Studio.

Logins

Inloggningar är enskilda användarkonton för att logga in på SQL Server Database Engine. SQL Server och SQL Database stöder inloggningar baserat på Windows-autentisering och inloggningar baserat på SQL Server-autentisering. Information om de två typerna av inloggningar finns i Välj ett autentiseringsläge.

Serverroller har åtgärdats

I SQL Server är fasta serverroller en uppsättning förkonfigurerade roller som ger en bekväm grupp med behörigheter på servernivå. Inloggningar kan läggas till i rollerna med hjälp av -instruktionen ALTER SERVER ROLE ... ADD MEMBER . Mer information finns i ÄNDRA SERVERROLL. SQL Database stöder inte fasta serverroller, men har två roller i master databasen (dbmanager och loginmanager) som fungerar som serverroller.

Användardefinierade serverroller

I SQL Server kan du skapa egna serverroller och tilldela behörigheter på servernivå till dem. Inloggningar kan läggas till i serverrollerna med hjälp av -instruktionen ALTER SERVER ROLE ... ADD MEMBER . Mer information finns i ÄNDRA SERVERROLL. SQL Database stöder inte användardefinierade serverroller.

Database users

Om du vill bevilja åtkomst för en inloggning till en databas skapar du en databasanvändare i databasen och mappar databasanvändaren till en inloggning. Databasanvändarnamnet är vanligtvis samma som inloggningsnamnet enligt konventionen, men det behöver inte vara detsamma. Varje databasanvändare mappar till en enda inloggning. En inloggning kan endast mappas till en användare i en databas, men den kan mappas som en databasanvändare i flera olika databaser.

Databasanvändare kan också skapas som inte har någon motsvarande inloggning. Dessa användare kallas inneslutna databasanvändare. Microsoft uppmuntrar användningen av inneslutna databasanvändare eftersom det gör det enklare att flytta databasen till en annan server. Precis som vid inloggning kan en oberoende databasanvändare använda antingen Windows-autentisering eller SQL Server-autentisering. Mer information finns i Gör databasen portabel med hjälp av inneslutna databaser.

Det finns 12 typer av användare med små skillnader i hur de autentiserar och vem de representerar. En lista över användare finns i SKAPA ANVÄNDARE.

Fasta databasroller

Fasta databasroller är en uppsättning förkonfigurerade roller som ger en bekväm grupp med behörigheter på databasnivå. Databasanvändare och användardefinierade databasroller kan läggas till i de fasta databasrollerna med hjälp av -instruktionen ALTER ROLE ... ADD MEMBER . Mer information finns i ÄNDRA ROLL.

Användardefinierade databasroller

Användare med behörigheten CREATE ROLE kan skapa nya användardefinierade databasroller för att representera grupper av användare med gemensamma behörigheter. Vanligtvis beviljas eller nekas behörigheter till hela rollen, vilket förenklar behörighetshantering och övervakning. Databasanvändare kan läggas till i databasrollerna med hjälp av -instruktionen ALTER ROLE ... ADD MEMBER . Mer information finns i ÄNDRA ROLL.

Other principals

Andra säkerhetsobjekt som inte beskrivs här är programroller och inloggningar och användare baserat på certifikat eller asymmetriska nycklar.

En bild som visar relationerna mellan Windows-användare, Windows-grupper, inloggningar och databasanvändare finns i Skapa en databasanvändare.

Typical scenario

Följande exempel representerar en vanlig och rekommenderad metod för att konfigurera behörigheter.

I Windows Active Directory eller Microsoft Entra ID

1. Skapa en användare för varje person.
2. Skapa Windows-grupper som representerar arbetsenheterna och arbetsfunktionerna.
3. Lägg till Windows-användare i Windows-grupperna.

Om användaren ansluter till många databaser

1. Skapa en inloggning för Windows-grupperna. (Om du använder SQL Server-autentisering hoppar du över Active Directory-stegen och skapar SQL Server-autentiseringsinloggningar här.)

2. I användardatabasen skapar du en databasanvändare för inloggningen som representerar Windows-grupperna.

3. I användardatabasen skapar du en eller flera användardefinierade databasroller som var och en representerar en liknande funktion. Du kan till exempel ha en finansanalytikerroll och en försäljningsanalytikerroll.

4. Lägg till databasanvändarna i en eller flera användardefinierade databasroller.

5. Bevilja behörigheter till de användardefinierade databasrollerna.

Om användaren bara ansluter till en databas

1. I användardatabasen skapar du en innesluten databasanvändare för Windows-gruppen. (Om du använder SQL Server-autentisering hoppar du över Active Directory-stegen och skapar sql server-autentisering för den oberoende databasanvändaren här.)

2. I användardatabasen skapar du en eller flera användardefinierade databasroller som var och en representerar en liknande funktion. Du kan till exempel ha en finansanalytikerroll och en försäljningsanalytikerroll.

3. Lägg till databasanvändarna i en eller flera användardefinierade databasroller.

4. Bevilja behörigheter till de användardefinierade databasrollerna.

Det typiska resultatet just nu är att en Windows-användare är medlem i en Windows-grupp. Windows-gruppen har en inloggning i SQL Server eller SQL Database. Inloggningen mappas till en användaridentitet i användardatabasen. Användaren är medlem i en databasroll. Nu måste du lägga till behörigheter i rollen.

Assign permissions

De flesta behörighetsuttryck har följande format:

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

• <authorization> måste vara GRANT, REVOKEeller DENY.

• Etablerar <permission> den åtgärd som du tillåter eller förbjuder. Det exakta antalet behörigheter skiljer sig mellan SQL Server och Azure SQL Database. Information om behörigheter finns i Behörigheter (databasmotor) och se diagrammet senare i den här artikeln.

• ON <securable>::<name> är typen av ett skyddbart objekt (server, serverobjekt, databas eller databasobjekt) och dess namn. Vissa behörigheter kräver <securable>::<name> inte eftersom det är tvetydigt eller olämpligt i kontexten. Behörigheten CREATE TABLE kräver inte klausulen <securable>::<name> (GRANT CREATE TABLE TO Mary; tillåter Mary att skapa tabeller).

• <principal> är säkerhetsobjektet (inloggning, användare eller roll) som tar emot eller förlorar behörigheten. Bevilja behörigheter till roller när det är möjligt.

Följande exempelsats ger behörigheten UPDATE i tabellen Parts eller vyn i Production schemat till rollen med namnet PartsTeam:

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

Följande exempeluttryck ger behörigheten UPDATE på schemat Production och därmed på alla tabeller eller vyer som ingår i detta schema, till rollen med namnet ProductionTeam, vilket är en effektivare och mer skalerbar metod för att tilldela behörigheter än på enskild objektnivå.

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Behörigheter beviljas säkerhetsprincipaler (inloggningar, användare och roller) med hjälp av GRANT-instruktionen. Behörigheter nekas uttryckligen genom att använda kommandot DENY. En tidigare beviljad eller nekad behörighet tas bort med hjälp av REVOKE-satsen. Behörigheterna är kumulativa, där användaren får alla behörigheter som beviljats användaren, inloggningen och eventuella gruppmedlemskap; dock åsidosätter varje nekning av behörighet alla beviljanden.

Gruppen Försäljning får SELECT behörigheter på OrderStatus-tabellen via instruktionen GRANT SELECT ON OBJECT::OrderStatus TO Sales;. Användaren Jae är medlem i Sales rollen. Jae har också beviljats SELECT behörighet till OrderStatus-tabellen under sitt eget användarnamn genom instruktionen GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Anta att administratören vill ta bort GRANT från rollen Sales.

• Om administratören kör REVOKE SELECT ON OBJECT::OrderStatus TO Sales; korrekt behåller Jae åtkomsten till tabellen SELECT genom deras enskilda OrderStatus uttalande.

• Om administratören felaktigt kör DENY SELECT ON OBJECT::OrderStatus TO Sales;, nekas Jae, som medlem i rollen Sales, åtkomsten SELECT eftersom DENY till Sales åsidosätter deras individuella GRANT.

Permission hierarchy

Behörigheter har en föräldra/barn-hierarki. Om du beviljar SELECT behörighet för en databas, inkluderar den även behörighet för alla (underordnade) scheman i databasen. Om du beviljar SELECT behörighet för ett schema innehåller SELECT det behörighet för alla (underordnade) tabeller och vyer i schemat. Behörigheterna är transitiva: om du beviljar SELECT behörighet för en databas inkluderar den behörighet för alla underordnade scheman och alla underordnade tabeller och vyer.

Behörigheter har också övergripande behörigheter. Behörigheten CONTROL för ett objekt ger dig normalt alla andra behörigheter för objektet.

Eftersom både den hierarki för förälder/barn och täckande hierarki kan påverka samma behörighet, kan behörighetssystemet bli komplicerat. Låt oss till exempel ta en tabell (Region), i ett schema (Customers), i en databas (SalesDB).

• CONTROL behörighet i tabellen Region innehåller alla andra behörigheter i tabellen Region, inklusive ALTER, SELECT, INSERT, UPDATE, och DELETEnågra andra behörigheter.

• SELECT i schemat Customers som äger Region tabellen innehåller behörigheten SELECT för Region tabellen.

Så SELECT behörighet i tabellen Region kan uppnås genom någon av dessa sex instruktioner:

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;

Bevilja minst behörighet

Den första behörigheten som anges tidigare (GRANT SELECT ON OBJECT::Region TO Jae;) är den mest detaljerade. Det uttalandet är den minsta möjliga behörigheten som beviljar SELECT. Det finns inga behörigheter för underordnade objekt. Det är en bra princip att alltid bevilja minsta möjliga behörighet, men du bör överväga att bevilja på högre nivåer för att förenkla beviljandesystemet.

Så om Jae behöver behörighet till hela schemat, beviljar du SELECT en gång på schemanivå i stället för att bevilja SELECT många gånger på tabell- eller vy-nivå. Databasens utformning kan avsevärt påverka hur framgångsrik den här strategin kan vara. Den här strategin fungerar bäst när databasen är utformad så att objekt som behöver identiska behörigheter inkluderas i ett enda schema.

Diagram över behörigheter

Följande bild visar behörigheter och deras relationer till varandra. Vissa behörigheter på högre nivå (till exempel CONTROL SERVER) visas många gånger. I den här artikeln är affischen alldeles för liten för att läsa. Du kan ladda ned den fullstora postern över behörigheter för databasmotorn i PDF-format.

En bild som visar relationerna mellan databasmotorns huvudnamn och server- och databasobjekt finns i Behörighetshierarki (databasmotor).

Behörigheter jämfört med fasta server- och fasta databasroller

Behörigheterna för fasta serverroller och fasta databasroller liknar, men inte exakt samma som, detaljerade behörigheter. Medlemmar i den fasta sysadmin-serverrollen har till exempel alla behörigheter för instansen av SQL Server, liksom inloggningar med behörigheten CONTROL SERVER .

Men att bevilja behörigheten CONTROL SERVER gör inte en inloggning till medlem i den fasta sysadmin-serverrollen , och att lägga till en inloggning till den fasta serverrollen sysadmin ger inte uttryckligen inloggningsbehörigheten CONTROL SERVER . Ibland kontrollerar en lagrad procedur behörigheter genom att kontrollera den fasta rollen och inte kontrollera den detaljerade behörigheten.

Om du till exempel vill koppla bort en databas krävs medlemskap i db_owner fast databasrollsfunktion. Motsvarande CONTROL DATABASE behörighet räcker inte. Dessa två system fungerar parallellt men interagerar sällan med varandra. Microsoft rekommenderar att du använder det nyare, detaljerade behörighetssystemet i stället för fasta roller när det är möjligt.

Monitor permissions

Följande visningar returnerar säkerhetsinformation. Alla säkerhetsrelaterade vyer finns i Säkerhetskatalogvyer (Transact-SQL).

¹ Den här vyn är inte tillgänglig i SQL Database.

Examples

Följande satser returnerar användbar information om behörigheter.

A. Lista över databasbehörigheter för varje användare

Om du vill returnera explicita behörigheter som beviljats eller nekats i en databas (SQL Server och SQL Database) kör du följande Transact-SQL-instruktion i databasen.

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. Visa en lista över medlemmar i serverrollen

Kör följande instruktion för att returnera medlemmarna i serverrollerna (endast SQL Server).

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. Visa en lista över alla databashuvudnamn som är medlemmar i en databasnivåroll

Om du vill returnera medlemmarna i databasrollerna (SQL Server och SQL Database) kör du följande instruktion i databasen.

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;

Related content

• Säkerhet för SQL Server Database Engine och Azure SQL Database
• Security Functions (Transact-SQL)
• Säkerhetsrelaterade vyer och funktioner för dynamisk hantering (Transact-SQL)
• säkerhetskatalogvyer (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• Fastställa gällande behörigheter för databasmotorn
• Självstudie: Komma igång med databasmotorn
• Lektion 1: Skapa och fråga databasobjekt
• Självstudie: SQL Server Management Studio
• Handledning: Skriva Transact-SQL-satser