Dela via

Säkerhet på radnivå

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-analysslutpunkt i Microsoft FabricLager i Microsoft FabricSQL-databas i Förhandsversion av Microsoft Fabric

Dekorativ bild av säkerhet på radnivå.

Med säkerhet på radnivå (RLS) kan du använda gruppmedlemskap eller exekveringskontext för att styra åtkomsten till rader i en databastabell.

Säkerhet på radnivå förenklar utformningen och kodningen av säkerhet i ditt program. RLS hjälper dig att implementera begränsningar för dataradsåtkomst. Du kan till exempel se till att arbetare endast kommer åt de datarader som är relevanta för deras avdelning. Ett annat exempel är att begränsa kundernas dataåtkomst till endast de data som är relevanta för deras företag.

Logiken för åtkomstbegränsning finns på databasnivån i stället för från data på en annan programnivå. Databassystemet tillämpar åtkomstbegränsningarna varje gång dataåtkomst görs från valfri nivå. Detta gör ditt säkerhetssystem mer tillförlitligt och robust genom att minska säkerhetssystemets yta.

Implementera RLS med hjälp av instruktionen CREATE SECURITY POLICY Transact-SQL och predikat som skapas som infogade tabellvärdesfunktioner.

Säkerhet på radnivå introducerades först i SQL Server 2016 (13.x).

Note

Den här artikeln fokuserar på SQL Server- och Azure SQL-plattformar. Information om Microsoft Fabric finns i Säkerhet på radnivå i Microsoft Fabric.

Description

Säkerhet på radnivå (RLS) stöder två typer av säkerhetspredikat:

  • Filterpredikat filtrerar tyst raderna som är tillgängliga för läsåtgärder (SELECT, UPDATE, och DELETE).

  • Blockpredikat blockerar skrivåtgärder (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) som bryter mot predikatet.

Åtkomst till data på radnivå i en tabell begränsas av en säkerhetspredikat som definieras som en infogad tabellvärdesfunktion. Funktionen anropas och framtvingas sedan av en säkerhetsprincip. För filterpredikat är programmet omedvetet om rader som filtreras från resultatuppsättningen. Om alla rader filtreras returneras en null-uppsättning. För blockpredikat misslyckas alla åtgärder som bryter mot predikatet med ett fel.

Filterpredikat tillämpas vid läsning av data från bastabellen. De påverkar alla get-åtgärder: SELECT, DELETEoch UPDATE. Användarna kan inte välja eller ta bort rader som filtreras. Användaren kan inte uppdatera rader som filtreras. Men det är möjligt att uppdatera rader på ett sådant sätt att de filtreras efteråt. Blockpredikat påverkar alla skrivåtgärder.

  • AFTER INSERT och AFTER UPDATE predikat kan hindra användare från att uppdatera rader till värden som bryter mot predikatet.

  • BEFORE UPDATE predikat kan hindra användare från att uppdatera rader som för närvarande bryter mot predikatet.

  • BEFORE DELETE predikat kan blockera borttagningsåtgärder.

Både filter- och blockpredikat och säkerhetsprinciper har följande beteende:

  • Du kan definiera en predikatfunktion som ansluter till en annan tabell och/eller anropar en funktion. Om säkerhetsprincipen skapas med SCHEMABINDING = ON (standard) är kopplingen eller funktionen tillgänglig från frågan och fungerar som förväntat utan extra behörighetskontroller. Om säkerhetsprincipen skapas med SCHEMABINDING = OFF, kommer användarna att behöva SELECT behörigheter för de ytterligare tabellerna och funktionerna för att utföra frågningar mot måltabellen. Om predikatfunktionen anropar en CLR-skalärvärdesfunktion krävs dessutom behörigheten EXECUTE .

  • Du kan utfärda en fråga mot en tabell som har ett definierat säkerhetspredikat men inaktiverat. Alla rader som filtreras eller blockeras påverkas inte.

  • Om en dbo användare, en medlem av db_owner rollen eller tabellägaren frågar en tabell som har en definierad och aktiverad säkerhetsprincip filtreras eller blockeras raderna enligt säkerhetsprincipens definition.

  • Försök att ändra schemat för en tabell som är bunden av en schemabunden säkerhetsprincip resulterar i ett fel. Kolumner som inte refereras till av predikatet kan dock ändras.

  • Försök att lägga till ett predikat i en tabell som redan har definierats för den angivna åtgärden resulterar i ett fel. Detta inträffar oavsett om predikatet är aktiverat eller inte.

  • Försök att ändra en funktion som används som predikat i en tabell i en schemabunden säkerhetsprincip resulterar i ett fel.

  • Det går att definiera flera aktiva säkerhetsprinciper som innehåller predikat som inte är överlappande.

Filterpredikat har följande beteende:

  • Definiera en säkerhetsprincip som filtrerar raderna i en tabell. Programmet känner inte till några rader som filtreras för SELECT, UPDATEoch DELETE åtgärder. Inklusive situationer där alla rader filtreras bort. Programmet kan INSERT rader, även om de filtreras under någon annan åtgärd.

Blockpredikat har följande beteende:

  • Blockpredikat för UPDATE delas upp i separata åtgärder för BEFORE och AFTER. Du kan till exempel inte blockera användare från att uppdatera en rad för att ha ett högre värde än det aktuella. Om den här typen av logik krävs måste du använda utlösare med de mellanliggande tabellerna DELETED och INSERTED för att referera till de gamla och nya värdena tillsammans.

  • Optimeraren kontrollerar inte ett AFTER UPDATE blockpredikat om kolumnerna som används av predikatfunktionen inte har ändrats. Till exempel: Alice ska inte kunna ändra en lön till över 100 000. Alice kan ändra adressen till en anställd vars lön redan är större än 100 000 så länge kolumnerna som refereras i predikatet inte har ändrats.

  • Inga ändringar har gjorts i mass-API:erna, inklusive BULK INSERT. Det innebär att blockpredikat AFTER INSERT gäller för massinfogningsåtgärder på samma sätt som vanliga infogningsåtgärder.

Användningsfall

Här är designexempel på hur säkerhet på radnivå (RLS) kan användas:

  • Ett sjukhus kan skapa en säkerhetsprincip som gör att sjuksköterskor endast kan visa datarader för sina patienter.

  • En bank kan skapa en princip för att begränsa åtkomsten till finansiella datarader baserat på en anställds affärsdivision eller roll i företaget.

  • Ett program med flera klienter kan skapa en princip för att framtvinga en logisk uppdelning av varje klients datarader från alla andra klientorganisationers rader. Effektivitet uppnås genom lagring av data för många klienter i en enda tabell. Varje klientorganisation kan bara se sina egna datarader.

RLS-filterpredikat är funktionellt likvärdiga med att lägga till en WHERE sats. Predikatet kan vara så avancerat som affärspraxis dikterar, eller så kan satsen vara så enkel som WHERE TenantId = 42.

I mer formella termer introducerar RLS predikatbaserad åtkomstkontroll. Den har en flexibel, centraliserad, predikatbaserad utvärdering. Predikatet kan baseras på metadata eller andra kriterier som administratören fastställer efter behov. Predikatet används som ett kriterium för att avgöra om användaren har rätt åtkomst till data baserat på användarattribut. Etikettbaserad åtkomstkontroll kan implementeras med hjälp av predikatbaserad åtkomstkontroll.

Permissions

För att skapa, ändra eller ta bort säkerhetsprinciper krävs behörigheten ALTER ANY SECURITY POLICY . För att skapa eller ta bort en säkerhetsprincip krävs ALTER behörighet för schemat.

Dessutom krävs följande behörigheter för varje predikat som läggs till:

  • SELECT och REFERENCES behörigheter för funktionen som används som predikat.

  • REFERENCES behörighet på måltabellen som är bunden till principen.

  • REFERENCES behörighet för varje kolumn från måltabellen som används som argument.

Säkerhetsprinciper gäller för alla användare, inklusive dbo-användare i databasen. Dbo-användare kan ändra eller släppa säkerhetsprinciper, men deras ändringar i säkerhetsprinciper kan granskas. Om högprivilegierade användare, till exempel sysadmin eller db_owner, behöver se alla rader för att felsöka eller verifiera data, måste säkerhetsprincipen skrivas för att tillåta det.

Om en säkerhetsprincip skapas med SCHEMABINDING = OFFmåste användarna ha behörigheten SELECT eller EXECUTE för predikatfunktionen och eventuella ytterligare tabeller, vyer eller funktioner som används i predikatfunktionen för att köra frågor mot måltabellen. Om en säkerhetsprincip skapas med SCHEMABINDING = ON (standardinställningen) kringgås dessa behörighetskontroller när användare kör frågor mot måltabellen.

Metodtips

  • Vi rekommenderar starkt att du skapar ett separat schema för RLS-objekten: predikatfunktioner och säkerhetsprinciper. Detta hjälper till att separera de behörigheter som krävs för dessa specialobjekt från måltabellerna. Ytterligare separation för olika principer och predikatfunktioner kan behövas i databaser med flera klientorganisationer, men inte som standard för varje fall.

  • Behörigheten ALTER ANY SECURITY POLICY är avsedd för högprivilegierade användare (till exempel en säkerhetsprinciphanterare). Säkerhetsprinciphanteraren kräver SELECT inte behörighet för de tabeller som de skyddar.

  • Undvik typkonverteringar i predikatfunktioner för att undvika potentiella körningsfel.

  • Undvik rekursion i predikatfunktioner där det är möjligt för att undvika prestandaförsämring. Frågeoptimeraren försöker identifiera direkta rekursioner, men är inte garanterad att hitta indirekta rekursioner. En indirekt rekursion är när en andra funktion anropar predikatfunktionen.

  • Undvik att använda för stora tabellkopplingar i predikatfunktioner för att maximera prestanda.

Undvik predikatlogik som är beroende av sessionsspecifika SET-alternativ: Även om det är osannolikt att de används i praktiska program, kan predikatfunktioner vars logik är beroende av vissa sessionsspecifika SET alternativ läcka information om användarna kan köra godtyckliga frågor. Till exempel kan en predikatfunktion som implicit konverterar en sträng till datetime filtrera olika rader baserat på SET DATEFORMAT alternativet för den aktuella sessionen. I allmänhet bör predikatfunktioner följa följande regler:

Säkerhetsanteckning: sidokanalattacker

Hanterare för skadlig säkerhetspolicy

Det är viktigt att observera att en ansvarig för skadlig säkerhetsprincip, med tillräcklig behörighet för att skapa en säkerhetsprincip ovanpå en känslig kolumn och har behörighet att skapa eller ändra infogade tabellvärdesfunktioner, kan samverka med en annan användare som har valt behörigheter i en tabell för att utföra dataexfiltrering genom att skadligt skapa infogade tabellvärdesfunktioner som är utformade för att använda sidokanalattacker för att härleda data. Sådana attacker skulle kräva samverkan (eller överdriven behörighet som beviljats en obehörig användare) och skulle sannolikt kräva flera iterationer av att ändra principen (kräver behörighet att ta bort predikatet för att bryta schemabindningen), ändra de infogade tabellvärdesfunktionerna och upprepade gånger köra select-instruktioner i måltabellen. Vi rekommenderar att du begränsar behörigheter efter behov och övervakar misstänkt aktivitet. Aktivitet som ständigt föränderliga policyer och infogade tabellvärdesfunktioner relaterade till säkerhet på radradnivå bör övervakas.

Noggrant utformade frågor

Det är möjligt att orsaka informationsläckage med hjälp av noggrant utformade frågor som använder fel för att exfiltera data. Till exempel SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; skulle låta en obehörig användare veta att John Doe lön är exakt $ 100,000. Även om det finns en säkerhetspredikat på plats för att förhindra att en obehörig användare direkt frågar andra personers lön, kan användaren avgöra när frågan returnerar ett divide-by-zero-undantag.

Kompatibilitet mellan funktioner

I allmänhet fungerar säkerhet på radnivå som förväntat för olika funktioner. Det finns dock några undantag. Det här avsnittet innehåller flera anteckningar och varningar för att använda säkerhet på radnivå med vissa andra funktioner i SQL Server.

  • DBCC SHOW_STATISTICS rapporterar statistik om ofiltrerade data och kan läcka information som annars skyddas av en säkerhetsprincip. Därför är åtkomsten till att visa ett statistikobjekt för en tabell med en säkerhetsprincip på radnivå begränsad. Användaren måste äga tabellen eller så måste användaren vara medlem i den sysadmin fasta serverrollen, den fasta databasrollen db_owner eller den fasta databasrollen db_ddladmin .

  • Filestream: RLS är inte kompatibelt med Filestream.

  • PolyBase: RLS stöds med externa tabeller i Azure Synapse och SQL Server 2019 CU7 eller senare versioner.

  • Memory-Optimized tabeller: Den infogade tabellvärdesfunktionen som används som säkerhetspredikat i en minnesoptimerad tabell måste definieras med hjälp av WITH NATIVE_COMPILATION alternativet . Med det här alternativet kommer språkfunktioner som inte stöds av minnesoptimerade tabeller att förbjudas och lämpligt fel utfärdas vid skapandetillfället. Mer information finns i Säkerhet på radnivå i Memory-Optimized-tabeller.

  • Indexerade vyer: I allmänhet kan säkerhetsprinciper skapas ovanpå vyer och vyer kan skapas ovanpå tabeller som är bundna av säkerhetsprinciper. Indexerade vyer kan dock inte skapas ovanpå tabeller som har en säkerhetsprincip, eftersom radsökningar via indexet skulle kringgå principen.

  • Change Data Capture: Change Data Capture (CDC) kan läcka hela rader som annars skulle ha filtrerats till personer som är medlemmar i db_owner eller användare som är medlemmar i den "gating"-roll som anges när CDC är aktiverat för en tabell. Du kan uttryckligen ställa in den här funktionen till NULL för att möjliggöra för alla användare att komma åt ändringsdata. I praktiken kan db_owner och medlemmar i den här gating-rollen se alla dataändringar i en tabell, även om det finns en säkerhetsprincip i tabellen.

  • Ändringsspårning: Ändringsspårning kan läcka den primära nyckeln för rader som ska filtreras till användare med både SELECT och VIEW CHANGE TRACKING behörigheter. Faktiska datavärden läcker inte; bara det faktum att kolumn A har uppdaterats/infogats/tagits bort för raden med en viss primärnyckel. Detta är problematiskt om primärnyckeln innehåller ett konfidentiellt element, till exempel ett personnummer. I praktiken är detta CHANGETABLE dock nästan alltid kopplat till den ursprungliga tabellen för att hämta de senaste data.

  • Fulltextsökning: En prestandapåverkan förväntas för frågor med hjälp av följande fulltextsöknings- och semantiska sökfunktioner, på grund av en extra join som introduceras för att tillämpa säkerhet på radnivå och undvika läckage av primärnycklarna för rader som ska filtreras: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • Kolumnlagringsindex: RLS är kompatibelt med både klustrade och icke-grupperade kolumnlagringsindex. Men eftersom säkerhet på radnivå tillämpar en funktion är det möjligt att optimeraren kan ändra frågeplanen så att den inte använder batchläge.

  • Partitionerade vyer: Blockpredikat kan inte definieras för partitionerade vyer och partitionerade vyer kan inte skapas ovanpå tabeller som använder blockpredikat. Filterpredikat är kompatibla med partitionerade vyer.

  • Temporala tabeller: Temporala tabeller är kompatibla med RLS. Säkerhetspredikat i den aktuella tabellen replikeras dock inte automatiskt till historiktabellen. Om du vill tillämpa en säkerhetsprincip på både de aktuella tabellerna och historiktabellerna måste du lägga till ett säkerhetspredikat individuellt i varje tabell.

Andra begränsningar:

  • Microsoft Fabric och Azure Synapse Analytics stöder endast filterpredikat. Blockpredikat stöds för närvarande inte i Microsoft Fabric och Azure Synapse Analytics.

Examples

A. Scenario för användare som autentiserar till databasen

Det här exemplet skapar tre användare och skapar och fyller en tabell med sex rader. Sedan skapas en infogad tabellvärdesfunktion och en säkerhetsprincip för tabellen. Sedan visar exemplet hur SELECT-satser filtreras för olika användare.

Skapa tre användarkonton som visar olika åtkomstfunktioner.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GO

Skapa en tabell för att lagra data.

CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
    (
    OrderID int,
    SalesRep nvarchar(50),
    Product nvarchar(50),
    Quantity smallint
    );

Fyll i tabellen med sex rader med data, som visar tre beställningar för varje försäljningsrepresentant.

INSERT INTO Sales.Orders  VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders  VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders  VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders  VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders  VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders  VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales.Orders;

Bevilja läsbehörighet i tabellen till var och en av användarna.

GRANT SELECT ON Sales.Orders TO Manager;
GRANT SELECT ON Sales.Orders TO SalesRep1;
GRANT SELECT ON Sales.Orders TO SalesRep2;
GO

Skapa ett nytt schema och en infogad tabellvärdesfunktion. Funktionen returnerar 1 när en rad i SalesRep kolumnen är samma som användaren som kör frågan (@SalesRep = USER_NAME()) eller om användaren som kör frågan är Manager-användaren (USER_NAME() = 'Manager'). Det här exemplet på en användardefinierad tabellvärdesfunktion är användbart för att fungera som ett filter för den säkerhetsprincip som skapas i nästa steg.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

Skapa en säkerhetsprincip som lägger till funktionen som ett filterpredikat. STATE måste anges till ON för att aktivera policyn.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);
GO

Tillåt SELECT behörigheter till tvf_securitypredicate funktionen:

GRANT SELECT ON Security.tvf_securitypredicate TO Manager;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2;

Testa nu filtreringspredikatet genom att välja från Sales.Orders tabellen som varje användare.

EXECUTE AS USER = 'SalesRep1';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'SalesRep2';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sales.Orders;
REVERT;

Chefen bör se alla sex raderna. Användarna Sales1 och Sales2 ska bara kunna se sin egen försäljning.

Ändra säkerhetsprincipen för att inaktivera principen.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Nu kan både Sales1- och Sales2-användare se alla sex raderna.

Anslut till SQL-databasen för att rensa resurser från den här exempelövningen:

DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;

B. Scenarier för att använda säkerhet på radnivå i en extern Azure Synapse-tabell

Det här korta exemplet skapar tre användare och en extern tabell med sex rader. Den skapar sedan en infogad tabellvärdesfunktion och en säkerhetsprincip för den externa tabellen. Exemplet visar hur SELECT-inställningar filtreras för de olika användarna.

Prerequisites

  1. Du måste ha en dedikerad SQL-pool. Se Skapa en dedikerad SQL-pool
  2. Servern som är värd för din dedikerade SQL-pool måste vara registrerad med Microsoft Entra-ID (tidigare Azure Active Directory) och du måste ha ett Azure Storage-konto med Storage Blog Data Contributor behörigheter. Följ stegen för att använda tjänstslutpunkter och regler för virtuella nätverk för servrar i Azure SQL Database.
  3. Skapa ett filsystem för ditt Azure Storage-konto. Använd Azure Storage Explorer för att visa ditt lagringskonto. Högerklicka på containrar och välj Skapa filsystem.

När du har förutsättningarna på plats skapar du tre användarkonton som visar olika åtkomstfunktioner.

--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO

--run in both the master database and in your dedicated SQL pool database
CREATE USER Manager FOR LOGIN Manager;
CREATE USER Sales1  FOR LOGIN Sales1;
CREATE USER Sales2  FOR LOGIN Sales2 ;

Skapa en tabell för att lagra data.

CREATE TABLE Sales
    (
    OrderID int,
    SalesRep sysname,
    Product varchar(10),
    Qty int
    );

Fyll i tabellen med sex rader med data, som visar tre beställningar för varje försäljningsrepresentant.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales;

Skapa en extern Azure Synapse-tabell från den Sales tabell som du skapade.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);

CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

Bevilja SELECT för de tre användarna på den externa tabellen Sales_ext som du skapade.

GRANT SELECT ON Sales_ext TO Sales1;
GRANT SELECT ON Sales_ext TO Sales2;
GRANT SELECT ON Sales_ext TO Manager;

Skapa ett nytt schema och en infogad tabellvärdesfunktion. Du kan ha slutfört detta i exempel A. Funktionen returnerar 1 när en rad i SalesRep kolumnen är densamma som användaren som kör frågan (@SalesRep = USER_NAME()) eller om användaren som kör frågan är Manager användaren (USER_NAME() = 'Manager').

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

Skapa en säkerhetsprincip i den externa tabellen med hjälp av den infogade tabellvärdesfunktionen som ett filterpredikat. STATE måste anges till ON för att aktivera policyn.

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext
WITH (STATE = ON);

Testa nu filtreringspredikatet genom att välja från den Sales_ext externa tabellen. Logga in som varje användare, Sales1, Sales2och Manager. Kör följande kommando för varje användare.

SELECT * FROM Sales_ext;

Det Manager bör se alla sex raderna. Användarna Sales1 och Sales2 bör bara se sin försäljning.

Ändra säkerhetsprincipen för att inaktivera principen.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Nu kan Sales1-användarna och Sales2-användarna se alla sex raderna.

Anslut till Azure Synapse-databasen för att rensa resurser från den här exempelövningen:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred;
DROP MASTER KEY;

Anslut till den logiska serverns master databas för att rensa resurser:

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;

C. Scenario för användare som ansluter till databasen via ett mellannivåprogram

Note

I det här exemplet stöds för närvarande inte blockpredikatfunktionen för Microsoft Fabric och Azure Synapse. Därför förhindras inte infogning av rader för fel användar-ID.

Det här exemplet visar hur ett mellannivåprogram kan implementera anslutningsfiltrering, där programanvändare (eller klientorganisationer) delar samma SQL Server-användare (programmet). Programmet anger det aktuella programanvändar-ID:t i SESSION_CONTEXT efter anslutning till databasen och sedan filtrerar säkerhetsprinciper transparent rader som inte ska vara synliga för det här ID:t och blockerar även användaren från att infoga rader för fel användar-ID. Inga andra appändringar krävs.

Skapa en tabell för att lagra data.

CREATE TABLE Sales (
    OrderId int,
    AppUserId int,
    Product varchar(10),
    Qty int
);

Fyll i tabellen med sex rader med data, som visar tre beställningar för varje programanvändare.

INSERT Sales VALUES
    (1, 1, 'Valve', 5),
    (2, 1, 'Wheel', 2),
    (3, 1, 'Valve', 4),
    (4, 2, 'Bracket', 2),
    (5, 2, 'Wheel', 5),
    (6, 2, 'Seat', 5);

Skapa en användare med låg behörighet som programmet ska använda för att ansluta.

-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;

-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;

Skapa ett nytt schema och predikatfunktion som använder programanvändar-ID:t som lagras i SESSION_CONTEXT() för att filtrera rader.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO

Skapa en säkerhetsprincip som lägger till den här funktionen som ett filterpredikat och ett blockpredikat på Sales. Blockpredikatet behöver bara AFTER INSERT eftersom BEFORE UPDATE och BEFORE DELETE redan är filtrerade, och AFTER UPDATE är onödigt eftersom kolumnen AppUserId inte kan uppdateras till andra värden på grund av kolumnbehörigheterna som tidigare satts.

CREATE SECURITY POLICY Security.SalesFilter
    ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales,
    ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales AFTER INSERT
    WITH (STATE = ON);

Nu kan vi simulera anslutningsfiltreringen genom att välja från Sales tabellen när du har angett olika användar-ID:t i SESSION_CONTEXT(). I praktiken ansvarar programmet för att ange det aktuella användar-ID SESSION_CONTEXT() :t när en anslutning har öppnats. Om du ställer in parametern @read_only på förhindras värdet från att 1 ändras igen tills anslutningen stängs (returneras till anslutningspoolen).

EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO

/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;

SELECT * FROM Sales;
GO

INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO

REVERT;
GO

Rensa databasresurser.

DROP USER AppUser;

DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

D. Scenario för att använda en uppslagstabell för säkerhetspredikatet

I det här exemplet används en uppslagstabell för länken mellan användaridentifieraren och värdet som filtreras, i stället för att behöva ange användaridentifieraren i faktatabellen. Den skapar tre användare och skapar och fyller en faktatabell, , Sample.Salesmed sex rader och en uppslagstabell med två rader. Sedan skapas en infogad tabellvärdesfunktion som kopplar faktatabellen till sökningen för att hämta användaridentifieraren och en säkerhetsprincip för tabellen. Sedan visar exemplet hur SELECT-satser filtreras för olika användare.

Skapa tre användarkonton som visar olika åtkomstfunktioner.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;

Skapa ett Sample schema och en faktatabell, Sample.Sales, för att lagra data.

CREATE SCHEMA Sample;
GO
CREATE TABLE Sample.Sales
    (
    OrderID int,
    Product varchar(10),
    Qty int
    );

Sample.Sales Fyll i med sex rader med data.

INSERT INTO Sample.Sales VALUES (1, 'Valve', 5);
INSERT INTO Sample.Sales VALUES (2, 'Wheel', 2);
INSERT INTO Sample.Sales VALUES (3, 'Valve', 4);
INSERT INTO Sample.Sales VALUES (4, 'Bracket', 2);
INSERT INTO Sample.Sales VALUES (5, 'Wheel', 5);
INSERT INTO Sample.Sales VALUES (6, 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sample.Sales;

Skapa en tabell för att lagra uppslagsdata – i det här fallet en relation mellan Salesrep och Product.

CREATE TABLE Sample.Lk_Salesman_Product
  ( Salesrep sysname,
    Product varchar(10)
  ) ;

Fyll i uppslagstabellen med exempeldata och länka en Product till varje säljare.

INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales1', 'Valve');
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales2', 'Wheel');
-- View the 2 rows in the table
SELECT * FROM Sample.Lk_Salesman_Product;

Bevilja läsbehörighet i faktatabellen till var och en av användarna.

GRANT SELECT ON Sample.Sales TO Manager;
GRANT SELECT ON Sample.Sales TO Sales1;
GRANT SELECT ON Sample.Sales TO Sales2;

Skapa ett nytt schema och en infogad tabellvärdesfunktion. Funktionen returnerar 1 när en användare frågar faktatabellen Sample.SalesSalesRep och kolumnen i tabellen Lk_Salesman_Product är densamma som användaren som kör frågan (@SalesRep = USER_NAME()) när den är ansluten till faktatabellen i Product kolumnen, eller om användaren som kör frågan är Manager användaren (USER_NAME() = 'Manager').

CREATE SCHEMA Security ;
GO
CREATE FUNCTION Security.fn_securitypredicate
         (@Product AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
           RETURN ( SELECT 1 as Result
                     FROM Sample.Sales f
            INNER JOIN Sample.Lk_Salesman_Product s
                     ON s.Product = f.Product
            WHERE ( f.product = @Product
                    AND s.SalesRep = USER_NAME() )
                 OR USER_NAME() = 'Manager'
                   ) ;

Skapa en säkerhetsprincip som lägger till funktionen som ett filterpredikat. STATE måste anges till ON för att aktivera policyn.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Product)
ON Sample.Sales
WITH (STATE = ON) ;

Tillåt SELECT behörigheter till fn_securitypredicate funktionen:

GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT SELECT ON Security.fn_securitypredicate TO Sales1;
GRANT SELECT ON Security.fn_securitypredicate TO Sales2;

Testa nu filtreringspredikatet genom att välja från Sample.Sales tabellen som varje användare.

EXECUTE AS USER = 'Sales1';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Valve' (as specified for 'Sales1' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Sales2';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Wheel' (as specified for 'Sales2' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sample.Sales;
-- This will return all rows with no restrictions
REVERT;

Det Manager bör se alla sex raderna. Användarna Sales1 och Sales2 ska bara kunna se sin egen försäljning.

Ändra säkerhetsprincipen för att inaktivera principen.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Nu kan både Sales1- och Sales2-användare se alla sex raderna.

Anslut till SQL-databasen för att rensa resurser från den här exempelövningen:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security;
DROP SCHEMA Sample;

E. Säkerhetsscenario på radnivå i Microsoft Fabric

Vi kan demonstrera säkerhetslager på radnivå och SQL-analysslutpunkt i Microsoft Fabric.

I följande exempel skapas exempeltabeller som fungerar med Warehouse i Microsoft Fabric, men i SQL-analysslutpunkten används befintliga tabeller. I SQL-analysslutpunkten kan du inte använda CREATE TABLE, men du kan använda CREATE SCHEMA, CREATE FUNCTIONoch CREATE SECURITY POLICY.

I det här exemplet skapar du först ett schema sales, en tabell sales.Orders.

CREATE SCHEMA sales;
GO

-- Create a table to store sales data
CREATE TABLE sales.Orders (
    SaleID INT,
    SalesRep VARCHAR(100),
    ProductName VARCHAR(50),
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);

-- Insert sample data
INSERT INTO sales.Orders (SaleID, SalesRep, ProductName, SaleAmount, SaleDate)
VALUES
    (1, 'Sales1@contoso.com', 'Smartphone', 500.00, '2023-08-01'),
    (2, 'Sales2@contoso.com', 'Laptop', 1000.00, '2023-08-02'),
    (3, 'Sales1@contoso.com', 'Headphones', 120.00, '2023-08-03'),
    (4, 'Sales2@contoso.com', 'Tablet', 800.00, '2023-08-04'),
    (5, 'Sales1@contoso.com', 'Smartwatch', 300.00, '2023-08-05'),
    (6, 'Sales2@contoso.com', 'Gaming Console', 400.00, '2023-08-06'),
    (7, 'Sales1@contoso.com', 'TV', 700.00, '2023-08-07'),
    (8, 'Sales2@contoso.com', 'Wireless Earbuds', 150.00, '2023-08-08'),
    (9, 'Sales1@contoso.com', 'Fitness Tracker', 80.00, '2023-08-09'),
    (10, 'Sales2@contoso.com', 'Camera', 600.00, '2023-08-10');

Skapa ett Security schema, en funktion Security.tvf_securitypredicateoch en säkerhetsprincip SalesFilter.

-- Creating schema for Security
CREATE SCHEMA Security;
GO

-- Creating a function for the SalesRep evaluation
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'manager@contoso.com';
GO

-- Using the function to create a Security Policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON sales.Orders
WITH (STATE = ON);
GO

När du har tillämpat säkerhetsprincipen och skapat funktionen kan användarna Sales1@contoso.com och Sales2@contoso.com bara se sina egna data i sales.Orders tabellen, där kolumnen SalesRep är lika med deras eget användarnamn som returneras av den inbyggda funktionen USER_NAME. Fabric-användaren manager@contoso.com kan se alla data i sales.Orders tabellen.

Relaterat innehåll