0

Env: Oracle 11g DB with a Java based application

We are looking to encrypt data in our database, for a few sensitive columns of a table.
We would like these columns to be decrypted and visible to a set of users A. And we DO NOT want these encrypted columns to be visible to another set of users B. But, this user set B should be able to see the rest of the non-encrypted columns of the table.

From various articles and posts, I understand TDE does encryption and decryption transperantly and at column level, but have not been able to find clear information if the above user/role based encryption, at a column level granularity is possible or not.

Can we achieve the above using TDE?

Artjom B.
  • 61,146
  • 24
  • 125
  • 222
Chitkala
  • 1
  • 2
  • I think it could be but it would be a problem in large enterprise environments because TDE doesn't support Referential Integrity columns. As long as that pre-requisite (and others) are met, it should work. What specifics are you looking for? Here's a clear example- http://oracle-base.com/articles/10g/transparent-data-encryption-10gr2.php – Anjan Biswas Jan 21 '15 at 01:05

1 Answers1

0

I'm not a DBA, but from my understanding of TDE the encryption is not noticeable when viewed from any query. It only encrypts the data in the disk data file so it can't be read if dumped directly from the file.

A good DBA may have a better answer but just off the cuff, here is what I would suggest.

Have two fields for the sensitive data. One is clear (though TDE may be a good idea) and the other is obfuscated in some way. These fields may be normalized into a separate table. Don't allow access directly to the table but use a view instead. The view would be defined like:

create view TableName as
  select ...,
         case ROLE when 'A' then clear_field else obfuscated_field end as FieldName,
         ...
  from  SensitiveTable
  join  PossibleNormalizedTable on ... ;

You would also need triggers on the view. If only A can clearly see that field, probably only A can insert and update it.

TommCatt
  • 5,498
  • 1
  • 13
  • 20