Automátic column-level encryption at rest

In Europe we have strong laws (GDPR) protecting personal data. Nobody, even programers or DBA can access personal data if not from an audited program who grants individual access to each user able to see this information.

As application examples, we have, healthcare apps, hotels, etc.

In healthcare, any DBA can access sensitive information just opening dbaccess que making some SELECTS or even UNLOAD information and transfer it to other applications. A DBA is not an authorized user with access to this kind of information.

Current encryption at rest, protects somebody stealing data by access to backups or direct access to chunks, but doesn't protects DBAs to use dbaccess to get sensitive information.

Also, current healthcare applications have old components and it's very difficult if not impossible to change all application code to use ENCRYPT_AES(), DECRYPT_CHAR() in all sensitive columns. And also, this can change in the future and most users doesn't have access to source code.

Proposal is to implement automatic column-level encryption at rest, by defining at the database schema wich columns should be encrypted. you can use current global key defined by using SET ENCRYPTION PASSWORD or using some environement variable using SET ENVIRONMENT MASK_PASSWORD 'mysamplepass';

Then, at schema level you should define some columns encryted at rest:

CREATE TABLE sample(

code CHAR(20) NOT NULL,

first_name VARCHAR(100) ENCRYPTED,

last_name VARCHAR(100) ENCRYPTED

);

A program or a session should set the global encryption pasword and when inserting data:

INSERT INTO sample VALUES("C001", "JOHN", "MITH"); thid will be encrypted automatically.
When retrieving data with SELECT, session password will be used and data decrypted.

If password is not set at session level or is incorrect, original encrypted data will be returned, so dbas or other users with access to the database will not be able to retrieve sensitive data because they don't know the password.