9. Using Data at Rest Encryption : Encryption Information Displayed with HELP TABLE
 
Share this page                  
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.