مطالب
پیاده سازی یک سیستم دسترسی Role Based در Web API و AngularJs - بخش دوم
در بخش پیشین مروری اجمالی را بر روی یک سیستم مبتنی بر نقش کاربر داشتیم. در این بخش تصمیم داریم تا به جزئیات بیشتری در مورد سیستم دسترسی ارائه شده بپردازیم.
همانطور که گفتیم ما به دو صورت قادر هستیم تا دسترسی‌های (Permissions) یک سیستم را تعریف کنیم. روش اول این بود که هر متد از یک کنترلر، دقیقا به عنوان یک آیتم در جدول Permissions قرار گیرد و در نهایت برای تعیین نقش جدید، مدیر باید جزء به جزء برای هر نقش، دسترسی به هر متد را مشخص کند. در روش دوم مجموعه‌ای از API Methodها به یک دسترسی تبدیل شده است.
مراحل توسعه این روش به صورت زیر خواهند بود:
  1. توسعه پایگاه داده سیستم دسترسی مبتنی بر نقش
  2. توسعه یک Customized Filter Attribute بر پایه Authorize Attribute
  3. توسعه سرویس‌های مورد استفاده در Authorize Attribute
  4. توسعه کنترلر Permissions: تمامی APIهایی که در جهت همگام سازی دسترسی‌ها بین کلاینت و سرور را بر عهده دارند در این کنترلر توسعه داده میشود.
  5. توسعه سرویس مدیریت دسترسی در کلاینت توسط AngularJS

توسعه پایگاه داده

در این مرحله پایگاه داده را به صورت Code First پیاده سازی مینماییم. مدل Permissions به صورت زیر میباشد:
    public class Permission
    {
        [Key]
        public string Id { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public string Area { get; set; }
        public string Control { get; set; }
        public virtual ICollection<Role> Roles { get; set; }
    }
در مدل فوق همانطور که مشاهده میکنید یک ارتباط چند به چند، به Roles وجود دارد که در EF به صورت توکار یک جدول اضافی Junction اضافه خواهد شد با نام RolesPermissions. Area و Control نیز طبق تعریف شامل محدوده مورد نظر و کنترل‌های روی ناحیه در نظر گرفته می‌شوند. به عنوان مثال برای یک سایت فروشگاهی، برای بخش محصولات می‌توان حوزه‌ها و کنترل‌ها را به صورت زیر تعریف نمود:
 Control Area 
 view  products
 add  products
 edit  products
 delete  products

با توجه به جدول فوق همانطور که مشاهده می‌کنید تمامی آنچه که برای دسترسی Products مورد نیاز است در یک حوزه و 4 کنترل گنجانده میشود. البته توجه داشته باشید سناریویی که مطرح کردیم برای روشن سازی مفهوم ناحیه یا حوزه و کنترل بود. همانطور که میدانیم در AngularJS تمامی اطلاعات توسط APIها فراخوانی می‌گردند. از این رو یک موهبت دیگر این روش، خوانایی مفهوم حوزه و کنترل نسبت به نام کنترلر و متد است.

مدل Roles را ما به صورت زیر توسعه داده‌ایم:

    public class Role
    {
        [Key]
        public string Id { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public virtual ICollection<Permission> Permissions { get; set; }
        public virtual ICollection<User> Users { get; set; }
    }

در مدل فوق می‌بینید که دو رابطه چند به چند وجود دارد. رابطه اول که همان Permissions است و در مدل پیشین تشریح شد. رابطه‌ی دوم رابطه چند به چند بین کاربر و نقش است. چند کاربر قادرند یک نقش در سیستم داشته باشند و همینطور چندین نقش میتواند به یک کاربر انتساب داده شود.

ما در این سیستم از ASP.NET Identity 2.1 استفاده و کلاس IdentityUser را override کرده‌ایم. در مدل override شده، برخی اطلاعات جدید کاربر، به جدول کاربر اضافه شده‌اند. این اطلاعات شامل نام، نام خانوادگی، شماره تماس و ... می‌باشد.

public class ApplicationUser : IdentityUser
    {
        [MaxLength(100)]
        public string FirstName { get; set; }
        [MaxLength(100)]
        public string LastName { get; set; }
        public bool IsSysAdmin { get; set; }
        public DateTime JoinDate { get; set; }

        public virtual ICollection<Role> Roles { get; set; }
    }

در نهایت تمامی این مدل‌ها به وسیله EF Code First پایگاه داده سیستم ما را تشکیل خواهند داد.

توسعه یک Customized Filter Attribute بر پایه Authorize Attribute 

اگر در مورد Custom Filter Attributeها اطلاعات ندارید نگران نباشید! مقاله «فیلترها در MVC» تمامی آنچه را که باید در اینباره بدانید، به شما خواهد گفت. همچنین در  مقاله وب سایت  مایکروسافت به صورت عملی (ایجاد یک سیستم Logger) همه چیز را برای شما روشن خواهد کرد. حال بپردازیم به Filter Attribute نوشته شده که قرار است وظیفه پیش پردازش تمامی درخواست‌های کاربر را انجام دهد. در ابتدا کمی در اینباره بگوییم که این فیلتر قرار است چه کاری را دقیقا انجام دهد!
این فیلتر قرار است پیش از پردازش هر API Method، درخواست کاربر را با استفاده از نقشی که او در سیستم دارد، بررسی نماید که آیا کاربر به API اجازه دسترسی دارد یا خیر. برای این کار باید ما در ابتدا نقش‌های کاربر را بررسی نماییم. پس از اینکه نقش‌های کاربر واکشی شدند، باید بررسی کنیم آیا نقشی که کاربر دارد، شامل این حوزه و کنترل بوده است یا خیر؟ Area و Control دو پارامتری هستند که در سیستم پیش از هر متد، Hard Code شده‌اند و در ادامه نمونه‌ای از آن را نمایش خواهیم داد.
    public class RBACAttribute : AuthorizeAttribute
    {
        public string Area { get; set; }
        public string Control { get; set; }
        AccessControlService _AccessControl = new AccessControlService();
        public override void OnAuthorization(HttpActionContext actionContext)
        {
            var userId = HttpContext.Current.User.Identity.GetCurrentUserId();
            // If User Ticket is Not Expired
            if (userId == null || !_AccessControl.HasPermission(userId, this.Area, this.Control))
            {
                actionContext.Response = new HttpResponseMessage(HttpStatusCode.Unauthorized);
            }
        }
    }
در خط پنجم، سرویس AccessControl را فراخوانی کرده‌ایم که در ادامه به پیاده سازی آن نیز خواهیم پرداخت. متد HasPermission از این سرویس دو پارامتر id کاربر و Area و Control را دریافت میکند و با استفاده از این سه پارامتر بررسی میکند که آیا کاربر جاری به این بخش دسترسی دارد یا خیر؟ در صورت منقضی شدن ticket کاربر و یا عدم دسترسی، سرور Unauthorized status code را به کاربر باز می‌گرداند.
بلوک زیر استفاده از این فیلتر را نمایش می‌دهد:
[HttpPost]
[Route("ChangeProductStatus")]
[RBAC(Area = "products", Control = "edit")]
public async Task<HttpResponseMessage> ChangeProductStatus(StatusCodeBindingModel model)
{
// Method Body
}
همانطور که مشاهده می‌کنید کافیست RBAC را با دو پارامتر، پیش از متد نوشت. به صورت خودکار پیش از فراخوانی این متد که وظیفه تغییر وضعیت کالا را بر عهده دارد، فیلتر نوشته شده فراخوانی خواهد شد.
در بخش بعدی به بیان ادامه جریان و توسعه سرویس Access Control خواهیم پرداخت.
اشتراک‌ها
DataMasker پروژه‌ای برای درهم سازی خروجی اطلاعات Sql Server
A free data masking and/or anonymizer library for Sql Server written in .NET If you've ever needed to pull down databases from a live environment to stage or even dev you'll need to think about masking any personal information. There are options out there paid and free, however the free ones I've found do not provide genuine data and the paid options are too pricey when it's only a few tables.
DataMasker پروژه‌ای برای درهم سازی خروجی اطلاعات Sql Server
اشتراک‌ها
پشتیبانی SQL Server 2017 از بانک های گراف محور

Graph extensions in SQL Server 2017 will facilitate users in linking different pieces of connected data to help gather powerful insights and increase operational agility. Graphs are well suited for applications where relationships are important, such as fraud detection, risk management, social networks, recommendation engines, predictive analysis, dependence analysis, and IoT applications. In this session we will demonstrate how you can use SQL Graph extensions to build your application using graph data. 

پشتیبانی SQL Server 2017 از بانک های گراف محور
مطالب
بررسی دو نکته (ترفند) کاربردی در SQL Server

1- اندازه گیری تعداد Transaction‌ها در واحد زمان روی یک Database خاص در SQL Server 

جهت بدست آوردن تعداد Transaction‌ها در واحد زمان( Transactions Per Second ) روی یک Database خاص در یک سیستم عملیاتی، جهت ارتقاء سخت افزاری ، تست فشار و ... می‌توانید از یک DMV با نام sys.dm_os_performance_counters به طریق زیر استفاده نمائید:
declare @cntr_value bigint

Select @cntr_value=cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'

/* ایجاد یک تاخیر مثلاً یک ثانیه */
waitfor delay '00:00:01'

Select cntr_value -@cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'
View معرفی شده تمامی شمارنده‌های عملکردی را برای یک Instance خاص شامل می‌شود، ستون instance_name  برابر نام بانک اطلاعاتی مورد نظر می‌باشد.

2- sys.sp_MSforeachtable 

از رویه‌های ذخیره شده UnDocumented در SQL Server می‌باشد و این قابلیت را دارا است که برای هر یک از جداول موجود در  یک بانک اطلاعاتی، یک رویه‌ای را اجرا کند. برای مثال با استفاده از دستور زیر، می‌توانید تعداد سطرها، اندازه‌ی داده‌ها و ایندکس‌های یک جدول را بدست آورید

EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';
به عنوان یک مثال کاربردی، با اجرای دستور زیر می‌توان جداول بانک اطلاعاتی مورد نظرتان را از لحاظ معیارهایی که پیشتر ذکر آن رفت، مورد بررسی قرار دهید.
 USE [AdventureWorksDW2008R2]
GO

CREATE TABLE #TableSpaceUsed(
[name] [nvarchar](120) NULL,
[rows] [nvarchar](120) NULL,
[reserved] [nvarchar](120) NULL,
[data] [nvarchar](120) NULL,
[index_size] [nvarchar](120) NULL,
[unused] [nvarchar](120) NULL
) ON [PRIMARY]

Insert Into #TableSpaceUsed
EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';

Select * from #TableSpaceUsed
Order by CAST([rows] as int) desc

Drop table #TableSpaceUsed
خروجی مثال فوق به شکل زیر است.


مطالب
ابزارهای مهاجرت به OLTP درون حافظه‌ای در SQL Server 2014
در SQL Server 2014، به Management studio آن ابزارهای جدیدی اضافه شده‌اند تا کار تبدیل و مهاجرت جداول معمولی، به جداول بهینه سازی شده‌ی برای حافظه را ساده‌تر کنند. برای مثال امکان جدیدی به نام Transaction performance collector جهت بررسی کارآیی تراکنش‌های جداول و یا رویه‌های ذخیره شده در محیط کاری جاری، طراحی شده‌است. پس از آن، این اطلاعات را آنالیز کرده و بر اساس میزان استفاده از آن‌ها، توصیه‌هایی را در مورد مهاجرت یا عدم نیاز به مهاجرت به سیستم جدید OLTP درون حافظه‌ای ارائه می‌دهد. در ادامه این ابزارهای جدید را بررسی خواهیم کرد.


ابزار Memory Optimization Advisor

Memory Optimization Advisor یک Wizard مانند است که از آن برای گرفتن مشاوره در مورد تبدیل جداول موجود مبتنی بر دیسک سخت، به نمونه‌های بهینه سازی شده برای حافظه می‌توان استفاده کرد. کار آن بررسی ساختار جداولی است که قصد مهاجرت آن‌ها را دارید. برای مثال همانطور که پیشتر نیز عنوان شد، جداول بهینه سازی شده برای حافظه محدودیت‌هایی دارند؛ مثلا نباید کلید خارجی داشته باشند. این Wizard یک چنین مواردی را آنالیز کرده و گزارشی را ارائه می‌دهد. پس از اینکه مراحل آن‌را به پایان رساندید و مشکلاتی را که گزارش می‌دهد، برطرف نمودید، کد تبدیل جدول را نیز به صورت خودکار تولید می‌کند.
برای دسترسی به آن، فقط کافی است بر روی نام جدول خود کلیک راست کرده و گزینه‌ی memory optimization advisor را انتخاب کنید.


در دو قسمت اول این Wizard، کار بررسی ساختار جدول در حال مهاجرت صورت می‌گیرد. اگر نوع داده‌ای در آن پشتیبانی نشود یا قیود ویژه‌ای در آن تعریف شده باشند، گزارشی را جهت رفع، دریافت خواهید کرد. پس از رفع آن، به صفحه‌ی گزینه‌های مهاجرت می‌رسیم:


همانطور که ملاحظه می‌کنید، گروه فایل ایجاد شده در قسمت قبل، به صورت خودکار انتخاب شده‌است.
در ادامه می‌توان نام دیگری را برای جدول مبتنی بر دیسک وارد کرد. در اینجا به صورت خودکار کلمه‌ی old به آخر نام جدول اضافه شده‌است. در حین تولید جدول جدید بهینه سازی شده‌ی بر اساس ساختار جدول فعلی، این جدول قدیمی به صورت خودکار تغییر نام خواهد یافت و کلیه اطلاعات آن حفظ می‌شود.
همچنین تخمینی را نیز از مقدار حافظه‌ی مورد نیاز برای نگهداری این جدول جدید درون حافظه‌ای نیز ارائه می‌دهد. در این مثال چون رکوردی در جدول انتخابی وجود نداشته‌است، تخمین آن صفر است. عدد ارائه شده توسط آن بسیار مهم است و باید به همین میزان برای سیستم خود حافظه تهیه نمائید و یا از حافظه‌ی موجود استفاده کنید.
در پایین صفحه می‌توان انتخاب کرد که آیا داده‌های جدول فعلی، به جدول درون حافظه‌ای انتقال یابند یا خیر. به علاوه نوع ماندگاری اطلاعات آن نیز قابل تنظیم است. اگر گزینه‌ی آخر را انتخاب کنید به معنای حالت SCHEMA_ONLY است. حالت پیش فرض آن SCHEMA_AND_DATA می‌باشد که در قسمت‌های قبل بیشتر در مورد آن بحث شد.

در دو صفحه‌ی بعد، کار انتخاب hash index و range index انجام می‌شود:


در اینجا hash index بر روی فیلد ID تولید شده‌است، به همراه تعیین bucket count آن و در صفحه‌ی بعدی range index بر روی فیلد تاریخ تعریف گردیده‌است:


در آخر می‌توان با کلیک بر روی دکمه‌ی Script، صرفا دستورات T-SQL تغییر ساختار جدول را دریافت کرد و یا با کلیک بر روی دکمه‌ی migrate به صورت خودکار کلیه موارد تنظیم شده را اجرا نمود.


خلاصه‌ی این مراحل که توسط دکمه‌ی Script آن تولید می‌شود، به صورت زیر است:
USE [testdb2]
GO

EXEC dbo.sp_rename @objname = N'[dbo].[tblNormal]', @newname = N'tblNormal_old', @objtype = N'OBJECT'
GO

USE [testdb2]
GO

SET ANSI_NULLS ON
GO

CREATE TABLE [dbo].[tblNormal]
(
[CustomerID] [int] NOT NULL,
[Name] [nvarchar](250) COLLATE Persian_100_CI_AI NOT NULL,
[CustomerSince] [datetime] NOT NULL,

INDEX [ICustomerSince] NONCLUSTERED 
(
[CustomerSince] ASC
),
CONSTRAINT [tblNormal_primaryKey] PRIMARY KEY NONCLUSTERED HASH 
(
[CustomerID]
)WITH ( BUCKET_COUNT = 131072)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

GO

INSERT INTO [testdb2].[dbo].[tblNormal] ([CustomerID], [Name], [CustomerSince]) SELECT [CustomerID], [Name], [CustomerSince] FROM [testdb2].[dbo].[tblNormal_old] 

GO
که در آن ابتدا کار تغییر نام جدول قبلی صورت می‌گیرد. سپس یک جدول جدید با ویژگی MEMORY_OPTIMIZED = ON را ایجاد می‌کند. در ساختار این جدول، hash index و range index تعریف شده، قابل مشاهده هستند. در آخر نیز کلیه اطلاعات جدول قدیمی را به جدول جدید منتقل می‌کند.

علاوه بر memory optimization advisor مخصوص جداول، ابزار دیگری نیز به نام Native compilation advisor برای آنالیز رویه‌های ذخیره شده تهیه شده‌است:



آیا سیستم فعلی ما واقعا نیازی به ارتقاء به جداول درون حافظه‌ای دارد؟

تا اینجا در مورد نحوه‌ی ایجاد جداول درون حافظه‌ای و یا نحوه‌ی تبدیل جداول موجود را به ساختار جدید بررسی کردیم. ولی آیا واقعا یک چنین تغییراتی برای ما سودمند هستند؟ برای پاسخ دادن به این سؤال ابزاری به نام AMR به management studio 2014 اضافه شده‌است (Analyze, Migrate, Report). کار آن تحت نظر قرار دادن جداول و رویه‌های ذخیره شده‌ی بانک اطلاعاتی است و سپس بر اساس بار سیستم، تعداد درخواست‌های همزمان و میزان استفاده از جداول و تراکنش‌های مرتبط با آن‌ها، گزارشی را ارائه می‌دهد. بر این اساس بهتر می‌توان تصمیم گرفت که کدام جداول بهتر است به جداول درون حافظه‌ای تبدیل شوند.
برای تنظیم آن باید مراحل ذیل طی شوند:
در Management Studio، به برگه‌ی Object Explorer آن مراجعه کنید. سپس پوشه‌ی Management آن‌را یافته و بر روی گزینه‌ی Data Collection کلیک راست نمائید:


در اینجا گزینه‌ی Configure Management Data Warehouse را انتخاب نمائید. در صفحه‌ی باز شده، ابتدا بانک اطلاعاتی مدنظر را انتخاب نمائید. همچنین بهتر است بر روی دکمه‌ی new کلیک کرده و یک بانک اطلاعاتی جدید را برای آن ایجاد نمائید، تا دچار تداخل اطلاعاتی و ساختاری نگردد:


در ادامه نام کاربری را که قرار است کار مدیریت ثبت و جمع آوری اطلاعات را انجام دهد، به همراه نقش‌های آن انتخاب نمائید:


و در آخر در صفحه‌ی بعدی بر روی دکمه‌ی Finish کلیک کنید.

پس از ایجاد و انتخاب بانک اطلاعاتی Management Data Warehouse، نوبت به تنظیم گزینه‌های جمع آوری اطلاعات است:


در اینجا ابتدا سرور جاری را انتخاب کنید. پس از آن به صورت خودکار در لیست بانک‌های اطلاعاتی قابل انتخاب، تنها همان بانک اطلاعاتی جدیدی را که برای مرحله‌ی قبل ایجاد کردیم، می‌توان مشاهده کرد.


در صفحه‌ی بعد، گزینه‌ی «Transaction Performance Collection Sets» را انتخاب نمائید که دقیقا گزینه‌ی مدنظر ما جهت یافتن آماری از وضعیت تراکنش‌های سیستم است.
در ادامه بر روی گزینه‌های next و finish کلیک کنید تا کار تنظیمات به پایان برسد.

اکنون اگر به لیست وظایف تعریف شده در SQL Server agent مراجعه کنید، می‌توانید، وظایف مرتبط با جمع آوری داده‌ها را نیز مشاهده نمائید:


وظایف Stored Procedure Usage Analysis هر نیم ساعت یکبار و وظایف Table Usage Analysis هر 15 دقیقه یکبار اجرا می‌شوند. البته امکان اجرای دستی این وظایف نیز مانند سایر وظایف SQL Server وجود دارند.

همچنین در پوشه‌ی management، گزینه‌ی Data collection نیز دو زیر شاخه اضافه شده‌اند که نمایانگر آنالیز میزان مصرف جداول و رویه‌های ذخیره شده می‌باشند:


پس از این کارها باید مدتی صبر کنید (مثلا یک ساعت) تا سیستم به صورت معمول کارهای متداول خودش را انجام دهد. پس از آن می‌توان به گزارشات AMR مراجعه کرد.


برای اینکار بر روی بانک اطلاعاتی Management Data Warehouse که در ابتدای عملیات ایجاد شد، کلیک راست نمائید و سپس مراحل ذیل را طی کنید:
Reports > Management Data Warehouse > Transaction Performance Analysis Overview


در گزارش ایجاد شده، ذیل گزینه‌ی usage analysis لینک‌هایی وجود دارند که با مراجعه به آن‌ها، چارت‌هایی از میزان مصرف بانک‌های اطلاعاتی مختلف سیستم ارائه می‌شود. اگر پیام No data available را مشاهده کردید، یعنی هنوز باید مقداری صبر کنید تا کار جمع آوری اطلاعات به پایان برسد.
در این چارت‌ها بانک‌های اطلاعاتی که در سمت راست، بالای تصویر قرار می‌گیرند، انتخاب مناسبی برای تبدیل به بانک‌های اطلاعاتی درون حافظه‌ای هستند. محور افقی آن از چپ به راست بیانگر میزان کاهش سختی انتقال یک جدول به جدول درون حافظه‌ای است (با درنظر گرفتن تمام مسایلی که باید تغییر کنند یا نوع‌های داده‌ای که باید اصلاح شوند) و محور عمودی آن نمایانگر میزان بالا رفتن پاسخ دهی سیستم در جهت انجام کار بیشتر است.


هر زمان هم که کار تصمیم‌گیری شما به پایان رسید، می‌توانید بر روی گزینه‌ی Data collection کلیک راست کرده و آن‌را غیرفعال نمائید.

 
برای مطالعه بیشتر

SQL Server 2014 Field Benchmarking In-Memory OLTP and Buffer Pool Extension Features 
New AMR Tool: Simplifying the Migration to In-Memory OLTP
A Tour of the Hekaton AMR Tool
SQL Server 2014 Memory Optimization Advisor
Getting started with the AMR tool for migration to SQL Server In-memory OLTP Tables
How to Use Microsoft's AMR Tool
SQL Server 2014's Analysis, Migrate, and Report Tool