آشنایی با SQL Server 2016 Row Level Security

آشنایی با SQL Server 2016 Row Level Security

نوشته شده توسط: سید محمد حسینی
تاریخ انتشار: ۱۸ اسفند ۱۳۹۴
آخرین بروزرسانی: ۲۷ خرداد ۱۴۰۳
زمان مطالعه: 7 دقیقه
۵
(۱)

مقدمه

فرض کنید که ما در SQL Server جدولی داریم که در آن اطلاعات تامین کنندگان و سفارشات قرار دارد. این اطلاعات برای کسب و کار ما اهمیت فراوانی دارند و می خواهیم دسترسی برخی از کارمندان را به این اطلاعات محدود کنیم. می خواهیم هر یک از کارمندان بتوانند فقط سفارشات ثبت شده توسط خودشان را بر اساس کد کارمندی شان مشاهده کنند. برای این کار SQL Server 2016 دارای ویژگی های جدیدی است.

در SQL Server 2016 قابلیتی به نام Row Level Security(RLS) معرفی شده است که می تواند امکان کنترل دسترسی به هر یک از سطرهای جدول را برای ما فراهم کند. RLS به ما امکان می دهد که به آسانی مشخص کنیم که کدام کاربر می تواند به کدام داده ها دسترسی داشته باشد-با حداکثر شفافیت برای برنامه کاربردی. این قابلیت به ما این امکان را می دهد که داده ها را بر اساس شناسه کاربری یا سیاست های امنیتی(Security Policy) خاصی محدود کنیم.

ایجاد داده های نمونه برای بررسی RLS

برای اینکه بتوانیم عملکرد RLS را مورد بررسی قرار دهیم، در ابتدا یک جدول ایجاد کرده و تعدادی رکورد در آن درج می کنیم:
CREATE TABLE dbo.Orders
(
Supplier_Code int,
[Supplier_Name] varchar(10),
[Orderdate] datetime,
[OrderQuantity] int,
[ProcessedBy] Varchar(10)
)
GO
-- Sample data
INSERT INTO dbo.orders VALUES(101, 'AXP Inc', '2015-08-11 00:34:51:090', 1789, 'LAX')
INSERT INTO dbo.orders VALUES(102, 'VFG Inc', '2014-01-08 19:44:51:090', 767, 'AURA')
INSERT INTO dbo.orders VALUES(103, 'ZAD Inc', '2015-08-19 19:44:51:090', 500, 'ZAP')
INSERT INTO dbo.orders VALUES(102, 'VFG Inc', '2014-08-19 19:44:51:090', 1099, 'ZAP')
INSERT INTO dbo.orders VALUES(101, 'AXP Inc', '2014-08-04 19:44:51:090', 654, 'LAX')
INSERT INTO dbo.orders VALUES(103, 'ZAD Inc', '2015-08-10 19:44:51:090', 498, 'AURA')
INSERT INTO dbo.orders VALUES(102, 'VFG Inc', '2015-04-17 19:44:51:090', 999, 'AURA')
INSERT INTO dbo.orders VALUES(101, 'AXP Inc', '2015-08-21 19:44:51:090', 543, 'ZAP')
INSERT INTO dbo.orders VALUES(103, 'ZAD Inc', '2015-08-06 19:44:51:090', 876, 'ZAP')
INSERT INTO dbo.orders VALUES(102, 'VFG Inc', '2015-08-26 19:44:51:090', 665, 'LAX')
GO
SELECT * FROM dbo.Orders
GO

رکوردهای ثبت شده در زیر لیست شده اند:

عملکرد SQL Server 2016 در مورد RLS

بر اساس نیازمندی های که داریم، می بایست تابع گزاره ای(Predicate Function) مشابه زیر-که عمل فیلتر کردن اطلاعات را کنترل می کند- ایجاد کنیم:
CREATE FUNCTION dbo.fn_securitypredicateOrder (@processedby sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS [fn_securityPredicateOrder_result]
FROM dbo.orders
WHERE @processedby=USER_NAME() -- it will be Filter applied while running the query
GO
در ادامه، می بایست سیاست های امنیتی که تابع ایجاد شده در فوق بر اساس آن عمل می کند را تعریف کنیم:

CREATE SECURITY POLICY dbo.fn_security
ADD
FILTER PREDICATE dbo.fn_securitypredicateOrder(processedby)
ON dbo.orders
GO

 سپس، با توجه به داده های نمونه ای که در این مقاله ارائه کرده ایم، می بایست تعدادی کاربر را جهت دسترسی به این اطلاعات تعریف کنیم که در اینجا نام های LAX، AURA و ZAP نام هایی هستند که در ستون Processedby از جدول dbo.orders وجود دارند:

CREATE USER LAX WITHOUT LOGIN
CREATE USER AURA WITHOUT LOGIN
CREATE USER ZAP WITHOUT LOGIN
GO
GRANT SELECT ON dbo.Orders TO LAX
GRANT SELECT ON dbo.Orders TO AURA
GRANT SELECT ON dbo.Orders TO ZAP
GO

در صورتی که پرسوجویی را بر اساس اصول امنیتی مد نظر برای هر یک از کاربران اجرا کنیم-پرسوجوهای زیر، خروجی به صورت زیر خواهد بود:

EXECUTE ('SELECT * FROM dbo.Orders') AS USER='LAX'
EXECUTE ('SELECT * FROM dbo.Orders') AS USER='AURA'
EXECUTE ('SELECT * FROM dbo.Orders') AS USER='ZAP'
GO
بر اساس این قابلیت، کاربران SQL Server با توجه به منطق تعریف شده فقط رکوردهای پردازش شده توسط خودشان را مشاهده می کنند.

پلان اجرایی SQL Server برای RLS

حالا برای اینکه ببینیم این قابلیت چگونه عمل می کند، باید پلان اجرایی پرسوجوی ایجاد شده را پیش و پس از اعمال سیاست RS مشاهده کنیم.
توجه: جهت مشاهده پلان اجرایی کاربران باید مجوز مشاهده پلان(ShowPlan) را داشته باشیم.
 
GRANT SHOWPLAN TO ZAP
GO
EXECUTE ('SELECT * FROM dbo.Orders') AS USER='ZAP'
GO
همانطور که در پلان اجرایی مشاهده کنیم، متوجه می شویم که پرسوجو به صورت زیر اجرا شده است:
SELECT * FROM dbo.orders WHERE processedby=USER_NAME()
--USER_NAME is the Security Context of the User executing the query
همچنین در صورتی که از قابلیت RS استفاده نکرده باشیم، در پلان اجرایی فقط یک عملگر table scan برای نمایش رکوردها ایجاد می شود.

اعمال تغییرات در RLS

با استفاده از دستورات زیر می توان RLS را غیر فعال کرد.
ALTER SECURITY POLICY fn_security WITH (STATE=OFF)
GO
همچنین با استفاده از دستورات زیر می توان سیاست های امنیتی و تابع گزاره ی ایجاد شده را حذف کرد.
DROP SECURITY POLICY fn_security
DROP FUNCTION dbo.fn_securitypredicateOrder
GO

انتخاب‌های بیشتر برای RLS

به عنوان مثالی دیگر، می خواهیم یک تابع گزاره با پیچیدگی بیشتر ایجاد کنیم. در این مثال می خواهیم کاربران بتوانند فقط رکوردهای پردازش شده توسط خودشان که حداکثر تا یک سال گذشته بوده است را مشاهده کنند. منطق کار به صورت زیر می باشد:

 CREATE FUNCTION dbo.fn_securitypredicateOrder (@processedby sysname,@Orderdate datetime)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS [fn_securityPredicateOrder_result]
FROM dbo.Orders
WHERE @processedby=USER_NAME() AND @orderdate>=GETDATE()-365
GO
CREATE SECURITY POLICY dbo.fn_security
ADD
FILTER PREDICATE dbo.fn_securitypredicateOrder(processedby,Orderdate)
ON dbo.orders
GO
به عنوان مثال، کاربری با نام AURA فقط دارای دو رکورد است که مربوط به یک سال گذشته می باشد.

چه رتبه ای می‌دهید؟

میانگین ۵ / ۵. از مجموع ۱

اولین نفر باش

title sign
معرفی نویسنده
سید محمد حسینی
مقالات
11 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
سید محمد حسینی
پروفایل نویسنده
title sign
دیدگاه کاربران

    • عالی و مفید بود ممنون مهندس

    •    سلام من یه سایت دارم می خوام بزارم رو سرور خودشون من چه جوری کاری کنم چون تو وب کانفیگ یوزر و پسورد هست به اس کی یو ال دسترسی نداشته باشن

    •    با سلام و عرض ادب

      ممنون از زحمات شما دوست عزیز . مطلب بسیار خوبی را ارائه دادید سپاسگزارم .
      یه خواهش از استاد مسعودی

      ممکنه یه مقاله راجع به این که میخواهیم ببینیم که چه کسانی در کل یک شرکت بر روی دستگاه خود SQL Server نصب کرده اند .

      در ضمن اگر ممکن است یک لیک مربوط به ذخیره مطالب را قرار دهید .

    •     سلام بسیار عالی کاربردی بود با تشکر

    •    سلام
      واقعا استفاده کردم.

    • سلام 

      استفاده از این روش به ازای Business یوزرها هم امکان پذیر است. کافی است که از Session Context کد مربوط به Business User را قرار دهید و…
      هدف نهایی : محدود کردن نمایش رکوردها با توجه به Business Userهای سیستم
      ضمنا این موضوع به همراه همین سناریوی که اشاره کردم در همایش SQL Server 2016 بررسی شد.
      موفق باشید
    • سلام

      از بابت ارائه مطلب خوب تون که میشه گفت یکی از نقاط قوت و کاربردی اسکوئل سرور۲۰۱۶ برای کاربرانش هست تشکر میکنم. ولی نکته اینجاست که برخی از برنامه نویسان دوتا User تعریف میکنند؛ یکی برای خودشون ویکی هم برای کلیه کاربران نهایی استفاده کننده از نرم افزارشون.(منظورم اینه که هرچندتا کاربر هم که به نرم افزارشون معرفی شده باشه با یک نام کاربری امکان دسترسی به بانک اطلاعاتی را دارند)؛ اینجاست که این قابلیت مهم رو یکجورایی از دست داده و باید با همون روش چرخ دستی وسنتی قبل، این کنترل رو در دست بگیرند. اگه راهکار مناسبی برای این دسته از برنامه نویسان هم در نظر دارید لطفاً بفرمائید.
    • سلام
      مقالتون بسیار عالی کاربردی و آموزنده بود …

    •     سلام

      در دوره امنیت این موارد بررسی شده است
      برای اینکار Connection String را Encrypt کنید و از Application Role استفاده کنید
هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
الکامپ ۲۷ با خبرهای هیجان انگیز در راه است | تنها چند روز تا شروع جشنواره 
از شروع جشنواره با خبر شو
close-image