Introduzione alle autorizzazioni del motore di database

Articolo
07/15/2024

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

Le autorizzazioni nel motore di database del vengono gestite a livello di server tramite gli account di accesso e i ruoli del server e a livello di database tramite gli utenti e i ruoli del database. Il modello per il database SQL espone lo stesso sistema all'interno di ogni database, ma le autorizzazioni a livello di server non saranno disponibili. Questo articolo illustra alcuni concetti di base sulla sicurezza e quindi descrive un'implementazione tipica delle autorizzazioni.

Nota

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

Entità di sicurezza

Con entità di sicurezza si definiscono le identità che usano SQL Server e a cui è possibile assegnare delle autorizzazioni per eseguire varie 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 con Transact-SQL elencato o con SQL Server Management Studio.

Account di accesso

Gli account di accesso sono account utente singoli per l'accesso al motore di database di SQL Server. SQL Server e il database SQL supportano gli account di accesso basati sull’autenticazione di Windows e gli 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 costituiti da un set di ruoli preconfigurati che forniscono una serie appropriata 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 (Transact-SQL). SQL Server non supporta i ruoli predefiniti del server, ma ha due ruoli nel masterdatabase (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 assegnarvi 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 (Transact-SQL). Il 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 in un database ed eseguendo il mapping di tale utente di database all'account di accesso. In genere, il nome utente di database è identico al nome dell'account di accesso, anche se non è necessario. 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.

Gli utenti di database possono anche essere creati senza avere un account di accesso corrispondente. Gli utenti vengono denominati utenti di database indipendente. Microsoft promuove l'uso di utenti di database indipendente perché semplifica lo spostamento di un database in un altro server. 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 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 vedere un elenco di utenti, vedere CREATE USER (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 con 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

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

Se l'utente che si connette verrà connesso a molti database

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 qui gli account di accesso con autenticazione di SQL Server.
Nel database utente creare un utente di database per l'account di accesso che rappresenta i gruppi di Windows.
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.
Aggiungere gli utenti di database a uno o più ruoli del database definiti dall'utente.
Concedere le autorizzazioni ai ruoli del database definiti dall'utente.

Se l'utente che si connette verrà connesso a un solo database

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 qui l'autenticazione di SQL Server per gli utenti di database indipendente.
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.
Aggiungere gli utenti di database a uno o più ruoli del database definiti dall'utente.
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

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 riportato più avanti.
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 è ambiguo o non è appropriato 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 di concessione dell'esempio seguente concede l'autorizzazione UPDATE per la tabella o vista Parts contenuta nello schema Production al ruolo denominato PartsTeam:

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

Nell'istruzione di concessione dell’esempio seguente viene concessa UPDATEl'autorizzazione per lo schema Production e per estensione in qualsiasi tabella o vista contenuta in questo schema al ruolo denominato ProductionTeam, che rappresenta un approccio più efficace e vendibile 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 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.

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. A Jae è stata concessa anche l'autorizzazione SELECT per la tabella OrderStatus con il proprio nome utente con 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;in modo corretto, Jae conserverà l'accesso SELECT alla tabella OrderStatus con l'istruzione GRANT personale.
Se l'amministratore esegue DENY SELECT ON OBJECT::OrderStatus TO Sales; in modo non corretto, a Jae, come membro del ruolo Sales, verrà negata l'autorizzazione SELECT perché l'istruzione DENY per il gruppo Sales prevale sull'istruzione personale 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, 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 ottimo principio concedere sempre l'autorizzazione minima possibile (ulteriori informazioni sul principio del privilegio minimo, ma allo stesso tempo (contraddicendo ciò) tentare di concederla a livelli superiori per semplificare il sistema di concessione. Se quindi Jae deve ottenere le autorizzazioni per l'intero schema, concedere SELECT una sola volta a livello di schema, anziché concedere SELECT molte volte a livello di tabella o di 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 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 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 ma non esattamente uguali alle autorizzazioni granulari. Ad esempio, i membri del ruolo predefinito del server sysadmin hanno tutte le autorizzazioni per l'istanza di SQL Server, come gli account di accesso con l'autorizzazione CONTROL SERVER. Ma concedere l'autorizzazione CONTROL SERVER non rende un account di accesso membro del ruolo predefinito del server sysadmin e aggiungere un account di accesso al ruolo predefinito del server sysadmin non concede in modo esplicito l'autorizzazione CONTROL SERVER all'account 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 database db_owner . L'autorizzazione CONTROL DATABASE equivalente 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 vista non è disponibile nel 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 vista non è disponibile nel 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 vista non è disponibile nel database SQL.
Per altre viste correlate alla sicurezza, vedere Viste del catalogo relative alla 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 che sono membri di un ruolo a livello di database

Per restituire i membri dei ruoli del database (SQL Server e database di 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;

Condividi tramite