فرض کنید که ما در 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 فقط دارای دو رکورد است که مربوط به یک سال گذشته می باشد.
منبع: آموزش SQL Server نیک آموز
امیدوارم که این مقاله برای دوستان مفید باشد
سیدمحمد حسینی
۱۳۹۴/۱۲/۱۰
9 Comments
مجتبی شهریور
سلام
مقالتون بسیار عالی کاربردی و آموزنده بود …
فرشید علی اکبری
سلام
مسعود طاهری
سلام
محمد رحیمی
سلام
واقعا استفاده کردم.
مهدی ربانی ذبیحی
سلام بسیار عالی کاربردی بود با تشکر
غلامحسین عبادی
با سلام و عرض ادب
ممنون از زحمات شما دوست عزیز . مطلب بسیار خوبی را ارائه دادید سپاسگزارم .
یه خواهش از استاد مسعودی
ممکنه یه مقاله راجع به این که میخواهیم ببینیم که چه کسانی در کل یک شرکت بر روی دستگاه خود SQL Server نصب کرده اند .
در ضمن اگر ممکن است یک لیک مربوط به ذخیره مطالب را قرار دهید .
محسن فرید
سلام من یه سایت دارم می خوام بزارم رو سرور خودشون من چه جوری کاری کنم چون تو وب کانفیگ یوزر و پسورد هست به اس کی یو ال دسترسی نداشته باشن
مسعود طاهری
سلام
داوود طاهرخانی
عالی و مفید بود ممنون مهندس