Testing with Hexplorer shows that ALTER TABLE ENCRYPTED does NOT encrypt existing data.

In other words, rows inserted before ALTER TABLE ENCRYPTED are not encrypted, but rows inserted after the ALTER are encrypted.

The Help does not seem say this explicitly but I dropped out of law school before learning how to read stuff like that :)

Question: How do I force those old rows to become encrypted?

(Version 17, table encryption, dbinit -ea AES256 -ek "abc" -et^)

asked 16 Aug, 09:07

Breck%20Carter's gravatar image

Breck Carter
29.3k486649957
accept rate: 20%

edited 16 Aug, 13:42

Well, as the according page claims:

When table encryption is enabled, table pages for the encrypted table, associated index pages, temporary file pages, and transaction log pages containing transactions on encrypted tables are encrypted.

and

After encrypting a table, any data for that table that was in temporary files or the transaction log before encryption still exists in unencrypted form. To address this, restart the database to remove the temporary files.

I would assume that encrypting a table SHOULD encrypt existing data, too - otherwise the second quote about data in temporary files or logs would be moot. But I have not tested this, I'm usually using database encryption...

(16 Aug, 09:38) Volker Barth
Replies hidden
1

encrypting a table SHOULD encrypt existing data

That's what I thought too, but it is NOT what it does, and I can't wait for a fix change in behavior.

The question remains, How do I force those old rows to become encrypted?

(should I post the reproducible?)

(16 Aug, 13:39) Breck Carter

I think what you are seeing are old table fragments in free pages. If you can, try the REORGANIZE TABLE statement on the table in question, shut down the database, and then see if any unencrypted data is still present.

If REORGANIZE table doesn't work, create a new table, fill it with lots of random data rows, delete it, and then check to see if the table pages containing unencrypted data were reclaimed in the process. I know this isn't any kind of solution but it should show that the unencrypted data that you are seeing resides in freed pages.

(26 Aug, 14:47) JBSchueler
Replies hidden

Excellent answer... alas, inserts only, no updates or deletes, so freed pages are ( cough :) unlikely.

(27 Aug, 15:26) Breck Carter
Comment Text Removed

I think Jack is right. When you run ALTER TABLE ENCRYPTED, we go through and encrypt each page associated with that table. My guess is that the previous (unencrypted) version of the page gets freed and the new (encrypted) version replaces it. Eventually the freed page will be reused.

(29 Aug, 07:59) Graeme Perrow

So you do not encrypt "in-situ"?

(29 Aug, 08:51) Volker Barth

Your guess makes sense, even though...

"The placement of each leading row segment on a page is immutable." ...at least according to my book :)

However, I'm guessing that doesn't affect most of the data in big fAt blob columns like in this case...

INSERT t2 VALUES ( 1, 1, 
   REPEAT ( 'This text should be INVISIBLE. ', 10000 ) );

It should be fairly easy to confirm your guess with some carefully crafted strings.

HOWEVER... "eventually the freed page will be reused" is not good enough for a client who wants to encrypt a VERY BIG table in-place; i.e., with minimal outage, so no unload reload.

So, back to the original question, restated...

How do I remove the clear data after ALTER TABLE ENCRYPTED?

Will the REORGANIZE suggestion work?

(29 Aug, 13:31) Breck Carter
showing 2 of 7 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×41

question asked: 16 Aug, 09:07

question was seen: 117 times

last updated: 29 Aug, 13:35