Dynamic Server supports built-in encryption and decryption functions. The encryption functions ENCRYPT_AES and ENCRYPT_TDES each returns an encrypted_data value that encrypts the data argument. Conversely, decryption functions DECRYPT_CHAR and DECRYPT_BINARY return a plain-text data value from the encrypted_data argument. Use this syntax to call these functions:
Encryption and Decryption Functions: |--+-+-ENCRYPT_AES--+--(--data--+----------------------------+--)-+--| | '-ENCRYPT_TDES-' '- ,--password--+----------+-' | | '- ,--hint-' | +-+-DECRYPT_CHAR---+--(--encrypted_data--+--------------+--)---+ | '-DECRYPT_BINARY-' '- ,--password-' | '-GETHINT--(--encrypted_data--)--------------------------------'
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| data | A plain text character string, variable, or large object of type BLOB or CLOB to be encrypted | Must be a character or BLOB data type | Expression,
p. Expression |
| encrypted _data | A character string or variable containing output from ENCRYPT_AES or from ENCRYPT_TDES | Decryption requires the encryption password | Expression,
p. Expression |
| hint | A character string that you define here. Default is the value from the WITH HINT clause of the SET ENCRYPTION statement that defined password. | No more than 32 bytes | Quoted string,
p. Quoted String |
| password | A character string that the encryption function defines. Default is the session password value defined by the SET ENCRYPTION statement | At least 6 bytes, but
no more than 128 bytes |
Quoted string,
p. Quoted String |
You can invoke these encryption and decryption functions from within DML statements or with the EXECUTE FUNCTION statement.
For distributed operations over a network, all participating database servers must support these (or equivalent) functions. If the network is not secure, the DBSA must enable the encryption communication support module (ENCCSM) to provide data encryption between the database server and client systems, in order to avoid transmitting passwords as plain text.
Encryption or decryption calls slow the performance of the SQL statement within which these functions are invoked, but have no effect on other statements.
The encryption and decryption functions can support two ways of using data encryption features, namely column level and cell level encryption.
Users of this form of encryption should consider not using the hint feature of these functions, but instead store a mnemonic hint for remembering the password in some other location. Otherwise, the same hint will occupy disk space in every row that contains an encrypted value.
This use of encryption is also called row-column level or set-column level encryption. Compared to column-level encryption, this makes the task of data management more complex, because if different passwords are required for decrypting different rows of the same table, it is not possible to write a single SELECT statement to fetch all the decrypted data. In some situations, however, individual users may need this technique to protect personal data.
To protect data security and confidentiality, the database server does not store information in the system catalog to indicate whether any table (or any column or row) includes encrypted data. Similarly, the logical logs of Dynamic Server do not record SET ENCRYPTION statements, nor calls to encryption or decryption functions. (The Trusted Facility feature for secure auditing, however, can use the 'STEP' audit-event mnemonic to record execution of the SET ENCRYPTION statement, and can use the 'CRPT' audit-event mnemonic to record successful or unsuccessful calls to DECRYPT_CHAR or DECRYPT_BINARY.)
The SET ENCRYPTION statement or an encryption function can define a password and hint for the current session. The password must be specified as a character expression that returns at least 6 bytes, but no more than 128. The optional hint is specified as a character expression that returns no more than 32 bytes.
The purpose of the hint is to help users to remember the password. When you call ENCRYPT_AES or ENCRYPT_TDES with a hint argument, it is encrypted and embedded in the encrypted_data, from which GETHINT can retrieve it. But if you define hint as NULL, or omit hint when SET ENCRYPTION specified no default hint for the session password, no hint is embedded in the encrypted_data.
The password used for encryption and decryption is either the password argument to the function, or if you omit this argument, it is the session password specified in the last SET ENCRYPTION statement executed before you invoke the function.
The DECRYPT_CHAR, DECRYPT_BINARY, or GETHINT function call fails with an error if the encrypted_data argument is not in an encrypted format, or if the password argument to a decryption function is omitted when no session password value was set by SET ENCRYPTION. An error also results if the password used for decryption is not the same password used for encryption.
Encryption key management, which is critical to the secure operation of the database, is delegated entirely to the application. This implementation means that the password itself is not stored in the database. Without help from the user through the application, the database server cannot decrypt the encrypted data.
If you invoke any of these functions from a UDR, you might prefer to set a session password in the SET ENCRYPTION statement. Otherwise, password will be visible to users who can view the sysprocbody.data column in the system catalog.
The data and corresponding encrypted_data values can be of any character type (CHAR, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR), or a smart large object of type BLOB or CLOB. Corresponding data and encrypted_data values that the encryption functions return have the same character, BLOB, or CLOB type. (Use CLOB in place of TEXT, which these functions do not support.)
Except for original data of BLOB or CLOB data types, the encrypted_data value is encoded in BASE64 format. An encrypted value requires more space than the corresponding plain text, because the database must also store the information (except for the encryption key) that is needed for decryption. If a hint is used, it adds to the length of encrypted_data.
The BASE64 encoding scheme stores 6 bits of input data as 8 bits of output. To encode N bytes of data, BASE64 requires at least ((4N+3/3) bytes of storage, where ( / ) represents integer division. Padding and headers can increase BASE64 storage requirements above this ((4N+3)/3) ratio. Example of Column Level Encryption lists formulae to estimate the size of data values encrypted in BASE64 format. It typically requires changes to the schema of an existing table that will store BASE64 format encrypted data, especially if a hint will also be stored.
The following table shows how the data type of the input string corresponds to the data type of the value that ENCRYPT_AES or ENCRYPT_TDES returns:
| Plain Text Data Type | Encrypted Data Type | Decryption Function |
|---|---|---|
| CHAR | CHAR | DECRYPT_CHAR |
| NCHAR | NCHAR | DECRYPT_CHAR |
| VARCHAR | VARCHAR or CHAR | DECRYPT_CHAR |
| NVARCHAR | NVARCHAR or NCHAR | DECRYPT_CHAR |
| LVARCHAR | LVARCHAR | DECRYPT_CHAR |
| BLOB | BLOB | DECRYPT_BINARY |
| CLOB | BLOB | DECRYPT_CHAR |
Columns of type VARCHAR and NVARCHAR store no more than 255 bytes. If the data string is too long for these data types to store both the encrypted data and encryption overhead, then the value returned by the encryption function is automatically changed from VARCHAR or NVARCHAR into a fixed CHAR or NCHAR value, with no trailing blanks in the encoded encrypted value.
Encrypted values of type BLOB or CLOB are not in BASE64 encoding format, and their size increase after encryption is independent of the original data size. For BLOB or CLOB values, the encrypted size (in bytes) has the following formula, where N is the original size of the plain text, and H is the size of the unencrypted hint string, if encryption is performed by ENCRYPT_TDES:
N + H + 24 bytes.
For BLOB or CLOB values that ENCRYPT_AES encrypts, the overhead is larger:
N + H + 32 bytes.
The following example illustrates how to use the built-in encryption and decryption functions of Dynamic Server to create and use a table that stores encrypted credit card numbers in a column that has a character data type.
For purposes of this example, assume that the plain text of the values to be encrypted consists of strings of 16 digits. Because encryption functions support character data types, these values will be stored in a CHAR column, rather than in an INT or INT8 column.
The LENGTH function provides a convenient way to calculate the storage requirements of encrypted data directly:
EXECUTE FUNCTION LENGTH(ENCRYPT_TDES("1234567890123456", "simple password"))This returns 55.
EXECUTE FUNCTION LENGTH(ENCRYPT_TDES("1234567890123456", "simple password",
"12345678901234567890123456789012"))This returns 107.
EXECUTE FUNCTION LENGTH(ENCRYPT_AES("1234567890123456", "simple password"))This returns 67.
EXECUTE FUNCTION LENGTH(ENCRYPT_AES("1234567890123456", "simple password",
"12345678901234567890123456789012"))This returns 119.
The required storage size for encrypted data is sensitive to three factors:
The following formulae describe the four possible cases, and are not simplified:
Encrypted size = (4 x ((8 x((N + 8)/8) + 10)/3) + 11)
Encrypted size = (4 x ((16 x((N + 16)/16) + 10)/3) + 11)
Encrypted size = (4 x ((8 x((N + 8)/8) + 50)/3) + 11)
Encrypted size = (4 x ((16 x((N + 16)/16) + 50)/3) + 11)
The integer divsion ( / ) returns an integer quotient and discards any remainder.
Based on these formulae, the following table shows the encrypted size (in bytes) for selected ranges of values of N:
| 3N | 3ENCRYPT_TDES
3 No Hint |
3ENCRYPT_AES
3 No Hint |
3ENCRYPT_TDES
3 With Hint |
3ENCRYPT_AES
3 With Hint |
|---|---|---|---|---|
| 31 to 7 | 335 | 343 | 387 | 399 |
| 38 to 15 | 343 | 343 | 3 99 | 399 |
| 316 to 23 | 355 | 367 | 3107 | 3119 |
| 324 to 31 | 367 | 367 | 3119 | 3119 |
| 332 to 39 | 375 | 387 | 3131 | 3139 |
| 340 to 47 | 387 | 387 | 3139 | 3139 |
| 3100 | 3163 | 3171 | 3215 | 3227 |
| 3200 | 3299 | 3299 | 3355 | 3355 |
| 3500 | 3695 | 3707 | 3747 | 3759 |
If the column size is smaller than the data size returned by encryption functions, the encrypted value is truncated when it is inserted. In this case, it will not be possible to decrypt the data, because the header will indicate that the length should be longer than the data value that the column contains.
These formulae and the values returned by the LENGTH function, however, indicate that the table schema in the next example can store the encrypted form of 16-digit credit card numbers (with a hint).
The following steps create a table from which a user who knows the password can retrieve rows that include one column of encrypted data.
CREATE TABLE customer (id CHAR(20), creditcard CHAR(107));
SET ENCRYPTION PASSWORD 'credit card number is encrypted'
WITH HINT 'Why is this difficult to read?';
INSERT INTO customer VALUES ('Alice',
encrypt_tdes('1234567890123456'));
INSERT INTO customer VALUES ('Bob',
encrypt_tdes('2345678901234567'));SELECT id, DECRYPT_CHAR(creditcard, 'credit card number is encrypted') FROM customer;
The following query call a decryption function in the WHERE clause, using the session password default, rather than an explicit password argument:
SELECT id FROM customer WHERE DECRYPT_CHAR(creditcard) = '2345678901234567'
Column level encryption offers the coding convenience of passing the implicit session password for all rows with encrypted columns, and in multiple encryption and decryption function calls in the same SQL statement. Confidentiality of the data, however, requires users who know the password on encrypted columns to avoid compromising its secrecy. Triggers and UDRs, for example, should always use the session password, rather than explicit password arguments if they invoke the encryption or decryption functions.
The DBSA can manage highly confidential data with column level encryption. Dynamic Server does not, however, prevent users with sufficient privileges from entering data encrypted by some other password into a table whose other rows use the designated column level encryption password.