Encryption Information Displayed with HELP TABLE
The HELP TABLE statement displays encryption information for tables that contain encrypted columns.
For example, column encryption of the socsec1 table is defined at the DBMS level. Here is an excerpt from the output from HELP TABLE socsec1:
Column Information:
Key
Column Name Type Length Nulls Defaults Seq
fname char 10 yes null
lname char 20 yes null
socsec char 11 yes null
Secondary indexes: none
Column encryption: AES256
Alter table totwidth: 48
Encrypted width: 48
Encrypted Column Name Type Width Salt
socsec char 16 no
Encryption-related fields are:
Column encryption
Encryption type
Alter table totwidth
Physical width of encrypted columns
Encrypted width
Physical width of the table
Encrypted Column Name…Width
Physical width of the named column
In this example, the logical width of the table (width as seen by applications) is 44, which is the sum of the column widths including the NULL bytes. Since AES is a block encryption algorithm, encrypted column widths as stored on disk will always be a multiple of 16. The NULL byte and a data verification hash value are included in the encrypted data. In this case, the socsec column with NULL (12 bytes) plus verifying hash (4 bytes) exactly fits in one AES block. Partial AES blocks are padded as needed in cases where the user data is not an exact fit.
In contrast, the socsec2 table is used to store encrypted data, but encryption is defined at the application level with the AES_ENCRYPT function. Here is an excerpt from the output of HELP TABLE socsec2:
Column Information:
Key
Column Name Type Length Nulls Defaults Seq
fname char 10 yes null
lname char 20 yes null
socsec byte 16 yes null
Secondary indexes: none
No encryption information is shown because there is nothing special about the table. The application has the responsibility to ensure that the encrypted data fits in the BYTE column that is used for that purpose. In this case, the sum of the VARBYTE length for the encrypted data (2) plus that data itself (11) fits within one AES block, which is stored in the BYTE(16) column.