Saturday Sep 19, 2015
By Todd Bottger-Oracle on Sep 19, 2015
Monday Apr 28, 2014
By Todd Bottger-Oracle on Apr 28, 2014
Database administrators who manage multiple Oracle Databases may need to take a quick inventory of the TDE encrypted objects in a given database. Taking an inventory may be necessary not only for fulfilling day-to-day DBA responsibilities but also for attestation as part of periodic IT security audits.
Fortunately, listing out the TDE encrypted objects in an Oracle Database is straightforward using simple SQL queries. These queries leverage the built-in DBA_* views, and you need to have the correct database privileges to use them (e.g. have DBA role, hold individual select privileges, be logged in as SYSDBA, etc.):
Below are example queries that use these DBA_* views to answer basic questions about what TDE encrypted objects exist in the database:
- What tables contain TDE encrypted columns?
sql> select table_name, column_name from dba_encrypted_columns;
- What tables are stored in TDE encrypted tablespaces?
sql> select a.table_name, a.tablespace_name from dba_tables a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES';
- What indexes are stored in TDE encrypted tablespaces?
sql> select a.index_name, a.tablespace_name from dba_indexes a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES' and index_name not like 'SYS_IL%';
- What are all of the TDE encrypted objects including tables, indexes and columns?
[Use a combination of the above queries]
Blog covering Oracle Advanced Security for Oracle Database Enterprise Edition. Specific topics include Transparent Data Encryption (TDE) and Data Redaction.
- Meet the Oracle Advanced Security Team at OpenWorld 2015 - October 25-29 in San Francisco
- Deploying TDE for Existing Data with Near-Zero Downtime Using Data Pump and Data Guard
- Further Customer Recordings
- Customer Videos
- News from Oracle OpenWorld 2014
- How to Take an Inventory of TDE Encrypted Objects
- Welcome to the Oracle Advanced Security Blog!