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

اگر برای شما پیش آمده که بخواهید یک ستون خاص مثل شماره تلفن یا پسورد را مستقیم در دیتابیس Encrypt کنید و در زمان مورد نیاز آن را تحویل دهید، این مقاله می تواند تا حد زیادی به شما کمک کند.

در این آموزش Master key را بطور کامل مرور و یک ستون از دیتابیس را encrypt و سپس decrypt می کنیم.

قبل از شروع هر چیزی باید متذکر باشم که روش های دیگری برای رمزگذاری برروی اطلاعات وجود دارد که مبحث این مقاله نیست و سنجش برتری روش ها به اعتقاد بنده وابستگی به نوع بیزنس مورد بحث، معماری، نحوه طراحی سیستم و منابع سازمانی ما دارد.

تشریح مراحل

1. در این شیوه باید بدانید که دسترسی به انجام عملیات ها توسط چه کاربرانی ممکن است و برای جلوگیری از سو استفاده ابتدا مدیریت کاربران سیستم را اصلاح کنید.
2. امکان Master key را فعال و با دستورات Open و Close آن را فراخوانی کنید تا دیتا را به مقصد برسانید.
3. برای این کار باید از 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