خانه SQL Server آشنایی با SQL Server 2016 Row Level Security (بخش دوم) SQL Server نوشته شده توسط: سید محمد حسینی ۰۴ اردیبهشت ۱۳۹۵ زمان مطالعه: 7 دقیقه ۵ (۱) مقدمه مقاله قبل(لینک مشاهده) با چگونگی استفاده از RLS و برخی از موارد کاربرد آن آشنا شدیم. در این مقاله نیز قصد داریم تا با برخی دیگر از ویژگی های RLS آشنا شویم. همانطور که قبلا مشاهده کردیم، RLS تابعی فیلتر کننده دارد( گزاره FILTER) که دسترسی به داده ها را فقط برای کاربران مجاز فراهم می کند. البته در صورتی که کاربری دارای مجوزهای بیشتری بر روی جدول باشد(نظیر درج، بروزرسانی و حذف)، نمی توانیم این کاربر را در انجام تغییرات محدود کنیم. در SQL Server 2016 گزاره ی BLOCK جهت دستیابی به این شرایط امنیتی ارائه شده است. در ادامه کدهای مورد نیاز برای ایجاد جدول و داده های آن جهت معرفی تابع SESSION_CONTEXT ارائه شده است. در SQL Server 2016 می توانیم جفت هایی از کلید و مقادیر را ذخیره کنیم که در سراسر جلسه به آنها دسترسی داشته باشیم. جفت های کلید و مقادیر را می توان توسط رویه ذخیره شده ی سیستمی به نام sp_set_session_context مقدار دهی و تنظیم کرد. همچنین این مقادیر را می توان توسط تابع SESSION_CONTEXT بازیابی نمود(هر بار یک مقدار). ایجاد جدول و داده های مورد نیاز CREATE TABLE CUSTOMER ( Customerid int identity(1,1) primary key, Name nvarchar(64), city nvarchar(20), Status nvarchar(64), EmpID int DEFAULT CAST(SESSION_CONTEXT(N'EmpID') AS int) ) -- This will automatically set EmpID to the value in SESSION_CONTEXT GO --Sample Data INSERT INTO customer(Name,City, Status,Empid) VALUES('Alex','London','Active',1) INSERT INTO customer(Name,City, Status,Empid) VALUES('Dirk','Slough','Active',2) INSERT INTO customer(Name,City, Status,Empid) VALUES('Mark','Slough','Inactive',1) GO با استفاده از کدهای فوق، جدول و داده های مورد نیاز جهت بررسی بیشتر RLS را ایجاد می کنیم. تابع و سیاست امنیتی مورد نیاز RLS در SQL Server فرض کنید در یک مرکز تماس(call center) هر کارمند برای چندین مشتری تخصیص داده شده است، همچنین این کارمند باید فقط مجوز اعمال هرگونه تغییری را بر روی داده های مشتریان خود داشته باشد. همچنین در صورتی که مشتری جدید اضافه شود، این کارمند باید با شناسه خود قادر به اضافه کردن رکورد جدید برای آن مشتری باشد. در ادامه می خواهیم با توجه به منطق و شرایط مشخص شده در فوق، تابع گزاره ای مورد نیاز را بر اساس SESSION_CONTEXT و مقادیر EmpID موجود در جدول ایجاد کنیم. CREATE FUNCTION dbo.CustomerAccesspredicate(@EmpID int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS accessResult WHERE CAST(SESSION_CONTEXT(N'EmpID') AS int) = @EmpID GO در ادامه سیاست امنیتی مورد نیاز را جهت دسترسی به داده ها برای گزاره های FILTER و BLOCK می بایست تعریف کنیم. CREATE SECURITY POLICY dbo.CustomerSecurityPolicy ADD FILTER PREDICATE dbo.CustomerAccesspredicate(Empid) ON dbo.Customer, ADD BLOCK PREDICATE dbo.CustomerAccesspredicate(Empid) ON dbo.Customer AFTER INSERT GO ایجاد کاربران آزمایشی در SQL Server در ادامه قصد داریم کاربری به نام Apps را با مجوزهای SELECT، INSERT، UPDATE و DELETE بر روی جدول مشتریان ایجاد کنیم. CREATE USER Apps WITHOUT LOGIN GO GRANT SELECT, INSERT, UPDATE, DELETE ON Customer TO Apps GO لیست داده هایی که آزمایش ما بر روی آنها انجام می شود در ادامه آورده شده اند: انجام آزمایش بر روی RLS حال اگر ما دستور SELECT را بدون استفاده از SESSION_CONTEXT استفاده کنیم، خروجی زیر را خواهیم داشت: اگر SESSION_CONTEXT را با استفاده از رویه ذخیره شده sp_set_session_context مقدار دهی کنیم خروجی های زیر را خواهیم داشت: حال اجازه دهید که یک دستور INSERT را بررسی کنیم EXECUTE AS USER ='APPS' GO EXEC sp_set_session_context N'EmpID', 1 INSERT INTO dbo.customer(Name,City, Status,Empid ) VALUES('Adam','york','Inactive',1) GO -- Output is : (1 row(s) affected) همانطور که مشاهده می کنید، EmpID 1 توانست با EMPID خود رکوردی را در جدول درج کند. در صورتی که این کاربر قصد درج رکوردی را با EmpID دیگری داشته باشد خطای زیر را دریافت خواهد کرد: EXECUTE AS USER ='APPS' GO EXEC sp_set_session_context N'EmpID', 1 INSERT INTO dbo.customer(Name,City, Status,Empid ) VALUES('Adam','york','Inactive',2) GO اجازه دهید دستور UPDATE را بررسی کنیم: EXECUTE AS USER ='APPS' GO EXEC sp_set_session_context N'EmpID', 1 UPDATE Customer SET Status='active' WHERE name='Mark' -- Output : 1 Row(s) Updated UPDATE Customer SET Status='Inactive' WHERE name='Dirk' -- Output : 0 Row(s) updated GO همانگونه که مشاهده می کنید، در قسمت اول داده ها بروزرسانی شده اند، ولی در قسمت دوم عملیات بروزرسانی انجام نشده است. خطایی نمایش داده نشده است، ولی پیغام خروجی نمایش داده شده نشان دهنده این است که هیچ رکوردی بروزرسانی نشده است. دستور DELETE نیز مشابه دستور UPDATE است. سیاست امنیتی مشخص شده امکان حذف رکوردهای سایر EmpID ها را نمی دهد. در صورتی که پلان اجرایی دستور DELETE را مشاهده کنیم، گزاره تعریف شده را مشاهده خواهیم کرد. جمع بندی گزاره های FILTER و BLOCK در SQL Server 2016 در این مقاله ما در مورد گزاره های FILTER و BLOCK آموختیم که: گزاره FILTER به شکل نامحسوسی سطرها را در عملیات های خواندن فیلتر می کند(SELECT، UPDATE و DELETE) گزاره BLOCK صراحتا عملیات های نوشتن را بلاک می کند(INSERT، UPDATE و DELETE) با استفاده از RLS می توانیم محدودیت های امنیتی را به شیوه ای بسیار ایمن تر اعمال کنیم. همچنین می توانیم با استفاده از ویووهای Security Catalog گزاره ها و سیاست های امنیتی تعریف شده را مشاهده کنیم: SELECT * FROM sys.security_policies برای هر سیاست امنیتی تعریف شده در بانک اطلاعاتی یک رکورد باز می گرداند SELECT * FROM sys.security_predicates برای هر گزاره امنیتی تعریف شده در بانک اطلاعاتی یک رکورد باز می گرداند. می توان این ویوو را با sys.security_policies جوین(join) کرد تا گزاره تعریف شده برای هر یک از سیاست های امنیتی را به دست آورد. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش برچسب ها # Row Level Security# SQL Server# SQL Server 2016# آموزش SQL Server# آموزش SQL Server 2016# امنیت در SQL Server معرفی نویسنده مقالات 11 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده سید محمد حسینی پروفایل نویسنده مقالات مرتبط ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی ۰۷ اردیبهشت SQL Server استفاده از Operator ها در SQL Server Agent حسن سلیمانی ۰۵ اردیبهشت SQL Server بررسی نحوه ایجاد Job در SQL Server حسن سلیمانی ۲۹ فروردین SQL Server آشنایی با بخش های مختلف SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ حسن ضرابی ۲۰ / ۱۲ / ۹۷ - ۰۹:۱۲ با سلام از مقاله بسیار عالیتون واقعا ممنونم لطف کردید با تشکر پاسخ به دیدگاه حسن ضرابی ۲۰ / ۱۲ / ۹۷ - ۰۹:۱۲ با سلام از مقاله بسیار عالیتون واقعا ممنونم لطف کردید با تشکر پاسخ به دیدگاه محمد رضا موسائی سجزی ۰۶ / ۰۲ / ۹۵ - ۰۰:۳۵ با سلام خدمت شما دوست عزیز از مقاله بسیار خوب شما ممنون واقعا عالی بود پاسخ به دیدگاه محمد رضا موسائی سجزی ۰۶ / ۰۲ / ۹۵ - ۰۰:۳۵ با سلام خدمت شما دوست عزیز از مقاله بسیار خوب شما ممنون واقعا عالی بود پاسخ به دیدگاه فرشید علی اکبری ۰۵ / ۰۲ / ۹۵ - ۰۸:۱۶ سلام مهندس حسینی عزیز خسته نباشید. بسیار هم عالی… در واقع کیفیت بخش دوم این مقله تون نسبت به بخش اول، خیلی بهتر و کاربردی تر بود. تشکر از شما. پاسخ به دیدگاه فرشید علی اکبری ۰۵ / ۰۲ / ۹۵ - ۰۸:۱۶ سلام مهندس حسینی عزیز خسته نباشید. بسیار هم عالی… در واقع کیفیت بخش دوم این مقله تون نسبت به بخش اول، خیلی بهتر و کاربردی تر بود. تشکر از شما. پاسخ به دیدگاه مجتبی شهریور ۰۵ / ۰۲ / ۹۵ - ۰۰:۳۶ سلامبسیار مقاله خوبی بوددسستون درد نکنه عالی بود پاسخ به دیدگاه مجتبی شهریور ۰۵ / ۰۲ / ۹۵ - ۰۰:۳۶ سلامبسیار مقاله خوبی بوددسستون درد نکنه عالی بود پاسخ به دیدگاه