Prevent Tampering of Encrypting Data Using add_authenticator Argument of EncryptByKey
This article is one of several articles discussing some of the best practices for encrypting data. This article demonstrates how the @add_authenticator argument of the ENCRYPTBYKEY function can help prevent tampering with encrypted data.
Imagine the following scenario: The DBA is encrypting the salary column for all employees in such a way that people with authorization to access the table, but no access to the encryption key can see and manipulate the table, but cannot access the salary in plaintext. Mallory is one such employee, who has SELECT, INSERT & UPDATE on the table as required for her daily job, but no access to the encryption keys protecting the salary column.
CREATE TABLE employees( employee_id int identity primary key, name nvarchar(256), salary_crypt varbinary(8000));
go
CREATE CERTIFICATE cert_demo WITH SUBJECT = 'Encryption demo';
go
CREATE SYMMETRIC KEY key_employee WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE cert_demo;
go
OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;
go
INSERT INTO employees VALUES ( N'Alice',
ENCRYPTBYKEY( key_guid('key_employee'),
CONVERT(varbinary(100), 50000.00)));
INSERT INTO employees VALUES ( N'Bob',
ENCRYPTBYKEY( key_guid('key_employee'),
CONVERT(varbinary(100), 1000.00)));
INSERT INTO employees VALUES ( N'Mallory',
ENCRYPTBYKEY( key_guid('key_employee'),
CONVERT(varbinary(100), 1000.00)));
go
--1 Alice 5000
--2 Bob 1000
--3 Mallory 1000
SELECT employee_id, name, CONVERT(decimal,
DECRYPTBYKEY(salary_crypt)) AS salary FROM employees;
go
CLOSE SYMMETRIC KEY key_employee;
Go
CLOSE SYMMETRIC KEY key_employee;
go
CREATE USER [mallory] WITHOUT LOGIN;
go
GRANT UPDATE ON employees TO [mallory];
go
GRANT SELECT ON employees TO [mallory];
go
In this scenario, Mallory may not be able to recover the plaintext from anybody else in the company, but she may still be able to modify her own salary. She may not be able to see the salary for Alice, her manager, but she can easily guess that Alice’s salary is higher than her own. What would Mallory do? Simply copy Alice’s salary into her own row.
EXECUTE AS USER = 'mallory';
go
--Msg 15151, Level 16, State 1, Line 1
--Cannot find the symmetric key 'key_employee', because it does not exist or you do not have permission.
OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;
go
--1 Alice 0x... (Alice’s salary)
--2 Bob 0x... (Bob’s salary)
--3 Mallory 0x... (Mallory’s salary)
SELECT * FROM employees;
go
-- Mallory can copy Alice’s salary into her own row
-- Alice's ID = 1
-- Mallory's ID = 3
DECLARE @ceo_grade_salary varbinary(8000);
SELECT @ceo_grade_salary = salary_crypt FROM employees WHERE employee_id = 1;
UPDATE employees SET salary_crypt = @ceo_grade_salary WHERE employee_id = 3;
go
--1 Alice 0x... (Alice’s salary)
--2 Bob 0x... (Bob’s salary)
--3 Mallory 0x... (Alice’s salary)
-- Looks like a successful attack at a glance…
SELECT * FROM employees;
go
REVERT;
go
If the ciphertext for salary was created without any form of integrity check that takes into account the context in which the value is meaningful (i.e. it hasn’t been copied from one row to another), mallory’s attack may be successful.
-- ... and it was indeed a successful attack!
--
--1 Alice 5000
--2 Bob 1000
--3 Mallory 5000
SELECT employee_id, name, CONVERT(decimal,
DECRYPTBYKEY(salary_crypt)) AS salary FROM employees;
Go
In order to prevent these kind of attacks using SQL Server encryption built-ins, the application developer may make use of the @add_authenticator parameter set to 1 and set the @authenticator parameter to a unique-per-row, immutable value such as the employee ID in this example (which also happens to be the primary key in this case).
DROP TABLE employees;
go
CREATE TABLE employees( employee_id int identity primary key,
name nvarchar(256), salary_crypt varbinary(8000));
go
OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;
go
-- This time we will use the employee ID as
-- @authenticator for the encryption field
-- Given the simplicity of the nature of this demo, I will create the
-- rows first (to populate the ID) and add the salaries later
--
INSERT INTO employees VALUES ( N'Alice', null);
INSERT INTO employees VALUES ( N'Bob', null);
INSERT INTO employees VALUES ( N'Mallory', null);
go
-- Update each salary using the employee_id as @authenticator
UPDATE employees SET salary_crypt =
ENCRYPTBYKEY( key_guid('key_employee'),
CONVERT(varbinary