مقدمه و مسیر یادگیری

ENCRYPTBYPASSPHRASE یکی از قابلیت‌های تخصصی SQL Server در حوزه امنیت داده است. استفاده درست از آن فقط حفظ Syntax نیست؛ باید نوع بایت‌های ورودی، محل نگهداری خروجی، سطح مجوز، رفتار تابع در برابر NULL و هزینه پردازشی را هم شناخت. این مقاله از تعریف پایه شروع می‌کند و با مثال‌های مستقل، سناریوی سازمانی، خطاهای رایج و نکات بهینه‌سازی ادامه می‌یابد.

ENCRYPTBYPASSPHRASE بدون ایجاد شیء کلید در Database، از یک عبارت عبور برای رمزنگاری استفاده می‌کند. سادگی آن برای ابزارهای کوچک جذاب است، ولی توزیع، نگهداری، تعویض و Audit عبارت عبور بر عهده معماری برنامه می‌ماند. هدف این راهنما آن است که Query نمونه به یک طراحی قابل اداره تبدیل شود؛ یعنی برنامه بتواند شکست را تشخیص دهد، تغییر تنظیم یا کلید را مدیریت کند و در زمان Restore نیز داده یا کنترل امنیتی از دست نرود.

برای دیدن جایگاه این موضوع در کنار سایر قابلیت‌ها، راهنمای جامع توابع رمزنگاری SQL Server را مطالعه کنید. در آن مقاله تفاوت هش، رمزنگاری متقارن، Passphrase، امضای دیجیتال و تنظیم VerifySignature مقایسه شده است.

تعریف و نحو ENCRYPTBYPASSPHRASE

ENCRYPTBYPASSPHRASE بدون ایجاد شیء کلید در Database، از یک عبارت عبور برای رمزنگاری استفاده می‌کند. سادگی آن برای ابزارهای کوچک جذاب است، ولی توزیع، نگهداری، تعویض و Audit عبارت عبور بر عهده معماری برنامه می‌ماند.

نکته امنیتی: نمونه‌های مقاله برای آموزش هستند. Passwordها و Secretهای نمایشی را در محیط Production استفاده نکنید و قبل از هر تغییر Server-wide یا ساخت شیء امنیتی، Change Management سازمان را رعایت کنید.

Syntax

ENCRYPTBYPASSPHRASE ( passphrase, cleartext [ , add_authenticator, authenticator ] )

پارامترها و خروجی

• passphrase: عبارت عبور مورد استفاده برای مشتق‌سازی کلید داخلی.
• cleartext: داده‌ای که باید رمز شود و می‌تواند متن یا باینری باشد.
• add_authenticator: فعال‌سازی اتصال رمزنگارانه به Authenticator.
• authenticator: مقدار sysname پایدار، مانند شناسه ردیف تبدیل‌شده.

خروجی varbinary با حداکثر ۸۰۰۰ بایت است. طول ciphertext از متن بیشتر خواهد بود و ستون مقصد باید ظرفیت مناسب داشته باشد. ورودی یا شرایط نامعتبر ممکن است NULL برگرداند.

مدل امنیت، مجوز و چرخه عمر

عبارت عبور را در متن Query، Source Control، Job Step و فایل تنظیمات ساده قرار ندهید. Secret Store و تزریق امن در Session یا Stored Procedure کنترل‌شده مناسب‌تر است. برای سامانه بزرگ، سلسله‌مراتب کلید SQL Server یا Always Encrypted معمولاً قابلیت اداره بهتری دارد.

در طراحی حرفه‌ای ENCRYPTBYPASSPHRASE مالک فنی، مالک داده و مسئول امنیت باید مشخص باشند. حساب برنامه فقط حداقل مجوز لازم را دریافت می‌کند و حساب استقرار یا DBA برای ایجاد اشیای امنیتی از مسیر کنترل‌شده استفاده می‌شود. هیچ Secret، Password، متن واضح حساس یا Private Key نباید در Source Control، خروجی خطا و لاگ عمومی ثبت شود.

چرخه عمر شامل ایجاد، فعال‌سازی، نسخه‌بندی، تعویض، Backup، Restore، ابطال و حذف کنترل‌شده است. تست بازیابی باید ثابت کند که Backup صرفاً وجود ندارد، بلکه واقعاً قابل استفاده است. در سامانه‌های چندمحیطی نیز کلیدها و Secretهای Development، Test و Production باید مستقل باشند.

مثال‌های عملی مستقل

ده مثال زیر جنبه‌های متفاوت ENCRYPTBYPASSPHRASE را از مقدار ثابت تا داده نمونه، NULL، حالت مرزی، خطای رایج، گزارش سازمانی و سنجش Performance پوشش می‌دهند. اشیایی با پیشوند Article صرفاً آزمایشی هستند و پیش از اجرا باید نام‌گذاری و سیاست Password محیط خود را جایگزین کنید.

مثال 1: رمزنگاری ساده متن

یک متن کوتاه با عبارت عبور نمایشی رمز می‌شود و اندازه ciphertext گزارش می‌گردد.

DECLARE @Cipher varbinary(8000)=ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',N'متن محرمانه');
SELECT @Cipher AS CipherText,DATALENGTH(@Cipher) AS CipherBytes;

نکته کاربردی: در تولید Passphrase را از Secret Store دریافت کنید و داخل متن Procedure ننویسید.

مثال 2: ذخیره مقدار رمز‌شده در جدول

Token دو سرویس در یک جدول حافظه‌ای رمز می‌شود.

DECLARE @Secrets TABLE(ID int PRIMARY KEY,SecretCipher varbinary(8000));
INSERT @Secrets VALUES
(1,ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',N'Token-A')),
(2,ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',N'Token-B'));
SELECT ID,DATALENGTH(SecretCipher) AS StoredBytes FROM @Secrets;

نکته کاربردی: ستون مقصد باید varbinary باشد و Backup آن همان سطح حفاظت داده اصلی را نیاز دارد.

مثال 3: استفاده از Authenticator ردیف

شناسه 501 به ciphertext قرارداد متصل می‌شود.

DECLARE @ContractID int=501;
SELECT ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',N'مبلغ قرارداد',1,CONVERT(sysname,@ContractID)) AS ContractCipher;

نکته کاربردی: Authenticator از کپی ciphertext میان قراردادها جلوگیری می‌کند، ولی جای کنترل مجوز را نمی‌گیرد.

مثال 4: رمزنگاری Unicode فارسی

ورودی nvarchar با پیشوند N ارسال می‌شود تا بایت‌های حروف فارسی حفظ شوند.

DECLARE @Text nvarchar(100)=N'پایگاه داده امن';
SELECT DATALENGTH(@Text) AS PlainBytes,DATALENGTH(ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',@Text)) AS CipherBytes;

نکته کاربردی: نوع متن اولیه را برای تبدیل صحیح در زمان Decrypt مستند کنید.

مثال 5: رفتار تابع با NULL

رمزنگاری مقدار تهی آزمایش می‌شود.

DECLARE @Text nvarchar(50)=NULL;
SELECT ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',@Text) AS CipherOfNull;

نکته کاربردی: برای فیلد اجباری، NULL را پیش از رمزنگاری رد کنید تا علت خطا مبهم نشود.

مثال 6: تمایز ciphertext در فراخوانی‌های تکراری

یک متن با یک عبارت دو بار رمز می‌شود و خروجی‌ها مقایسه می‌گردند.

DECLARE @A varbinary(8000)=ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',N'یکسان');
DECLARE @B varbinary(8000)=ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',N'یکسان');
SELECT CASE WHEN @A=@B THEN N'برابر' ELSE N'متفاوت' END AS CipherComparison;

نکته کاربردی: تصادفی‌سازی داخلی مانع نتیجه قطعی برای جست‌وجو می‌شود؛ برای Lookup راهکار جدا لازم است.

مثال 7: رمزنگاری داده مالی با Authenticator

شماره پرداخت به‌عنوان Authenticator و شرح پرداخت به‌عنوان متن واضح ورودی استفاده می‌شود.

DECLARE @PaymentID bigint=99001,@Description nvarchar(100)=N'پرداخت تأمین‌کننده الف';
SELECT ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',@Description,1,CONVERT(sysname,@PaymentID)) AS PaymentCipher;

نکته کاربردی: قالب تبدیل bigint به sysname را در همه سرویس‌ها ثابت نگه دارید.

مثال 8: نمایش روش اشتباه ذخیره در رشته و روش صحیح

به‌جای تبدیل ciphertext به متن عادی، HEX فقط برای نمایش ساخته می‌شود و مقدار واقعی باینری باقی می‌ماند.

DECLARE @Cipher varbinary(8000)=ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',N'نمونه');
SELECT @Cipher AS CorrectBinary,CONVERT(varchar(max),@Cipher,2) AS HexForDisplay;

نکته کاربردی: HEX حجم را دو برابر می‌کند؛ برای ذخیره تولیدی همان varbinary انتخاب بهتر است.

مثال 9: کنترل طول ورودی و خروجی

payload چند هزار بایتی را پیش از ورود به طراحی نهایی اندازه‌گیری می‌کنیم.

DECLARE @Payload varchar(max)=REPLICATE(CONVERT(varchar(max),'X'),5000);
DECLARE @Cipher varbinary(8000)=ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',@Payload);
SELECT DATALENGTH(@Payload) AS PlainBytes,DATALENGTH(@Cipher) AS CipherBytes;

نکته کاربردی: حداکثر اندازه را با بدترین داده و نسخه SQL Server مقصد تست کنید.

مثال 10: پردازش Batch و خط پایه کارایی

پانصد مقدار در یک Statement رمز می‌شوند و تعداد خروجی‌های معتبر شمارش می‌شود.

SET STATISTICS TIME ON;
DECLARE @Batch TABLE(ID int,CipherValue varbinary(8000));
INSERT @Batch
SELECT TOP(500) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
       ENCRYPTBYPASSPHRASE(N'Demo-Secret#1405!',CONVERT(nvarchar(20),ROW_NUMBER() OVER(ORDER BY(SELECT NULL))))
FROM sys.all_objects;
SELECT COUNT(*) AS EncryptedRows FROM @Batch WHERE CipherValue IS NOT NULL;
SET STATISTICS TIME OFF;

نکته کاربردی: هزینه مشتق‌سازی Passphrase را در نرخ تراکنش واقعی با گزینه‌های دیگر مقایسه کنید.

خطاهای رایج و روش عیب‌یابی

در عیب‌یابی ENCRYPTBYPASSPHRASE ابتدا یک نمونه کوچک با مقدار ثابت بسازید و سپس همان Session، Database Context، نوع داده و مجوز حساب برنامه را بازسازی کنید. Error Message، مقدار NULL، طول بایتی ورودی و خروجی و وضعیت اشیای امنیتی را ثبت کنید، اما داده محرمانه را در Log قرار ندهید.

• 1. هاردکدکردن Passphrase در کد. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 2. استفاده از عبارت کوتاه و قابل حدس. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 3. فراموشی Authenticator هنگام رمزگشایی. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 4. ذخیره ciphertext در varchar. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 5. نداشتن برنامه تعویض Secret. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.

روش اشتباه رایج این است که با COALESCE یک NULL امنیتی به رشته خالی تبدیل شود و فرایند موفق تلقی گردد. مسیر درست باید میان «داده واقعاً تهی»، «مجوز ناکافی»، «کلید یا Secret نامعتبر» و «ورودی خراب» تفاوت بگذارد و در رخداد مشکوک Fail Closed باشد.

Performance Considerations

مشتق‌سازی و رمزنگاری برای هر فراخوانی هزینه دارد. عملیات را Batch کنید، از رمزنگاری مکرر مقدار ثابت جلوگیری کنید و هرگز تابع را برای همه ردیف‌های جدول بزرگ در WHERE اجرا نکنید. اندازه ورودی و نرخ تراکنش را با داده واقعی Benchmark کنید.

برای ساخت Baseline، زمان CPU، Duration، تعداد Logical Read، اندازه Log و نرخ تراکنش را پیش و پس از افزودن ENCRYPTBYPASSPHRASE اندازه بگیرید. Query Store برای تغییر Plan و Extended Events برای خطا و زمان‌های غیرعادی مفید است؛ ثبت payload حساس در Session پایش ممنوع است.

بهینه‌سازی باید با حفظ مدل امنیت انجام شود. حذف Authenticator، نگهداری متن واضح یا بازکردن بیش‌ازحد مجوزها شاید آزمایش را سریع‌تر نشان دهد، ولی ریسک را به‌شدت بالا می‌برد. نتیجه قابل قبول توازنی مستند میان محرمانگی، تمامیت، دسترس‌پذیری و هزینه است.

Best Practices و کاربرد واقعی

• Passphrase بلند و تصادفی را در Secret Store نگه دارید.
• برای هر محیط Secret جدا داشته باشید.
• از Authenticator برای داده ردیفی استفاده کنید.
• Ciphertext را varbinary ذخیره کنید.
• خطا و NULL را کنترل کنید.
• برای مقیاس سازمانی جایگزین‌های قابل مدیریت را ارزیابی کنید.

این تابع برای ابزار مهاجرت کوتاه‌مدت، بسته تنظیمات رمز‌شده یا داده محدودی که ساخت Key Hierarchy برای آن توجیه ندارد قابل استفاده است. هرچه تعداد مصرف‌کنندگان بیشتر شود، مدیریت عبارت مشترک سخت‌تر و ریسک افشا بزرگ‌تر خواهد شد.

در اجرای سازمانی ENCRYPTBYPASSPHRASE بهتر است منطق در Stored Procedure یا Service مشخص متمرکز شود تا همه برنامه‌ها قرارداد یکسانی برای نوع داده، خطا و نسخه امنیتی داشته باشند. تست خودکار باید نتیجه صحیح، ورودی دستکاری‌شده، مجوز ناکافی و بازیابی پس از Restore را پوشش دهد.

سؤالات متداول

1. ENCRYPTBYPASSPHRASE در SQL Server دقیقاً چه کاری انجام می‌دهد؟

ENCRYPTBYPASSPHRASE بدون ایجاد شیء کلید در Database، از یک عبارت عبور برای رمزنگاری استفاده می‌کند. سادگی آن برای ابزارهای کوچک جذاب است، ولی توزیع، نگهداری، تعویض و Audit عبارت عبور بر عهده معماری برنامه می‌ماند. در یک پروژه واقعی باید ورودی، خروجی، مجوز و رفتار خطا پیش از استفاده نهایی با نسخه SQL Server مقصد آزمایش شود.

2. نوع خروجی ENCRYPTBYPASSPHRASE چیست و چگونه باید ذخیره شود؟

خروجی varbinary با حداکثر ۸۰۰۰ بایت است. طول ciphertext از متن بیشتر خواهد بود و ستون مقصد باید ظرفیت مناسب داشته باشد. ورودی یا شرایط نامعتبر ممکن است NULL برگرداند. انتخاب نوع ستون کوتاه یا تبدیل ضمنی از خطاهای مهم طراحی است؛ Schema باید بر پایه حداکثر خروجی قابل انتظار ساخته شود.

3. استفاده تجاری از ENCRYPTBYPASSPHRASE چه ارزشی ایجاد می‌کند؟

این قابلیت می‌تواند بخشی از کنترل محرمانگی، تمامیت یا پایش امنیتی سامانه باشد و ریسک تغییر یا افشای داده را کاهش دهد. ارزش تجاری زمانی واقعی است که کنار کنترل دسترسی، Audit، Backup و فرایند پاسخ‌گویی به رخداد پیاده‌سازی شود.

4. هزینه اجرای پروژه ENCRYPTBYPASSPHRASE چگونه برآورد می‌شود؟

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

5. ENCRYPTBYPASSPHRASE چه تفاوتی با HASHBYTES یا Always Encrypted دارد؟

HASHBYTES یک‌طرفه است و برای بازگرداندن متن طراحی نشده؛ قابلیت‌های رمزنگاری سمت سرور داده را در موتور قابل پردازش می‌کنند؛ Always Encrypted می‌تواند کلید و plaintext را از Database Engine دور نگه دارد. انتخاب درست تابع به مدل تهدید و نیاز عملیاتی بستگی دارد.

6. چه زمانی برای ENCRYPTBYPASSPHRASE به مشاوره SQL Server نیاز داریم؟

وقتی داده حساس تولیدی، چند برنامه مصرف‌کننده، چرخش کلید، الزامات قانونی یا دسترس‌پذیری بالا مطرح است، بازبینی معماری ارزش زیادی دارد. مشاوره باید خروجی‌های قابل تحویل مانند Threat Model، ماتریس مجوز، Runbook بازیابی و آزمون Performance داشته باشد.

7. رایج‌ترین علت خطا یا NULL در ENCRYPTBYPASSPHRASE چیست؟

هاردکدکردن Passphrase در کد، استفاده از عبارت کوتاه و قابل حدس، فراموشی Authenticator هنگام رمزگشایی از علت‌های متداول‌اند. عیب‌یابی را با نوع داده، طول واقعی، وضعیت اشیای امنیتی، مجوز کاربر و اجرای یک نمونه حداقلی در همان Session شروع کنید.

8. اثر ENCRYPTBYPASSPHRASE بر Performance چقدر است؟

مشتق‌سازی و رمزنگاری برای هر فراخوانی هزینه دارد. عملیات را Batch کنید، از رمزنگاری مکرر مقدار ثابت جلوگیری کنید و هرگز تابع را برای همه ردیف‌های جدول بزرگ در WHERE اجرا نکنید. اندازه ورودی و نرخ تراکنش را با داده واقعی Benchmark کنید. نتیجه را با STATISTICS TIME، Query Store یا ابزار پایش مناسب روی بار مشابه تولید بسنجید و از تعمیم یک آزمایش کوچک خودداری کنید.

9. بهترین روش امنیتی هنگام استفاده از ENCRYPTBYPASSPHRASE چیست؟

Passphrase بلند و تصادفی را در Secret Store نگه دارید، برای هر محیط Secret جدا داشته باشید، از Authenticator برای داده ردیفی استفاده کنید. علاوه بر آن، اصل کمترین دسترسی، جداسازی محیط‌ها و آزمون Restore باید به‌صورت مستند و دوره‌ای اجرا شود.

10. ENCRYPTBYPASSPHRASE با کدام نسخه‌های SQL Server سازگار است؟

سطح پشتیبانی دقیق تابع، الگوریتم و محدودیت طول میان نسخه‌ها و سرویس‌های ابری می‌تواند متفاوت باشد. پیش از انتشار، مستند نسخه مقصد و Compatibility Level را بررسی و همه مثال‌ها را در محیط Stage اجرا کنید؛ الگوریتم‌های قدیمی را برای طراحی جدید انتخاب نکنید.

سؤالات مصاحبه SQL Server

1. چرا ENCRYPTBYPASSPHRASE به‌تنهایی یک راهکار امنیتی کامل نیست؟

زیرا امنیت به مدیریت هویت، مجوز، کلید یا Secret، ثبت رخداد، Backup، چرخه تغییر و مدل تهدید وابسته است و یک تابع فقط یکی از کنترل‌ها را اجرا می‌کند.

2. چگونه نوع داده ورودی و خروجی ENCRYPTBYPASSPHRASE را کنترل می‌کنید؟

تبدیل را صریح می‌کنم، طول بایتی را با DATALENGTH می‌سنجم، Unicode را از varchar جدا می‌کنم و تست Round-trip یا اعتبارسنجی خودکار می‌نویسم.

3. برای جلوگیری از افت Performance چه می‌کنید؟

ابتدا با Predicate ایندکس‌پذیر دامنه ردیف را کم می‌کنم، عملیات امنیتی را فقط روی داده لازم انجام می‌دهم و هزینه CPU، حافظه و Log را با بار واقعی اندازه می‌گیرم.

4. برنامه بازیابی قابلیت ENCRYPTBYPASSPHRASE چیست؟

وابستگی‌ها و نسخه‌ها را مستند، Backup امن تهیه، Restore را در محیط جدا تمرین و معیار RTO و RPO را با مالک کسب‌وکار هماهنگ می‌کنم.

5. چه تست‌هایی پیش از Production لازم است؟

تست مقدار صحیح، NULL، طول مرزی، ورودی Unicode، مجوز ناکافی، داده دستکاری‌شده، همزمانی، Failover و بازیابی از Backup را اجرا می‌کنم.

چک‌لیست نهایی

1. Syntax و محدودیت ENCRYPTBYPASSPHRASE با نسخه مقصد کنترل شده است.
2. نوع و طول ورودی و خروجی صریح است.
3. مجوزها بر اساس اصل کمترین دسترسی تنظیم شده‌اند.
4. Secret یا plaintext در Log و کد منبع وجود ندارد.
5. تست NULL، Unicode، مرز طول و داده دستکاری‌شده اجرا شده است.
6. Benchmark و معیار قابل قبول Performance ثبت شده است.
7. Backup، Restore، چرخش و Runbook رخداد آزمایش شده‌اند.

جمع‌بندی

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

برای مقایسه این موضوع با شش قابلیت دیگر، به مقاله مادر توابع رمزنگاری SQL Server با مثال‌های کامل بازگردید و پیش از انتخاب نهایی، مدل تهدید و محدودیت نسخه مقصد را مستند کنید.