by Alexandre Nestor

Introduction

The Oracle database Standard Edition do not have the Transparent Tablespace Encryption (TDE) feature which is a part of ASO (Advanced Security), and ASO is availabale only on Entreprise Edition version.

But encryption can still be used, with a little developpement effort.$

The test platform

I have an Oracle 19c SE2 version with one pdb (PDB11).

I will create a schema named safe, which will contains the encript/decrypt function and th key to be used to encrypt and decrypt.

Also the safe schema will contain a secret table with encrypted data.

On the database I will create an user scott, with minimal rights (only create session) which can read encrypted data.

Let’s build the environnement:

Connect on the pdb11 as sysdba and create the safe tablespace, and user safe.

SQL> alter session set container=pdb11;

Session altered.

SQL> create tablespace safe;

Tablespace created.

SQL> create user safe identified by tiger default tablespace safe;

User created.

SQL>  grant create session to safe;

Grant succeeded.

SQL> alter user safe quota unlimited on safe;

User altered.

SQL>  grant resource to safe;

Grant succeeded.

SQL> grant create view to safe;

Grant succeeded.

User safe need to have execution rights on dbms_crypto package

SQL>  grant execute on dbms_crypto to safe;

Grant succeeded.

From now on we are going to do all commands as safe user. Connect as user safe to the PDB11. and create the secret_keys table to hold all needed keys for decrypt.

[CDB01][oracle@db ~]$ sqlplus safe/[email protected]:1521/pdb11

SQL> create table secret_keys(key_bytes_raw RAW (32), iv_raw RAW (16));

Table created.

Insert random keys in the secret_keys table.

Do not loose, or change these key. Your data will be lost.

SQL> insert into secret_keys(key_bytes_raw,iv_raw) values(DBMS_CRYPTO.RANDOMBYTES(256/8),DBMS_CRYPTO.RANDOMBYTES(16));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from secret_keys;

KEY_BYTES_RAW
----------------------------------------------------------------
IV_RAW
--------------------------------
CCF9303B9AE706AC6212F9672FF8720113EC75D7C5FFAC267E42128AC40A33FA
A39F34AB9705CE91A6E6B4E592B32E28

Create the encrypt and decrypt functions.

SQL> CREATE OR REPLACE FUNCTION encr(input_string in VARCHAR2)
RETURN RAW
  IS 
    encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
                       + DBMS_CRYPTO.CHAIN_CBC
                       + DBMS_CRYPTO.PAD_PKCS5;
    iv_raw             RAW (16);
    encrypted_raw      RAW (2000);
    key_bytes_raw      RAW (32);
    BEGIN
    --DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
    select key_bytes_raw,iv_raw into key_bytes_raw,iv_raw FROM secret_keys;
    encrypted_raw := DBMS_CRYPTO.ENCRYPT(
       src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),
       typ => encryption_type,
       key => key_bytes_raw,
       iv  => iv_raw
    );
    --DBMS_OUTPUT.PUT_LINE ( 'Encrypted raw: ' || encrypted_raw);
    return encrypted_raw;
  END encr;
/

Function created.


SQL> CREATE OR REPLACE FUNCTION decr(encrypted_raw in RAW) 
return varchar2
IS 
  encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
                 + DBMS_CRYPTO.CHAIN_CBC
                 + DBMS_CRYPTO.PAD_PKCS5;
  iv_raw             RAW (16);
  key_bytes_raw      RAW (32);
  decrypted_raw      RAW (2000);
BEGIN
  select key_bytes_raw,iv_raw into key_bytes_raw,iv_raw FROM secret_keys;
  decrypted_raw := DBMS_CRYPTO.DECRYPT(
    src => encrypted_raw,
    typ => encryption_type,
    key => key_bytes_raw,
    iv  => iv_raw
  );
  return UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
END decr;
/

Function created.

Test how these fonctions work:

-- encypt
SQL> select encr('Hello') from dual;

ENCR('HELLO')
--------------------------------------------------------------------------------
75388B4D940B559DD1A5D5BCA753101B

--decrypt
SQL> select decr('75388B4D940B559DD1A5D5BCA753101B') from dual;

DECR('75388B4D940B559DD1A5D5BCA753101B')
--------------------------------------------------------------------------------
Hello

--encrypt/decrypt
SQL> select decr(encr('Hello')) from dual;

DECR(ENCR('HELLO'))
--------------------------------------------------------------------------------
Hello

On the schema safe create the table that need to be protected, let’s call it, secret_emp, and insert an encrypted string.

SQL> create table secret_emp(name_encrypted RAW(2000));

Table created.

SQL> insert into secret_emp(name_encrypted) values(encr('Obama'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from secret_emp;

NAME_ENCRYPTED
--------------------------------------------------------------------------------
9115E6FD54D676DB043CD0D1BF982027

Finally we create a view on this table named authorized_read_emp. On this view authorised users will select to get the decrypted data.

SQL> create view authorized_read_emp as select decr(name_encrypted) name_decrypted from secret_emp;

View created.

SQL> select * from authorized_read_emp;

NAME_DECRYPTED
--------------------------------------------------------------------------------
Obama

Now we are going to create an user which has the rights to read the plan text data. For this user the encryption is transparent.

Connect as sysdba to the pdb11 and create an user scott. This user is allowed to read the data from authorized_read_emp view.

SQL> create user scott identified by tiger;

User created.

SQL> grant create session to scott;

Grant succeeded.

And finally as safe user, give the rights to user scott to read the view:

[CDB01][oracle@db ~]$  sqlplus safe/[email protected]:1521/pdb11

SQL> grant select on authorized_read_emp to scott;

Grant succeeded.

SQL> exit;

Back to user scott, let’s try to read the plan text data: 

[CDB01][oracle@db ~]$  sqlplus scott/[email protected]:1521/pdb11

SQL> select * from safe.authorized_read_emp;

NAME_DECRYPTED
--------------------------------------------------------------------------------
Obama

Conclusion

Ok is not really like the TDE implementation, and maybe a better implementation could be done. But the main points are:

  • Schema ‘safe‘ contains only encrypted data. If someone stole the storage disks from the datacenter it cannot have access to tha data as it is stored encrypted. This is one of the main goal of TDE.
  • Schema ‘safe‘ contains also the keys needed to decrypt the data. This is a limitation. The keys to decrypt data can be stored outside the database to increase the security.
  • The user ‘scott‘ can access the data in a transparent mode as the data is decrypted on the fly. No data in plain is stored in the database.
  • The user ‘scott‘ has only the rights to connect and to read the data which is decrypted on the fly.
Post Views: 1,483
Thumbnail [60x60]
by
Alexandre Nestor
Share on