Column level encryption - IDS 10

Hi Folks, AFAIK column level encryption is available in IDS 10 only for char or smart large object data types. What about other data type such as date, integer etc. ? Does any one know the logic behind implementing only for char/BLOB data type. I am sure there must be some technical reason. Just wondering for my curiosity. Hari

Because we are using block encryption algorthms with chaining. That means you can not store an encrypted number in only 4 bytes. <hariog@yahoo.com> wrote in message news:1134601844.476897.14850@f14g2000cwb.googlegroups.com... > Hi Folks, > > AFAIK column level encryption is available in IDS 10 only for char or > smart large object data types. What about other data type such as date, > integer etc. ? Does any one know the logic behind implementing only for > char/BLOB data type. I am sure there must be some technical reason. > Just wondering for my curiosity. > > Hari >

Thanks Madison very quick response. I do not know much about encryption algorithms but I am sure there may be many instances where date, integer etc. fields might need encryption as a part of privacy legislation. It would be a big task for application developers to reviews thier codes if they need to implement this feature, I guess. Any chance in future to have this for other data types ?

Probably not. That said, there is nothing to prevent the underlying data storage of a date, integer, etc. from being a varchar and then creating a view on top of the underlying table to perform the necessary type casting so that the application would see the same data type as it is now. <hariog@yahoo.com> wrote in message news:1134608719.775818.167780@g47g2000cwa.googlegroups.com... > Thanks Madison very quick response. I do not know much about encryption > algorithms but I am sure there may be many instances where date, > integer etc. fields might need encryption as a part of privacy > legislation. It would be a big task for application developers to > reviews thier codes if they need to implement this feature, I guess. > Any chance in future to have this for other data types ? >

Madison, Thanks very much for explaining. Appreciated.

Madison Pruet wrote: > Because we are using block encryption algorthms with chaining. That means > you can not store an encrypted number in only 4 bytes. That is correct; but you can store it in a much larger CHAR column. > hariog@yahoo.com wrote: >>AFAIK column level encryption is available in IDS 10 only for char or >>smart large object data types. What about other data type such as date, >>integer etc. ? Does any one know the logic behind implementing only for >>char/BLOB data type. I am sure there must be some technical reason. >>Just wondering for my curiosity. Distinguish between the type of the column where you store encrypted data and the type of the values that you can encrypt. At least one of the documents on the IBM web site (http://www-1.ibm.com/support/docview.wss?uid=swg21221013&aid=1 - gives you a copy of 'Column%20Level%20Encryption.pdf') misses this point, unlike some of my papers on the subject. Unfortunately, my 'Chat with the Lab' (CWTL) from March 2005 on the subject glossed over this detail; it hadn't occurred to me that people would think it a problem. (Google "ibm informix column encryption chat lab"; the Powerpoint was listed second when I looked). Slides 17 and 29-30 allude (but only because I know what I'm looking for) to the possibility of encrypting other types. The first bullet on 17 says "encrypted data will be stored in character columns", which at least leaves open the possibility that the unencrypted data is of different types. Undermining that effect is last comment on slide 17 "do not normally encrypt 4-byte integer numbers", which can be taken as meaning 'you cannot encrypt 4-byte integers'. However, the 'normally' can be also be construed as indicating that 'under some circumstances you can encrypt them', but it is hardly written plainly. I've recycled that presentation; it hasn't changed enough to matter - that information has been stated clearly. OK - let's get some corrections going! You can encrypt any type of data. Let's repeat - to make sure the audience is awake. ** YOU CAN ENCRYPT ANY TYPE OF DATA ** Ignoring blobs (BYTE, TEXT, BLOB, CLOB), you will store the encrypted data in a CHAR(n) column; if you are dealing with blobs, you'll still store those in blob columns. But the source value can be any type of data. This is because IDS is very good about converting non-strings into strings across a function call interface. Consider: CREATE FUNCTION make_string(x VARCHAR(255)) RETURNING VARCHAR(255); RETURN x; END FUNCTION; EXECUTE FUNCTION make_string(CURRENT YEAR TO FRACTION(5)); This returns a nice 25-character string - quite happily. It isn't what I passed in, but the conversion takes place as surely as if I'd used an explicit cast. I assumed 'everyone knew' this - and you know what 'assume' does(*). Slide 18 of the CWTL has some size calculations. If you are planning to encrypt a 4-byte integer, you need to realize that it will be converted by the ENCRYPT_XXX function into a string value - IDS is good at that. And the size of the string representation will determine the size of the receiving CHAR column. For example, a 4-byte signed integer can store a value as small as -2,000,000,000 (roughly, and without the commas), requiring 11 characters of input. Therefore, using the table on slide 18, you should allocate 43 bytes to store it without a hint, and 99 bytes to store it with a hint. The reason you won't normally want to do that is that an 11:1 expansion in storage is quite expensive - but that is a different proposition from 'it cannot be done'. For any other type, do the equivalent calculation. For example, a DATETIME YEAR TO FRACTION(5) such as '2005-12-14 12:34:56.78901' requires 25 characters, so you'd need to use 55, 67, 107 or 119 bytes for 3DES, AES, 3DES with hint, AES with hint respectively. For example, a BOOLEAN will require {35, 43, 87, 99} bytes; a SMALLINT likewise; a DECIMAL(5,2) ditto, but a DECIMAL(6,2) would require {43, 43, 99, 99} bytes (because it needs 8 bytes to represent '+1234.56'). Slide 17 has a formula for the size needed; slide 18 has a table showing sizes required up to 47 bytes of input data - which covers all built-in types except the CHAR, VARCHAR and LVARCHAR (NCHAR, NVARCHAR) types. For those, or for sizing blobs, look at the formula. Note too that binary blobs (BLOB, BYTE) do not get the Base-64 encoding. (I need to refresh my memory for what happens with BYTE and TEXT blobs; there's an outside chance my 'ANY TYPE' needs to omit the classic blob types.) As indicated on slides 29-30, you can use cast on the DECRYPT function to get at the original value. I suppose I should add one caveat - I've not formally verified what happens with row types or collection types or user-defined types. However, if there is an automatic conversion to a string representation, then that *should* be used and the size of that string will determine the size of column you should store the result in. Since lists are open ended, it could be hard to determine the maximum size reliably. On a faintly related note - be sure you do allocate enough space for the encrypted values. Because IDS interprets the functions (ENCRYPT_AES, ENCRYPT_TDES) as returning a string [I'm ignoring blobs again], and because IDS does not know that a particular column is intended to store encrypted values, if you try to encrypt an INTEGER without a hint and store it in a CHAR(4) column, IDS will truncate the 43-byte string and store just the first four bytes of the string. This is futile; IDS doesn't even have all its control information, much less the complete string, but the insert operation and update operation will succeed; the decrypt operations will, however, fail horribly. That's why slides 17 and 18 are there - the size calculation is very important. Yes - you can look forward to enhancements in the area of encrypted data on disk in future releases of IDS. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ (*) ASSUME makes an ASS of U and ME.