Я создаю базу данных, в которой хранится информация о сотрудниках, включая данные банковского счета. Реквизиты банковского счета хранятся, чтобы мы могли проверять наличие нынешнего или бывшего персонала, использующего нашу компанию, что в нашем случае может быть поводом для беспокойства о мошенничестве. Я читал о шифровании и расшифровке SQL Server 2008 R2 на MSDN и в других местах и придумали следующий пример сценария:
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BankDetai!5'
GO
CREATE CERTIFICATE Employees_01 WITH SUBJECT = 'Employee Bank Account Details'
GO
CREATE SYMMETRIC KEY AccountNos_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Employees_01
GO
CREATE TABLE #Bank_Accounts
( Id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_BankAccount_Id PRIMARY KEY CLUSTERED (Id ASC)
,AccountNumber VARBINARY(128) NOT NULL
,BankName NVARCHAR(255) NOT NULL
,CountryCode VARCHAR(2) NOT NULL
,CreateDate DATETIME2(0) NOT NULL
,EmployeeId INT NOT NULL
,IsActive BIT NOT NULL
,SortCode VARBINARY(128) NOT NULL
);
OPEN SYMMETRIC KEY AccountNos_01
DECRYPTION BY CERTIFICATE Employees_01;
CREATE TABLE #SampleBankAccounts
( Id INT IDENTITY(1,1)
,AccountNumber VARCHAR(255)
,BankName NVARCHAR(255)
,CountryCode VARCHAR(2)
,EmployeeId INT NOT NULL
,IsActive BIT
,SortCode VARCHAR(255))
INSERT #SampleBankAccounts
SELECT * FROM
( SELECT
'123456789' AccountNo
,'Barclays' BankName
,'US' Country
,1 Employee
,1 IsActive
,'12-34-56' SortCode
UNION
SELECT
'9876543210'
,'Barclays'
,'US'
,1
,0
,'12-34-56'
UNION
SELECT
'111111111111'
,'HSBC'
,'UK'
,2
,1
,'222222'
UNION
SELECT
'IBAN 123 456 9875 3215'
,'Nationwide'
,'ES'
,3
,1
,'00_gn321654'
)AS Samples
ORDER BY Employee
MERGE #Bank_Accounts AS Target
USING #SampleBankAccounts AS Source ON Target.EmployeeId = Source.EmployeeId
AND Source.AccountNumber = Target.AccountNumber
AND Source.BankName = Target.BankName
AND Source.CountryCode = Target.CountryCode
AND Source.SortCode = Target.SortCode
WHEN NOT MATCHED
THEN INSERT (AccountNumber, BankName,CountryCode,CreateDate,EmployeeId,IsActive,SortCode)
VALUES (
ENCRYPTBYKEY(KEY_GUID('AccountNos_01'),Source.AccountNumber)
,Source.BankName
,Source.CountryCode
,GETDATE()
,Source.EmployeeId
,Source.IsActive
,ENCRYPTBYKEY(KEY_GUID('AccountNos_01'),Source.SortCode));
GO
DROP TABLE #SampleBankAccounts
Кажется, это работает нормально, но теперь мне нужно взять переменную, например. @AccountNo
и посмотрите, совпадает ли он с каким-либо из сохраненных нами банковских счетов (по всей вероятности, я буду использовать хранимую процедуру). Я бы предпочел избегать дешифрования в любых сценариях, если это возможно, поэтому я подумал, можно ли зашифровать переменную @AccountNo
, а затем сравнить ее, чтобы мы могли увидеть совпадение:
DECLARE @MyTestBankAccount NVARCHAR(255) = '123456789'
DECLARE @MyEncryptedTestBankAccount VARBINARY(128)
OPEN SYMMETRIC KEY AccountNos_01
DECRYPTION BY CERTIFICATE Employees_01;
SELECT @MyEncryptedTestBankAccount = ENCRYPTBYKEY(KEY_GUID('AccountNos_01'),@MyTestBankAccount)
SELECT
AccountNumber
,EmployeeId
FROM Bank_Accounts
WHERE
@MyEncryptedTestBankAccount = AccountNumber
Это не работает; как видите, я использую номер счета, который должен присутствовать в таблице, но результаты не возвращаются. Я попытался расшифровать столбец с номером счета и сравнить его с переменной, но у меня появляется множество китайских иероглифов (как в this question), и они не соответствуют символам, созданным путем шифрования и дешифрования переменной, поэтому это тоже не работает для соответствия ...
Так почему же следующий запрос (с использованием данных выше) не дает мне зашифрованных и расшифрованных значений для данных учетной записи? Могу ли я использовать этот метод для поиска банковских счетов сотрудников по данному банковскому счету?
OPEN SYMMETRIC KEY AccountNos_01
DECRYPTION BY CERTIFICATE Employees_01;
SELECT
AccountNumber AS Encrypted_AccountNo
,EmployeeId AS EmployeeId
,CONVERT(NVARCHAR,DECRYPTBYKEY(AccountNumber)) AS Decrypted_AccountNo
FROM Bank_Accounts