Clausola EXECUTE AS (Transact-SQL)

  • Articolo

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure

In SQL Server è possibile definire il contesto di esecuzione dei seguenti moduli definiti dall'utente: funzioni, ad eccezione delle funzioni inline con valori di tabella, procedure, code e trigger.

Specificando il contesto in cui viene eseguito il modulo, è possibile controllare quale account utente viene usato dal motore di database per convalidare le autorizzazioni per gli oggetti a cui viene fatto riferimento dal modulo. Ciò consente maggiore flessibilità e controllo nella gestione delle autorizzazioni all'interno della catena di oggetti esistente tra i moduli definiti dall'utente e gli oggetti cui viene fatto riferimento da tali moduli. È necessario concedere agli utenti le autorizzazioni solo nel modulo stesso, senza dover concedere loro le autorizzazioni esplicite per gli oggetti a cui viene fatto riferimento. Solo l'account utente con il quale viene eseguito il modulo deve disporre delle autorizzazioni per gli oggetti a cui ha accesso il modulo.

Convenzioni di sintassi Transact-SQL

Sintassi

In questa sezione viene descritta la sintassi di SQL Server per EXECUTE AS.

Funzioni (ad eccezione delle funzioni con valori di tabella inline), stored procedure e trigger DML:

{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

Trigger DDL con ambito database:

{ EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }

Trigger DDL con ambito server e trigger di accesso:

{ EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }

Code:

{ EXEC | EXECUTE } AS { SELF | OWNER | 'user_name' }

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

Argomenti

CALLER

Specifica che le istruzioni all'interno del modulo vengono eseguite nel contesto del chiamante del modulo. L'utente che esegue il modulo deve disporre delle autorizzazioni appropriate non solo per il modulo stesso, ma anche per tutti gli oggetti di database a cui fa riferimento il modulo.

CALLER è l'impostazione predefinita per tutti i moduli ad eccezione delle code e corrisponde al comportamento di SQL Server 2005 (9.x).

CALLER non può essere specificato in un'istruzione CREATE QUEUE o ALTER QUEUE .

SELF

EXECUTE AS SELF equivale a EXECUTE AS <user_name>, dove l'utente specificato è la persona che crea o modifica il modulo. L'ID utente effettivo della persona che crea o modifica i moduli viene archiviato nella execute_as_principal_id colonna nella vista del sys.sql_modules catalogo o sys.service_queues .

SELF è l'impostazione predefinita per le code.

Nota

Per modificare l'ID utente della execute_as_principal_id colonna nella sys.service_queues vista del catalogo, è necessario specificare in modo esplicito l'impostazione EXECUTE AS nell'istruzione ALTER QUEUE .

OWNER

Specifica che le istruzioni all'interno del modulo vengono eseguite nel contesto del proprietario corrente del modulo. Se il modulo non ha un proprietario specificato, viene usato il proprietario dello schema del modulo. OWNER non può essere specificato per i trigger DDL o di accesso.

Importante

OWNER deve eseguire il mapping a un account singleton e non può essere un ruolo o un gruppo.

'user_name'

Specifica che le istruzioni all'interno del modulo vengono eseguite nel contesto dell'utente specificato in user_name. Le autorizzazioni per gli oggetti del modulo vengono verificate in base a user_name. user_name non è possibile specificare per i trigger DDL con ambito server o trigger di accesso. Usare invece login_name.

user_name deve esistere nel database corrente e deve essere un account singleton. user_name non può essere un gruppo, un ruolo, un certificato, una chiave o un account predefinito, ad esempio NT AUTHORITY\LocalService, NT AUTHORITY\NetworkServiceo NT AUTHORITY\LocalSystem.

L'ID utente del contesto di esecuzione viene archiviato nei metadati e può essere visualizzato nella execute_as_principal_id colonna nella vista del sys.sql_modules catalogo o sys.assembly_modules .

'login_name'

Specifica che le istruzioni all'interno del modulo vengono eseguite nel contesto dell'account di accesso di SQL Server specificato in login_name. Le autorizzazioni per gli oggetti del modulo vengono verificate in base a login_name. È possibile specificare login_name solo per i trigger DDL con ambito server o i trigger LOGON.

login_name non può essere un gruppo, un ruolo, un certificato, una chiave o un account predefinito, ad esempio NT AUTHORITY\LocalService, NT AUTHORITY\NetworkServiceo NT AUTHORITY\LocalSystem.

Osservazioni:

La modalità in cui il motore di database valuta le autorizzazioni per gli oggetti cui viene fatto riferimento nel modulo dipende dalla catena di proprietà esistente tra gli oggetti chiamanti e gli oggetti cui viene fatto riferimento. Nelle versioni precedenti di SQL Server, la concatenazione della proprietà rappresenta l'unico metodo disponibile per evitare di dover concedere all'utente chiamante l'accesso a tutti gli oggetti a cui viene fatto riferimento.

La concatenazione della proprietà presenta le limitazioni seguenti:

  • Si applica solo alle istruzioni DML: SELECT, INSERT, UPDATEe DELETE.
  • I proprietari degli oggetti chiamanti devono corrispondere a quelli degli oggetti chiamati.
  • Non si applica alle query dinamiche all'interno del modulo.

A prescindere dal contesto di esecuzione specificato nel modulo, vengono sempre applicate le azioni seguenti:

  • Quando il modulo viene eseguito, il motore di database verifica prima che l'utente che esegue il modulo disponga EXECUTE dell'autorizzazione per il modulo.

  • Le regole di concatenazione della proprietà continuano ad essere applicate. Ciò vuol dire che se i proprietari degli oggetti chiamanti corrispondono a quelli degli oggetti chiamati, le autorizzazioni per gli oggetti sottostanti non vengono controllate.

Quando un utente esegue un modulo specificato per l'esecuzione in un contesto diverso CALLERda , viene verificata l'autorizzazione dell'utente per eseguire il modulo, ma vengono eseguite autorizzazioni aggiuntive per gli oggetti a cui si accede dal modulo sull'account utente specificato nella EXECUTE AS clausola . In sostanza, l'utente che esegue il modulo rappresenta l'utente specificato.

Il contesto specificato nella EXECUTE AS clausola del modulo è valido solo per la durata dell'esecuzione del modulo. Il contesto ritorna al chiamante al termine dell'esecuzione del modulo.

Specificare un utente o un nome di accesso

Un utente di database o un account di accesso al server specificato nella EXECUTE AS clausola di un modulo non può essere eliminato fino a quando il modulo non viene modificato per l'esecuzione in un altro contesto.

Il nome dell'utente o dell'account di accesso specificato nella EXECUTE AS clausola deve esistere rispettivamente come entità in sys.database_principals o sys.server_principalsoppure l'operazione di creazione o modifica del modulo non riesce. Inoltre, l'utente che crea o modifica il modulo deve disporre delle autorizzazioni IMPERSONATE per l'entità.

Se l'utente ha accesso implicito al database o all'istanza di SQL Server tramite un'appartenenza a un gruppo di Windows, l'utente specificato nella EXECUTE AS clausola viene creato in modo implicito quando il modulo viene creato quando esiste uno dei requisiti seguenti:

  • L'utente o l'account di accesso specificato è un membro del ruolo predefinito del server sysadmin.
  • L'utente che crea il modulo dispone dell'autorizzazione per creare entità.

Se non viene soddisfatto nessuno di questi requisiti, l'operazione di creazione del modulo non può essere completata.

Importante

Se il servizio SQL Server (MSSQL edizione Standard RVER) è in esecuzione come account locale (servizio locale o account utente locale), non avrà privilegi per ottenere le appartenenze ai gruppi di un account di dominio di Windows specificato nella EXECUTE AS clausola . Di conseguenza, l'esecuzione del modulo non potrà essere completata.

Si suppongano ad esempio le condizioni seguenti:

  • CompanyDomain\SQLUsers il gruppo ha accesso al Sales database.

  • CompanyDomain\SqlUser1 è un membro di SQLUsers e pertanto ha accesso al Sales database.

  • L'utente che crea o modifica il modulo possiede le autorizzazioni per creare entità.

Quando viene eseguita l'istruzione CREATE PROCEDURE seguente, CompanyDomain\SqlUser1 viene creato implicitamente come entità di database nel database Sales.

USE Sales;
GO
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'CompanyDomain\SqlUser1'
AS
SELECT USER_NAME();
GO

Usare l'istruzione autonoma EXECUTE AS CALLER

Usare l'istruzione EXECUTE AS CALLER autonoma all'interno di un modulo per impostare il contesto di esecuzione sul chiamante del modulo.

Presupporre che la stored procedure seguente venga chiamata da SqlUser2.

CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'SqlUser1'
AS
SELECT USER_NAME(); -- Shows execution context is set to SqlUser1.
EXECUTE AS CALLER;
SELECT USER_NAME(); -- Shows execution context is set to SqlUser2, the caller of the module.
REVERT;
SELECT USER_NAME(); -- Shows execution context is set to SqlUser1.
GO

Usare EXECUTE AS per definire set di autorizzazioni personalizzati

Specificare un contesto di esecuzione per un modulo può essere utile quando si desidera definire set di autorizzazioni personalizzati. Ad esempio, alcune azioni, ad TRUNCATE TABLE esempio, non dispongono di autorizzazioni concesse. Incorporando l'istruzione TRUNCATE TABLE all'interno di un modulo e specificando che il modulo viene eseguito come utente che dispone delle autorizzazioni per modificare la tabella, è possibile estendere le autorizzazioni per troncare la tabella all'utente a cui si concedono EXECUTE le autorizzazioni per il modulo.

Per visualizzare la definizione del modulo con il contesto di esecuzione specificato, usare la vista del catalogo sys.sql_modules (Transact-SQL).

Procedure consigliate

Specificare un account di accesso o un utente che dispone delle autorizzazioni minime necessarie per eseguire le operazioni definite nel modulo. Ad esempio, non specificare un account proprietario del database a meno che non siano necessarie tali autorizzazioni.

Autorizzazioni

Per eseguire un modulo specificato con EXECUTE AS, il chiamante deve disporre EXECUTE delle autorizzazioni per il modulo.

Per eseguire un modulo CLR specificato con EXECUTE AS che accede alle risorse in un altro database o server, il database o il server di destinazione deve considerare attendibile l'autenticatore del database da cui ha origine il modulo (il database di origine).

Per specificare la EXECUTE AS clausola quando si crea o si modifica un modulo, è necessario disporre IMPERSONATE delle autorizzazioni per l'entità specificata e anche le autorizzazioni per creare il modulo. È sempre possibile rappresentare se stessi. Quando non viene specificato alcun contesto di esecuzione o EXECUTE AS CALLER viene specificato, IMPERSONATE le autorizzazioni non sono necessarie.

Per specificare un login_name o un user_name con accesso implicito al database tramite un'appartenenza a un gruppo di Windows, è necessario disporre CONTROL delle autorizzazioni per il database.

Esempi

Nell'esempio seguente viene creata una stored procedure nel database AdventureWorks2022 e viene assegnato il contesto di esecuzione a OWNER.

CREATE PROCEDURE HumanResources.uspEmployeesInDepartment @DeptValue INT
    WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

SELECT e.BusinessEntityID,
    c.LastName,
    c.FirstName,
    e.JobTitle
FROM Person.Person AS c
INNER JOIN HumanResources.Employee AS e
    ON c.BusinessEntityID = e.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE edh.DepartmentID = @DeptValue
ORDER BY c.LastName,
    c.FirstName;
GO

-- Execute the stored procedure by specifying department 5.
EXECUTE HumanResources.uspEmployeesInDepartment 5;
GO

Contenuto correlato