First, this is significantly MORE than just the column.
Yes, if, for example, the account_number appears in a string that can be
parsed, then it needs to be masked in all such strings if that is sensitive
information in the context of your access rules.
As for the feasibility of joins, if you use the same encryption in each such
instance that magically works out for itself for joins and equality but beggars
the imagination for ranges. (Actually you could imagine a lookup table that
translates encryption back to a value that you can range, but it is very
complicated to both make it work and NOT weaken the encryption.)
Your goal is also important. If you just don’t want folks to casually see an
account_number browsing the data that is less severe than actually making it
take considerable computer time to decrypt such values.
VPD has been mentioned on the thread. That might accomplish your goal. But
probably you need to hire an expert in this, whose first task should probably
be to pin down very carefully the goals and tolerable side effects of the
encryption.
Good luck,
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Lok P
Sent: Friday, September 15, 2023 10:00 PM
To: Oracle L
Subject: Column encryption use-case
Hello Friends, It's Oracle version 19C.
We are using tablespace TDE for "data at rest" encryption. We have got a
security requirement , as part of which TDE is not sufficient as , although
it's encrypted in storage , the data is visible to clear text to all whoever
selects from that table. The sensitive column(e.g. account_number) should not
be visible to others in clear text and should be stored as encrypted only. And
should also be decrypted as and when required by the application logic from the
app account but not from the individual user account. And currently the
application is required to query/fetch/join that column as and when required
and we have indexes that exist on the column in few cases. Also there are
cases, the column/attribute which we want to encrypt is part of a clob column
which stores Json format strings. Would it be okay to encrypt a full clob or
should we extract the sensitive column out from the clob and store it as a
different column and then encrypt that?
While searching over the internet , I found dbms_crypto. But I have never used
it in the past. So I wanted to know from experts here , if this above use case
should be catered by the dbms_crypto package and then how the key management
happens here? or any other possible easy/more performant methods are available
in latest Oracle versions? Because having indexes created on the encrypted
column may have performance overhead while decrypting , so I want to be
cautious.
And this activity we need to do on the existing data and the newly coming data
to the table. There are 100's of million rows inserted/queried per day to/from
the table along with that column.
Appreciate your guidance.
Regards
Lok
