TDE and DDM

Someone asked a question about TDE (Transparent Data Encryption) and DDM (Dynamic Data Masking), which are two different technologies that are in the security area. As I’ve mentioned in the Stairway to Dynamic Data Masking, DDM is not a security technology. It makes programming some obfuscation easy, but it’s not really security. It can be easily bypassed.

In any case, how do these work together? Or do they work together? Good questions, and I’ll answer them.

First, TDE is a technology that encrypts your data at rest, meaning when on storage devices. This handles the encryption and decryption as data is read or written from storage, without coding for it or the user having to do anything.

DDM is a technology that takes results from a query and replaces some of the data with masked values. This works in memory, after a query runs. Query processing, with WHERE, GROUP BY, etc. clauses is not affected by DDM.

These work together since data is encrypted on disk. It is read into RAM and decrypted. The query processor then assembles some of this data into a result set. Once this is done, before the results are sent to the client, DDM will make data.

Demo

I’ll setup TDE and then add DDM and run a query. Here’s a quick table and enabling TDE:

CREATE DATABASE TDE_Primer;
GO
-- create and populate a table
USE TDE_Primer
go
CREATE TABLE MyTable
( myid INT
, myname VARCHAR(20)
, mychar VARCHAR(200) 
) ;
go
DECLARE @i INT = 65;
WHILE @i < 92
  begin
   INSERT mytable SELECT @i, 'Steve Jones', REPLICATE(CHAR(@i), 200);
   SELECT @i = @i + 1;
  END;
GO

From here, let’s enable TDE. If you have a master key in the master database, you’ll get an error, but it won’t really affect things.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AlwaysU$eaStr0ngP@ssword4This';
go

-- create certificate to secure TDE
CREATE CERTIFICATE TDEPRimer_CertSecurity WITH SUBJECT = 'TDE_Primer DEK Certificate';
go

USE TDE_Primer;
GO
-- Create DEK
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDEPRimer_CertSecurity;
GO

ALTER DATABASE TDE_Primer
   SET ENCRYPTION ON;
GO

Once this is done, let’s alter our table. We also need a user that isn’t privileged.

ALTER TABLE dbo.MyTable ALTER COLUMN myname ADD MASKED WITH (FUNCTION = 'partial(1,"XXX",0)')
GO

CREATE USER JoeDBA FROM LOGIN JoeDBA

GRANT SELECT ON dbo.mytable TO JoeDBA

Now we can check the execution of a query.

2018-11-14 10_37_40-Microsoft Edge

Masked data with TDE.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.