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.

We store encrypted credit card data in our database. With our latest PA-DSS certification, I'm being asked to perform "secure delete" of credit card data when dropping card numbers and CVCs from the database. Dropping for us is either updating the field to an empty string, or deleting the record altogether.

My QSA suggested a method that I have found referenced for file system deletes, but not for databases; the old overwrite-with-random-junk 3 times kind of thing. But I have a hard time understanding how that would be effective in the database.

Has anyone had to answer to this before? I did read where SQLite and MySQL have settings that directly address the issue, but I don't see anything similar in SQL Anywhere.

Thanks for any info.


Update: the url below is one of the few I've found that reference database and secure delete...but the 5 step example they give doesn't make any sense to me (from a database perspective...or any other for that matter)

asked 15 Apr '14, 20:53

Dan%20Hacker's gravatar image

Dan Hacker
accept rate: 0%

edited 15 Apr '14, 20:59

FWIW the 5 step example is this...

Develop a manual procedure to over write PAN stored 
in databases three times, then drop or delete the data. 
For example:
Enter 1111111111111111111s.
Enter 0000000000000000000s.
Enter 1010101010101010101s.
Delete / Drop.
Replace the data in field with Tokenized PAN.

...and PAN is elsewhere documented as primary account number.

Except for not knowing what "Tokenized PAN" means, the steps make sense and are what I was talking about in my reply.

HOWEVER I am now of the opiniion (and in agreement with AlexyK77) that strong encryption of the data when it is first received is a prerequisite... SQL Anywhere is like any other DBMS, one of its primary goals is the protection of data from loss, so it make MANY redundant copies at MANY times in MANY locations... so if any of those copies are in clear text your job is infinitely harder.

(16 Apr '14, 07:44) Breck Carter

Much secure is to use database encryption: 1) pcidss also demand using DB encryption 2) when DB encrypted it's impossible to get deleted values directly from DB file. Also temporary files and transaction logs also be encrypted. Or you can use only table encryption mode.

"When table encryption is enabled, table pages for the encrypted table, associated index pages, and temporary file pages are encrypted, as well as the transaction log pages that contain transactions on encrypted tables."

permanent link

answered 16 Apr '14, 02:44

AlexeyK77's gravatar image

accept rate: 8%

I agree with you now, and I regret blathering on about overwriting... overwriting won't help if you miss one of the clear text copies.

(16 Apr '14, 07:50) Breck Carter
Replies hidden

A possible CAVEAT might be the chance that the UPDATE on the encrypted columns might lead to a row split so that the old data may not be really overwritten, as Breck has explained in his answer. That may depend on whether the new contents would require more or less bytes. I'm not sure whether this may vary for encrypted data (and possibly, encrypting NULL or empty strings might generella need no more bytes than real contents...). - In case that would be possible (or if the data row would just be deleted), it might be possible that anyone knowing the DBKEY (say, a DBA) might be able to decipher data pages that would still contain the old data.

"Might" is here used several times, so chances that this is a real threat may be very small... (and this kind of threat would be possible with current data in strongly encrypted databases in general, so it would be a risk one needs to bear, methinks).

Aside: I have never tried to decrypt database pages from strongly encrypted databases knowing the according DBKEY - it's not my sports. But I would think that the SQL Anywhere support should be able to do so, even when IVs and the like are used...

(16 Apr '14, 09:02) Volker Barth

> the UPDATE on the encrypted columns might lead to a row split

@Volker points out ANOTHER reason why my original answer is complete sh** [cough] inadequate, and why the data should be encrypted before it is inserted (or more simply, the table or the entire database should be defined as ENCRYPTED from the beginning).

(16 Apr '14, 10:38) Breck Carter

Fwiw, we never store unencrypted PAN's in the database. We are being asked to securely delete the encrypted data that we "drop" after either approval is complete or the retention policy kicks in.

(16 Apr '14, 11:10) Dan Hacker

FYI the dbkey is not the same as the actual key used to encrypt the data. The actual encryption key is derived from the dbkey.

(16 Apr '14, 11:17) Graeme Perrow

OK, that's the way it shoud be:) - So that window of opportunity (for those already knowing the DBKEY) is even smaller - one would have to guess the key derivation function (PBKDF2?) and re-engineer the used salt and the like... - It's simply secure enough, methinks:)

(17 Apr '14, 03:36) Volker Barth
showing 2 of 6 show all flat view

When the SQLite secure delete pragma is turned on "SQLite overwrites deleted content with zeros"... I couldn't find a discussion of what MySQL does.

SQL Anywhere doesn't have an automatic "secure delete" mode of operation, but if you share the "secure delete" requirements you are expected to meet, someone here might be able to offer suggestions.

Here's a guess...

You might be able to do it yourself by using UPDATE to replace the column to be erased with a value of exactly the same length (random, zeroes, multiple passes, your choice). To be especially thorough, take care not to change the size of the row before doing a COMMIT and a CHECKPOINT. Not changing the size of the row eliminates the possibility of a row split where data is copied to another page, and a COMMIT plus CHECKPOINT ensures the erased data is written to the disk. You could do multiple UPDATE erasure operations in a batch, but don't do anything else to the rows before the COMMIT and CHECKPOINT. Then, after all that is said and done, you can DELETE the rows if you so choose.

Potential problems include old copies of the column in the transaction log file (permanent until erased), backup files (permanent until erased) and in the checkpoint and rollback logs (temporary until CHECKPOINT)... at this point, encryption is starting to look better and better :)

Change of heart!... strong encryption of the data when it is first received is a prerequisite... SQL Anywhere is like any other DBMS, one of its primary goals is the protection of data from loss, so it make MANY redundant copies at MANY times in MANY locations... so if any of those copies are in clear text your job is infinitely harder.

permanent link

answered 15 Apr '14, 21:23

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 16 Apr '14, 07:48

I can understand exactly how this works when working directly with a file on the file-system, and it is in fact an NIST approved "algorithm". My problem is not knowing the db engine internals well enough to assume that this would have the same affect within the database. The funny part is that the QSA will probably accept your the method you describ, even though you have since backed off from it.

(16 Apr '14, 11:31) Dan Hacker
Replies hidden

So... you are already encrypting the data, so everything else is moot (but don't tell anyone that "wiping" encrypted data is pointless... the only ones you have to worry about is the NSA and they probably already have their own copy :)

Seriously, the most important part is the way you document what you are doing, the style and tone of the memos you write, make it sound serious and thorough and authoritative.

(16 Apr '14, 13:51) Breck Carter
Comment Text Removed

You are exactly correct. Just got out of a meeting with the QSA, and he conceded that he has no idea whether the overwrite will have any effect at all. At this point, we're doing it only so we can document it (and he can select the checkbox that says we do it).

I do appreciate the feedback I've received, so thank you.

(16 Apr '14, 14:42) Dan Hacker
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 15 Apr '14, 20:53

question was seen: 5,553 times

last updated: 17 Apr '14, 03:36