امن سازی اکانت sa در SQL Server
گاهی برای عیب یابی عدم ارسال ایمیل نیاز هست بررسی کنیم آیا smtp server مورد نظر جواب میدهد؟ آیا در شبکه به آن دسترسی داریم؟ یا برای مثال آیا ISP ما این عملیات را بلاک نکرده است؟
مثال الف) بررسی دسترسی به gmail
در خط فرمان تایپ کنید، telnet و سپس دکمه enter را فشار دهید.
در ادامه سطر زیر را وارد نمائید و enter کنید:
open smtp.gmail.com 587
Connecting To smtp.gmail.com...
صفحه زیر را باید مشاهده نمائید:
مثال ب) بررسی دسترسی به میل سرور لوکال
همانند مثال قبل است فقط قسمت open آن به شکل زیر تغییر خواهد کرد:
open localhost 25
open 127.0.0.1 25
در بسیاری موارد (مانند سیستمهای Multi Tenant) لازم هست تا مانع از این شویم که دادههای کاربران با هم تداخل پیدا کند و یا آنها بتوانند به دادههای هم دسترسی داشته باشند. مثلا میخواهیم کاربران هر شعبه از سازمان، تنها به اطلاعات شعبه خودشان دسترسی داشته باشند. یک کار ساده، پردردسر و بسیار بد آن است که از برنامه نویسها بخواهیم در هر کوئری عبارتی را اضافه کنند که سطح دسترسی را چک کند. اما اگر برنامه نویس جایی فراموش کرد چی؟ اگر سیاست دسترسی پیچیدهتر بود و مبنی بر پارامترهای مختلف محاسبه میشد چه خواهد شد؟ این راهکار در حجم بزرگ غیر مطمئن و غیرقابل نگهداری است.
در EF6 قابلیتی به نام Interception وجود دارد که با استفاده از آن میتوان سیاست دسترسی به داده را در لایههای پایینی طراحی کرد. در این روش برنامه نویس لایه هایی بالا، بدون آنکه درگیر مفاهیمی مانند Tenant و سیاستها بشود، میتواند به راحتی کوئری هایش را تولید کند. سپس EF به طور خودکار تغییری در کوئریها خواهد داد تا دسترسیهای لازم رعایت کرده باشد. برای اینکار میتوانید از کتابخانه EntityFramework.DynamicFilters استفاده کنید.
این روش هم علی رغم همه مزایا معایبی هم دارد. اگر بخواهیم از همین پایگاه داده استفاده کنیم ولی در محیط دات نت نباشیم و یا از EF6 استفاده نکنیم، دوباره مشکلات اغاز میشوند. سیاستها را باید در همه جا کپی کنیم و در صورت لزوم هم، مجددا همه را تغییر دهیم.
در SQL Server 2016 قابلیتی به نام Row Level Security وجود دارد، که به ما اجازه میدهد سیاستهای دسترسی با داده را در لایه پایگاه داده متمرکز کنیم. در این صورت اپلیکشنها هیچگونه آگاهی ایی نسبت به سیاستها نخواهند داشت و درگیر این مفاهیم در سطح کد نخواهیم بود. همچنین در صورت لزوم به تغییر سیاست ها، فقط لازم است تغییراتی را در پایگاه داده بدهیم. با این روش، به هر طریقی و از هر ابزاری که به پایگاه داده کوئری هایمان را ارسال کنیم، سیاستهای دسترسی به داده اعمال خواهند شد و امنیت بالا و البته ریزدانه ای (granular) را خواهیم داشت.
در مثال زیر خواهیم دید که چگونه میتوان با استفاده از EF6 از ویژگی RLS بهره برد. این مثال یکی دیگر از کاربردهای Interception را نیز توضیح میدهد.
میانگین متحرک یا moving average به چند دسته تقسیم میشود که سادهترین آنها میان متحرک ساده است.
برای محاسبه میانگین متحرک باید بازه زمانی مورد نظر را مشخص کنیم. مثلا میانگین فروش در 3 روز گذشته.
به جدول زیر توجه بفرمایید:
میانگین متحرک فروش سه روز و چهار روز گذشته در جدول فوق قابل مشاهده است.
بطور مثال مقدار میانگین متحرک سه روزه برای روز چهارم برابر است با جمع فروش سه روز گذشته تقسیم بر سه. یعنی 3/(10+12+13)
و برای روز ششم میانگین متحرک 4 روزه برابر است با جمع فروش چهار روز گذشته و تقسیم آنها بر چهار. یعنی 10+12+13+16 تقسیم بر 4 که برابر است با 12.7
در نمودار زیر، خط قرم رنگ مربوط به میانگین متحرک ساده (میانگین فروش سه روز گذشته) است و خط آبی رنگ نیز میزان فروش است
راه حل در SQL Server 2012
توسط توابع window این مساله را به سادگی میتوانیم حل کنیم. همانطور که مشاهده میشود در تصویر زیر. کافیست ما به سطرهایی در بازهی سه سطر قبل تا یک سطر قبل (برای میانگین متحرک سه روزه) دسترسی پیدا کرده و میانگین آن را بگیریم.
ابتدا این جدول را ایجاد و تعدادی سطر برای نمونه در آن درج کنید:
CREATE TABLE Samples ( [date] SMALLDATETIME, selling SMALLMONEY ); INSERT Samples VALUES ('2010-12-01 00:00:00', 10), ('2010-12-02 00:00:00', 12), ('2010-12-03 00:00:00', 13), ('2010-12-04 00:00:00', 16), ('2010-12-05 00:00:00', 19), ('2010-12-06 00:00:00', 23), ('2010-12-07 00:00:00', 26), ('2010-12-08 00:00:00', 27), ('2010-12-09 00:00:00', 20), ('2010-12-10 00:00:00', 18), ('2010-12-11 00:00:00', 19);
SELECT [date], selling, CASE WHEN rnk < 4 THEN NULL ELSE mv END AS SimpleMovingAverage FROM (SELECT *, AVG(selling) OVER(ORDER BY [date] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS mv, ROW_NUMBER() OVER(ORDER BY [date]) AS rnk FROM Samples ) AS d;
قلب query دستور ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING میباشد.
به این معنا که سطرهایی در بازهی سه سطر قبل و یک سطر قبل را در Window انتخاب کرده و عمل میاگنین گیری را بر اساس مقادیر مورد نظر انجام بده.
راه حل در SQL Server 2005
به درخواست یکی از کاربران من راه حلی را پیشنهاد میکنم که جایگزین مناسبی برای روش قبلی است در صورت عدم استفاده از نسخه 2012. توابع window در اینگونه مسائل بهترین عملکرد را خواهند داشت.
SELECT S.[date], S.selling, CASE WHEN COUNT(*) < 3 THEN NULL ELSE AVG(s) END AS SimpleMovingAverage FROM Samples AS S OUTER APPLY (SELECT TOP(3) selling FROM Samples WHERE [date] < S.[date] ORDER BY [date] DESC) AS D(s) GROUP BY S.[date], S.selling ORDER BY S.[date];
FOR FUN
توسط توابع Analytical ای چون LAG نیز میتوان اینگونه مسائل را حل نمود. بطور مثال توسط تابع LAG به یک مقدار قبلی، دو مقدار قبلی و سه مقدار قبلی دسترسی پیدا کرده و آنها را با یکدیگر جمع نموده و تقسیم بر تعدادشان میکنیم یعنی:
select [date], selling, ( lag(selling, 1) over(order by [date]) + lag(selling, 2) over(order by [date]) + lag(selling, 3) over(order by [date]) ) / 3 from Samples;
public AddUserStatus Add(User user) { if (ExistsByEmail(user.Email)) return AddUserStatus.EmailExist; if (ExistsByUserName(user.UserName)) return AddUserStatus.UserNameExist; _users.Add(user); return AddUserStatus.AddingUserSuccessfully; }
using System.Linq; using System.Web.Mvc; using Iris.Datalayer.Context; namespace Iris.Web.Controllers { public class MigrationController : Controller { public ActionResult RemoveDuplicateUsers() { var db = new IrisDbContext(); var lstDuplicateUserGroup = db.Users .GroupBy(u => u.UserName) .Where(g => g.Count() > 1) .ToList(); foreach (var duplicateUserGroup in lstDuplicateUserGroup) { foreach (var user in duplicateUserGroup.Skip(1).Where(user => user.UserMetaData != null)) { db.UserMetaDatas.Remove(user.UserMetaData); } db.Users.RemoveRange(duplicateUserGroup.Skip(1)); } db.SaveChanges(); return new EmptyResult(); } } }
مقایسه ساختار جداول دیتابیس کاربران IRIS با ASP.NET Identity
ساختار جداول ASP.NET Identity به شکل زیر است:
ساختار جداول سیستم کنونی هم بدین شکل است:
همان طور که مشخص است در هر دو سیستم، بین ساختار جداول و رابطهی بین آنها شباهتها و تفاوت هایی وجود دارد. سیستم Identity دو جدول بیشتر از IRIS دارد و برای جداولی که در سیستم کنونی وجود ندارند نیاز به انجام کاری نیست و به هنگام پیاده سازی Identity، این جداول به صورت خودکار به دیتابیس اضافه خواهند شد.
دو جدول مشترک در این دو سیستم، جداول Users و Roles هستندکه نحوهی ارتباطشان با یکدیگر متفاوت است. در Iris بین User و Role رابطهی یک به چند وجود دارد ولی در Identity، رابطهی بین این دو جدول چند به چند است و جدول واسط بین آنها نیز UserRoles نام دارد.
از آن جایی که من قصد دارم در سیستم جدید هم رابطهی بین کاربر و نقش چند به چند باشد، به پیش فرضهای Identity کاری ندارم. به رابطهی کنونی یک به چند کاربر و نقشش نیز دست نمیگذارم تا در انتها با یک کوئری از دیتابیس، اطلاعات نقشهای کاربران را به جدول جدیدش منتقل کنم.
جدولی که در هر دو سیستم مشترک است و هستهی آنها را تشکیل میدهد، جدول Users است. اگر دقت کنید میبینید که این جدول در هر دو سیستم، دارای یک سری فیلد مشترک است که دقیقا هم نام هستند مثل Id، UserName و Email؛ پس این فیلدها از نظر کاربرد در هر دو سیستم یکسان هستند و مشکلی ایجاد نمیکنند.
یک سری فیلد هم در جدول User در سیستم IRIS هست که در Identity نیست و بلعکس. با این فیلدها نیز کاری نداریم چون در هر دو سیستم کار مخصوص به خود را انجام میدهند و تداخلی در کار یکدیگر ایجاد نمیکنند.
اما فیلدی که برای ذخیره سازی پسورد در هر دو سیستم استفاده میشود دارای نامهای متفاوتی است. در Iris این فیلد Password نام دارد و در Identity نامش PasswordHash است.
برای اینکه در سیستم کنونی، نام فیلد Password جدول User را به PasswordHash تغییر دهیم قدمهای زیر را بر میداریم:
وارد پروژهی DomainClasses شده و کلاس User را باز کنید. سپس نام خاصیت Password را به PasswordHash تغییر دهید. پس از این تغییر بلافاصله یک گزینه زیر آن نمایان میشود که میخواهد در تمام جاهایی که از این نام استفاده شده است را به نام جدید تغییر دهد؛ آن را انتخاب کرده تا همه جا Password به PasswordHash تغییر کند.
برای این که این تغییر نام بر روی دیتابیس نیز اعمال شود باید از Migration استفاده کرد. در اینجا من از مهاجرت دستی که بر اساس کد هست استفاده میکنم تا هم بتوانم کدهای مهاجرت را پیش از اعمال بررسی و هم تاریخچهای از تغییرات را ثبت کنم.
برای این کار، Package Manager Console را باز کرده و از نوار بالایی آن، پروژه پیش فرض را بر روی DataLayer قرار دهید. سپس در کنسول، دستور زیر را وارد کنید:
Add-Migration Rename_PasswordToPasswordHash_User
اگر وارد پوشه Migrations پروژه DataLayer خود شوید، باید کلاسی با نامی شبیه به 201510090808056_Rename_PasswordToPasswordHash_User ببینید. اگر آن را باز کنید کدهای زیر را خواهید دید:
public partial class Rename_PasswordToPasswordHash_User : DbMigration { public override void Up() { AddColumn("dbo.Users", "PasswordHash", c => c.String(nullable: false, maxLength: 200)); DropColumn("dbo.Users", "Password"); } public override void Down() { AddColumn("dbo.Users", "Password", c => c.String(nullable: false, maxLength: 200)); DropColumn("dbo.Users", "PasswordHash"); } }
بدیهی هست که این کدها عمل حذف ستون Password را انجام میدهند که سبب از دست رفتن اطلاعات میشود. کدهای فوق را به شکل زیر ویرایش کنید تا تنها سبب تغییر نام ستون Password به PasswordHash شود.
public partial class Rename_PasswordToPasswordHash_User : DbMigration { public override void Up() { RenameColumn("dbo.Users", "Password", "PasswordHash"); } public override void Down() { RenameColumn("dbo.Users", "PasswordHash", "Password"); } }
سپس باز در کنسول دستور Update-Database را وارد کنید تا تغییرات بر روی دیتابیس اعمال شود.
دلیل اینکه این قسمت را مفصل بیان کردم این بود که میخواستم در مهاجرت از سیستم اعتبارسنجی خودتان به ASP.NET Identity دید بهتری داشته باشید.
تا به این جای کار فقط پایگاه داده سیستم کنونی را برای مهاجرت آماده کردیم و هنوز ASP.NET Identity را وارد پروژه نکردیم. در بخشهای بعدی Identity را نصب کرده و تغییرات لازم را هم انجام میدهیم.