Row Level Security در SQL Server | محدود کردن دسترسی کاربران SQL Server امنیت SQL Server نوشته شده توسط: سید محمد حسینی تاریخ انتشار: ۱۸ اسفند ۱۳۹۴ آخرین بروزرسانی: 13 اسفند 1403 زمان مطالعه: 4 دقیقه ۵ (۲) مفهوم Row Level Security، فرض کنید که ما در SQL Server جدولی داریم که در آن اطلاعات تامین کنندگان و سفارشات قرار دارد. این اطلاعات برای کسب و کار ما اهمیت فراوانی دارند و می خواهیم دسترسی برخی از کارمندان را به این اطلاعات محدود کنیم. می خواهیم هر یک از کارمندان بتوانند فقط سفارشات ثبت شده توسط خودشان را بر اساس کد کارمندی شان مشاهده کنند. برای این کار SQL Server 2016 دارای ویژگی های جدیدی است. مفهوم Row Level Security در SQL Server 2016 قابلیتی به نام Row Level Security (RLS) معرفی شده است. که می تواند امکان کنترل دسترسی به هر یک از سطرهای جدول را برای ما فراهم کند. RLS به ما امکان می دهد که به آسانی مشخص کنیم که کدام کاربر می تواند به کدام داده ها دسترسی داشته باشد. با حداکثر شفافیت برای برنامه کاربردی. این قابلیت به ما این امکان را می دهد که داده ها را بر اساس شناسه کاربری یا سیاست های امنیتی(Security Policy) خاصی محدود کنیم. مشاهده کاملترین و بروزترین آموزش sql server در نیک آموز ایجاد داده های نمونه برای بررسی 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 را غیر فعال کرد. افراد علاقهمند میتوانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئرینویسی گسترش دهند. 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 فقط دارای دو رکورد است که مربوط به یک سال گذشته می باشد. مفهوم Row Level Security، مقاله قبل با چگونگی استفاده از 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 مفهوم Row Level Security، حال اگر ما دستور SELECT را بدون استفاده از SESSION_CONTEXT استفاده کنیم، خروجی زیر را خواهیم داشت: اگر SESSION_CONTEXT را با استفاده از رویه ذخیره شده sp_set_session_context مقداردهی کنیم خروجی های زیر را خواهیم داشت: افراد علاقهمند میتوانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئرینویسی گسترش دهند. حال اجازه دهید که یک دستور 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 را مشاهده کنیم، گزاره تعریف شده را مشاهده خواهیم کرد. سخن پایانی مفهوم Row Level Security، در این مقاله ما در مورد گزاره های 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) کرد تا گزاره تعریف شده برای هر یک از سیاست های امنیتی را به دست آورد. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۲ اولین نفر باش معرفی نویسنده مقالات 11 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده سید محمد حسینی معرفی محصول مسعود طاهری دوره آموزش امنیت در SQL Server 2022 7.000.000 تومان 4.200.000 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ داوود طاهرخانی ۱۲ / ۱۰ / ۹۵ - ۱۰:۴۵ عالی و مفید بود ممنون مهندس پاسخ به دیدگاه محسن فرید ۱۶ / ۰۱ / ۹۵ - ۰۱:۵۶ سلام من یه سایت دارم می خوام بزارم رو سرور خودشون من چه جوری کاری کنم چون تو وب کانفیگ یوزر و پسورد هست به اس کی یو ال دسترسی نداشته باشن پاسخ به دیدگاه مسعود طاهری ۱۶ / ۰۱ / ۹۵ - ۰۲:۱۷ سلام در دوره امنیت این موارد بررسی شده است https://nikamooz.com/product/security-course-sql-server/ برای اینکار Connection String را Encrypt کنید و از Application Role استفاده کنید پاسخ به دیدگاه ebadi577@hotmail.com ۲۷ / ۱۲ / ۹۴ - ۱۲:۵۶ با سلام و عرض ادب ممنون از زحمات شما دوست عزیز . مطلب بسیار خوبی را ارائه دادید سپاسگزارم .یه خواهش از استاد مسعودی ممکنه یه مقاله راجع به این که میخواهیم ببینیم که چه کسانی در کل یک شرکت بر روی دستگاه خود SQL Server نصب کرده اند . در ضمن اگر ممکن است یک لیک مربوط به ذخیره مطالب را قرار دهید . پاسخ به دیدگاه مهدی ربانی ذبیحی ۲۴ / ۱۲ / ۹۴ - ۱۲:۰۵ سلام بسیار عالی کاربردی بود با تشکر پاسخ به دیدگاه محمد رحیمی ۲۲ / ۱۲ / ۹۴ - ۱۰:۲۲ سلامواقعا استفاده کردم. پاسخ به دیدگاه مسعود طاهری ۱۹ / ۱۲ / ۹۴ - ۰۱:۱۶ سلام استفاده از این روش به ازای Business یوزرها هم امکان پذیر است. کافی است که از Session Context کد مربوط به Business User را قرار دهید و… هدف نهایی : محدود کردن نمایش رکوردها با توجه به Business Userهای سیستم ضمنا این موضوع به همراه همین سناریوی که اشاره کردم در همایش SQL Server 2016 بررسی شد. موفق باشید پاسخ به دیدگاه فرشید علی اکبری ۱۹ / ۱۲ / ۹۴ - ۰۱:۰۶ سلام از بابت ارائه مطلب خوب تون که میشه گفت یکی از نقاط قوت و کاربردی اسکوئل سرور۲۰۱۶ برای کاربرانش هست تشکر میکنم. ولی نکته اینجاست که برخی از برنامه نویسان دوتا User تعریف میکنند؛ یکی برای خودشون ویکی هم برای کلیه کاربران نهایی استفاده کننده از نرم افزارشون.(منظورم اینه که هرچندتا کاربر هم که به نرم افزارشون معرفی شده باشه با یک نام کاربری امکان دسترسی به بانک اطلاعاتی را دارند)؛ اینجاست که این قابلیت مهم رو یکجورایی از دست داده و باید با همون روش چرخ دستی وسنتی قبل، این کنترل رو در دست بگیرند. اگه راهکار مناسبی برای این دسته از برنامه نویسان هم در نظر دارید لطفاً بفرمائید. پاسخ به دیدگاه مجتبی شهریور ۱۸ / ۱۲ / ۹۴ - ۱۰:۵۷ سلاممقالتون بسیار عالی کاربردی و آموزنده بود … پاسخ به دیدگاه مسعود طاهری ۱۶ / ۰۱ / ۹۵ - ۰۲:۱۷ سلام در دوره امنیت این موارد بررسی شده است https://nikamooz.com/product/security-course-sql-server/ برای اینکار Connection String را Encrypt کنید و از Application Role استفاده کنید پاسخ به دیدگاه