Mistake: treating encrypted values as interchangeable. An attacker can copy one employee’s ciphertext into another row; without an authenticator, decryption may reveal the attacker’s chosen value. An authenticator column binds the ciphertext to an additional value, often the row’s primary key, and decryption fails when that value does not match. section 14.6.2 section 14.8
Employee salary substitution
In the Employees table, EmployeeID authenticates an encrypted salary. Copying Bob Walford’s encrypted salary into Robin Round’s row leaves Robin’s decryption as NULL because the EmployeeID authenticator does not match. section 14.6.2
Listing 14.13 Encrypting salaries with an authenticator
ALTER TABLE dbo.Employees
ADD EncryptedSalaryWithAuth VARBINARY(8000) ;
GO
OPEN SYMMETRIC KEY SalaryKey
DECRYPTION BY CERTIFICATE SalaryCert ;
UPDATE dbo.Employees
SET EncryptedSalaryWithAuth =
ENCRYPTBYKEY(
Key_GUID('SalaryKey'),
CAST(Salary AS VARCHAR(10)),
1,
CAST(EmployeeID AS VARBINARY(8000))
) ;
CLOSE SYMMETRIC KEY SalaryKey ;
Adds an encrypted salary column and encrypts each salary alongside its EmployeeID authenticator. section 14.6.2
Listing 14.14 Simulating the attack with an authenticator
UPDATE dbo.Employees
SET EncryptedSalaryWithAuth =
(
SELECT EncryptedSalaryWithAuth
FROM dbo.Employees
WHERE FirstName = 'Bob'
AND LastName = 'Walford'
)
WHERE FirstName = 'Robin'
AND LastName = 'Round' ;
Simulates replacing Robin’s encrypted value with Bob’s value. section 14.6.2
Listing 14.15 Assessing the impact of the attack with authenticator column
OPEN SYMMETRIC KEY SalaryKey
DECRYPTION BY CERTIFICATE SalaryCert ;
SELECT
FirstName
, LastName
, Role
, CAST(
CONVERT(
VARCHAR(10),
DECRYPTBYKEY(
EncryptedSalaryWithAuth,
1 ,
CONVERT(VARBINARY, EmployeeID)
)
) AS MONEY
)
FROM dbo.Employees
WHERE (FirstName = 'Bob' AND LastName = 'Walford') OR
(FirstName = 'Robin' AND LastName = 'Round') ;
CLOSE SYMMETRIC KEY SalaryKey ;
Decrypts with the current row’s EmployeeID and demonstrates that the copied ciphertext no longer decrypts successfully. section 14.6.2
