On 27 Apr 2012 12:40, Stone, Joel wrote:
OK now that I have graduated from iseries SQL encryption 101 (OK the
first hour of class anyways):
Is it possible to encrypt a 10 byte character field in place? (Let's
assume it was created with FOR BIT DATA).
The minimum size for the available encryption options, Encrypt_RC2
being the least, is 16-bytes. So using SQL, effectively, the ability to
encrypt-in-place is not possible; at least not using the provided scalar
encrypt\decrypt builtin functions.
For example: TaxFile contains 9 digit SSN.
Is it possible in SQL to use the following to provide an encrypted
SSN?: update TaxFile set SSN=Encrypt_RC2(SSN)
Not generally. Could do so, if the column SSN were VARCHAR(24) FOR
BIT DATA and all values were 9-digit, left-adjusted, and right-trimmed.
Often the file would be split into two files instead, having the
primary key track the data that needs to be encrypted in another file
and the data that needs not be encrypted to remain in the original file
[with some decision(s) about what to do with the current column]. A
VIEW could then encapsulate a JOIN of the two, to present the format of
the original file only for those applications that would need to see the
decrypted values; possibly showing something like *NOTAVAIL or the NULL
value returned for those applications\users which have no access.
It doesn't seem possible since the receiving field is larger than
the source field.
The receiver, a 10-byte character field, is too small. The source
"field length" is potentially irrelevant, as it is the length of the
value(s) which matters. The field would need to be at least 24-bytes to
store an encrypted value of a 9-character\digit value. Even if the SSN
value could be re-represented first as a 4-byte string representing the
4-byte integer, the minimal physical storage requirement would still be
too large, requiring 16-bytes. But that would introduce a new problem,
because most encryption is designed to encrypt /plaintext/ data, so the
binary data would first need to be passed through a base64 encoding to
enable proper encryption.
So if the SSN field were 24-bytes varying but all plain text values
stored in that column were always less than 16-bytes, all being 9-byte
strings would certainly qualify, then the request to "update TaxFile set
SSN=Encrypt_RC2(SSN)" to change all plain text values to a cipher could
succeed; some selection might be required to ensure avoiding an attempt
to encrypt an already encrypted value.
Any ideas or other methods that can encrypt a field in place
(without expansion)?
Just about anything but using the typical robust encryption schemes,
e.g. the two or three provided as SQL scalars. However AFaIK, no method
which could still use the ENCRYPTION PASSWORD; i.e. any password would
have to be implemented separately.
Basically anything that would involve scrambling the data using any
private means of reversible obfuscation, using "protected" algorithm(s)
for each of the encrypt and decrypt. The algorithm could be from
ultra-simplistic ranging to extremely complicated\sophisticated. The
cipher could be specific to the primary key value, require keys, ensure
that the unscrambling algorithm breaks if both the data and unscrambling
code is restored to another system, or even make the decryption [seem]
specific to the file in which the encrypted data resides [such that the
decryption is unlikely to function on a copy of the row data to another
file even on the same system].
It doesn't seem theoretically impossible (although maybe less
secure).
Exactly. However "less secure" may also mean "not secure enough to
meet specifications, or does not meet a public-encryption rule,
according to some regulations [or laws] applicable to the environment
and data"; i.e. nullifying any consideration of a private encryption scheme.
Regards, Chuck
midrange.com
