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
Creare un utente per ogni persona.
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 gli account di accesso per l'autenticazione di SQL Server qui.
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 l'autenticazione sql Server dell'utente del database indipendente qui.
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 è 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 essereGRANT
,REVOKE
oDENY
.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 richiedonoON SECURABLE::NAME
perché non sono ambigue o inappropriate nel contesto. Ad esempio, l'autorizzazioneCREATE TABLE
non richiede laON 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 singolaGRANT
.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'overrideSELECT
DENY
del singoloGRANT
oggetto .
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 cuiALTER
,SELECT
,INSERT
,UPDATE
,DELETE
e altre autorizzazioni.SELECT
per lo schema Customers a cui appartiene la tabella Region include l'autorizzazioneSELECT
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.
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
- Centro 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 correlate alla sicurezza (Transact-SQL)
- Viste del catalogo di sicurezza (Transact-SQL)
- sys.fn_builtin_permissions (Transact-SQL)
- Determinare le autorizzazioni valide per il motore di database
Passaggi successivi
Commenti e suggerimenti
https://aka.ms/ContentUserFeedback.
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedereInvia e visualizza il feedback per