Bagikan melalui

Keamanan tingkat baris

  • Artikel

Berlaku untuk: Titik akhir analitik SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL di Microsoft Fabric Warehouse di Microsoft Fabric

Grafik dekoratif keamanan tingkat baris.

Keamanan tingkat baris (RLS) memungkinkan Anda menggunakan keanggotaan grup atau konteks eksekusi untuk mengontrol akses ke baris dalam tabel database.

Keamanan tingkat baris menyederhanakan desain dan pengodean keamanan dalam aplikasi Anda. RLS membantu Anda menerapkan pembatasan pada akses baris data. Misalnya, Anda dapat memastikan bahwa pekerja hanya mengakses baris data yang relevan dengan departemennya. Contoh lain adalah membatasi akses data pelanggan hanya ke data yang relevan dengan perusahaannya.

Logika pembatasan akses terletak di tingkat database daripada jauh dari data di tingkat aplikasi lain. Sistem database menerapkan pembatasan akses setiap kali akses data dicoba dari tingkat mana pun. Tindakan ini membuat sistem keamanan Anda lebih andal dan kuat dengan mengurangi luas permukaan sistem keamanan Anda.

Terapkan RLS dengan menggunakan pernyataan CREATE SECURITY POLICY Transact-SQL, dan predikat yang dibuat sebagai fungsi bernilai tabel sebaris.

Keamanan tingkat baris pertama kali diperkenalkan ke SQL Server 2016 (13.x).

Catatan

Artikel ini difokuskan pada platform SQL Server dan Azure SQL. Untuk Microsoft Fabric, lihat Keamanan tingkat baris di Microsoft Fabric.

Deskripsi

Keamanan tingkat baris (RLS) mendukung dua jenis predikat keamanan:

  • Predikat filter secara diam-diam memfilter baris yang tersedia untuk operasi baca (SELECT, UPDATE, dan DELETE).

  • Predikat blok secara eksplisit memblokir operasi tulis (AFTER INSERT, , AFTER UPDATEBEFORE UPDATE, BEFORE DELETE) yang melanggar predikat.

Akses ke data tingkat baris dalam tabel dibatasi oleh predikat keamanan yang didefinisikan sebagai fungsi bernilai tabel sebaris. Fungsi ini kemudian dipanggil dan diberlakukan oleh kebijakan keamanan. Untuk predikat filter, aplikasi tidak menyadari baris yang difilter dari kumpulan hasil. Jika semua baris difilter, maka set null dikembalikan. Untuk predikat blok, setiap operasi yang melanggar predikat akan gagal dengan kesalahan.

Predikat filter diterapkan saat membaca data dari tabel dasar. Mereka mempengaruhi semua operasi get: SELECT, , DELETEdan UPDATE. Pengguna tidak dapat memilih atau menghapus baris yang difilter. Pengguna tidak dapat memperbarui baris yang difilter. Tapi, dimungkinkan untuk memperbarui baris sedih sehingga baris tersebut akan difilter setelahnya. Predikat blok mempengaruhi semua operasi tulis.

  • AFTER INSERT dan AFTER UPDATE predikat dapat mencegah pengguna memperbarui baris ke nilai yang melanggar predikat.

  • BEFORE UPDATE predikat dapat mencegah pengguna memperbarui baris yang saat ini melanggar predikat.

  • BEFORE DELETE predikat dapat memblokir operasi penghapusan.

Baik predikat filter maupun blokir dan kebijakan keamanan memiliki perilaku berikut:

  • Anda dapat menentukan fungsi predikat yang bergabung dengan tabel lain dan/atau memanggil fungsi. Jika kebijakan keamanan dibuat dengan SCHEMABINDING = ON (default), maka gabungan atau fungsi dapat diakses dari kueri dan berfungsi seperti yang diharapkan tanpa pemeriksaan izin tambahan. Jika kebijakan keamanan dibuat dengan SCHEMABINDING = OFF, maka pengguna akan memerlukan SELECT izin pada tabel dan fungsi tambahan ini untuk mengkueri tabel target. Jika fungsi predikat memanggil fungsi bernilai skalar CLR, EXECUTE izin diperlukan sebagai tambahan.

  • Anda mungkin mengeluarkan kueri terhadap tabel yang memiliki predikat keamanan yang ditentukan tetapi dinonaktifkan. Baris apa pun yang difilter atau diblokir tidak terpengaruh.

  • dbo Jika pengguna, anggota db_owner peran, atau pemilik tabel meminta tabel yang memiliki kebijakan keamanan yang ditentukan dan diaktifkan, baris difilter atau diblokir seperti yang ditentukan oleh kebijakan keamanan.

  • Upaya untuk mengubah skema tabel yang terikat oleh kebijakan keamanan terikat skema mengakibatkan kesalahan. Namun, kolom yang tidak direferensikan oleh predikat dapat diubah.

  • Upaya untuk menambahkan predikat pada tabel yang sudah memiliki predikat yang ditentukan untuk operasi yang ditentukan menghasilkan kesalahan. Ini terjadi apakah predikat diaktifkan atau tidak.

  • Upaya untuk mengubah fungsi yang digunakan sebagai predikat pada tabel dalam kebijakan keamanan terikat skema mengakibatkan kesalahan.

  • Menentukan beberapa kebijakan keamanan aktif yang berisi predikat yang tidak tumpang tindih, berhasil.

Predikat filter memiliki perilaku berikut:

  • Tentukan kebijakan keamanan yang memfilter baris tabel. Aplikasi ini tidak menyadari baris apa pun yang difilter untuk SELECToperasi , UPDATE, dan DELETE . Termasuk situasi di mana semua baris difilter. Aplikasi dapat INSERT baris, bahkan jika mereka akan difilter selama operasi lain.

Predikat blok memiliki perilaku berikut:

  • Predikat blok untuk UPDATE dibagi menjadi operasi terpisah untuk BEFORE dan AFTER. Misalnya, Anda tidak dapat memblokir pengguna untuk memperbarui baris agar memiliki nilai yang lebih tinggi dari yang saat ini. Jika logika semacam ini diperlukan, Anda harus menggunakan pemicu dengan tabel perantara DELETED dan INSERTED untuk mereferensikan nilai lama dan baru bersama-sama.

  • Pengoptimal tidak akan memeriksa AFTER UPDATE predikat blok jika kolom yang digunakan oleh fungsi predikat tidak diubah. Misalnya: Alice seharusnya tidak dapat mengubah gaji menjadi lebih besar dari 100.000. Alice dapat mengubah alamat karyawan yang gajinya sudah lebih besar dari 100.000 selama kolom yang dirujuk dalam predikat tidak diubah.

  • Tidak ada perubahan yang dilakukan pada API massal, termasuk BULK INSERT. Ini berarti bahwa predikat AFTER INSERT blok berlaku untuk operasi penyisipan massal sama seperti operasi penyisipan reguler.

Kasus penggunaan

Berikut adalah contoh desain tentang bagaimana keamanan tingkat baris (RLS) dapat digunakan:

  • Rumah sakit dapat membuat kebijakan keamanan yang memungkinkan perawat untuk melihat baris data hanya untuk pasien mereka.

  • Bank dapat membuat kebijakan untuk membatasi akses ke baris data keuangan berdasarkan divisi bisnis atau peran karyawan di perusahaan.

  • Aplikasi multipenyewa dapat membuat kebijakan untuk memberlakukan pemisahan logis baris data setiap penyewa dari setiap baris penyewa lainnya. Efisiensi dicapai oleh penyimpanan data untuk banyak penyewa dalam satu tabel. Setiap penyewa hanya dapat melihat baris datanya.

Predikat filter RLS secara fungsional setara dengan menambahkan WHERE klausa. Predikatnya bisa secanggih yang didiktekan oleh praktik bisnis, atau klausulnya bisa sesederhana WHERE TenantId = 42.

Dalam istilah yang lebih formal, RLS memperkenalkan kontrol akses berbasis predikat. Ini fitur evaluasi fleksibel, terpusat, berbasis predikat. Predikat dapat didasarkan pada metadata atau kriteria lain yang ditentukan administrator yang sesuai. Predikat ini digunakan sebagai kriteria untuk menentukan apakah pengguna memiliki akses yang sesuai ke data berdasarkan atribut pengguna. Kontrol akses berbasis label dapat diimplementasikan dengan menggunakan kontrol akses berbasis predikat.

Izin

Membuat, mengubah, atau menghilangkan kebijakan keamanan memerlukan ALTER ANY SECURITY POLICY izin. Membuat atau menghilangkan kebijakan keamanan memerlukan ALTER izin pada skema.

Selain itu, izin berikut diperlukan untuk setiap predikat yang ditambahkan:

  • SELECT dan REFERENCES izin pada fungsi yang digunakan sebagai predikat.

  • REFERENCES izin pada tabel target yang terikat pada kebijakan.

  • REFERENCES izin pada setiap kolom dari tabel target yang digunakan sebagai argumen.

Kebijakan keamanan berlaku untuk semua pengguna, termasuk pengguna dbo dalam database. Pengguna Dbo dapat mengubah atau menghilangkan kebijakan keamanan namun perubahan kebijakan keamanan mereka dapat diaudit. Jika pengguna dengan hak istimewa tinggi, seperti sysadmin atau db_owner, perlu melihat semua baris untuk memecahkan masalah atau memvalidasi data, kebijakan keamanan harus ditulis untuk mengizinkannya.

Jika kebijakan keamanan dibuat dengan SCHEMABINDING = OFF, maka untuk mengkueri tabel target, pengguna harus memiliki SELECT izin atau EXECUTE pada fungsi predikat dan tabel, tampilan, atau fungsi tambahan yang digunakan dalam fungsi predikat. Jika kebijakan keamanan dibuat dengan SCHEMABINDING = ON (default), pemeriksaan izin ini akan dilewati saat pengguna mengkueri tabel target.

Praktik terbaik

  • Sangat disarankan untuk membuat skema terpisah untuk objek RLS: fungsi predikat, dan kebijakan keamanan. Ini membantu memisahkan izin yang diperlukan pada objek khusus ini dari tabel target. Pemisahan tambahan untuk kebijakan dan fungsi predikat yang berbeda mungkin diperlukan dalam database multipenyewa, tetapi bukan sebagai standar untuk setiap kasus.

  • Izin ALTER ANY SECURITY POLICY ini ditujukan untuk pengguna yang sangat istimewa (seperti manajer kebijakan keamanan). Manajer kebijakan keamanan tidak memerlukan SELECT izin pada tabel yang mereka lindungi.

  • Hindari konversi jenis dalam fungsi predikat untuk menghindari potensi kesalahan runtime.

  • Hindari rekursi dalam fungsi predikat sedapat mungkin untuk menghindari penurunan performa. Pengoptimal kueri akan mencoba mendeteksi rekursi langsung, tetapi tidak dijamin untuk menemukan rekursi tidak langsung. Rekursi tidak langsung adalah tempat fungsi kedua memanggil fungsi predikat.

  • Hindari menggunakan gabungan tabel yang berlebihan dalam fungsi predikat untuk memaksimalkan performa.

Hindari logika predikat yang bergantung pada opsi SET khusus sesi: Meskipun tidak mungkin digunakan dalam aplikasi praktis, fungsi predikat yang logikanya bergantung pada opsi spesifik SET sesi tertentu dapat membocorkan informasi jika pengguna dapat menjalankan kueri arbitrer. Misalnya, fungsi predikat yang secara implisit mengonversi string menjadi datetime dapat memfilter baris yang berbeda berdasarkan SET DATEFORMAT opsi untuk sesi saat ini. Secara umum, fungsi predikat harus mematuhi aturan berikut:

Catatan keamanan: serangan saluran samping

Manajer kebijakan keamanan berbahaya

Penting untuk diamati bahwa manajer kebijakan keamanan berbahaya, dengan izin yang memadai untuk membuat kebijakan keamanan di atas kolom sensitif dan memiliki izin untuk membuat atau mengubah fungsi bernilai tabel sebaris, dapat berkolaborasi dengan pengguna lain yang telah memilih izin pada tabel untuk melakukan eksfiltrasi data dengan membuat fungsi bernilai tabel sebaris yang dirancang untuk menggunakan serangan saluran samping untuk menyimpulkan data. Serangan tersebut akan memerlukan kolusi (atau izin berlebihan yang diberikan kepada pengguna berbahaya) dan kemungkinan akan memerlukan beberapa iterasi untuk memodifikasi kebijakan (memerlukan izin untuk menghapus predikat untuk memutus pengikatan skema), memodifikasi fungsi bernilai tabel sebaris, dan berulang kali menjalankan pernyataan pemilihan pada tabel target. Sebaiknya batasi izin seperlunya dan pantau aktivitas yang mencurigakan. Aktivitas seperti kebijakan yang terus berubah dan fungsi bernilai tabel sebaris yang terkait dengan keamanan tingkat baris harus dipantau.

Kueri yang dibuat dengan hati-hati

Dimungkinkan untuk menyebabkan kebocoran informasi dengan menggunakan kueri yang dibuat dengan hati-hati yang menggunakan kesalahan untuk menyelundupkan data. Misalnya, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; akan membiarkan pengguna jahat tahu bahwa gaji John Doe tepat $ 100.000. Meskipun ada predikat keamanan untuk mencegah pengguna jahat mengkueri gaji orang lain secara langsung, pengguna dapat menentukan kapan kueri mengembalikan pengecualian divide-by-zero.

Kompatibilitas lintas fitur

Secara umum, keamanan tingkat baris akan berfungsi seperti yang diharapkan di seluruh fitur. Namun, ada beberapa pengecualian. Bagian ini mendikumentasikan beberapa catatan dan peringatan untuk menggunakan keamanan tingkat baris dengan fitur SQL Server tertentu lainnya.

  • DBCC SHOW_STATISTICS melaporkan statistik tentang data yang tidak difilter, dan dapat membocorkan informasi yang dilindungi oleh kebijakan keamanan. Untuk alasan ini, akses untuk melihat objek statistik untuk tabel dengan kebijakan keamanan tingkat baris dibatasi. Pengguna harus memiliki tabel atau pengguna harus menjadi anggota sysadmin peran server tetap, db_owner peran database tetap, atau db_ddladmin peran database tetap.

  • Filestream: RLS tidak kompatibel dengan Filestream.

  • PolyBase: RLS didukung dengan tabel eksternal di Azure Synapse dan SQL Server 2019 CU7 atau versi yang lebih tinggi.

  • Tabel yang Dioptimalkan Memori: Fungsi bernilai tabel sebaris yang digunakan sebagai predikat keamanan pada tabel yang dioptimalkan memori harus ditentukan menggunakan WITH NATIVE_COMPILATION opsi . Dengan opsi ini, fitur bahasa yang tidak didukung oleh tabel yang dioptimalkan memori akan dilarang dan kesalahan yang sesuai akan dikeluarkan pada waktu pembuatan. Untuk informasi selengkapnya, lihat Keamanan tingkat baris dalam Tabel yang Dioptimalkan Memori.

  • Tampilan terindeks: Secara umum, kebijakan keamanan dapat dibuat di atas tampilan, dan tampilan dapat dibuat di atas tabel yang terikat oleh kebijakan keamanan. Namun, tampilan terindeks tidak dapat dibuat di atas tabel yang memiliki kebijakan keamanan, karena pencarian baris melalui indeks akan melewati kebijakan.

  • Ubah Data Capture: Change Data Capture (CDC) dapat membocorkan seluruh baris yang harus difilter kepada anggota db_owner atau pengguna yang merupakan anggota peran "gating" yang ditentukan saat CDC diaktifkan untuk tabel. Anda dapat secara eksplisit mengatur fungsi ini ke NULL untuk memungkinkan semua pengguna mengakses data perubahan. Akibatnya, db_owner dan anggota peran pembatasan ini dapat melihat semua perubahan data pada tabel, bahkan jika ada kebijakan keamanan pada tabel.

  • Pelacakan Perubahan: Pelacakan Perubahan dapat membocorkan kunci utama baris yang harus difilter kepada pengguna dengan izin dan SELECT VIEW CHANGE TRACKING . Nilai data aktual tidak bocor; hanya fakta bahwa kolom A diperbarui/disisipkan/dihapus untuk baris dengan kunci primer tertentu. Ini bermasalah jika kunci utama berisi elemen rahasia, seperti Nomor Jaminan Sosial. Namun, dalam praktiknya, ini CHANGETABLE hampir selalu digabungkan dengan tabel asli untuk mendapatkan data terbaru.

  • Pencarian Teks Lengkap: Hit performa diharapkan untuk kueri menggunakan fungsi Pencarian Teks Lengkap dan Pencarian Semantik berikut, karena gabungan tambahan yang diperkenalkan untuk menerapkan keamanan tingkat baris dan menghindari kebocoran kunci utama baris yang harus difilter: CONTAINSTABLE, , semanticsimilaritydetailstableFREETEXTTABLEsemantickeyphrasetable, . semanticsimilaritytable

  • Indeks Penyimpan Kolom: RLS kompatibel dengan indeks penyimpan kolom berkluster dan berkluster. Namun, karena keamanan tingkat baris menerapkan fungsi, ada kemungkinan pengoptimal dapat mengubah rencana kueri sehingga tidak menggunakan mode batch.

  • Tampilan Yang Dipartisi: Predikat blok tidak dapat ditentukan pada tampilan yang dipartisi, dan tampilan yang dipartisi tidak dapat dibuat di atas tabel yang menggunakan predikat blok. Predikat filter kompatibel dengan tampilan yang dipartisi.

  • Tabel temporal: Tabel temporal kompatibel dengan RLS. Namun, predikat keamanan pada tabel saat ini tidak direplikasi secara otomatis ke tabel riwayat. Untuk menerapkan kebijakan keamanan ke tabel saat ini dan riwayat, Anda harus menambahkan predikat keamanan secara individual pada setiap tabel.

Batasan lainnya:

  • Microsoft Fabric dan Azure Synapse Analytics hanya mendukung predikat filter. Predikat blok saat ini tidak didukung di Microsoft Fabric dan Azure Synapse Analytics.

Contoh

J. Skenario untuk pengguna yang mengautentikasi ke database

Contoh ini membuat tiga pengguna dan membuat dan mengisi tabel dengan enam baris. Kemudian membuat fungsi bernilai tabel sebaris dan kebijakan keamanan untuk tabel. Contoh kemudian menunjukkan bagaimana pernyataan pemilih difilter untuk berbagai pengguna.

Buat tiga akun pengguna yang menunjukkan kemampuan akses yang berbeda.

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

Buat tabel untuk menyimpan data.

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

Isi tabel dengan enam baris data yang menunjukkan tiga pesanan untuk setiap perwakilan penjualan.

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;

Berikan akses baca pada tabel kepada masing-masing pengguna.

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

Buat skema baru, dan fungsi bernilai tabel sebaris. Fungsi mengembalikan 1 ketika baris di SalesRep kolom sama dengan pengguna yang menjalankan kueri (@SalesRep = USER_NAME()) atau jika pengguna yang menjalankan kueri adalah pengguna Manajer (USER_NAME() = 'Manager'). Contoh fungsi bernilai tabel yang ditentukan pengguna ini berguna untuk berfungsi sebagai filter untuk kebijakan keamanan yang dibuat pada langkah berikutnya.

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

Buat kebijakan keamanan yang menambahkan fungsi sebagai predikat filter. STATE harus diatur ke ON untuk mengaktifkan kebijakan.

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

Izinkan SELECT izin ke tvf_securitypredicate fungsi:

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

Sekarang uji predikat pemfilteran, dengan dipilih dari Sales.Orders tabel sebagai setiap pengguna.

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;

Manajer akan melihat keenam baris. Pengguna Sales1 dan Sales2 seharusnya hanya melihat penjualan mereka sendiri.

Ubah kebijakan keamanan untuk menonaktifkan kebijakan.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Sekarang Sales1 dan Sales2 pengguna dapat melihat keenam baris.

Sambungkan ke database SQL untuk membersihkan sumber daya dari latihan sampel ini:

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. Skenario untuk menggunakan Keamanan Tingkat Baris pada tabel eksternal Azure Synapse

Contoh singkat ini membuat tiga pengguna dan tabel eksternal dengan enam baris. Kemudian fungsi nilai tabel sebaris dan kebijakan keamanan untuk tabel eksternal akan dibuat. Contoh menunjukkan bagaimana pernyataan pemilihan difilter untuk berbagai pengguna.

Prasyarat

  1. Anda harus memiliki kumpulan SQL khusus. Lihat Membuat kumpulan SQL khusus
  2. Server yang menghosting kumpulan SQL khusus Anda harus terdaftar dengan ID Microsoft Entra (sebelumnya Azure Active Directory) dan Anda harus memiliki akun penyimpanan Azure dengan Storage Blog Data Contributor izin. Ikuti langkah-langkah untuk Menggunakan titik akhir dan aturan layanan jaringan virtual untuk server di Azure SQL Database.
  3. Buat sistem file untuk akun Azure Storage Anda. Gunakan Azure Storage Explorer untuk melihat akun penyimpanan Anda. Klik kanan pada kontainer dan pilih Buat sistem file.

Setelah Anda memiliki prasyarat, buat tiga akun pengguna yang menunjukkan kemampuan akses yang berbeda.

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

Buat tabel untuk menyimpan data.

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

Isi tabel dengan enam baris data yang menunjukkan tiga pesanan untuk setiap perwakilan penjualan.

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;

Buat tabel eksternal Azure Synapse dari tabel yang Sales Anda buat.

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;

Berikan SELECT untuk tiga pengguna pada tabel Sales_ext eksternal yang Anda buat.

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

Buat skema baru, dan fungsi bernilai tabel sebaris, Anda mungkin telah menyelesaikan ini dalam contoh A. Fungsi mengembalikan 1 ketika baris dalam SalesRep kolom sama dengan pengguna yang menjalankan kueri (@SalesRep = USER_NAME()) atau jika pengguna yang menjalankan kueri adalah Manager pengguna (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';

Buat kebijakan keamanan di tabel eksternal Anda menggunakan fungsi nilai tabel sebaris sebagai predikat filter. STATE harus diatur ke ON untuk mengaktifkan kebijakan.

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

Sekarang uji predikat pemfilteran, dengan memilih dari Sales_ext tabel eksternal. Masuk sebagai setiap pengguna, Sales1, Sales2, dan Manager. Jalankan perintah berikut ini sebagai setiap pengguna.

SELECT * FROM Sales_ext;

akan Manager melihat keenam baris. Pengguna Sales1 dan Sales2 seharusnya hanya melihat penjualan mereka.

Ubah kebijakan keamanan untuk menonaktifkan kebijakan.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Sales1 Sekarang pengguna dan Sales2 dapat melihat keenam baris.

Sambungkan ke database Azure Synapse untuk membersihkan sumber daya dari latihan sampel ini:

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;

Sambungkan ke database server master logis untuk membersihkan sumber daya:

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

C. Skenario untuk pengguna yang tersambung ke database melalui aplikasi tingkat menengah

Catatan

Dalam contoh ini, fungsionalitas predikat blok saat ini tidak didukung untuk Microsoft Fabric dan Azure Synapse, sehingga menyisipkan baris untuk ID pengguna yang salah tidak diblokir.

Contoh ini menunjukkan bagaimana aplikasi tingkat menengah dapat menerapkan pemfilteran koneksi, di mana pengguna aplikasi (atau penyewa) berbagi pengguna SQL Server yang sama (aplikasi). Aplikasi menetapkan ID pengguna aplikasi saat ini di SESSION_CONTEXT setelah menyambungkan ke database, lalu kebijakan keamanan secara transparan memfilter baris yang seharusnya tidak terlihat oleh ID ini, dan juga memblokir pengguna untuk menyisipkan baris untuk ID pengguna yang salah. Tidak ada perubahan aplikasi lain yang diperlukan.

Buat tabel untuk menyimpan data.

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

Isi tabel dengan enam baris data, memperlihatkan tiga pesanan untuk setiap pengguna aplikasi.

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

Buat pengguna dengan hak istimewa rendah yang akan digunakan aplikasi untuk terhubung.

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

Buat skema baru dan fungsi predikat, yang akan menggunakan ID pengguna aplikasi yang disimpan SESSION_CONTEXT() untuk memfilter baris.

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

Buat kebijakan keamanan yang menambahkan fungsi ini sebagai predikat filter dan predikat blok pada Sales. Predikat blok hanya membutuhkan AFTER INSERT, karena BEFORE UPDATE dan BEFORE DELETE sudah difilter, dan AFTER UPDATE tidak perlu karena AppUserId kolom tidak dapat diperbarui ke nilai lain, karena izin kolom yang ditetapkan sebelumnya.

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

Sekarang kita dapat mensimulasikan pemfilteran koneksi dengan memilih dari Sales tabel setelah mengatur ID pengguna yang berbeda di SESSION_CONTEXT(). Dalam praktiknya, aplikasi bertanggung jawab untuk mengatur ID SESSION_CONTEXT() pengguna saat ini setelah membuka koneksi. @read_only Mengatur parameter untuk 1 mencegah nilai berubah lagi hingga koneksi ditutup (dikembalikan ke kumpulan koneksi).

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

Bersihkan sumber daya database.

DROP USER AppUser;

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

D. Skenario untuk menggunakan tabel pencarian untuk predikat keamanan

Contoh ini menggunakan tabel pencarian untuk tautan antara pengidentifikasi pengguna dan nilai yang difilter, daripada harus menentukan pengidentifikasi pengguna dalam tabel fakta. Ini membuat tiga pengguna dan membuat dan mengisi tabel fakta, Sample.Sales, dengan enam baris dan tabel pencarian dengan dua baris. Kemudian membuat fungsi bernilai tabel sebaris yang menggabungkan tabel fakta ke pencarian untuk mendapatkan pengidentifikasi pengguna, dan kebijakan keamanan untuk tabel. Contoh kemudian menunjukkan bagaimana pernyataan pemilih difilter untuk berbagai pengguna.

Buat tiga akun pengguna yang menunjukkan kemampuan akses yang berbeda.

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

Buat Sample skema dan tabel fakta, Sample.Sales, untuk menyimpan data.

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

Isi Sample.Sales dengan enam baris 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;

Buat tabel untuk menyimpan data pencarian – dalam hal ini hubungan antara Salesrep dan Product.

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

Isi tabel pencarian dengan data sampel, menautkan satu Product ke setiap perwakilan penjualan.

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;

Berikan akses baca pada tabel fakta kepada setiap pengguna.

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

Buat skema baru dan fungsi bernilai tabel sebaris. Fungsi mengembalikan 1 saat pengguna mengkueri tabel Sample.Sales fakta dan SalesRep kolom tabel Lk_Salesman_Product sama dengan pengguna yang menjalankan kueri (@SalesRep = USER_NAME()) saat bergabung ke tabel fakta pada Product kolom, atau jika pengguna yang menjalankan kueri adalah Manager pengguna (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'
                   ) ;

Buat kebijakan keamanan yang menambahkan fungsi sebagai predikat filter. STATE harus diatur ke ON untuk mengaktifkan kebijakan.

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

Izinkan SELECT izin ke fn_securitypredicate fungsi:

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

Sekarang uji predikat pemfilteran, dengan dipilih dari Sample.Sales tabel sebagai setiap pengguna.

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;

akan Manager melihat keenam baris. Pengguna Sales1 dan Sales2 seharusnya hanya melihat penjualan mereka sendiri.

Ubah kebijakan keamanan untuk menonaktifkan kebijakan.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Sekarang Sales1 dan Sales2 pengguna dapat melihat keenam baris.

Sambungkan ke database SQL untuk membersihkan sumber daya dari latihan sampel ini:

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. Skenario keamanan tingkat baris di Microsoft Fabric

Kami dapat menunjukkan gudang keamanan tingkat baris dan titik akhir analitik SQL di Microsoft Fabric.

Contoh berikut membuat tabel sampel yang akan berfungsi dengan Warehouse di Microsoft Fabric, tetapi di titik akhir analitik SQL menggunakan tabel yang ada. Di titik akhir analitik SQL, Anda tidak dapat menggunakan CREATE TABLE, tetapi Anda dapat menggunakan CREATE SCHEMA, CREATE FUNCTION, dan CREATE SECURITY POLICY.

Dalam contoh ini, pertama-tama buat skema sales, tabel 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');

Buat Security skema, fungsi Security.tvf_securitypredicate, dan kebijakan SalesFilterkeamanan .

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

Setelah menerapkan kebijakan keamanan dan membuat fungsi, pengguna Sales1@contoso.com dan Sales2@contoso.com hanya akan dapat melihat data mereka sendiri dalam sales.Orders tabel, di mana kolom SalesRep sama dengan nama pengguna mereka sendiri yang dikembalikan oleh fungsi bawaan USER_NAME. Pengguna Fabric manager@contoso.com dapat melihat semua data dalam sales.Orders tabel.

Konten terkait