Introduzione alle autorizzazioni del motore di database

Si applica a: sìSQL Server (tutte le versioni supportate) Sìdatabase SQL di Azure SìIstanza gestita di SQL di Azure sìAzure Synapse Analytics sìParallel Data Warehouse

Le autorizzazioni del Motore di database 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 argomento illustra alcuni concetti di base sulla sicurezza e quindi descrive un'implementazione tipica delle autorizzazioni.

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 il linguaggio Transact-SQL elencato o con SQL Server Management Studio.

Logins

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 account di accesso basati sull'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 Serveri 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). Database SQL non supporta i ruoli predefiniti del server, ma ha due ruoli nel database master (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). 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 e 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 Active Directory o Azure Active Directory:

  1. Creare un utente di Windows 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 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 qui gli account di accesso con autenticazione di SQL Server .

  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 qui l'autenticazione di SQL Server per gli utenti di database indipendente.

  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 è un membro di un gruppo di Windows. Il gruppo di Windows ha un account di accesso in SQL Server o nel 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.

Assegnazione delle 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'argomento 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, con l'autorizzazione CREATE TABLE non è richiesta la clausola ON SECURABLE::NAME. Ad esempio, l'istruzione 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'esempio seguente viene concessa l'autorizzazione per lo schema e l'autorizzazione per qualsiasi tabella o vista contenuta in questo schema al ruolo denominato , che rappresenta un approccio più efficace e saldibile per l'assegnazione delle autorizzazioni rispetto a quello a livello di UPDATE Production singolo ProductionTeam 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 Ted è un membro del ruolo Sales. A Ted è stata concessa anche l'autorizzazione SELECT per la tabella OrderStatus con il proprio nome utente con l'istruzione GRANT SELECT ON OBJECT::OrderStatus TO Ted;. 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, Ted 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 Ted, come membro del ruolo Sales, verrà negata l'autorizzazione SELECT perché l'istruzione DENY per il gruppo Sales prevale sull'istruzione GRANTpersonale.

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 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 Ted;   
  
GRANT CONTROL ON OBJECT::Region TO Ted;   
  
GRANT SELECT ON SCHEMA::Customers TO Ted;   
  
GRANT CONTROL ON SCHEMA::Customers TO Ted;   
  
GRANT SELECT ON DATABASE::SalesDB TO Ted;   
  
GRANT CONTROL ON DATABASE::SalesDB TO Ted;  

Concedere l'autorizzazione minima

La prima autorizzazione elencata in precedenza (GRANT SELECT ON OBJECT::Region TO Ted;) è la più granulare, ovvero tale istruzione è l'autorizzazione minima possibile che concede l'autorizzazione SELECT. Non sono incluse autorizzazioni per gli oggetti subordinati. È una buona entità concedere sempre il minor numero possibile di autorizzazioni (è possibile leggere altre informazioni sul principio dei privilegi minimi),ma allo stesso tempo (contraddittorio) provare a concedere a livelli più elevati per semplificare il sistema di concessione. Se quindi Ted 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 ha un notevole impatto sull'efficacia 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, fin dall'inizio, pianificare chi o quali applicazioni accederanno a quali oggetti e in base a tale posizione, ovvero tabelle ma anche viste, funzioni e stored procedure negli schemi in base ai bucket di tipo di accesso per quanto possibile. Per altre informazioni su questo approccio, vedere questo post di blog di Mates Wolter Schema-design for SQL Server: recommendations for Schema design with security in mind.

Diagramma delle autorizzazioni

La figura 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. Fare clic sull'immagine per scaricare il poster relativo alle autorizzazioni del motore di database in formato pdf.

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

Monitoraggio delle 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. 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.

A. Elenco delle autorizzazioni per i 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],   
    dPrinc.name AS [to User Name]  
FROM sys.database_permissions AS perms  
JOIN sys.database_principals AS dPrinc  
    ON perms.grantee_principal_id = dPrinc.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 OUTER JOIN sys.server_principals AS members 
    ON server_role_members.member_principal_id = members.principal_id  
;

C. Elenco di 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], dPrinc.name AS [Members]  
FROM sys.database_role_members AS dRo  
JOIN sys.database_principals AS dPrinc  
    ON dRo.member_principal_id = dPrinc.principal_id  
JOIN sys.database_principals AS dRole  
    ON dRo.role_principal_id = dRole.principal_id;  

Passaggi successivi

Per altri argomenti introduttivi, vedere:

Vedere anche

Centro di sicurezza per il motore di database di SQL Server e il database SQL di Azure
Funzioni di sicurezza (Transact-SQL)
Funzioni e viste a gestione dinamica relative alla sicurezza (Transact-SQL)
Viste del catalogo relative alla sicurezza (Transact-SQL)
sys.fn_builtin_permissions (Transact-SQL)
Determinare le autorizzazioni valide per il motore di database