Implementare la sicurezza a livello di riga con il contesto di sessione in Generatore API dati

Usare la funzionalità del contesto di sessione di SQL per implementare la sicurezza a livello di riga in Generatore API dati.

Prerequisiti

• Sql Server e database esistenti.
• CLI generatore di API dati. Installare l'interfaccia della riga di comando

Creare una tabella e dati SQL

Creare una tabella con dati fittizi da usare in questo scenario di esempio.

1. Connettersi al database SQL usando il client o lo strumento preferito.

2. Creare una tabella denominata Revenues con le colonne id, category, revenue e accessible_role.

   DROP TABLE IF EXISTS dbo.Revenues;
   
   CREATE TABLE dbo.Revenues(
       id int PRIMARY KEY,  
       category varchar(max) NOT NULL,  
       revenue int,  
       accessible_role varchar(max) NOT NULL  
   );
   GO
   

3. Inserire quattro righe di esempio nella Revenues tabella.

   INSERT INTO dbo.Revenues VALUES
       (1, 'Book', 5000, 'Oscar'),  
       (2, 'Comics', 10000, 'Oscar'),  
       (3, 'Journals', 20000, 'Hannah'),  
       (4, 'Series', 40000, 'Hannah')
   GO
   

   In questo esempio, la colonna accessible_role archivia il nome del ruolo che può accedere alla riga.

1. Testate i vostri dati con una semplice query SELECT *.

   SELECT * FROM dbo.Revenues
   

2. Creare una funzione denominata RevenuesPredicate. Questa funzione filtra i risultati in base al contesto di sessione corrente.

   CREATE FUNCTION dbo.RevenuesPredicate(@accessible_role varchar(max))
   RETURNS TABLE
   WITH SCHEMABINDING
   AS RETURN SELECT 1 AS fn_securitypredicate_result
   WHERE @accessible_role = CAST(SESSION_CONTEXT(N'roles') AS varchar(max));
   

3. Creare un criterio di sicurezza denominato RevenuesSecurityPolicy usando la funzione .

   CREATE SECURITY POLICY dbo.RevenuesSecurityPolicy
   ADD FILTER PREDICATE dbo.RevenuesPredicate(accessible_role)
   ON dbo.Revenues;
   

(Facoltativo) Creare una stored procedure

Questa sezione illustra un semplice modello "hello world" per l'uso dei valori di contesto della sessione direttamente in T-SQL.

1. Creare una stored procedure che legge il valore del contesto della sessione roles e lo utilizza per filtrare i risultati.

   CREATE OR ALTER PROCEDURE dbo.GetRevenuesForCurrentRole
   AS
   BEGIN
       SET NOCOUNT ON;
   
       DECLARE @role varchar(max) = CAST(SESSION_CONTEXT(N'roles') AS varchar(max));
   
       SELECT id, category, revenue, accessible_role
       FROM dbo.Revenues
       WHERE accessible_role = @role;
   END
   GO
   

Eseguire lo strumento

Eseguire lo strumento Data API Builder (DAB) per generare un file di configurazione e una singola entità.

1. Creare una nuova configurazione impostando --set-session-context su true.

   dab init \
       --database-type mssql \
       --connection-string "<sql-connection-string>" \
       --set-session-context true \
       --auth.provider Simulator
   

   Quando il contesto di sessione è abilitato per SQL Server, Generatore API dati invia attestazioni utente autenticate a SQL chiamando sp_set_session_context (ad esempio, roles). L'abilitazione del contesto della sessione disabilita anche la memorizzazione nella cache delle risposte per l'origine dati.

1. Aggiungere una nuova entità denominata revenue per la dbo.Revenues tabella.

   dab add revenue \
       --source "dbo.Revenues" \
       --permissions "Authenticated:read"
   

2. Avviare lo strumento Generatore API dati.

   dab start
   

3. Eseguire una query sull'endpoint senza specificare un ruolo effettivo. Osservare che non vengono restituiti dati perché:

   • Per impostazione predefinita, il ruolo effettivo è Authenticated.
   • Nessuna riga ha accessible_role = 'Authenticated'.
   • I criteri di sicurezza filtrano i risultati quando il ruolo non corrisponde.

   curl http://localhost:5000/api/revenue
   

4. Eseguire una query sull'endpoint impostando il ruolo effettivo su Oscar. Osservare che i risultati filtrati includono solo le Oscar righe.

   curl -H "X-MS-API-ROLE: Oscar" http://localhost:5000/api/revenue
   

5. Ripeti utilizzando il ruolo Hannah.

   curl -H "X-MS-API-ROLE: Hannah" http://localhost:5000/api/revenue
   

Testare con GraphQL

Il contesto di sessione funziona anche con le query GraphQL.

query {
    revenues {
        items {
            id
            category
            revenue
            accessible_role
        }
    }
}

Trasmetti l'intestazione del ruolo:

curl -X POST http://localhost:5000/graphql \
    -H "Content-Type: application/json" \
    -H "X-MS-API-ROLE: Oscar" \
    -d '{"query": "{ revenues { items { id category revenue accessible_role } } }"}'

Cosa invia il Builder API Dati a SQL Server

Quando il contesto della sessione è abilitato, Generatore API dati imposta i valori di contesto della sessione in ogni richiesta prima di eseguire la query.

EXEC sp_set_session_context 'roles', 'Oscar', @read_only = 0;
-- Then executes your query
SELECT * FROM dbo.Revenues;

Tutte le attestazioni utente autenticate vengono inviate come coppie chiave-valore. Le attestazioni comuni includono roles, sub o oid, e qualsiasi attestazione personalizzata del fornitore di identità.

Test in SQL

Testare direttamente il filtro e il predicato in SQL per assicurarsi che funzioni.

1. Connettersi di nuovo a SQL Server usando il client o lo strumento preferito.

2. Esegui il sp_set_session_context per impostare manualmente l'attestazione roles del contesto sessione al valore statico Oscar.

   EXEC sp_set_session_context 'roles', 'Oscar';
   

3. Esegue una query tipica SELECT *. Osservare che i risultati vengono filtrati automaticamente usando il predicato.

   SELECT * FROM dbo.Revenues;  
   

4. (Facoltativo) Eseguire una query sulla tabella utilizzando la stored procedure.

   EXEC dbo.GetRevenuesForCurrentRole;
   

Pulire le risorse

Se si desidera rimuovere gli oggetti di esempio, eseguire:

DROP SECURITY POLICY IF EXISTS dbo.RevenuesSecurityPolicy;
DROP FUNCTION IF EXISTS dbo.RevenuesPredicate;
DROP PROCEDURE IF EXISTS dbo.GetRevenuesForCurrentRole;
DROP TABLE IF EXISTS dbo.Revenues;

Risoluzione dei problemi

• Nessun risultato restituito: verificare che i criteri di sicurezza siano attivi (SELECT * FROM sys.security_policies), controllare il valore del contesto di sessione (SELECT SESSION_CONTEXT(N'roles')) e verificare che --set-session-context true sia impostato nella configurazione di Generatore API dati.
• Tutte le righe restituite: verificare che i criteri di sicurezza non siano disabilitati (WITH STATE = OFF) e che il predicato restituisca 1 solo per le righe autorizzate.
• Problemi di prestazioni: indicizzare la colonna predicato (accessible_role) e valutare la possibilità di disabilitare temporaneamente i criteri per isolare l'impatto sulle prestazioni.

Contenuti correlati

• Funzionalità specifiche del database
• Usare gli ambienti