Condividi tramite

Ruoli del server del database SQL di Azure per la gestione delle autorizzazioni

  • Articolo

Si applica a: database SQL di Azure

Questo articolo descrive i ruoli predefiniti a livello di server nel database SQL di Azure.

Nota

I ruoli predefiniti a livello di server in questo articolo sono disponibili in anteprima pubblica per il database SQL di Azure. Questi ruoli a livello di server fanno parte anche della versione di SQL Server 2022.

Panoramica

Nel database SQL di Azure, il server è un concetto logico e le autorizzazioni non possono essere concesse a livello di server. Per semplificare la gestione delle autorizzazioni, il database SQL di Azure fornisce un set di ruoli predefiniti a livello di server che consentono di gestire le autorizzazioni in un server logico. I ruoli sono entità di sicurezza che raggruppano gli account di accesso.

Nota

Il concetto di ruoli in questo articolo è simile a quello di gruppi nel sistema operativo Windows.

Per questi ruoli predefiniti speciali a livello di server si usa il prefisso ##MS_ e il suffisso ## per distinguerli da altre entità normali create dall'utente.

Analogamente a SQL Server locale, le autorizzazioni del server sono organizzate gerarchicamente. Le autorizzazioni utilizzate da questi ruoli a livello di server possono essere propagate alle autorizzazioni del database. Affinché le autorizzazioni siano effettivamente utili a livello di database, un account di accesso deve essere un membro del ruolo ##MS_DatabaseConnector## a livello di server, che concede CONNECT a tutti i database o ha un account utente in singoli database. Questo vale anche per il database master virtuale.

Ad esempio, il ruolo ##MS_ServerStateReader## a livello di server contiene l'autorizzazione VIEW SERVER STATE. Se un account di accesso membro di questo ruolo dispone di un account utente nei database master e WideWorldImporters, l'utente dispone dell'autorizzazione VIEW DATABASE STATE in questi due database.

Nota

Qualsiasi autorizzazione può essere negata all'interno dei database utente, ignorando di fatto la concessione a livello di server tramite l'appartenenza al ruolo. Tuttavia, nel database di sistema master, le autorizzazioni non possono essere concesse o negate.

Il database SQL di Azure attualmente fornisce sette ruoli predefiniti del server. Le autorizzazioni concesse ai ruoli predefiniti del server non possono essere modificate e questi ruoli non possono avere altri ruoli predefiniti come membri. Puoi aggiungere account di accesso a livello di server come membri ai ruoli a livello di server.

Importante

Tutti i membri di un ruolo predefinito del server possono aggiungere altri account di accesso allo stesso ruolo.

Per altre informazioni su account di accesso e utenti del database SQL di Azure, vedi Autorizzare l'accesso al database a database SQL, Istanza gestita di SQL e Azure Synapse Analytics.

Ruoli predefiniti a livello di server

Nella tabella seguente vengono illustrati i ruoli predefiniti a livello di server e le relative funzionalità.

Ruolo predefinito a livello di server Descrizione
##MS_DatabaseConnector## I membri del ruolo predefinito del server ##MS_DatabaseConnector## possono connettersi a qualsiasi database senza la connessione a un account utente nel database.

Per negare l'autorizzazione CONNECT a un database specifico, gli utenti possono creare un account utente corrispondente per questo account di accesso nel database e quindi DENY l'autorizzazione CONNECT per l'utente del database. Questa autorizzazione DENY sovrascrive l'autorizzazione GRANT CONNECT proveniente da questo ruolo.
##MS_DatabaseManager## I membri del ruolo predefinito del server ##MS_DatabaseManager## possono creare ed eliminare database. Un membro del ruolo ##MS_DatabaseManager## che crea un database diventa il proprietario del database e questo permette all'utente di connettersi al database come utente dbo. L'utente dbo ha tutte le autorizzazioni database nel database. I membri del ruolo ##MS_DatabaseManager## non hanno necessariamente l'autorizzazione per accedere ai database di cui non sono proprietari. È consigliabile usare questo ruolo del server sul ruolo a livello di database dbmanager esistente in master.
##MS_DefinitionReader## I membri del ruolo predefinito del server ##MS_DefinitionReader## possono leggere tutte le viste del catalogo coperte da VIEW ANY DEFINITION, rispettivamente VIEW DEFINITION in qualsiasi database in cui il membro di questo ruolo dispone di un account utente.
##MS_LoginManager## I membri del ruolo predefinito del server ##MS_LoginManager## possono creare ed eliminare account di accesso. È consigliabile usare questo ruolo del server sul ruolo a livello di database loginmanager esistente in master.
##MS_SecurityDefinitionReader## I membri del ruolo predefinito del server ##MS_SecurityDefinitionReader## possono leggere tutte le viste del catalogo coperte da VIEW ANY SECURITY DEFINITION, rispettivamente avere l’autorizzazione VIEW SECURITY DEFINITION per qualsiasi database in cui il membro di questo ruolo dispone di un account utente. Si tratta di un piccolo sottoinsieme di ciò a cui il ruolo del server ##MS_DefinitionReader## ha accesso.
##MS_ServerStateManager## I membri del ruolo predefinito del server ##MS_ServerStateManager## hanno le stesse autorizzazioni del ruolo ##MS_ServerStateReader##. Inoltre, il ruolo dispone dell'autorizzazione ALTER SERVER STATE, che consente l'accesso a diverse operazioni di gestione, ad esempio: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF();
##MS_ServerStateReader## I membri del ruolo predefinito del server ##MS_ServerStateReader## possono leggere tutte le DMV (dynamic management view) e le funzioni coperte da VIEW SERVER STATE, rispettivamente VIEW DATABASE STATE in qualsiasi database in cui il membro di questo ruolo dispone di un account utente.

Autorizzazioni dei ruoli predefiniti del server

A ogni ruolo predefinito a livello di server vengono assegnate autorizzazioni specifiche. La tabella seguente mostra le autorizzazioni assegnate ai ruoli a livello di server. Mostra inoltre le autorizzazioni a livello di database, che vengono ereditate finché l'utente può connettersi a singoli database.

Ruolo predefinito a livello di server Autorizzazioni a livello di server Autorizzazioni a livello di database (se esiste un utente del database corrispondente all'account di accesso)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE, ALTER ANY DATABASE ALTER
##MS_DefinitionReader## VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW ANY SECURITY DEFINITION VIEW DEFINITION, VIEW SECURITY DEFINITION
##MS_LoginManager## CREATE LOGIN, ALTER ANY LOGIN N/D
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateManager## ALTER SERVER STATE, VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

Autorizzazioni

Solo l'account amministratore del server o l'account amministratore di Microsoft Entra (che può essere un gruppo di Microsoft Entra) può aggiungere altri account di accesso ai ruoli del server o rimuoverli. Questa caratteristica è specifica del database SQL di Azure.

Nota

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

Gestire i ruoli a livello di server

Nella tabella seguente vengono illustrate le viste di sistema e le funzioni che puoi utilizzare per gestire i ruoli a livello di server nel database SQL di Azure.

Funzionalità Tipo Descrizione
IS_SRVROLEMEMBER Metadati UFX Indica se un account di accesso di SQL è un membro del ruolo a livello di server specificato.
sys.server_role_members Metadati UFX Restituisce una riga per ogni membro di ogni ruolo a livello di server.
sys.sql_logins Metadati UFX Restituisce una riga per ogni account di accesso SQL.
ALTER SERVER ROLE Comando Modifica l’appartenenza di un ruolo del server.

Esempi

Gli esempi in questa sezione illustrano come gestire i ruoli a livello di server nel database SQL di Azure.

R. Aggiungere un account di accesso SQL a un ruolo a livello di server

Nell'esempio seguente l'account di accesso SQL Jiao viene aggiunto al ruolo a livello di server ##MS_ServerStateReader##. Questa istruzione deve essere eseguita nel database master virtuale.

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B. Elencare tutte le entità (autenticazione SQL) che sono membri di un ruolo a livello di server

L'istruzione seguente restituisce tutti i membri di qualsiasi ruolo a livello di server predefinito usando le viste del catalogo sys.server_role_members e sys.sql_logins. Questa istruzione deve essere eseguita nel database master virtuale.

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
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
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

C. Esempio completo: aggiungere un account di accesso a un ruolo a livello di server, recuperare i metadati per l'appartenenza ai ruoli e le autorizzazioni ed eseguire una query di test

Parte 1: Preparazione dell'appartenenza ai ruoli e dell'account utente

Esegui questo comando dal database master virtuale.

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
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
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

Questo è il set di risultati.

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

Esegui questo comando da un database utente.

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

Parte 2: Test dell'appartenenza ai ruoli

Accedi come account di accesso Jiao ed esegui la connessione al database utente usato nell'esempio.

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D. Controlla i ruoli a livello di server per gli account di accesso di Microsoft Entra

Esegui questo comando nel database master virtuale per visualizzare tutti gli account di accesso di Microsoft Entra che fanno parte dei ruoli a livello di server in database SQL. Per altre informazioni sugli account di accesso del server di Microsoft Entra, vedi Entità server di Microsoft Entra.

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
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
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. Controllare i ruoli del database master virtuale per account di accesso specifici

Esegui questo comando nel database master virtuale per verificare i ruoli di cui bob dispone o modificare il valore in modo che corrisponda all'entità.

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

Limitazioni dei ruoli a livello di server

  • Le assegnazioni di ruolo potrebbero richiedere fino a 5 minuti per diventare effettive. Anche per le sessioni esistenti, le modifiche apportate alle assegnazioni di ruolo del server non diventano effettive fino a quando la connessione non viene chiusa e riaperta. Ciò è dovuto all'architettura distribuita tra il database master e gli altri database nello stesso server logico.

    • Soluzione alternativa parziale: per ridurre il periodo di attesa e garantire che le assegnazioni di ruolo del server siano aggiornate in un database, un amministratore del server o un amministratore di Microsoft Entra può eseguire DBCC FLUSHAUTHCACHE nei database utente disponibili per l'account di accesso. Gli utenti attualmente connessi devono comunque riconnettersi dopo l'esecuzione di DBCC FLUSHAUTHCACHE per rendere effettive le modifiche di appartenenza.

  • IS_SRVROLEMEMBER() non è supportato nel database master.

Contenuto correlato