I am quoting what has been stated and offer my perspective to
"BTW encrypting single columns is not really state of the art
today anymore. ;-).....
Native Encryption is database-wise and will work on the datapage
level. So if the Db2 engine will pull up a page from disk to the
bufferpools, it will get decrypted... Or encrypted while paging
out. The password is either provided by the sysadmin starting the
database manager or is stored in a stashfile."
I think we may be missing the critical point.
A system managed encryption is only good to encrypt the data at
rest scenario. It does not offer protection to sensitive data from
unauthorized views of the data. If the user is a privileged user he
can see what he is not supposed to see and an encryption scheme
must be in place to address that scenario. Your statement implies
the row / column level access security (separate from encryption)
as deprecated items but in fact they are all there to stay to
satisfy stringent mandates.
When encryption is done at the dataset (in zOS we call a file as
a dataset) level using SMS policy, the DBMS started task user and
anyone else who needs to operate standalone utilities on that
dataset will need authorization to the key label to see the
decrypted contents. Allocating a dataset does not require any
authorization for the key label. This check is done at OPEN time
only. However this poses a risk. Say your application relies on the
above, claiming data is encrypted, by virtue of disk / dataset
encryption scheme, it doesn't preclude anyone who has access to the
table, see the decrypted data and col level encryption can prevent
Both fieldproc and a customized user defined function can check
who the invoker is and ask the security product if the invoker is
authorized to see the decrypted data from the environment he
identified to the database. What if SSN and LAST NAME is indexed
for a hypothetical reason? What about a potential exposure through
copies of data and log? Column level encryption using either
fieldproc or function index may optionally prevent exposure in a
wide variety of scenario, if implemented correctly.
Dataset encryption is only good for encrypting data at rest.
Wherever the data goes, encryption must also follow. Does it meet
compliance requirements if the objective is to prevent data theft?.
Whole disk encryption is different and it doesn't necessarily
provide dataset encryption.
The disk / dataset encryptions occur at a fairly low level and
by suitable buffering (read ahead etc), overhead is usually
minimized or mitigated. Application processing / adhoc SQL workload
has more overhead and this little overhead is simply absorbed in
most cases. Whenever it comes to data security and compliance,
there is really no point in pondering over "overhead".
“Yes, the methods to have single encrypted columns is
deprecated and works in a way, that you have to provide a password
while accessing this column.“
This is not true at all. Using the security product,
co-processor and ICSF, the cipher key can be secured from the
invoker. You almost certainly do not want crypto ops in any address
space as a dump of the core might reveal the key. Program can be
secured so it can only run in its intended environment. When we
mention user defined function it is 100% user defined and not
delivered by DB2 framework. IBM delivers certain built in functions
within DB2 and sells enhanced offering as an encryption suite.
There are wide differences between the two and what I consider in
my mind is either a home grown encryption function or an encryption
function from a vendor’s product line including that enhanced
offering “IBM Data encryption for DB2 / IMS”.
Built in functions have to work in all environments hence
they will be minimal in what they can deliver. There are numerous
vendors that offer support in this area.
"The encryption/decryption is done in Db2 code and your
application has to have this password stashed in the application
A function or fieldproc is an external program to which the
database / application user / DBA need not be privy to the security
label which makes it a highly reliable tamper proof medium.
"AFAIK there is a similar option on z/OS, where you need the
encryption card to encrypt/decrypt datapages transferred between
memory and disk. You don't want to have software encryption there,
but only hardware supported encryption to make it fast."
Encryption as above or dataset encryption is relatively a new
concept with recent enhancements (zOS 2.3, DB2 V11 / V12 etc,
specific hardware models etc) and most sites are not yet (should
add probably here as there may be a few sites) fully exploiting it
in zOS although I must say VSAM has supported various forms of
encryption for quite a long time. The integration with DB2 / SMS
policy is fairly new. The machine instructions such as KM and KMC
for cipher and chained cipher are rather too old in zArchitecture
instruction set. We can do clear key encryption with ease. Their
adoption is rather new.
We are automatically driven to think that h/w is the best choice
in all cases. There have been reported cases where software based
operation was cheaper than hardware operation. Fairly recently, DB2
performed de-compression in software as it was found to be cheaper
than its on chip h/w implement. In certain other h/w platforms
tcpip checksum was performed in software as it was cheaper than
hardware. So just because something is done in hardware it may not
always mean it is superior. There are opportunities there too. Not
all hardware issues can be patched. Software can be patched.
Compiler optimizations versus the perceived cpu stall during the
co-processor engagement varies depending on the involved hardware.
Similarly just because there is a machine instruction for cipher
block chaining implement, that cannot be deemed faster as the
ultra-complex instructions themselves can be implemented using
millicode, a specialty subroutine technique to which only the cpu
has access. These instructions may not be available in all
countries due to regulatory requirements and usually a fallback is
necessary when a product adopts it. The OS / DBMS / TOOL vendor
knows what is best because only he has access to his proprietary
implement and often they make the appropriate choice when it comes
to hardware and software even when accelerating hardware is
Obfuscation of data is not encryption for if it were to be true
compression in itself sufficiently obfuscates the data as
decompression is not for “faint hearted”. Such a
“not for faint hearted” logic will not convince even an
entry level information auditor.
I will stand corrected if my thought is no longer valid. I like
a pervasive encryption which utilizes a dataset level encryption, a
data traffic encryption using TLS and column level encryption for
sensitive columns. Above all if storage system offers its own
encryption, independent of a system managed encryption facility
provided by the OS, I would do that in addition to the above.
The original question was for zOS but you take LUW as an analogy
to put forth the argument. I assume DB2 LUW is similar and I expect
whatever I say is equally valid for LUW knowing well that I lack
insight into the internals of DB2 LUW.
Now, I rest my case in support of column level encryption