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

  • Articolo

Si applica a:database SQL di Azure

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

Nota

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

Panoramica

In 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, 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 gruppi nel sistema operativo Windows.

Questi ruoli speciali a livello di server usano il prefisso ##MS_ e il suffisso ## per distinguere 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 virtuale master .

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 tali due database.

Nota

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

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. È possibile 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 database SQL di Azure account di accesso e utenti, vedere 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 ##MS_DatabaseConnector## server possono connettersi a qualsiasi database senza richiedere 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 DENY autorizzazione sovraruole l'autorizzazione GRANT CONNECT proveniente da questo ruolo.
##MS_DatabaseManager## I membri del ruolo predefinito del ##MS_DatabaseManager## server possono creare ed eliminare database. Un membro del ##MS_DatabaseManager## ruolo che crea un database diventa il proprietario di tale database, che consente all'utente di connettersi a tale database come dbo utente. L'utente dbo dispone di tutte le autorizzazioni di database nel database. I membri del ##MS_DatabaseManager## ruolo 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 ##MS_DefinitionReader## server possono leggere tutte le viste del catalogo coperte VIEW ANY DEFINITIONda , 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 ##MS_LoginManager## server 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 ##MS_SecurityDefinitionReader## server possono leggere tutte le viste del catalogo coperte da VIEW ANY SECURITY DEFINITIONe, rispettivamente, dispone VIEW SECURITY DEFINITION dell'autorizzazione per qualsiasi database in cui il membro di questo ruolo dispone di un account utente. Si tratta di un piccolo subset di ciò a cui il ruolo del ##MS_DefinitionReader## server ha accesso.
##MS_ServerStateManager## I membri del ruolo predefinito del ##MS_ServerStateManager## server hanno le stesse autorizzazioni del ##MS_ServerStateReader## ruolo. Contiene anche l'autorizzazione, che consente l'accesso ALTER SERVER STATE a diverse operazioni di gestione, ad esempio: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF();
##MS_ServerStateReader## I membri del ruolo predefinito del ##MS_ServerStateReader## server possono leggere tutte le viste a gestione dinamica (DMV) e le funzioni coperte rispettivamente 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 a livello di server predefinito sono assegnate determinate autorizzazioni. Nella tabella seguente vengono illustrate le autorizzazioni assegnate ai ruoli a livello di server. Vengono inoltre visualizzate le autorizzazioni a livello di database, 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 STATEVIEW 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 Microsoft Entra) può aggiungere o rimuovere altri account di accesso da o verso i ruoli del server. Questo è specifico per database SQL di Azure.

Nota

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

Usare i ruoli a livello di server

La tabella seguente illustra le viste di sistema e le funzioni che è possibile usare per usare i ruoli a livello di server in database SQL di Azure.

Funzionalità Tipo Descrizione
IS_SRVROLEMEMBER Metadati UFX Indica se un account di accesso 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 edizione Standard RVER ROLE Comando Modifica l'appartenenza di un ruolo del server.

Esempi

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

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

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

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 sys.server_role_members catalogo e sys.sql_logins . Questa istruzione deve essere eseguita nel database virtuale master .

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

Eseguire questo comando dal database virtuale master .

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

Eseguire 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

Accedere come account di accesso Jiao e connettersi 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. Controllare i ruoli a livello di server per gli account di accesso di Microsoft Entra

Eseguire questo comando nel database virtuale master 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 al server Microsoft Entra, vedere Entità server 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 virtuale master per gli account di accesso specifici

Eseguire questo comando nel database virtuale master per verificare con i ruoli bob 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

  • L'efficacia delle assegnazioni di ruolo potrebbe richiedere fino a 5 minuti. 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 master database e gli altri database nello stesso server logico.

    • Soluzione alternativa parziale: per ridurre il periodo di attesa e assicurarsi che le assegnazioni di ruolo del server siano correnti in un database, un amministratore del server o un amministratore di Microsoft Entra possano essere eseguite DBCC FLUSHAUTHCACHE nei database utente in cui l'account di accesso ha accesso. Gli utenti connessi correnti devono comunque riconnettersi dopo l'esecuzione DBCC FLUSHAUTHCACHE per rendere effettive le modifiche di appartenenza.

  • IS_SRVROLEMEMBER() non è supportato nel master database.

Contenuto correlato