How to Build TDE in Oracle Database SE2

Learn how to replicate (or build) Transparent Data Encryption (TDE) functionality in Oracle SE2. This is the second learning event in the “SE2 on Steroids” series. To watch the lesson (25 minutes), click on the Lesson Tab above (no registration is required) or watch on YouTube.

Presentation Slides (PDF) – SkillBuilders Replicating TDE in Oracle SE2 January 2022 Presentation Slides

Presentation Scripts (ZIP) – SkillBuilders Replicating TDE in Oracle SE2 January 2022 SQL Commands

-- SkillBuilders - Replicating TDE in Oracle SE2 Webinar commands.txt
-- January 2022. - DISCLAIMER -
These commands were used in a webinar demonstration and should be
-- used on a TEST system by an experienced DBA with caution.
-- While they worked fine for the purposes of our demonstration, they were
-- not written for a production deployment.
--
-- SkillBuilders accepts no responsibility for their use.
-- Please contact us if you would like mentoring, training or consulting
-- assistance implementing TDE on SE2.
--
-- © www.skillbuilders.com +1-401-741-9246

–preparation:

conn / as sysdba
drop user jw cascade;
grant dba to jw identified by jw;
grant execute on dbms_crypto to jw;
conn jw/jw
alter session set statistics_level=all;
col ename for a32
col ename_enc for a32

drop tablespace no_encryption including contents and datafiles;
drop tablespace with_encryption including contents and datafiles;
=================

create tablespace no_encryption datafile ‘c:\tmp\no_enc.dbf’ size 1m;

create table emp tablespace no_encryption as select * from scott.emp;
alter system checkpoint;

host
strings c:\tmp\no_enc.dbf
exit

drop table emp;
———

create tablespace with_encryption datafile ‘c:\tmp\with_enc.dbf’ size 1m;

— 4358 == aes128

create table emp_enc tablespace with_encryption as
select
empno,
dbms_crypto.encrypt(utl_raw.cast_to_raw(ename),4358,utl_raw.cast_to_raw(‘1234567812345678’)) ename_enc,
job,mgr,hiredate,sal,comm,deptno
from scott.emp;

I’ll go through these calls to utl_raw and dbma_crypto in a moment, let’s cherck out the result first:

alter system checkpoint;

host
strings c:\tmp\with_enc.dbf |more
exit

and you see that the enames are gone/

select ename_enc from emp_enc;

desc dbms_crypto
desc utl_raw

you do need high privileges to use dbms_crypto. For this triviual example, I have the
encryption key embedded in the code. Usually it would be in the database protected
by definers rights code, or perhaps accessed through contrext variables.

Now we need to make the encryption and decryption transparent.
crypto
First, a view:

create or replace view emp as
select
empno,
utl_raw.cast_to_varchar2(dbms_crypto.decrypt(ename_enc,4358,utl_raw.cast_to_raw(‘1234567812345678’))) ename,
job,mgr,hiredate,sal,comm,deptno
from emp_enc;

select * from emp;

create or replace trigger enc_ename
instead of insert on emp
begin
insert into emp_enc values(
:new.empno,
dbms_crypto.encrypt(utl_raw.cast_to_raw(:new.ename),4358,utl_raw.cast_to_raw(‘1234567812345678′)),
:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);
end;
/

–4358 = aes128

insert into emp (empno,ename) values (9999,’JW’);
select * from emp_enc;
select * from emp;

That’s it – colimn TDE in standard editiopn, using a view to decryopt and a trigger to encrypt.
There is of couse a lot more. Most im[portant;ly, key management.

I hard coded the codes in the objects, which is not ideal.
TDE uses a wallet, file based or something more sophisticated.
SE2 could do it in various ways – our developers can advise.

—————————-

select utl_raw.cast_to_raw(‘1234567812345678’) from dual;
select dbms_crypto.encrypt(utl_raw.cast_to_raw(‘SMITH’),4358,utl_raw.cast_to_raw(‘1234567812345678’)) from dual;

create or replace function enc_to_clear(enc_text varchar2) return varchar2 as
begin
return utl_raw.cast_to_varchar2(dbms_crypto.decrypt(enc_text,4358,utl_raw.cast_to_raw(‘1234567812345678’)));
end;
/

create or replace function clear_to_enc(clear_text varchar2) return raw as
begin
return dbms_crypto.encrypt(utl_raw.cast_to_raw(clear_text),4358,utl_raw.cast_to_raw(‘1234567812345678’));
end;
/
— © www.skillbuilders.com +1-401-741-9246