Function-based Encryption (Application-level Encryption)
The SQL functions AES_ENCRYPT and AES_DECRYPT allow AES 128-bit 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 byte(16));
INSERT INTO socsec2 SELECT
fname, lname,
AES_ENCRYPT(socsec,'user function encryption')
FROM socsec1;
The stored data is encrypted, even though Ingres does not regard table socsec2 as encrypted. There is no need to MODIFY socsec2 to enable access to encrypted data.
A SELECT on the table shows that the data is not plain text:
SELECT fname, lname, HEX(socsec) AS socsec FROM socsec2
Executing . . .
+----------+--------------------+--------------------------------+
|fname |lname |socsec |
+----------+--------------------+--------------------------------+
|John |Smith |5722A4EEDA081CB25955E826DDFA2A3F|
|Lois |Lane |814ACE20D419A8F36944625941155709|
|Charlie |Brown |40CD699016608827C7A9A4E7CDB161DF|
+----------+--------------------+--------------------------------+
(3 rows)
For AES_ENCRYPT, the data is stored as a variable length byte string that encrypts both the contents and length of the value being encrypted.
To be able to read the data, use the AES_DECRYPT function, supplying the secret passphrase:
SELECT fname, lname,
AES_DECRYPT(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(AES_DECRYPT(socsec,'user function encryption'),'Smith socsec')
WHERE lname='Smith';
SELECT AES_DECRYPT(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, and a blank string is returned for that row.
SELECT AES_DECRYPT(socsec,'user function encryption') FROM socsec2
Executing . . .
+----------------+
|col1 |
+----------------+
| |
|010-40-1234 |
|012-44-9876 |
+----------------+
(3 rows)
Function-based decryption with the wrong passphrase typically returns an empty string, but may return random data. If necessary, the application can add a scheme for sanity checking returned data. (Such sanity checking is built into transparent encryption through a hash validation value that is stored with the encrypted data.)
Last modified date: 01/30/2023