3

As mentioned in the topic: How to tell if in Oracle a table is encrypted with TDE or not? Couldn't find anything asking Google.

royskatt
  • 1,190
  • 2
  • 15
  • 35

1 Answers1

3

This information can be obtained from [dba | all | user]_encrypted_columns data dictionary view(s)

administer key management set keystore open identified by password;
administer key management set key identified by password with backup;

-- test table with one encrypted column   
create table tb_encrpt (
  c1 varchar2(10) encrypt
)
tablespace encrypt_tbs;

Display information about encrypted tables' columns

column table_name format a10;
column column_name format a10;
column encryption_alg format a10;

select table_name
     , column_name
     , encryption_alg
  from dba_encrypted_columns

The result:

TABLE_NAME COLUMN_NAM ENCRYPTION
---------- ---------- ----------
TB_ENCRPT  C1         AES 192 bi


1 row selected.

How to tell if in Oracle a table is encrypted with TDE or not?

If a table is not present in the [dba | all | user]_encrypted_columns then it has no encrypted columns.

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
  • No columns encrypted by Oracle, anyway. (A column may store a value that was encrypted before it was ever imported into the database.) –  Oct 10 '17 at 14:23
  • @mathguy Oracle Transparent Data Encryption (TDE) is a specific database technology where the encryption is specifically done by the database in a way that is transparent to the application. What you're talking about is application encrypted data. – jbo5112 Jan 30 '18 at 23:08
  • I would add that this solution does not locate any tables that are encrypted at a tablespace level (e.g. `select table_name from dba_tables where tablespace_name in (select tablespace_name from dba_tablespaces where encrypted='YES');` – jbo5112 Jan 30 '18 at 23:08
  • @jbo5112 - I used shorthand there, to point out that the last statement in Nick's answer should be "... then it has no **TDE** encrypted columns." That should cause no confusion to the OP, since the OP specifically asked about encrypted with TDE, but I wasn't so sure it won't confuse other readers in the future. –  Jan 31 '18 at 00:54