نوشته های امیرحسین قاسمی

ایجاد دسترسی Dynamic RLS برای Multidimensional OLAP

امیرحسین قاسمی — Wed, 20 Oct 2021 18:32:14 +0330

ایجاد دسترسی Dynamic Row Level Security برای پروژه Multidimensional OLAP زمانی که نیاز دارید تعداد کاربرهای زیادی به پروژه BI شما دسترسی داشته باشند کاربرد فراوانی دارد.Dynamic Row Level Security With OLAP MDXدر حالت های معمول با تعریف Role و ایجاد Member که از جنس لاگین ویندوزی بودند میتوانستیم به یک یا چند کاربر از طریق Active directory و Domain دسترسی RLS بدهیم. این کار ضریب خطای بالا و از طرفی زمان بر است. راه حل سریع تر و ایمن تر DRLS است.در این مقاله مراحلی که برای این پیاده سازی گذرانده شد رو با شما به اشتراک میگذارم.فرض بر این است که Cub بطور صحیح ایجاد شده و در حال کار کردن است.Cubدر اینجا میخواهیم بر اساس Dimension project کاربران دیتا ها را مربوط به پروژه خود را مشاهده کنند.حالا به دو چیز دیگر نیازمندیم :به یک جدول که Account ها را برای ما مشخص کند.و جدول دیگری که بگوید کدام Account به کدام پروژه دسترسی دارد. این کار با استفاده از رابطه AccountId و ProjectId امکان پذیر است :Resource 1 ----> Project A Resource 1 ----> Project B Resource 1 ----> Project C Resource 2 ----> Project A Resource 2 ----> Project E Resource 3 ----> Project FNew measure and Dimensionباید دقت داشت که جدول Fact / Measure با سطوح دسترسی مد نظر ایجاد و یک Dimension برای Account ها که منبع اصلی فیلتر ما هستند ایجاد نموده.ایده ای که در اینجا وجود دارد این است که در زمان اتصال کاربر ابتدا AccountId او گرفته میشود و بعد گزارش به او نمایش داده میشود.حالا باید رل داینامیک را ایجاد کرد.با باز کردن Solution Explorer و کلیک راست بر روی رل یک رل جدید ایجاد کرده.New roleدرتب data source گزینه read را انتخاب کنیدRead Access sourceدر تب Cub نیز دسترسی ها را به Read داده شود.Read access cubبرای همه Dimension ها نیز دسترسی Read را تنظیم داده شود.و مهمترین اتفاق و جادوی این تکنیک در اینجا اتفاق میافتد :اگر به دیاگرام دقت کرده باشید ارتباط بین حساب ها با دسترسی پروژه ها و بعد از پروژه ها به ترتیب است.حالا اگر بتوانیم حساب محدودی را معرفی کنیم آن وقت بر اساس دسترسی آن حساب به پروژه ها، فقط پروژه هایی فیلتر میشود که آن حساب به آن دسترسی دارد و بقیه جداول و ارتباطات نیز از همین روند استفاده میکنند. چرا که جدول پروژه به Fact های ما متصل است و از این مسیر این دیتاها محدود میشود.حال Dimension Data را باز میکنیم :Dimension Dataحالا Attribute Hierarchy مورد نظر که ProjectName هست را انتخاب میکنیم.حالا برروی تب Advanced رفته و در فیلد اول کوئری MDX زیر را مینویسیم:NONEMPTY( [Project].[Project Name].[Project Name].MEMBERS, ( [Measures].[Project ID], StrToMember(“[Users].[Resource NT Account].&[” + username() + “]”) ) )برای تست این عملکرد در Visual studio میتوان خط NT Account را میتوان تغییر داد و نتیجه را بررسی کرد. [Users].[Resource NT Account].&[ad\amirhossein]و برای مشاهده بدون فیلتر میتوان یک رل متناسب با رل دیگری داشت که قاعدتا کوئری MDX مجزایی نیاز دارد. وارد بخش Cub شده، تب Browser را زده از Security Context گزینه Current User را انتخاب نمود.Set userاگر شما دسترسی ادمین داشته باشید همه دیتاها را مشاهده میکنید.View sample data by adminحالا دسترسی را به یک مدیر پروژه محدود میکنیم و انتظارباید داشته باشیم که دیتاهای متناسب با آن حساب فیلتر بشوند.برای این کار کد MDX را تغییر و بجای amirhossein از حسابی به نام account استفاده میکنم.Set roleاین بار بجای current user از role استفاده کرده و رل dynamic را انتخاب کرده.View sample data by roleخوب این نتیجه نشان میدهد که با تغییر رخ داده در MDX متغییری که در جدول داشتیم میتواند مدیریت کند که کدام حساب چه دیتایی را نمایش دهد.اکنون اگر این گزارش را در بستر هایی نظیر اکسل و گزارش سازها ارائه دهید فقط نیاز دارید که اطلاعات حساب کاربری برای user ها در جدول users قرار داده و هر فردی دیتای مورد نظر خود را مشاهده کند.برگرفته از مقاله Diego Pereira

رمز گذاری بر روی یک ستون در SQL SERVER با روش Master key

امیرحسین قاسمی — Thu, 22 Oct 2020 01:22:54 +0330

اگر برای شما پیش آمده که بخواهید یک ستون خاص مثل شماره تلفن یا پسورد را مستقیم در دیتابیس Encrypt کنید و در زمان مورد نیاز آن را تحویل دهید، این مقاله می تواند تا حد زیادی به شما کمک کند.در این آموزش Master key را بطور کامل مرور و یک ستون از دیتابیس را encrypt و سپس decrypt می کنیم.رمزگذاری در SQL SERVERقبل از شروع هر چیزی باید متذکر باشم که روش های دیگری برای رمزگذاری برروی اطلاعات وجود دارد که مبحث این مقاله نیست و سنجش برتری روش ها به اعتقاد بنده وابستگی به نوع بیزنس مورد بحث، معماری، نحوه طراحی سیستم و منابع سازمانی ما دارد.تشریح مراحلدر این شیوه باید بدانید که دسترسی به انجام عملیات ها توسط چه کاربرانی ممکن است و برای جلوگیری از سو استفاده ابتدا مدیریت کاربران سیستم را اصلاح کنید. امکان Master key را فعال و با دستورات Open و Close آن را فراخوانی کنید تا دیتا را به مقصد برسانید.برای این کار باید از SP, Function, View می توان استفاده نمود اما باید توجه داشته باشید که پسورد Master key شما درون آنها باید Open شود. پس دو راه وجود دارد 1. سایر کاربران به ویرایش آنها و باز کردن آنها دسترسی نداشته باشند. 2. تمامی دستورات را دستور with encryption قبل از کلمه کلیدی AS برروی دیتابیس اجرا نمایید. توجه داشته باشید که در سناریو دوم حتما باید نسخه ای از SP/Function/View ها داشته باشید چرا که بعد از encrypt شدن با این شیوه حتی خود شما هم دیگر به ویرایش آن ها دسترسی نخواهید داشت.چگونه یک MASTER KEY بسازیم؟برای شروع یک دیتابیس با نام Encrypt_Test و جدول Person می سازیم. هدف رمزنگاری برروی شماره تلفن اشخاص است :Create database Encrypt_Test GO Create table Person( ID int not null identity ,FirstName Nvarchar(50) null ,LastName Nvarchar(50) null ,Phone nvarchar(30) null ) -- ثبت اطلاعات نمونه Insert into Person (FirstName,LastName,Phone) values(N'علی',N'محمدی','09121212112') ,(N'ساناز',N'کرمی','09121313113') ,(N'سیاوش',N'قلی زاده','09121515115')حال برای این دیتابیس یک Master key ایجاد می کنیم:Use Encrypt_Test GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asdfghjkl1`234567)(*&^%LKJHGF'; --پسورد بسیار قوی ایجاد کنیدبرای بررسی صحت وجود Master Key از کوئری زیر استفاده نمایید:Select name ,principal_id -- این آی دی برای مالک این کلید است ,symmetric_key_id -- آی دی منحصربفرد کلید در دیتابیس ,key_length -- طول کلید ایجاد شده به bit ,key_algorithm -- الگوریتم استفاده شده ربای ساخت کلید ,algorithm_desc -- تشریح الگوریتم مورد استفاده ,create_date -- تاریخ ایجاد ,modify_date -- آخرین تاریخ تغییرات ,key_guid -- (GUID) که خودکار تولید می شود from sys.symmetric_keysهمچنین با دستور زیر می توانید MASTER KEY ایجاد شده را حذف نمایید:DROP MASTER KEY;از یک Master key می توان Backup تهیه کرد و آن را در زمان مورد نیاز Restore نمود.OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sfj5300osdVdgwdfkli7'; BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey' ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg'; RESTORE MASTER KEY FROM FILE = 'c:\backups\keys\AdventureWorks2012_master_key' DECRYPTION BY PASSWORD = '3dH85Hhk003#GHkf02597gheij04' ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';خوب، تا اینجا یادگرفته شد که یک Master key ایجاد و با آن رفتارهای مختلفی داشته باشیم.حال نیاز داریم که بدانیم چگونه یک ستون را encrypt کنیم :روش اول: رمزگذاری متقارن شامل احراز هویتدر این روش یک کلید Salt باید وجود داشته باشد که در اینجا ساده ترین حالت ممکن از آن وجود دارد.الف. یک Certificate برای این کار می سازیم :CREATE CERTIFICATE PhoneNumber WITH SUBJECT = 'Customer Phone Numbers'; GO CREATE SYMMETRIC KEY PhoneNumber_Key256 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE PhoneNumber; ب. ستون جدیدی به نام PhoneNumber اضافه می کنیم:ALTER TABLE Person ADD PhoneNumber varbinary(160);ج. حالا اطلاعات قدیمی را باید رمزنگاری کنیم و برروی ستون جدید قرار دهیم.UPDATE Person SET PhoneNumber = EncryptByKey(Key_GUID('PhoneNumber_Key256') , Phone, 1, ('SHA1', CONVERT( varbinary , Phone))); GOد. حال برای فراخوانی و بررسی صحت کار انجام شده روی این ستون از دستور زیر استفاده کنید:OPEN SYMMETRIC KEY PhoneNumber_Key256 DECRYPTION BY CERTIFICATE PhoneNumber; GO SELECT Phone ,PhoneNumber AS 'Encrypted PhoneNumber' ,CONVERT(nvarchar,DecryptByKey(PhoneNumber, 1 , HashBytes('SHA1', CONVERT(varbinary, Phone)))) AS 'Decrypted card number' FROM Person; روش دوم : رمزگذاری متقارن سادهبرای این مثال ستون Phone را به ستون Mobile بصورت رمزگذاری انتقال و بدون نیاز به آن و احراز هویت مورد استفاده قرار می دهیم.الف. یک Certificate برای این کار می سازیم :CREATE CERTIFICATE MobileNumber WITH SUBJECT = 'Customer cell phone number'; GO CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MobileNumber;ب. ستون جدیدی به نام Mobile اضافه می کنیم:ALTER TABLE Person ADD Mobile varbinary(128);ج. حالا اطلاعات قدیمی را باید رمزنگاری کنیم و برروی ستون جدید قرار دهیم:OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MobileNumber; GO UPDATE Person SET Mobile = EncryptByKey(Key_GUID('SSN_Key_01'), Phone); GO د. حال برای فراخوانی و بررسی صحت کار انجام شده روی این ستون از دستور زیر استفاده کنید:OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE Person; GO SELECT Phone, Mobile AS 'Encrypted ID Number', CONVERT(nvarchar, DecryptByKey(Mobile)) AS 'Decrypted ID Number' FROM Personدرصورتی که این مطلب برای شما مفید بود یا نکته ای در خصوص آن داشتید، ممنون خواهم شد که نظر خودتون رو برای بنده کامنت کنید.منبع : doc.microsoft

ثبت یا ذخیره سابقه تغییرات در SQL SERVER با Temporal table

امیرحسین قاسمی — Sun, 18 Oct 2020 16:18:23 +0330

شاید برای شما هم پیش آمده باشد که به دنبال بهترین راه حل تاریخچه نویسی/لاگ برداری از تغییرات جداول دیتابیس در SQL باشید. در این مقاله بایک مثال کاربردی به تشریح این مهم پرداخته ایم.برای این کار در دیتابیس های SQL SERVER می توان چند راه حل پیشنهاد داد:1. استفاده از تریگر یا جاب برای نگارش یک نسخه متناسب با کد مد نظر خودمان، این کار ضریب خطای بالا و یا کاهش سرعت OLTP همراه است.2. استفاده از CDC برای عدم I/O از فایل mdf وخواندن سریع اطلاعات توسط از ldf و در نهایت انتقال آنها با یک جاب به مقصد مورد نظر بدون درگیر کردن فایل اصلی دیتابیس، با سرعتی بالا این عملیات صورت میپذیرد اما باید دقت داشت که CDC ها عموما در فرآیند ETL کاربرد دارند و مدیریت JOB در استفاده از آنها کمی پیچیده تر است.3. استفاده از temporal tables که از نسخه 2016 به بعد اختیار کاربران قرار گرفت. مدیریت ساده امکان ایجاد تغییرات سریع و پیاده سازی آسان، از خصوصیات مثبت این شیوه است. سرعت مطلوب تر نسبت به تریگرها، ضریب اطمینان بالاتر در ذخیره دیتا نسبت به روش CDC این روش را منطقی تر از سایر روش ها معرفی میکند. (برخی محدودیت ها در نسخه 2017 و 2019 برداشته شده است.)TEMPORAL TABLEدر این مقاله سعی داریم به محدودیت ها و چرایی های استفاده از امکان جدیدتر شرکت مایکروسافت بپردازیم که به آن temporal table , system version و historical table نیز گفته می شود.اگر قصد استفاده از جداول نسخه سازی یا همان Temporal Table را در SQL-SERVER داریم، باید ملاحظاتی را به علت ماهیت System-versioning بودن آنها لحاظ کنیم، که بصورت تیتر وارد به موارد زیر را اشاره شده :یک temporal table باید دارای کلید اصلی برای حفظ یکپارچگی و ارتباط با جدول سوابق باشد و جدول سوابق نمی تواند دارای کلید اصلی باشد.ستون های SysStartTime و SysEndTime برای ذخیره مقادیر شروع و پایان تغییرات هستند باید با نوع داده datetime2 ایجاد شوند. و آنها را به PERIOD FOR SYSTEM_TIME باید معرفی کرد همانند مثال زیر که ValidForm و ValidTo در آنها وجود دارد:CREATE TABLE Employee ( [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED , [Name] nvarchar(100) NOT NULL , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));اگر یک جدول از نوع temporal را بخواهید تبدیل به دو جدول عادی کنید باید از دستور زیر استفاده کنید :ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF )اگر بخواهید یک جدول عادی را تبدیل به جدول temporal کنید از دستور زیر می توان استفاده کرد :1. ستون های مورد نیاز را افزوده2. فعال کردن جدول سوابقALTER TABLE Employee ADD ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME()) , ValidTo datetime2 (2) GENERATED ALWAYS AS ROW END HIDDEN constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99' , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo); GO ALTER TABLE Employee SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));اگر میخواهید نام جدول سوابق و جدول اصلی را همانند انتخاب کنید باید از Schema متفاوت برای تعریف آنها استفاده کنید.بصورت پیش فرض این جدوال PAGE compressed هستند.محدودیت ها:اگر جدول کنونی شما پارتیشن شود، جدول سوابق در فایل گروه اصلی قرار خواهد گرفت، زیرا تنظیمات پارتیشنینگ به طور خودکار از جدول اصلی به جدول سوابق replicate نخواهد شد.امکان استفاده از FILETABLE و ستون هایی با نوع داده FILESTREAM ندارند.جداول از نوع node و edge را نمی توان به جداول temporal تغییر داد.در هنگام طراحی باید دقت داشته باشید که علیرقم پشتیبانی از نوع داده های varchar(max), varbinary(max), text و image در این جداول سرعت و عملکرد سیستم بشدت کاهش پیدا خواهد کرد.امکان استفاده از Linked Server نیز وجود ندارد و باید از همان دیتابیس با این جداول کار کنید. این مساله از جهتی مثبت و از جهت دیگر منفی است این موضوع را خودتان در زمان طراحی باید تشخیص دهید.این جداول نمی توانند شامل primary key, foreign key, table or column constraints باشند.اگر از این جداول در view ها استفاده کنیم، نمیتوان برای آن view این ستون ها را index نمود. (کوئری ها از SYSTEM_TIME استفاده می کنند. )امکان آنلاین با دستور WITH ONLINE = ON هیچ تاثیری برروی ستون های جداول سوابق ایجاد نمیکند و ویرایش ستون ها نیز در صورت فعال بودن این امکان هیچ تفاوتی با حالت غیرفعال بودن آن ندارد.دستورات TRUNCATE TABLE و DROP TABLE نیز در جداول temporal پشتیبانی نمی شوند.در جداول سوابق بصوررت مستقیم نمیتوان ویرایش داده انجام داد و گزینه Edit 200 row values نیز غیر فعال است.در این جداول گزینه Design table نیز دیگر وجود ندارد و برای تغییرات باید کوئری نوشته شود. امکان ON DELETE CASCADE و ON UPDATE CASCADE در این جداول پشتیبانی نمی شود. برای چنین امکانی باید از تریگر نوع after یا منطق برنامه نویسی استفاده نمایید. (این محدودیت از نسخه 2017 به بالا برداشته شده است.)تریگرهای نوع INSTEAD OF پشتیبانی نمی شوند.امکان replication با محدودیت هایی قابل دسترس است :Always On: پشتیبانی کاملChange Data Capture and Change Data Tracking : پشتیبانی فقط از جدول اصلیSnapshot and transactional replication : فقط برای یک ناشر قابل استفاده است.Merge replication : پشتیبانی نمی شود.مزایا:در نگارش کوئری ها فقط از جدول اصلی استفاده می شود و برای استفاده از جدول سوابق کوئری temporal نوشته شود. این مساله موجب درک و خوانایی بهتر کوئری ها می شود.از این قابلیت در جداول in-memory می توان استفاده نمود. در مثال زیر ProductInventory یک جدول in-memory system-versioned است :البته برای این منظور باید اطمینان حاصل کنید که دیتابیس شما از امکان in-memory پشتیبانی می کند یا خیر اگر چنین فرضی را در نظر بگیریم با کد زیر میتوانیم چنین جدولی را ایجاد کنیم :USE TemporalProductInventory GO BEGIN --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first IF ((SELECT temporal_type FROM SYS.TABLES WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2) BEGIN ALTER TABLE [dbo].[ProductInventory] SET (SYSTEM_VERSIONING = OFF) END DROP TABLE IF EXISTS [dbo].[ProductInventory] DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory] END GO CREATE TABLE [dbo].[ProductInventory] ( ProductId int NOT NULL, LocationID INT NOT NULL, Quantity int NOT NULL CHECK (Quantity >=0), SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME(SysStartTime,SysEndTime), --Primary key definition CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId) ) WITH ( MEMORY_OPTIMIZED=ON, SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[ProductInventoryHistory], DATA_CONSISTENCY_CHECK = ON ) ) CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);اگر بخواهیم برای مدل بالا یک روال ذخیره شده داشته باشیم از کوئری زیر می توان کمک گرفت :CREATE PROCEDURE [dbo].[spUpdateInventory] @productId int, @locationId int, @quantityIncrement int WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'English') UPDATE dbo.ProductInventory SET Quantity = Quantity + @quantityIncrement WHERE ProductId = @productId AND LocationId = @locationId /*If zero rows were updated than this is insert of the new product for a given location*/ IF @@rowcount = 0 BEGIN IF @quantityIncrement < 0 SET @quantityIncrement = 0 INSERT INTO [dbo].[ProductInventory] ( [ProductId] ,[LocationID] ,[Quantity] ) VALUES ( @productId ,@locationId ,@quantityIncrement END END;یک استراتژی بهینه برای index کردن می توان داشت که بشدت کارایی را بهبود می بخشد و آن هم استفاده از B-tree rowstore index در جدول اصلی و clustered columns store index and برای جدول سوابق است. در ضمن مقالات مایکروسافت به شدت توصیه می کنند که در جدول سوابق بر روی ستون های پایان ورژن گذاری مانند ValidTo و ValidForm در مثال بالا index گذاری صورت پذیرد. مطلوب است این index از نوع clustered باشد و در غیر اینصورت دست کم nonclustered آن استفاده شود.موارد زیر (objects/properties ) بعد از ایجاد یا تبدیل به جدول temporal به جدول سوابق منتقل نمیشود:Period definitionIdentity definitionIndexesStatisticsCheck constraintsTriggersPartitioning configurationPermissionsRow-level security predicatesاز این جدوال برای خروجی های power BI نیز می توان استفاده کرد :در نهایت جداول سوابق را زیر مجموعه جداول اصلی می شوند نمیتوان به عنوان یک زنجیره اطلاعاتی با سایر جداول سوابق پیوند و ارتباط داد. البته این امکان از طریق جدول اصلی ممکن است. همانند مثال زیر میتوان از اطلاعات این سوابق بهرمند شد :SELECT * FROM Employee FOR SYSTEM_TIME BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000' WHERE EmployeeID = 1000 ORDER BY ValidFrom;و یاSELECT * FROM Employee FOR SYSTEM_TIME ALL WHERE EmployeeID = 1000 ORDER BY ValidFrom;و یاSELECT * FROM Employee FOR SYSTEM_TIME CONTAINED IN ('2014-01-01 00:00:00.0000000', '2015-01-01 00:00:00.0000000') WHERE EmployeeID = 1000 ORDER BY ValidFrom;برگرفته از: doc.microsoft