encrypting oracle data dictionary tables

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Thu, 26 Sep 2013 16:46:09 -0400
Message-ID: <CAE-dsO+soPjfm+SnEyDtZstoyQoY0XJfL7tUAJzbUjpGCaVcqw_at_mail.gmail.com>
I am using transparent encryption in one of my databases to protect sensitive data.

However, it is possible for queries to have bind variables that use sensitive data. The first time a query is parsed, its bind variable data is stored in plain text in v$sql_plan.other_xml. My understanding is that v$/x$ constructs point to link list in memory and this does not generate undo/redo (have not run the strings command on the archive logs to verify this). Is this accurate?

However, the AWR snapshots this and copies it to WRH$_SQL_PLAN. Which is a physical table so I am pretty sure this generates redo/archive and is physically stored in a file. Does anyone know if its possible to use transparent encryption on the oracle data dictionary? I plan to open a ticket on this. Thought I would ask here. If not, I'll probably be required to turn the AWR snapshots off.

There are some other v$views that I might have issues with

v$sql/v$sqlarea: stores plain text of a query. It is possible for someone to use sqlplus to run queries and they can use plain text. I know this is snapshotted by the AWR also. So I would need to encrypt these fields as well.

V$SQL_BIND_CAPTURE: Random sampling of bind variable data. I don't believe this is physically stored on disk anywhere right?

V$SQL_MONITOR: Not too familiar with this, but I believe it can store some bind variable data. Is this snapshotted by any utilities?

Any other views/tables I might be missing?

The operations team is using the OEM with the repository. I don't know what version (asking isn't as easy as you might think). We don't have that here. Does anyone know if versions 10,11, or 12 copy any data that might store plain text of queries or bind variable data to the OEM repository?

yeah, I seriously have to look into this... not trolling.

Received on Thu Sep 26 2013 - 22:46:09 CEST

Original text of this message