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

Read more: SQL Server Security

Posted via email from Jasper-net