MySQL Bugs: #7846: document whether server-side variables will be swapped out and robustness

Bug #7846	document whether server-side variables will be swapped out and robustness
Submitted:	12 Jan 2005 22:01	Modified:	20 May 2007 12:57
Reporter:	Ralf Hauser	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	4+	OS:	Any (any)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/mysql/en/Encryption_functions.html recommends to store the AES key in a server-side variable.

Two points might be worthwhile clarifying to have users more happily follow this advice:

How to repeat:
1) what happens if the machine runs short in memory, will that key be swapped out? If so, having it on the jdbc client side e.g. as java "transient" type to avoid it being visible to illegit analysers of swapped files becomes futile

2) robustness upon auto-reconnect: If my application accesses the db via jdbc:mysql://127.0.0.1:3306/myDB?autoReconnect=true and I do a 
   SELECT @password:='my password';
upon the web-app initialization, if in any situation the db disconnects and then auto-reconnects the AES key is gone and all subsequent queries will fail since the User http://dev.mysql.com/doc/mysql/en/Variables.html are connection-specific?

Suggested fix:
explain it on that page

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Actually, the advice appears to be questionable, as it
still involves sending the key over the connection in the
first place. I'm going to remove the suggestion from the
manual.

Regarding swap behavior, that's not really relevant. Someone
who could examine swap files probably could also examine
memory images of unswapped processes.

Thanks for the clarification.

Two thoughts to consider.

When initially setting the AES key to the server, I guess doing this via SSL or tunneled through SSH could solve the first issue you are listing.

As per the swapping.

While I agree that someone who can see the swap files typically also can see the RAM is certainly true. 
However, we got the requirement several times that when a machine gets physically stolen and only after this thoroughly analyzed, a swap file may well still be around while a non-swapped key in RAM typically no longer is...

as per the swapping, /sbin/cryptsetup  allows to setup encrypted swap space such that the notorious "after decommissioning the hardware, someone found secret key material from hard-disks on the recycling ground by bank xyz..." problem can be avoided.

Similarly, one has to make sure that there is no sql-log that contains the key as well or if there is, at least run it on an encrypted partition (see lvm)

fix the bug

Attachment: SQL.PHP2 (application/octet-stream, text), 21 bytes.