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

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

DECRYPTBYKEY ciphertext ساخته‌شده با کلید متقارن باز در Session را به بایت‌های اولیه برمی‌گرداند. تابع نام کلید نمی‌گیرد، زیرا اطلاعات لازم در ciphertext وجود دارد و SQL Server کلید باز مناسب را پیدا می‌کند. هدف این راهنما آن است که Query نمونه به یک طراحی قابل اداره تبدیل شود؛ یعنی برنامه بتواند شکست را تشخیص دهد، تغییر تنظیم یا کلید را مدیریت کند و در زمان Restore نیز داده یا کنترل امنیتی از دست نرود.

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

تعریف و نحو DECRYPTBYKEY

DECRYPTBYKEY ciphertext ساخته‌شده با کلید متقارن باز در Session را به بایت‌های اولیه برمی‌گرداند. تابع نام کلید نمی‌گیرد، زیرا اطلاعات لازم در ciphertext وجود دارد و SQL Server کلید باز مناسب را پیدا می‌کند.

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

Syntax

DECRYPTBYKEY ( { ciphertext | @ciphertext } [ , add_authenticator, authenticator ] )

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

• ciphertext: مقدار varbinary تولیدشده توسط ENCRYPTBYKEY.
• add_authenticator: باید با مقدار استفاده‌شده هنگام رمزنگاری هماهنگ باشد.
• authenticator: همان مقدار پایدار و همان نوع منطقی مرحله رمزنگاری.

خروجی DECRYPTBYKEY از نوع varbinary با حداکثر ۸۰۰۰ بایت است. برای بازیابی متن باید آن را صریحاً به nvarchar یا varchar مناسب تبدیل کنید؛ نوع اشتباه می‌تواند خروجی ناخوانا یا بریده بسازد.

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

اعطای SELECT روی جدول نباید به‌تنهایی به معنای اجازه رمزگشایی باشد. دسترسی به Certificate، کلید و رویه رمزگشایی را محدود کنید و داده واضح را در Log، جدول موقت طولانی‌عمر یا خروجی عمومی قرار ندهید.

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

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

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

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

مثال 1: Round-trip پایه رمزنگاری و رمزگشایی

متن را رمز می‌کنیم و سپس خروجی DECRYPTBYKEY را به nvarchar اولیه برمی‌گردانیم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Cipher varbinary(8000)=ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'داده محرمانه');
SELECT CONVERT(nvarchar(100),DECRYPTBYKEY(@Cipher)) AS PlainText;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: نوع و طول CONVERT باید با نوع ورودی اصلی هماهنگ باشد.

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

مقدار رمز‌شده را در جدول حافظه‌ای قرار می‌دهیم و فقط ردیف انتخابی را بازیابی می‌کنیم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Vault TABLE(ID int PRIMARY KEY,CipherValue varbinary(8000));
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
INSERT @Vault VALUES(1,ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'Alpha')),(2,ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'Beta'));
SELECT ID,CONVERT(nvarchar(20),DECRYPTBYKEY(CipherValue)) AS PlainText FROM @Vault WHERE ID=2;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: WHERE پیش از محاسبه روی ردیف‌های غیرضروری اعمال می‌شود و هزینه را محدود می‌کند.

مثال 3: رمزگشایی با Authenticator صحیح

ciphertext به OrderID پیوند خورده و با همان Authenticator باز می‌شود.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @OrderID int=7001,@Cipher varbinary(8000);
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
SET @Cipher=ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'1250000',1,CONVERT(sysname,@OrderID));
SELECT CONVERT(nvarchar(30),DECRYPTBYKEY(@Cipher,1,CONVERT(sysname,@OrderID))) AS Amount;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: هم Flag و هم Authenticator باید با مرحله Encrypt یکسان باشند.

مثال 4: شکست امن با Authenticator نادرست

همان ciphertext را با شناسه ردیف دیگری باز می‌کنیم تا حفاظت در برابر جابه‌جایی را ببینیم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Cipher varbinary(8000);
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
SET @Cipher=ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'مقدار ردیف ۱۰',1,N'10');
SELECT DECRYPTBYKEY(@Cipher,1,N'20') AS WrongRowResult;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: برنامه باید NULL را به‌عنوان شکست اعتبارسنجی مدیریت کند و plaintext حدسی نسازد.

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

تابع روی مقدار NULL اجرا می‌شود تا قرارداد داده در View یا Procedure مشخص باشد.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Cipher varbinary(8000)=NULL;
SELECT DECRYPTBYKEY(@Cipher) AS PlainBinary;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: NULL واقعی را با شکست رمزگشایی از طریق وضعیت داده یا ستون همراه تفکیک کنید.

مثال 6: بازیابی متن فارسی بدون خراب‌شدن حروف

متن nvarchar رمز‌شده را با تبدیل درست nvarchar بازیابی می‌کنیم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Cipher varbinary(8000)=ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'گزارش محرمانه فارسی');
SELECT CONVERT(nvarchar(200),DECRYPTBYKEY(@Cipher)) AS PersianText;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: تبدیل به varchar می‌تواند در Code Page نامناسب اطلاعات Unicode را از بین ببرد.

مثال 7: محدودکردن دسترسی در Stored Procedure نمونه

الگوی Procedure فقط یک EmployeeID را می‌گیرد و همان ردیف را پس از Filter رمزگشایی می‌کند.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DROP TABLE IF EXISTS dbo.EmployeeSecretDemo;
CREATE TABLE dbo.EmployeeSecretDemo(EmployeeID int PRIMARY KEY,SecretValue varbinary(8000));
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
INSERT dbo.EmployeeSecretDemo VALUES(1,ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'شماره حساب ۱'));
SELECT EmployeeID,CONVERT(nvarchar(100),DECRYPTBYKEY(SecretValue)) AS SecretValue
FROM dbo.EmployeeSecretDemo WHERE EmployeeID=1;
CLOSE SYMMETRIC KEY Article_AES256_Key;
DROP TABLE dbo.EmployeeSecretDemo;

نکته کاربردی: در تولید، Query را داخل Procedure دارای کنترل نقش و Audit قرار دهید.

مثال 8: تشخیص کلید بسته و نسخه اصلاح‌شده

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

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Cipher varbinary(8000)=ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'آزمون کلید');
CLOSE SYMMETRIC KEY Article_AES256_Key;
SELECT DECRYPTBYKEY(@Cipher) AS ClosedKeyResult;
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
SELECT CONVERT(nvarchar(50),DECRYPTBYKEY(@Cipher)) AS CorrectResult;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: این الگو علت رایج NULL را بدون چاپ خود کلید عیب‌یابی می‌کند.

مثال 9: فیلتر پیش از رمزگشایی در گزارش

هزار ردیف ساخته می‌شود اما فقط وضعیت فعال و بازه شناسه کوچک رمزگشایی می‌گردد.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Rows TABLE(ID int PRIMARY KEY,IsActive bit,CipherValue varbinary(8000));
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
INSERT @Rows
SELECT TOP(1000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),1,ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'مقدار')
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
SELECT ID,CONVERT(nvarchar(20),DECRYPTBYKEY(CipherValue)) AS PlainText FROM @Rows WHERE IsActive=1 AND ID BETWEEN 10 AND 20;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: شرط ایندکس‌پذیر باید پیش از عملیات CPU-heavy دامنه داده را کم کند.

مثال 10: اندازه‌گیری هزینه و شمارش موفقیت

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

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
SET STATISTICS TIME ON;
DECLARE @Rows TABLE(ID int,CipherValue varbinary(8000));
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
INSERT @Rows SELECT TOP(500) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'Payload') FROM sys.all_objects;
SELECT COUNT(*) AS ValidRows FROM @Rows WHERE DECRYPTBYKEY(CipherValue) IS NOT NULL;
CLOSE SYMMETRIC KEY Article_AES256_Key;
SET STATISTICS TIME OFF;

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

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

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

• 1. تبدیل خروجی به نوع و طول نادرست. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 2. انتظار خطای واضح به‌جای NULL هنگام بسته‌بودن کلید. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 3. استفاده Authenticator متفاوت. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 4. رمزگشایی همه ردیف‌ها پیش از Filter. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 5. نمایش داده واضح در گزارش یا Log بدون کنترل. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.

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

Performance Considerations

رمزگشایی هر ردیف عملیات CPU است و روی مجموعه بزرگ گران تمام می‌شود. ابتدا با ستون‌های معمولی و ایندکس‌شده ردیف‌ها را محدود کنید و فقط ستون‌های لازم را رمزگشایی کنید. View یا Procedure کنترل‌شده می‌تواند این ترتیب را پایدار کند.

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

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

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

• Filter را پیش از Decrypt اجرا کنید.
• خروجی را به نوع اصلی و طول کافی تبدیل کنید.
• NULL را از داده واقعاً NULL تفکیک کنید.
• دسترسی رمزگشایی را از SELECT عادی جدا کنید.
• زمان بازبودن کلید را کوتاه نگه دارید.
• سناریوی Restore و چرخش کلید را تست کنید.

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

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

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

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

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

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

خروجی DECRYPTBYKEY از نوع varbinary با حداکثر ۸۰۰۰ بایت است. برای بازیابی متن باید آن را صریحاً به nvarchar یا varchar مناسب تبدیل کنید؛ نوع اشتباه می‌تواند خروجی ناخوانا یا بریده بسازد. انتخاب نوع ستون کوتاه یا تبدیل ضمنی از خطاهای مهم طراحی است؛ Schema باید بر پایه حداکثر خروجی قابل انتظار ساخته شود.

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

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

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

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

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

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

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

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

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

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

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

رمزگشایی هر ردیف عملیات CPU است و روی مجموعه بزرگ گران تمام می‌شود. ابتدا با ستون‌های معمولی و ایندکس‌شده ردیف‌ها را محدود کنید و فقط ستون‌های لازم را رمزگشایی کنید. View یا Procedure کنترل‌شده می‌تواند این ترتیب را پایدار کند. نتیجه را با STATISTICS TIME، Query Store یا ابزار پایش مناسب روی بار مشابه تولید بسنجید و از تعمیم یک آزمایش کوچک خودداری کنید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

جمع‌بندی

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

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