Function-based Encryption (Application-level Encryption)
The SQL functions AES_ENCRYPT_IV and AES_DECRYPT_IV allow AES encryption at the application (rather than the DBMS Server) level, by using encryption options on DML such as SELECT, INSERT, and UPDATE statements.
This example creates table socsec2 to store function-encrypted data and insert the data from table socsec1.
CREATE TABLE socsec2
(fname char(10),
lname char(20),
socsec varchar(30));
INSERT INTO socsec2 SELECT
fname, lname,
AES_ENCRYPT_IV(socsec,'user function encryption')
FROM socsec1;
The stored data is encrypted, even though Actian X does not regard table socsec2 as encrypted. There is no need to issue MODIFY socsec2 to enable access to encrypted data.
A SELECT on the table shows that the data is not plain text:
SELECT fname, lname, socsec FROM socsec2
Executing . . .
+----------+--------------------+--------------------------------+
|fname |lname |socsec |
+----------+--------------------+--------------------------------+
|John |Smith |&ab6dC@]>LYRGaIK\\,L-2M!]' |
|Lois |Lane |'ab6dp6iL^=2#f[1U2.3;s"2A |
|Charlie |Brown |(ab6d<h0WJY)!$NLQQ05Q;Y$P |
+----------+--------------------+--------------------------------+
(3 rows)
For AES_ENCRYPT_IV, the data is stored as a variable length byte string that encrypts both the contents and length of the value being encrypted.
WARNING! Data will be silently lost if the result of the function is longer than the defined column width. Use the following calculation to determine how much space the resulting cipher will require: cipher_length = ( (string_length in bytes) / 16 + 2 ) * 20.
To be able to read the data, use the AES_DECRYPT_IV function, supplying the secret passphrase:
SELECT fname, lname,
AES_DECRYPT_IV(socsec,'user function encryption') AS socsec
FROM socsec2
Executing . . .
+----------+--------------------+----------------+
|fname |lname |socsec |
+----------+--------------------+----------------+
|John |Smith |012-33-4567 |
|Lois |Lane |010-40-1234 |
|Charlie |Brown |012-44-9876 |
+----------+--------------------+----------------+
(3 rows)
With function-based encryption, the application decides what byte values are saved in the column. Data in a single column can be unencrypted, encrypted with different passphrases, doubly encrypted, and so on.
In this example we change the passphrase for just one row in the table.
UPDATE socsec2 SET socsec =
AES_ENCRYPT_IV(AES_DECRYPT_IV(socsec,'user function encryption'),'Smith socsec')
WHERE lname='Smith';
SELECT AES_DECRYPT_IV(socsec,'Smith socsec') FROM socsec2 WHERE lname='Smith'
Executing . . .
+----------------+
|col1 |
+----------------+
|012-33-4567 |
+----------------+
(1 row)
If we then select all rows from the table, supplying the original passphrase, decryption of the row for 'Smith' fails.
SELECT AES_DECRYPT_IV(socsec,'user function encryption') FROM socsec2
Executing . . .
+------------------+
|col1 |
+------------------+
| |
|010-40-1234 |
|012-44-9876 |
+------------------+
(3 rows)
Function-based decryption with the wrong passphrase may return an empty string or random data. If necessary, the application can add a scheme for sanity checking returned data.
Caution! The application source code calling the AES_ENCRYPT_IV or AES_DECRYPT_IV functions should be secured because the passphrase is visible in the call.
Caution! The SET SESSION_TRACE command logs SQL statement text, which includes the passphrase. This command is limited to users with TRACE privilege and is written to a file owned by the installation owner, but session tracing should be used with caution if the encrypt and decrypt functions are in use.
Last modified date: 04/03/2024