Introduzione alle autorizzazioni di motore di database

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di sistemi analitici (PDW)

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 modello per database SQL espone lo stesso sistema all'interno di ogni database, ma le autorizzazioni a livello di server non sono disponibili. Questo articolo esamina alcuni concetti di base relativi alla sicurezza e quindi descrive un'implementazione tipica delle autorizzazioni.

Nota

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

Entità di sicurezza

L'entità di sicurezza è il nome ufficiale delle identità che usano SQL Server e a cui è possibile assegnare l'autorizzazione per eseguire azioni. Si tratta in genere di utenti o gruppi di utenti, ma possono essere altre entità che fingono di essere utenti. Le entità di sicurezza possono essere create e gestite usando Transact-SQL elencate 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 database SQL supportano gli account di accesso basati su autenticazione di Windows e account di accesso basati sull'autenticazione di SQL Server. Per informazioni sui due tipi di account di accesso, vedere Choose an Authentication Mode.

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 edizione Standard RVER ROLE (Transact-SQL). 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 edizione Standard RVER ROLE (Transact-SQL). database SQL non supporta i ruoli del server definiti dall'utente.

Utenti di database

Agli account di accesso viene concesso l'accesso a un database creando un utente del database in un database e eseguendo il mapping dell'utente del database per l'accesso. In genere, il nome utente del database è uguale al nome dell'account di accesso, anche se non deve essere lo stesso. Ogni utente di database esegue il mapping a un singolo account di accesso. Il mapping di un account di accesso può essere eseguito a un solo utente in un database, ma può essere eseguito come utente di database in diversi database.

È anche possibile creare utenti di database che non dispongono di un account di accesso corrispondente. Questi utenti sono denominati utenti di database indipendenti. Microsoft incoraggia l'uso di utenti di database indipendenti perché semplifica lo spostamento del database in un server diverso. Come un account di accesso, un utente di database indipendente può usare autenticazione di Windows o l'autenticazione di SQL Server. Per altre informazioni, vedere Utenti di database indipendente: rendere portabile un database.

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 U edizione Standard R (Transact-SQL).

Ruoli predefiniti del database

I ruoli predefiniti del database sono costituiti da un set di ruoli preconfigurati che forniscono una serie appropriata 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 usando l'istruzione ALTER ROLE ... ADD MEMBER . Per altre informazioni, vedere ALTER ROLE (Transact-SQL).

Ruoli del database definiti dall'utente

Gli utenti con l'autorizzazione CREATE ROLE possono creare nuovi ruoli del 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 (Transact-SQL).

Altre entità

Altre entità di sicurezza non illustrate nel presente articolo includono ruoli applicazione nonché account di accesso 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 Create a Database User.

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 persona.

  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 che si connette verrà connesso 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 analista finanziario e analista 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 che si connette verrà connesso 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'autenticazione sql Server dell'utente del database indipendente qui.

  2. Nel database utente creare uno o più ruoli del database definiti dall'utente, ognuno dei quali rappresenta una funzione simile, ad esempio analista finanziario e analista 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 è 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

Di seguito è riportato il formato della maggior parte delle istruzioni di autorizzazione:

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;
  • AUTHORIZATION deve essere GRANT, REVOKE o DENY.

  • PERMISSION stabilisce l'azione consentita o quella non consentita. Il numero esatto di autorizzazioni è diverso tra SQL Server e database SQL. Le autorizzazioni sono elencate nell'articolo Autorizzazioni (motore di database) e nel grafico a cui si fa riferimento di seguito.

  • 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 ON SECURABLE::NAME perché non sono ambigue o inappropriate nel contesto. Ad esempio, l'autorizzazione CREATE TABLE non richiede la ON 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 grant di esempio seguente concede l'autorizzazione UPDATE per la tabella o la Parts vista contenuta nello Production schema al ruolo denominato PartsTeam:

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

Nell'istruzione grant di esempio seguente viene concessa l'autorizzazione UPDATE per lo Production schema e per estensione in qualsiasi tabella o vista contenuta in questo schema al ruolo denominato ProductionTeam, che rappresenta un approccio più efficace e salable per l'assegnazione di autorizzazioni rispetto 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 tramite il DENY comando . 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.

Suggerimento

Un errore comune consiste nel provare a rimuovere l'autorizzazione GRANT con DENY anziché REVOKE. Ciò può causare problemi quando un utente riceve autorizzazioni da più origini, e tale circostanza è piuttosto comune. L'esempio seguente illustra l'uso delle entità.

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

  • Se l'amministratore esegue REVOKE SELECT ON OBJECT::OrderStatus TO Sales;correttamente , Jae manterrà SELECT l'accesso alla tabella OrderStatus tramite l'istruzione singola GRANT .

  • Se l'amministratore esegue DENY SELECT ON OBJECT::OrderStatus TO Sales; in modo non corretto Jae, come membro del ruolo Sales, verrà negata l'autorizzazione perché to Sales esegue l'override SELECTDENY del singolo GRANToggetto .

Nota

Le autorizzazioni possono essere configurate tramite Management Studio. Trovare l'entità a protezione diretta in Esplora oggetti, fare clic con il pulsante destro del mouse sull'entità a protezione diretta e quindi scegliere 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, ovvero se si concede l'autorizzazione SELECT per un database, tale autorizzazione include l'autorizzazione SELECT 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 concede in genere 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. Prendiamo ad esempio una tabella (Region) di uno schema (Customers) in un database (SalesDB).

  • CONTROL per la tabella Region include tutte le altre autorizzazioni per la tabella Region, tra cui ALTER, SELECT, INSERT, UPDATE, DELETEe altre autorizzazioni.

  • SELECT per lo schema Customers a cui appartiene la tabella Region include l'autorizzazione SELECT per la tabella Region.

L'autorizzazione SELECT per la tabella Region può quindi essere ottenuta con una di queste sei istruzioni:

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, ovvero tale istruzione è l'autorizzazione minima possibile che concede l'autorizzazione SELECT. Non sono incluse autorizzazioni per gli oggetti subordinati. È un buon principio concedere sempre il minimo permesso possibile (si può leggere di più sul principio dei privilegi minimi), ma allo stesso tempo (in contraddizione) cercare di concedere a livelli più elevati per semplificare il sistema di concessione. Pertanto, se Jae necessita delle autorizzazioni 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 viene progettato in modo tale che gli oggetti che devono ottenere autorizzazioni identiche vengano inclusi in un singolo schema.

Suggerimento

Quando si progetta un database e i relativi oggetti, dall'inizio, pianificare chi o quali applicazioni accederanno agli oggetti e in base a tale posizione, ovvero tabelle, ma anche viste, funzioni e stored procedure negli schemi in base ai bucket del tipo di accesso il più possibile.

Diagramma delle autorizzazioni

L'immagine seguente mostra le autorizzazioni e le relative relazioni tra loro. 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 delle autorizzazioni di motore di database di dimensioni complete in formato PDF.

A screenshot from the Database Engine permissions PDF.

Per un grafico che mostra le relazioni tra le entità di motore di database e gli oggetti server e database, vedere Gerarchia delle autorizzazioni (motore di database).

Autorizzazioni e ruoli predefiniti del server e del database predefiniti

Le autorizzazioni dei ruoli predefiniti del server e dei ruoli predefiniti del database sono simili ma non esattamente uguali alle autorizzazioni granulari. Ad esempio, i membri del ruolo predefinito del sysadmin server hanno tutte le autorizzazioni per l'istanza di SQL Server, come 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 sysadmin server non concede in modo esplicito l'autorizzazione CONTROL SERVER di accesso. Una stored procedure potrebbe talvolta verificare le autorizzazioni controllando il ruolo predefinito e non controllando l'autorizzazione granulare. Ad esempio, per scollegare un database è necessaria l'appartenenza al ruolo predefinito del db_owner database. L'autorizzazione equivalente CONTROL DATABASE non è sufficiente. Questi due sistemi operano in parallelo ma raramente interagiscono tra loro. Se possibile, Microsoft consiglia di usare il sistema più recente di autorizzazioni granulari anziché i ruoli predefiniti.

Monitorare le autorizzazioni

Le viste seguenti restituiscono informazioni sulla sicurezza.

  • Gli account di accesso e i ruoli del server definiti dall'utente in un server possono essere esaminati con la vista sys.server_principals . Questa visualizzazione non è disponibile in database SQL.

  • Gli utenti e i ruoli definiti dall'utente in un database possono essere esaminati con la vista sys.database_principals .

  • Le autorizzazioni concesse agli account di accesso e ai ruoli predefiniti del server definiti dall'utente possono essere esaminate con la vista sys.server_permissions . Questa visualizzazione non è disponibile in database SQL.

  • Le autorizzazioni concesse agli utenti e ai ruoli predefiniti del database definiti dall'utente possono essere esaminate con la vista sys.database_permissions .

  • L'appartenenza ai ruoli del database può essere esaminata con la vista sys.database_role_members .

  • L'appartenenza ai ruoli del server può essere esaminata con la vista sys.server_role_members . Questa visualizzazione non è disponibile in database SQL.

  • Per altre viste correlate alla sicurezza, vedere Viste del catalogo di sicurezza (Transact-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 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
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
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 JOIN sys.server_principals AS members
    ON server_role_members.member_principal_id = members.principal_id;

C. Elencare tutte le entità di database 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
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;

Vedi anche

Passaggi successivi