Function-based 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 Vector does not regard table socsec2 as encrypted. There is no need to issue ENABLE PASSPHRASE 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.
Last modified date: 03/21/2024