Condividi tramite

Introduzione alle autorizzazioni del motore di database

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure database SQL di Azure Synapse Analytics Platform System (PDW)in Microsoft Fabric

Questo articolo esamina alcuni concetti di base relativi alla sicurezza e quindi descrive un'implementazione tipica delle autorizzazioni. Le autorizzazioni nel motore di database vengono gestite a livello di server tramite account di accesso e ruoli del server e a livello di database tramite utenti del database e ruoli del database.

Il database SQL e il database SQL in Microsoft Fabric offrono le stesse opzioni all'interno di ogni database, ma le autorizzazioni a livello di server non sono disponibili.

Nota

Microsoft Entra ID era precedentemente conosciuto come Azure Active Directory (Azure AD).

Entità di sicurezza

Un principale di sicurezza è l'identità usata da SQL Server, a cui è possibile assegnare autorizzazioni per compiere azioni. Le entità di sicurezza sono in genere persone o gruppi di persone, ma possono essere altre entità che fanno finta di essere persone. Le entità di sicurezza possono essere create e gestite usando gli esempi di Transact-SQL illustrati in questo articolo o tramite SQL Server Management Studio.

Account di accesso

Gli account di accesso sono singoli account utente per l'accesso al motore di database di SQL Server. SQL Server e il database SQL supportano gli account di accesso in base all'autenticazione di Windows e agli account di accesso in base all'autenticazione di SQL Server. Per informazioni sui due tipi di account di accesso, vedere Scegliere una modalità di autenticazione.

Ruoli predefiniti del server

In SQL Server i ruoli predefiniti del server sono un set di ruoli preconfigurati che forniscono un gruppo pratico di autorizzazioni a livello di server. Gli account di accesso possono essere aggiunti ai ruoli con l'istruzione ALTER SERVER ROLE ... ADD MEMBER . Per altre informazioni, vedere ALTER SERVER ROLE. Il database SQL non supporta i ruoli predefiniti del server, ma ha due ruoli nel master database (dbmanager e loginmanager) che fungono da ruoli del server.

Ruoli del server definiti dall'utente

In SQL Server è possibile creare ruoli del server personalizzati e assegnare autorizzazioni a livello di server. Gli account di accesso possono essere aggiunti ai ruoli del server con l'istruzione ALTER SERVER ROLE ... ADD MEMBER . Per altre informazioni, vedere ALTER SERVER ROLE. Il database SQL non supporta i ruoli del server definiti dall'utente.

Utenti di database

Per concedere l'accesso di un login a un database, si crea un utente del database in tale database e si esegue il mapping dell'utente del database a un login. Il nome utente del database è in genere uguale al nome dell'account di accesso per convenzione, anche se non deve essere lo stesso. Ogni utente di database esegue il mapping a un singolo account di accesso. È possibile associare un account di accesso a un solo utente in un database, ma può essere associato come utente del database in diversi database.

Gli utenti di database possono anche essere creati senza avere un account di accesso corrispondente. Gli utenti vengono denominati utenti di database indipendente. Microsoft incoraggia l'uso di utenti di database indipendenti, perché semplifica lo spostamento del database in un server diverso. Analogamente agli account di accesso, gli utenti di database indipendente possono usare l'autenticazione di Windows o l'autenticazione di SQL Server. Per altre informazioni, vedere Rendere il database portabile utilizzando database indipendenti.

Esistono 12 tipi di utenti con piccole differenze per la modalità di autenticazione e la relativa rappresentazione. Per visualizzare un elenco di utenti, vedere CREATE USER.

Ruoli predefiniti del database

I ruoli predefiniti del database sono un set di ruoli preconfigurati che forniscono un gruppo pratico di autorizzazioni a livello di database. Gli utenti del database e i ruoli del database definiti dall'utente possono essere aggiunti ai ruoli predefiniti del database con l'istruzione ALTER ROLE ... ADD MEMBER. Per altre informazioni, vedere ALTER ROLE.

Ruoli del database definiti dall'utente

Gli utenti con l'autorizzazione CREATE ROLE possono creare nuovi ruoli di database definiti dall'utente per rappresentare gruppi di utenti con autorizzazioni comuni. In genere, le autorizzazioni vengono concesse o negate per l'intero ruolo, semplificando la gestione e il monitoraggio delle autorizzazioni. Gli utenti di database possono essere aggiunti ai ruoli del database con l'istruzione ALTER ROLE ... ADD MEMBER . Per altre informazioni, vedere ALTER ROLE.

Altre entità

Altre entità di sicurezza non descritte includono ruoli applicativi e logon e utenti basati su certificati o chiavi asimmetriche.

Per un grafico che mostra le relazioni tra utenti di Windows, gruppi di Windows, account di accesso e utenti di database, vedere Creare un utente di database.

Scenario tipico

L'esempio seguente rappresenta un metodo comune e consigliato di configurazione delle autorizzazioni.

In Windows Active Directory o Microsoft Entra ID

  1. Creare un utente per ogni utente.
  2. Creare gruppi di Windows che rappresentano le unità di lavoro e le funzioni di lavoro.
  3. Aggiungere gli utenti di Windows ai gruppi di Windows.

Se l'utente si connetterà a molti database

  1. Creare un account di accesso per i gruppi di Windows. Se si usa l'autenticazione di SQL Server, ignorare i passaggi di Active Directory e creare gli account di accesso per l'autenticazione di SQL Server qui.

  2. Nel database utente creare un utente di database per l'account di accesso che rappresenta i gruppi di Windows.

  3. Nel database utente creare uno o più ruoli del database definiti dall'utente, ognuno dei quali rappresenta una funzione simile, Ad esempio, si potrebbe avere un ruolo di analista finanziario e un ruolo di analista delle vendite.

  4. Aggiungere gli utenti di database a uno o più ruoli del database definiti dall'utente.

  5. Concedere le autorizzazioni ai ruoli del database definiti dall'utente.

Se l'utente si connetterà a un solo database

  1. Nel database utente creare un utente di database indipendente per il gruppo di Windows. Se si usa l'autenticazione di SQL Server, ignorare i passaggi di Active Directory e creare l'utente contenuto del database con l'autenticazione di SQL Server qui.

  2. Nel database utente creare uno o più ruoli del database definiti dall'utente, ognuno dei quali rappresenta una funzione simile, Ad esempio, si potrebbe avere un ruolo di analista finanziario e un ruolo di analista delle vendite.

  3. Aggiungere gli utenti di database a uno o più ruoli del database definiti dall'utente.

  4. Concedere le autorizzazioni ai ruoli del database definiti dall'utente.

Il risultato tipico a questo punto è che un utente di Windows è un membro di un gruppo di Windows. Il gruppo di Windows ha un account di accesso in SQL Server o database SQL. Viene eseguito il mapping dell'account di accesso a un'identità utente nel database utente. L'utente è un membro di un ruolo del database. È necessario ora aggiungere le autorizzazioni al ruolo.

Assegnare autorizzazioni

La maggior parte delle istruzioni di autorizzazione ha il formato seguente:

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

  • <authorization> deve essere GRANT, REVOKEo DENY.

  • <permission> Stabilisce l'azione consentita o vietata. Il numero esatto di autorizzazioni è diverso tra SQL Server e il database SQL di Azure. Per informazioni sulle autorizzazioni, vedere Autorizzazioni (motore di database) e fare riferimento al grafico più avanti in questo articolo.

  • ON <securable>::<name> specifica il tipo di oggetto a protezione diretta (server, oggetto server, database o oggetto di database) e il nome corrispondente. Alcune autorizzazioni non richiedono <securable>::<name> perché sono ambigue o inappropriate nel contesto. Ad esempio, l'autorizzazione CREATE TABLE non richiede la <securable>::<name> clausola (GRANT CREATE TABLE TO Mary; consente a Mary di creare tabelle).

  • <principal> è l'entità di sicurezza (account di accesso, utente o ruolo) che riceve o perde l'autorizzazione. Concedere le autorizzazioni ai ruoli quando possibile.

L'istruzione di esempio seguente conferisce l'autorizzazione UPDATE nella Parts tabella o nella vista contenuta nello Production schema, nel ruolo denominato PartsTeam:

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

L'istruzione di esempio seguente concede l'autorizzazione UPDATE , per lo Production schema e per estensione a qualsiasi tabella o vista contenuta in questo schema, al ruolo denominato ProductionTeam, che è un approccio più efficace e scalabile rispetto all’assegnazione di autorizzazioni a livello di singolo oggetto.

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Le autorizzazioni vengono concesse alle entità di sicurezza (account di accesso, utenti e ruoli) con l'istruzione GRANT . Le autorizzazioni vengono negate in modo esplicito con il comando DENY . Un'autorizzazione concessa o negata in precedenza viene rimossa con l'istruzione REVOKE . Le autorizzazioni sono cumulative, con l'utente che riceve tutte le autorizzazioni concesse all'utente, all'account di accesso e a qualsiasi appartenenza a un gruppo. Tuttavia, la negazione di un'autorizzazione prevale su tutte le concessioni.

Attenzione

Un errore comune consiste nel provare a rimuovere l'autorizzazione GRANT con DENY anziché REVOKE. Ciò può causare problemi quando un utente riceve le autorizzazioni da più origini, che può essere uno scenario comune. Nell'esempio seguente viene illustrato il principio .

Il gruppo Sales riceve le autorizzazioni SELECT sulla tabella OrderStatus tramite l'istruzione GRANT SELECT ON OBJECT::OrderStatus TO Sales;. L'utente Jae è un membro del Sales ruolo. Jae ha anche ricevuto SELECT l'autorizzazione per la OrderStatus tabella con il proprio nome utente tramite l'istruzione GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Si presuma che l'amministratore desideri rimuovere l'oggetto GRANT dal ruolo Sales.

  • Se l'amministratore esegue correttamente REVOKE SELECT ON OBJECT::OrderStatus TO Sales;, Jae mantiene l'accesso alla tabella OrderStatus tramite la sua istruzione individuale GRANT.

  • Se l'amministratore esegue DENY SELECT ON OBJECT::OrderStatus TO Sales; in modo non corretto, Jae, in qualità di membro del ruolo Sales, vede negata l'autorizzazione SELECT perché l'override di DENY di Sales prevale sul suo permesso individuale GRANT.

Nota

Le autorizzazioni possono essere configurate con Management Studio. Cercare l'oggetto a protezione diretta in Esplora oggetti, fare clic con il pulsante destro del mouse sull'oggetto e quindi selezionare Proprietà. Selezionare la pagina Autorizzazioni . Per informazioni sull'uso della pagina delle autorizzazioni, vedere Permissions or Securables Page.

Gerarchia delle autorizzazioni

Le autorizzazioni hanno una gerarchia padre/figlio, ovvero se si concede l'autorizzazione SELECT per un database, tale autorizzazione include l'autorizzazione SELECT per tutti gli schemi (figlio) presenti nel database. Se si concede l'autorizzazione SELECT per uno schema, tale autorizzazione include l'autorizzazione SELECT per tutte le tabelle e le viste (figlio) presenti nello schema. Le autorizzazioni sono transitive: se si concede SELECT l'autorizzazione per un database, include SELECT l'autorizzazione per tutti gli schemi (figlio) e tutte le tabelle e le viste (nipote).

Le autorizzazioni contengono anche le autorizzazioni implicite. L'autorizzazione CONTROL per un oggetto in genere fornisce tutte le altre autorizzazioni per l'oggetto .

Poiché sia la gerarchia padre/figlio che la gerarchia implicita possono agire sulla stessa autorizzazione, il sistema di autorizzazioni può diventare complicato. Si prenda, ad esempio, una tabella (Region), in uno schema (Customers), in un database (SalesDB).

  • CONTROLL'autorizzazione per la tabella Region include tutte le altre autorizzazioni per la tabella Region, tra cui ALTER, , UPDATESELECTINSERT, DELETE, e altre autorizzazioni.

  • SELECT nello schema Customers che possiede la tabella Region include l'autorizzazione SELECT sulla tabella Region.

Puoi quindi ottenere l'autorizzazione sulla tabella Region tramite una delle sei seguenti dichiarazioni:

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;

Concedere l'autorizzazione minima

La prima autorizzazione elencata in precedenza (GRANT SELECT ON OBJECT::Region TO Jae;) è la più granulare. Questa dichiarazione è l'autorizzazione minima possibile che concede all'oggetto SELECT. Non sono incluse autorizzazioni per gli oggetti subordinati. È un buon principio concedere sempre il minor numero possibile di autorizzazioni, ma è consigliabile concedere a livelli più elevati, per semplificare il sistema di concessione.

Pertanto, se Jae deve disporre dell'autorizzazione per l'intero schema, concedere SELECT una sola volta a livello di schema, anziché concedere SELECT più volte al livello di tabella o vista. La progettazione del database può influire notevolmente sulla riuscita di questa strategia. Questa strategia funziona meglio quando il database è progettato in modo che gli oggetti che necessitano di autorizzazioni identiche siano inclusi in un singolo schema.

Suggerimento

Quando si progetta un database e i relativi oggetti, pianificare fin dall'inizio il modo in cui le applicazioni e gli utenti accedono a tali oggetti. Utilizzare queste informazioni per controllare l'accesso a tabelle, viste, funzioni e stored procedure tramite schemi. Gli schemi consentono di raggruppare più facilmente i tipi di accesso.

Diagramma delle autorizzazioni

L’immagine seguente illustra le autorizzazioni e le relative relazioni. Alcune delle autorizzazioni di livello superiore (ad esempio CONTROL SERVER) sono elencate più volte. In questo articolo l'anteprima è molto piccola e non può essere consultata. È possibile scaricare il Poster relativo alle autorizzazioni del motore di database a dimensione intera in formato PDF.

Screenshot del PDF delle autorizzazioni del motore di database.

Per una rappresentazione grafica delle relazioni tra le entità del motore di database e gli oggetti server e di database, vedere Gerarchia delle autorizzazioni (motore di database).

Autorizzazioni e ruoli predefiniti del server e ruoli predefiniti del database

Le autorizzazioni dei ruoli predefiniti del server e dei ruoli predefiniti del database sono simili a, ma non esattamente uguali a autorizzazioni granulari. Ad esempio, i membri del ruolo predefinito del server sysadmin hanno tutte le autorizzazioni per l'istanza di SQL Server, come fanno gli account di accesso con l'autorizzazione CONTROL SERVER .

Tuttavia, la concessione dell'autorizzazione CONTROL SERVER non rende un account di accesso membro del ruolo predefinito del server sysadmin e l'aggiunta di un account di accesso al ruolo predefinito del server sysadmin non concede in modo esplicito all'account di accesso l'autorizzazione CONTROL SERVER . In alcuni casi una stored procedure controlla le autorizzazioni controllando il ruolo predefinito e non controllando l'autorizzazione granulare.

Ad esempio, lo scollegamento di un database richiede l'appartenenza al ruolo predefinito del database db_owner . L'autorizzazione CONTROL DATABASE equivalente non è sufficiente. Questi due sistemi operano in parallelo ma raramente interagiscono tra loro. Microsoft consiglia di usare il sistema di autorizzazioni più recente e granulare anziché i ruoli predefiniti quando possibile.

Monitorare le autorizzazioni

Le viste seguenti restituiscono informazioni sulla sicurezza. Per tutte le viste correlate alla sicurezza, vedere Viste del catalogo di sicurezza (Transact-SQL).

Visualizza Descrizione
sys.server_principals 1 Account di accesso e ruoli server definiti dall'utente in un server
sys.database_principals Utenti e ruoli definiti dall'utente in un database
sys.server_permissions 1 Autorizzazioni concesse agli account di accesso e ai ruoli predefiniti del server definiti dall'utente
sys.database_permissions Autorizzazioni concesse agli utenti e ai ruoli predefiniti del database definiti dall'utente
sys.database_role_members Appartenenza al ruolo del database
sys.server_role_members 1 Appartenenza al ruolo del server

1 Questa vista non è disponibile nel database SQL.

Esempi

Le istruzioni seguenti restituiscono informazioni utili sulle autorizzazioni.

R. Elenco di autorizzazioni di database per ogni utente

Per restituire le autorizzazioni esplicite concesse o negate in un database (SQL Server e database SQL), eseguire l'istruzione Transact-SQL seguente nel 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. Elencare i membri del ruolo del server

Per restituire i membri dei ruoli del server (solo SQL Server), eseguire l'istruzione seguente.

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. Elencare tutte le entità di database che sono membri di un ruolo a livello di database

Per restituire i membri dei ruoli del database (SQL Server e database SQL), eseguire l'istruzione seguente nel 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;

Contenuti correlati