|
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^) |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- 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:
question asked: 16 Aug '19, 09:07
question was seen: 853 times
last updated: 29 Aug '19, 13:35
Well, as the according page claims:
and
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...
That's what I thought too, but it is NOT what it does, and I can't wait for a
fixchange in behavior.The question remains, How do I force those old rows to become encrypted?
(should I post the reproducible?)
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.
Excellent answer... alas, inserts only, no updates or deletes, so freed pages are ( cough :) unlikely.
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.
So you do not encrypt "in-situ"?
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...
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?