Function-based Encryption (Application-level Encryption)
The SQL functions AES_ENCRYPT_VARCHAR and AES_DECRYPT_VARCHAR 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_VARCHAR(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 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_VARCHAR, 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_VARCHAR function, supplying the secret passphrase:
SELECT fname, lname,
AES_DECRYPT_VARCHAR(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_VARCHAR(AES_DECRYPT_VARCHAR(socsec,'user function encryption'),'Smith socsec')
WHERE lname='Smith';
SELECT AES_DECRYPT_VARCHAR(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_VARCHAR(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. (Such sanity checking is built into transparent encryption through a hash validation value that is stored with the encrypted data.)