Satır düzeyi güvenlik

Şunlar için geçerlidir:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric'te SQL analitiği uç noktasıMicrosoft Fabric'te DepoMicrosoft Fabric'te SQL veritabanı

Satır düzeyi güvenlik (RLS), veritabanı tablosundaki satırlara erişimi denetlemek için grup üyeliğini veya yürütme bağlamını kullanmanızı sağlar.

Satır düzeyi güvenlik, uygulamanızda güvenliğin tasarımını ve kodlamasını basitleştirir. RLS, veri satırı erişiminde kısıtlamalar uygulamanıza yardımcı olur. Örneğin, çalışanların yalnızca kendi departmanlarıyla ilgili veri satırlarına erişmesini sağlayabilirsiniz. Bir diğer örnek de müşterilerin veri erişimini yalnızca şirketleriyle ilgili verilerde kısıtlamaktır.

Erişim kısıtlama mantığı, başka bir uygulama katmanındaki verilerden uzakta değil veritabanı katmanında bulunur. Veritabanı sistemi, herhangi bir katmandan veri erişimi denenen her durumda erişim kısıtlamalarını uygular. Bu, güvenlik sisteminizin yüzey alanını azaltarak güvenlik sisteminizi daha güvenilir ve sağlam hale getirir.

CREATE SECURITY POLICY Transact-SQL ifadesini ve Satır içi tablo değerli fonksiyonlar olarak oluşturulan koşulları kullanarak RLS'yi uygulayın.

Satır düzeyi güvenlik ilk olarak SQL Server 2016(13.x) ile kullanıma sunulmuştur.

Note

Bu makale SQL Server ve Azure SQL platformlarına odaklanmıştır. Microsoft Fabric için bkz. Microsoft Fabric'te satır düzeyi güvenlik.

Description

Satır düzeyi güvenlik (RLS), iki tür güvenlik koşullarını destekler:

• Filtre önkoşulları, okuma işlemleri (SELECT, UPDATEve DELETE) için kullanılabilir satırları sessizce filtreler.

• Engelleme önkoşulları, koşulu ihlal eden yazma işlemlerini (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) açıkça engeller.

Tablodaki satır düzeyi verilere erişim, satır içi tablo değerli işlev olarak tanımlanan bir güvenlik koşuluyla kısıtlanır. İşlev, daha sonra bir güvenlik ilkesi tarafından çağrılır ve uygulanır. Filtre koşullarında, uygulama sonuç kümesinden filtrelenen satırların farkında değildir. Tüm satırlar filtrelenirse null bir küme döndürülür. Blok önermeleri için, önerme ile çelişen tüm işlemler bir hata ile başarısız olur.

Temel tablodan veriler okunurken filtre önkoşulları uygulanır. Tüm alma işlemlerini etkiler: SELECT, DELETE, ve UPDATE. Kullanıcılar filtrelenmiş satırları seçemez veya silemez. Kullanıcı filtrelenmiş satırları güncelleştiremez. Ancak, satırları daha sonra filtrelenecek şekilde güncelleştirmek mümkündür. Blok önkoşulları tüm yazma işlemlerini etkiler.

• AFTER INSERT ve AFTER UPDATE önkoşulları, kullanıcıların satırları koşulu ihlal eden değerlere güncelleştirmesini engelleyebilir.

• BEFORE UPDATE önkoşullar, kullanıcıların şu anda koşulu ihlal eden satırları güncelleştirmesini engelleyebilir.

• BEFORE DELETE önkoşulları silme işlemlerini engelleyebilir.

Hem filtre hem de engelleme önkoşulları ve güvenlik ilkeleri aşağıdaki davranışa sahiptir:

• Başka bir tabloyla birleştiren ve/veya işlev çağıran bir koşul işlevi tanımlayabilirsiniz. Güvenlik ilkesi ile SCHEMABINDING = ON oluşturulursa (varsayılan), birleştirme veya işlev sorgudan erişilebilir ve ek izin denetimleri olmadan beklendiği gibi çalışır. ile güvenlik ilkesi oluşturulduysa SCHEMABINDING = OFF, kullanıcıların hedef tabloyu sorgulamak için bu ek tablolarda ve işlevlerde izinlere sahip olması gerekir SELECT . Predikat fonksiyonu bir CLR skaler değerli işlevi çağırırsa, ek olarak EXECUTE iznine ihtiyaç duyulur.

• Tanımlanmış ancak devre dışı bırakılmış bir güvenlik koşulu olan bir tabloya yönelik bir sorgu yayımlayabilirsiniz. Filtrelenen veya engellenen satırlar etkilenmez.

• Kullanıcı dbo , rolün db_owner bir üyesi veya tablo sahibi güvenlik ilkesi tanımlanmış ve etkin olan bir tabloyu sorgularsa, satırlar güvenlik ilkesi tarafından tanımlandığı şekilde filtrelenir veya engellenir.

• Şemaya bağlı bir güvenlik ilkesine bağlı bir tablonun şemasını değiştirme girişimi hatayla sonuçlanır. Ancak koşul tarafından başvurulmayan sütunlar değiştirilebilir.

• Belirtilen işlem için zaten tanımlanmış olan bir tabloya koşul ekleme girişimleri hatayla sonuçlanır. Bu durum, ön koşul etkin hale getirilmiş olsun ya da olmasın ortaya çıkar.

• Şemaya bağlı bir güvenlik ilkesi içindeki bir tabloda koşul olarak kullanılan bir işlevi değiştirme girişimleri hatayla sonuçlanır.

• Örtüşmeme koşullarını içeren birden çok etkin güvenlik ilkesi tanımlama başarılı olur.

Filtre önkoşulları aşağıdaki davranışa sahiptir:

• Tablonun satırlarını filtreleyen bir güvenlik ilkesi tanımlayın. Uygulama, SELECT, UPDATE ve DELETE işlemleri için filtrelenen satırların farkında değildir. Tüm satırların filtrelendiği durumlar dahil. Uygulama, başka bir işlem sırasında filtrelense bile satırlar oluşturabilir INSERT .

Blok önkoşulları aşağıdaki davranışa sahiptir:

• UPDATE için blok önermeleri, BEFORE ve AFTER için ayrı işlemler olarak ayrılır. Örneğin, kullanıcıların geçerli satırdan daha yüksek bir değere sahip olacak şekilde satır güncelleştirmesini engelleyemezsiniz. Bu tür bir mantık gerekiyorsa, eski ve yeni değerlere birlikte başvurmak için DELETED ve INSERTED ara tablolarıyla tetikleyicileri kullanmanız gerekir.

• Optimizasyon aracı, şart işlevi tarafından kullanılan sütunlar değiştirilmediyse bir AFTER UPDATE blok koşul ifadesini denetlemez. Örneğin: Alice bir maaşı 100.000'den büyük olacak şekilde değiştirememelidir. Alice, önermede referans verilen sütunlar değiştirilmediği sürece maaşı zaten 100.000'den büyük olan bir çalışanın adresini değiştirebilir.

• dahil olmak üzere BULK INSERTtoplu API'lerde değişiklik yapılmadı. Bu, blok koşullarının AFTER INSERT , normal ekleme işlemleri gibi toplu ekleme işlemleri için de geçerli olduğu anlamına gelir.

Kullanım örnekleri

Satır düzeyi güvenliğin (RLS) nasıl kullanılabileceğini gösteren tasarım örnekleri aşağıda verilmiştir:

• Bir hastane, hemşirelerin yalnızca hastalarının veri satırlarını görüntülemesine olanak tanıyan bir güvenlik ilkesi oluşturabilir.

• Banka, bir çalışanın şirketteki iş bölümünü veya rolünü temel alarak finansal veri satırlarına erişimi kısıtlamak için bir ilke oluşturabilir.

• Çok kiracılı bir uygulama, her kiracının veri satırlarının diğer kiracı satırlarından mantıksal ayrımını zorunlu kılmak için bir ilke oluşturabilir. Verimlilikler, tek bir tablodaki birçok kiracı için verilerin depolanmasıyla elde edilir. Her kiracı yalnızca veri satırlarını görebilir.

RLS filtre koşulları işlevsel olarak bir WHERE koşul cümlesi eklemeye eşdeğerdir. Koşul, iş uygulamalarının belirttiği kadar karmaşık veya yan tümcesi kadar WHERE TenantId = 42basit olabilir.

Daha resmi bir ifadeyle RLS, koşul tabanlı erişim denetimi sağlar. Esnek, merkezi, koşul tabanlı değerlendirmeye sahiptir. Koşul, meta verileri veya yöneticinin uygun şekilde belirlediği diğer ölçütleri temel alabilir. Koşul, kullanıcının kullanıcı özniteliklerine göre verilere uygun erişimi olup olmadığını belirlemek için bir ölçüt olarak kullanılır. Etiket tabanlı erişim denetimi, koşul tabanlı erişim denetimi kullanılarak uygulanabilir.

Permissions

Güvenlik ilkelerini oluşturmak, değiştirmek veya bırakmak için izin gerekir ALTER ANY SECURITY POLICY . Güvenlik ilkesi oluşturmak veya bırakmak, şemada ALTER izni gerektirir.

Ek olarak, eklenen her koşul için aşağıdaki izinler gereklidir:

• SELECT ve REFERENCES koşul olarak kullanılan işlev üzerindeki izinler.

• REFERENCES ilkeye bağlı olan hedef tablo üzerindeki izin.

• REFERENCES bağımsız değişken olarak kullanılan hedef tablodaki her sütunda izin.

Güvenlik ilkeleri, veritabanındaki dbo kullanıcıları da dahil olmak üzere tüm kullanıcılar için geçerlidir. Dbo kullanıcıları güvenlik ilkelerini değiştirebilir veya bırakabilir ancak güvenlik ilkelerindeki değişiklikleri denetlenebilir. Sysadmin veya db_owner gibi yüksek ayrıcalıklı kullanıcıların veri sorunlarını gidermek veya doğrulamak için tüm satırları görmesi gerekiyorsa, buna izin vermek için güvenlik ilkesi yazılmalıdır.

Eğer SCHEMABINDING = OFF ile bir güvenlik ilkesi oluşturulursa, hedef tabloyu sorgulamak için kullanıcıların koşul işlevi ve koşul işlevinde kullanılan ek tablolar, görünümler veya işlevler üzerinde SELECT veya EXECUTE iznine sahip olmaları gerekir. ile SCHEMABINDING = ON bir güvenlik ilkesi oluşturulursa (varsayılan), kullanıcılar hedef tabloyu sorguladığında bu izin denetimleri atlanır.

En iyi yöntemler

• RLS nesneleri için ayrı bir şema oluşturmanız kesinlikle önerilir: koşul işlevleri ve güvenlik ilkeleri. Bu, bu özel nesneler üzerinde gerekli izinleri hedef tablolardan ayırmaya yardımcı olur. Çok kiracılı veritabanlarında farklı ilkeler ve koşul işlevleri için ek ayrım gerekebilir, ancak her durum için standart olarak gerekli olmayabilir.

• İzin ALTER ANY SECURITY POLICY , yüksek ayrıcalıklı kullanıcılara (güvenlik ilkesi yöneticisi gibi) yöneliktir. Güvenlik ilkesi yöneticisi, koruduğu tablolarda izin gerektirmez SELECT .

• Olası çalışma zamanı hatalarını önlemek için koşul işlevlerinde tür dönüştürmelerinden kaçının.

• Performans düşüşü önlemek için mümkün olan her yerde koşul işlevlerinde özyinelemeden kaçının. Sorgu iyileştirici doğrudan özyinelemeleri algılamaya çalışır, ancak dolaylı özyinelemeleri bulması garanti edilemez. Dolaylı özyineleme, ikinci bir işlevin koşul işlevini çağırdığı yerdir.

• Performansı en üst düzeye çıkarmak için koşul işlevlerinde aşırı tablo birleştirmeleri kullanmaktan kaçının.

Oturuma özgü SET seçeneklerine bağlı koşul mantığını kullanmaktan kaçının: Pratik uygulamalarda kullanılma olasılığı düşük olsa da, mantığı belirli oturuma özgü SET seçeneklere bağlı olan koşul işlevleri, kullanıcılar rastgele sorgular yürütebiliyorsa bilgileri sızdırabilir. Örneğin, bir dizeyi örtük olarak tarih saatine dönüştüren bir koşul işlevi, geçerli oturum seçeneğine SET DATEFORMAT göre farklı satırları filtreleyebilse de. Koşul işlevleri genel olarak aşağıdaki kurallara uymalıdır:

• Önerme işlevleri, karakter dizelerini date, smalldatetime, datetime, datetime2 veya datetimeoffset türlerine ya da tersi yönde örtük olarak dönüştürmemelidir, çünkü bu dönüştürmeler SET DATEFORMAT (Transact-SQL) ve SET LANGUAGE (Transact-SQL) seçeneklerinden etkilenir. Bunun yerine işlevini kullanın CONVERT ve stil parametresini açıkça belirtin.

• Koşul işlevleri haftanın ilk gününün değerine güvenmemelidir, çünkü bu değer SET DATEFIRST (Transact-SQL) seçeneğinden etkilenir.

• Koşul işlevleri, hata verirse (taşma veya sıfıra bölme gibi) NULL döndüren aritmetik veya toplama ifadelerine güvenmemelidir, çünkü bu davranış SET ANSI_WARNINGS (Transact-SQL), SET NUMERIC_ROUNDABORT (Transact-SQL) ve SET ARITHABORT (Transact-SQL) seçeneklerinden etkilenir.

• Predicate işlevleri, bu davranışın NULL seçeneğinden etkilendiğinden, birleştirilen dizeleri ile karşılaştırmamalıdır.

Güvenlik notu: yan kanal saldırıları

Zararlı güvenlik politikası yöneticisi

Hassas bir sütunun üzerinde güvenlik ilkesi oluşturmak için yeterli izinlere sahip olan ve satır içi tablo değerli işlevler oluşturma veya değiştirme iznine sahip kötü amaçlı bir güvenlik ilkesi yöneticisinin verileri çıkarmak için yan kanal saldırılarını kullanmak üzere tasarlanmış satır içi tablo değerli işlevler oluşturarak veri sızdırma gerçekleştirmek için tabloda belirli izinlere sahip olan başka bir kullanıcıyla işbirliği yapabildiğini gözlemlemek önemlidir. Bu tür saldırılar, iş birliği (veya kötü amaçlı bir kullanıcıya aşırı izinler verilmesi) gerektirebilir ve genellikle politikayı değiştirme (şema bağlamasını bozmak için koşulu kaldırma izni gerektiren), satır içi tablo değerli işlevleri değiştirme ve hedef tabloda tekrar tekrar SELECT deyimlerini çalıştırma gibi çeşitli yinelemeler gerektirir. İzinleri gerektiği gibi sınırlamanızı ve şüpheli etkinlikleri izlemenizi öneririz. Satır düzeyi güvenlikle ilgili sürekli değişen ilkeler ve satır içi tablo değerli işlevler gibi etkinlikler izlenmelidir.

Dikkatlice hazırlanmış sorgular

Verileri dışarı aktarmak için hataları kullanan dikkatlice hazırlanmış sorgular kullanarak bilgi sızıntısına neden olmak mümkündür. Örneğin, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; kötü niyetli bir kullanıcıya John Doe'nun maaşının tam olarak 100.000 ABD doları olduğunu bildirir. Kötü amaçlı bir kullanıcının diğer kişilerin maaşını doğrudan sorgulamasını önlemeye yönelik bir güvenlik koşulu olsa da, kullanıcı sorgunun sıfıra bölme özel durumu döndürdüğü zamanı belirleyebilir.

Özellikler arası uyumluluk

Genel olarak, satır düzeyi güvenlik özellikler arasında beklendiği gibi çalışır. Ancak, birkaç özel durum vardır. Bu bölümde, SQL Server'ın diğer bazı özellikleriyle satır düzeyi güvenliği kullanmaya yönelik çeşitli notlar ve uyarılar belgelenmiştir.

• DBCC SHOW_STATISTICS filtrelenmemiş verilerle ilgili istatistikleri raporlar ve bir güvenlik ilkesi tarafından korunan bilgileri sızdırabilir. Bu nedenle, satır düzeyi güvenlik ilkesine sahip bir tablonun istatistik nesnesini görüntüleme erişimi kısıtlanmıştır. Kullanıcının tabloya sahip olması veya kullanıcının sabit sunucu rolünün sysadmin , sabit veritabanı rolünün db_owner veya sabit veritabanı rolünün db_ddladmin üyesi olması gerekir.

• Dosya akışı: RLS, Dosya Akışı ile uyumsuz.

• PolyBase: RLS, Azure Synapse ve SQL Server 2019 CU7 veya üzeri sürümlerde dış tablolarla desteklenir.

• Bellek İçin İyileştirilmiş Tablolar: Bellek için iyileştirilmiş bir tabloda güvenlik koşulu olarak kullanılan satır içi tablo-değerli işlev, WITH NATIVE_COMPILATION olarak tanımlanmalıdır. Bu seçenekle, bellek için optimize edilmiş tablolar tarafından desteklenmeyen dil özellikleri yasaklanır ve oluşturma sırasında uygun bir hata mesajı verilir. Daha fazla bilgi için bkz. Bellek Optimizasyonlu Tablolarında satır seviyesinde güvenlik.

• Dizinlenmiş görünümler: Genel olarak, görünümlerin üzerinde güvenlik ilkeleri oluşturabilir ve güvenlik ilkelerine bağlı tablolar üzerine görünümler oluşturulabilir. Ancak dizine alınan görünümler, güvenlik ilkesi olan tabloların üzerinde oluşturulamaz çünkü dizin üzerinden yapılan satır aramaları ilkeyi atlar.

• Değişiklik Veri Yakalama: Değişiklik Veri Yakalama (CDC), bir tablo için CDC etkinleştirildiğinde belirtilen "gating" rolü üyelerine veya kullanıcılara filtrelenmesi gereken satırların tamamını açığa çıkarabilir. Bu işlevi NULL , tüm kullanıcıların değişiklik verilerine erişmesini sağlamak için açıkça olarak ayarlayabilirsiniz. Aslında, db_owner ve bu varlık rolünün üyeleri, tabloda bir güvenlik ilkesi olsa bile tablodaki tüm veri değişikliklerini görebilir.

• Değişiklik İzleme: Değişiklik İzleme, hem SELECT hem de VIEW CHANGE TRACKING izinlerine sahip kullanıcılara filtrelenmesi gereken satırların birincil anahtarını sızdırabilir. Gerçek veri değerleri sızdırılamaz; yalnızca belirli bir birincil anahtara sahip satır için A sütununun güncelleştirildiği/eklendiği/silindiği gerçeği. Birincil anahtar, Sosyal Güvenlik Numarası gibi gizli bir öğe içeriyorsa bu sorun olur. Ancak uygulamada, en son verileri almak için bu CHANGETABLE neredeyse her zaman özgün tabloyla birleştirilir.

• Full-Text Arama: Satır düzeyi güvenlik uygulamak ve filtrelenmesi gereken satırların birincil anahtarlarının sızdırılmasını önlemek için ek bir birleşim sağlandığından, aşağıdaki Full-Text Arama ve AnlamSal Arama işlevlerini kullanan sorgular için performans isabeti beklenir: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, , semanticsimilaritydetailstable, semanticsimilaritytable.

• Columnstore Dizinleri: RLS hem kümelenmiş hem de kümelenmemiş columnstore dizinleriyle uyumludur. Ancak satır düzeyi güvenlik bir işlevi uyguladığından, iyileştirici sorgu planını toplu iş modunu kullanmaması için değiştirebilir.

• Bölümlenmiş Görünümler: Blok önkoşulları bölümlenmiş görünümlerde tanımlanamaz ve bölümlenmiş görünümler, blok koşullarını kullanan tabloların üzerinde oluşturulamaz. Filtre önkoşulları bölümlenmiş görünümlerle uyumludur.

• Zamana bağlı tablolar: Zamana bağlı tablolar RLS ile uyumludur. Ancak, geçerli tablodaki güvenlik önkoşulları otomatik olarak geçmiş tablosuna çoğaltılamaz. Hem geçerli hem de geçmiş tablolarına bir güvenlik ilkesi uygulamak için, her tabloya ayrı ayrı bir güvenlik koşulu eklemeniz gerekir.

Diğer sınırlamalar:

• Microsoft Fabric ve Azure Synapse Analytics yalnızca filtre koşullarını destekler. Blok önkoşulları şu anda Microsoft Fabric ve Azure Synapse Analytics'te desteklenmemektedir.

Examples

A. Veritabanında kimlik doğrulaması yapan kullanıcılar için senaryo

Bu örnek üç kullanıcı oluşturur ve bir tabloyu altı satırla oluşturur ve doldurur. Ardından tablo için satır içi tablo değerli bir işlev ve bir güvenlik ilkesi oluşturur. Örnek daha sonra çeşitli kullanıcılar için select deyimlerinin nasıl filtrelendiğini gösterir.

Farklı erişim özelliklerini gösteren üç kullanıcı hesabı oluşturun.

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

Verileri tutmak için bir tablo oluşturun.

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

Tabloyu, her satış temsilcisi için üç siparişi gösteren altı veri satırıyla doldurun.

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;

Her kullanıcıya tabloda okuma erişimi verin.

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

Yeni bir şema oluşturun ve satır içi tablo değerli bir işlev oluşturun. İşlev, 1 sütunundaki bir satır sorguyu yürüten kullanıcı (SalesRep) ile aynı olduğunda veya sorguyu yürüten kullanıcı Yönetici kullanıcı (@SalesRep = USER_NAME()) olduğunda USER_NAME() = 'Manager' değerini döndürür. Kullanıcı tanımlı, tablo değerli işlevin bu örneği, bir sonraki adımda oluşturulan güvenlik ilkesi için filtre işlevi görecek şekilde kullanışlıdır.

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

İşlevi filtre koşulu olarak ekleyerek bir güvenlik ilkesi oluşturun. STATE İlkeyi etkinleştirmek için ON olarak ayarlanmalıdır.

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

İşlev için izinlere SELECT izin vertvf_securitypredicate:

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

Her kullanıcı adına tablodan seçerek filtreleme koşulunu Sales.Orders şimdi test edin.

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;

Yönetici altı satırın tümünü görmelidir. Sales1 ve Sales2 kullanıcıları yalnızca kendi satışlarını görmelidir.

İlkeyi devre dışı bırakmak için güvenlik ilkesini değiştirin.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Artık Sales1 ve Sales2 kullanıcılar altı satırın tümünü görebilir.

Bu örnek alıştırmadaki kaynakları temizlemek için SQL veritabanına bağlanın:

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. Azure Synapse dış tablosunda Satır Düzeyi Güvenliği kullanma senaryoları

Bu kısa örnek, üç kullanıcı ve altı satırlı bir dış tablo oluşturur. Ardından, dış tablo için satır içi tablo değerli bir işlev ve bir güvenlik ilkesi oluşturur. Örnekte, çeşitli kullanıcılar için select deyimlerinin nasıl filtrelendiği gösterilmektedir.

Prerequisites

1. Ayrılmış bir SQL havuzunuz olmalıdır. Bkz . Ayrılmış SQL havuzu oluşturma
2. Ayrılmış SQL havuzunuzu barındıran sunucunun Microsoft Entra Id (eski adıYla Azure Active Directory) ile kayıtlı olması ve izinlere sahip bir Azure depolama hesabınız Storage Blog Data Contributor olması gerekir. Azure SQL Veritabanı'ndaki sunucular için sanal ağ hizmet uç noktalarını ve kurallarını kullanma adımlarını izleyin.
3. Azure Depolama hesabınız için bir dosya sistemi oluşturun. Depolama hesabınızı görüntülemek için Azure Depolama Gezgini'ni kullanın. Kapsayıcılara sağ tıklayın ve Dosya sistemi oluştur'u seçin.

Önkoşulları karşıladıktan sonra, farklı erişim özelliklerini gösteren üç kullanıcı hesabı oluşturun.

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

Verileri tutmak için bir tablo oluşturun.

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

Tabloyu, her satış temsilcisi için üç siparişi gösteren altı veri satırıyla doldurun.

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;

Oluşturduğunuz tablodan Sales bir Azure Synapse dış tablosu oluşturun.

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;

Oluşturduğunuz dış tablodaki SELECT üç kullanıcıya vermeSales_ext.

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

Yeni bir şema ve satır içi tablo değerli fonksiyon oluşturun, bunu A örneğinde tamamlamış olabilirsiniz. Fonksiyon, 1 sütunundaki bir satır, sorguyu yürüten kullanıcı (SalesRep) ile aynı olduğunda veya sorguyu yürüten kullanıcı @SalesRep = USER_NAME() kullanıcısı (Manager) ise USER_NAME() = 'Manager' döndürür.

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';

Satır içi tablo değerli işlevini filtre koşulu olarak kullanarak dış tablonuzda bir güvenlik ilkesi oluşturun. STATE İlkeyi etkinleştirmek için ON olarak ayarlanmalıdır.

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

Şimdi dış tablodan seçim yaparak filtreleme koşulunu test edin Sales_ext . Her kullanıcı, Sales1, Sales2ve Managerolarak oturum açın. Her kullanıcı olarak aşağıdaki komutu çalıştırın.

SELECT * FROM Sales_ext;

Manager tüm altı satırı görmelidir. Sales1 ve Sales2 kullanıcıları yalnızca satışlarını görmelidir.

İlkeyi devre dışı bırakmak için güvenlik ilkesini değiştirin.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Artık Sales1 ve Sales2 kullanıcıları tüm altı satırı görebilir.

Bu örnek alıştırmadaki kaynakları temizlemek için Azure Synapse veritabanına bağlanın:

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;

Kaynakları temizlemek için mantıksal sunucunun master veritabanına bağlanın:

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

C. Orta katman uygulaması aracılığıyla veritabanına bağlanan kullanıcılar için senaryo

Note

Bu örnekte, blok önermeleri işlevselliği şu anda Microsoft Fabric ve Azure Synapse için desteklenmediğinden, yanlış kullanıcı kimliği için satır eklenmesi engellenmez.

Bu örnekte, bir orta katman uygulamasının bağlantı filtrelemeyi nasıl uygulayabileceği, uygulama kullanıcılarının (veya kiracıların) aynı SQL Server kullanıcısını (uygulama) paylaştığı durumlar gösterilmektedir. Uygulama, veritabanına bağlandıktan sonra geçerli uygulama kullanıcı kimliğini SESSION_CONTEXT ayarlar ve güvenlik ilkeleri bu kimliğe görünmemesi gereken satırları saydam bir şekilde filtreler ve kullanıcının yanlış kullanıcı kimliği için satır eklemesini engeller. Başka hiçbir uygulama değişikliği gerekmez.

Verileri tutmak için bir tablo oluşturun.

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

Tabloyu, her uygulama kullanıcısı için üç siparişi gösteren altı veri satırıyla doldurun.

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);

Uygulamanın bağlanmak için kullanacağı düşük ayrıcalıklı bir kullanıcı oluşturun.

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

Satırları filtrelemek için içinde SESSION_CONTEXT() depolanan uygulama kullanıcı kimliğini kullanacak yeni bir şema ve koşul işlevi oluşturun.

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

Bu işlevi filtre predikatı ve üzerinde Sales bir blok predikatı olarak ekleyen bir güvenlik ilkesi oluşturun. Blok koşulunun yalnızca AFTER INSERT öğesine ihtiyacı vardır çünkü BEFORE UPDATE ve BEFORE DELETE zaten filtrelendi ve AFTER UPDATE gereksizdir; çünkü AppUserId sütunu daha önce ayarlanan sütun izni nedeniyle diğer değerlere güncellenemez.

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);

Artık Sales içinde farklı kullanıcı kimlikleri ayarladıktan sonra SESSION_CONTEXT() tablodan seçim yaparak bağlantı filtrelemenin simülasyonunu oluşturabiliriz. Uygulamada uygulama, bağlantı açıldıktan sonra geçerli kullanıcı kimliğini SESSION_CONTEXT() ayarlamakla sorumludur. parametresini @read_only olarak 1 ayarlamak, bağlantı kapatılana kadar değerin yeniden değişmesini engeller (bağlantı havuzuna döndürülür).

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

Veritabanı kaynaklarını temizleyin.

DROP USER AppUser;

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

D. Güvenlik koşulu için arama tablosu kullanma senaryosu

Bu örnekte, olgu tablosunda kullanıcı tanımlayıcısını belirtmek yerine kullanıcı tanımlayıcısı ile filtrelenen değer arasındaki bağlantı için bir arama tablosu kullanılır. Üç kullanıcı oluşturur ve altı satırlık bir olgu tablosu Sample.Sales ve iki satırlık bir arama tablosu oluşturup doldurur. Ardından, kullanıcı tanımlayıcısını almak için olgu tablosunu aramaya ekleyen satır içi tablo değerli bir işlev ve tablo için bir güvenlik ilkesi oluşturur. Örnek daha sonra çeşitli kullanıcılar için select deyimlerinin nasıl filtrelendiğini gösterir.

Farklı erişim özelliklerini gösteren üç kullanıcı hesabı oluşturun.

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

Verileri tutmak için bir Sample şema ve olgu tablosu Sample.Salesoluşturun.

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

Sample.Sales altı sıralık veriyle doldurun.

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;

Arama verileri için bir tablo oluşturun – bu durumda Salesrep ile Product arasındaki ilişkiyi tabloya yerleştirin.

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

Her satış temsilcisine bir Product bağlayarak arama tablosunu örnek verilerle doldurun.

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;

Kullanıcıların her birine olgu tablosunda okuma erişimi verin.

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

Yeni bir şema ve satır içi tablo değerli fonksiyon oluşturun. Fonksiyon, bir kullanıcı olgu tablosunu sorguladığında ve bu tablonun 1 sütunu, sorguyu yürüten kullanıcıya ait Sample.Sales sütunu ile aynıysa veya sorguyu yürüten kullanıcı, SalesRep sütunu üzerinden olgu tablosuna katıldığında Lk_Salesman_Product döndürür. Ayrıca sorguyu yürüten kullanıcı @SalesRep = USER_NAME() kullanıcıysa Lk_Salesman_Product döndürülür.

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'
                   ) ;

İşlevi filtre koşulu olarak ekleyerek bir güvenlik ilkesi oluşturun. STATE İlkeyi etkinleştirmek için ON olarak ayarlanmalıdır.

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

İşlev için izinlere SELECT izin verfn_securitypredicate:

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

Her kullanıcı adına tablodan seçerek filtreleme koşulunu Sample.Sales şimdi test edin.

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;

Manager tüm altı satırı görmelidir. Sales1 ve Sales2 kullanıcıları yalnızca kendi satışlarını görmelidir.

İlkeyi devre dışı bırakmak için güvenlik ilkesini değiştirin.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Artık Sales1 ve Sales2 kullanıcılar altı satırın tümünü görebilir.

Bu örnek alıştırmadaki kaynakları temizlemek için SQL veritabanına bağlanın:

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. Microsoft Fabric'te satır düzeyi güvenlik senaryosu

Microsoft Fabric'te satır düzeyi güvenlik Ambarı ve SQL analiz uç noktasını gösterebiliriz.

Aşağıdaki örnek, Microsoft Fabric'te Warehouse ile çalışacak örnek tablolar oluşturur, ancak SQL analytics uç noktasında mevcut tabloları kullanır. SQL analizi uç noktasında kullanamazsınızCREATE TABLE, ancak , CREATE SCHEMAve CREATE FUNCTIONkullanabilirsinizCREATE SECURITY POLICY.

Bu örnekte, önce bir şema sales, bir tablo sales.Ordersoluşturun.

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');

Şema Security , işlev Security.tvf_securitypredicateve güvenlik ilkesi SalesFilteroluşturun.

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

Güvenlik ilkesini uyguladıktan ve işlevi oluşturduktan sonra, Sales1@contoso.com ve Sales2@contoso.com kullanıcıları, yerleşik işlev sales.Orders tarafından döndürülen kendi kullanıcı adlarına eşit olan SalesRep sütununda, tablosunda yalnızca kendi verilerini görebilir. Fabric kullanıcısı manager@contoso.com tablodaki sales.Orders tüm verileri görebilir.

İlgili içerik

• GÜVENLIK POLITIKASI OLUŞTUR (Transact-SQL)
• ALTER SECURITY POLICY (Transact-SQL)
• GÜVENLİK POLİTİKASINI KALDIR (Transact-SQL)
• fonksiyon oluştur (Transact-SQL)
• SESSION_CONTEXT (Transact-SQL)
• sp_set_session_context (Transact-SQL)
• sys.security_policies (Transact-SQL)
• sys.security_predicates (Transact-SQL)
• Kullanıcı tanımlı işlevler oluşturma (Veritabanı Altyapısı)
• GRANT nesne izinleri (Transact-SQL)