database SQL di Azure ruoli del server per la gestione delle autorizzazioni
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 DEFINITION da , 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 DEFINITION e, 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 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 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'esecuzioneDBCC FLUSHAUTHCACHE
per rendere effettive le modifiche di appartenenza.
- 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
IS_SRVROLEMEMBER()
non è supportato nelmaster
database.
Contenuto correlato
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