OraFAQ Forum: Security » Transparent Database Encryption

Home » RDBMS Server » Security » Transparent Database Encryption (Oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Transparent Database Encryption [message #660110]

Thu, 09 February 2017 03:14

jvanh
Messages: 17
Registered: August 2013

Junior Member

Hi,

I understand TDE is used to encrypt data stored on media. Is there however a possibility to encrypt columns, or whole tables for that matter, in such a manner that Oracle users viewing table data in SQL Developer or alike will only be able to view the scrambled data, whereas the application can operate transparently and use this data without need to encrypt/decrypt?

Thanks in advance for any feedback!

"Transparent Data Encryption (TDE) enables you to encrypt sensitive data, such as
Personally Identifiable Information (PII), that you store in tables and tablespaces.
After the data is encrypted, this data is transparently decrypted for authorized users
or applications when they access this data. TDE helps protect data stored on media
(also called data at rest) in the event that the storage media or data file is stolen.
Oracle Database uses authentication, authorization, and auditing mechanisms to
secure data in the database, but not in the operating system data files where data is
stored. To protect these data files, Oracle Database provides Transparent Data
Encryption (TDE). TDE encrypts sensitive data stored in data files. To prevent
unauthorized decryption, TDE stores the encryption keys in a security module
external to the database, called a keystore."

Report message to a moderator

Re: Transparent Database Encryption [message #660111 is a reply to message #660110]

Thu, 09 February 2017 03:19

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

What you are describing is a perfect usage case for Data Redaction. It is part of the Advanced Security Option, as is TDE.

Report message to a moderator

Re: Transparent Database Encryption [message #660113 is a reply to message #660111]

Thu, 09 February 2017 03:35

jvanh
Messages: 17
Registered: August 2013

Junior Member

Thanks John. I read:

"Take care when using Oracle Data Redaction with applications that perform updates back to the database, because redacted data can be written back to this database."

Our application will use the DB as transaction DB, so we will be reading and updating.

Do you know if there's a profile based bypass of this, seemingly, limitation?

Report message to a moderator

Re: Transparent Database Encryption [message #660114 is a reply to message #660113]

Thu, 09 February 2017 03:38

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

In what way is that a "limitation"? If your users are able to write garbage to tables, they can do so. Redaction is nothing to do with it. There are some comments in the docs that are pretty silly.

Report message to a moderator

Re: Transparent Database Encryption [message #660115 is a reply to message #660114]

Thu, 09 February 2017 03:46

jvanh
Messages: 17
Registered: August 2013

Junior Member

Ehr, how to put this ... so users, people, executing SQL against this DB, or viewing data using SQL Developer, should not be able to see some data. OK, that works nicely with redaction it seems.

However the app, when it selects and receives this data, it should not be redacted. It will have to take the (obviously un-redacted) data from this column and will need to update this data at end of processing.

So, the limitation I thought was on the application side, or is it not?

Report message to a moderator

Re: Transparent Database Encryption [message #660119 is a reply to message #660115]

Thu, 09 February 2017 04:04

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

So, the limitation I thought was on the application side, or is it not?

No, it isn't. Your application sees clear text data. It will write clear text data: whatever your users choose to write. All Redaction does is adjust the values returned to the user process, it doesn't do anything with INSERT or UPDATE.

[Updated on: Thu, 09 February 2017 04:06]

Report message to a moderator

Re: Transparent Database Encryption [message #660122 is a reply to message #660119]

Thu, 09 February 2017 04:18

jvanh
Messages: 17
Registered: August 2013

Junior Member

So this implies Oracle knows, based on Oracle user ID, which SELECT should be returned redacted and which one not? All clear on the INSERT and UPDATE. Apologies for laboring the point ...

Report message to a moderator

Re: Transparent Database Encryption [message #660123 is a reply to message #660122]

Thu, 09 February 2017 04:22

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

What you need to apologise for is not reading the docs. When and how data is redacted is determined by the design of your redaction policy.

Report message to a moderator

Re: Transparent Database Encryption [message #660128 is a reply to message #660123]

Thu, 09 February 2017 05:27

jvanh
Messages: 17
Registered: August 2013

Junior Member

Apologies herewith & thanks again for your prompt feedback.

Will start testing to take this concern away: "Even though Oracle Data Redaction is not designed to prevent data exposure to database users who run ad hoc queries directly against the database, it can provide an additional layer to reduce the chances of accidental data exposure."

Report message to a moderator

Re: Transparent Database Encryption [message #660130 is a reply to message #660119]

Thu, 09 February 2017 05:52

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

John Watson wrote on Thu, 09 February 2017 10:04

Quote:
So, the limitation I thought was on the application side, or is it not?
No, it isn't. Your application sees clear text data. It will write clear text data: whatever your users choose to write. All Redaction does is adjust the values returned to the user process, it doesn't do anything with INSERT or UPDATE.

I believe the concern is apps which are lazy and write everything what the database shows them, redacted, thus wrecking the data underneath.

e.g. some HR guy updates a record with say a holiday and it also has redacted SSN. the app is poorly written and writes ALL the current values back in totality instead of just the holiday thus replacing the SSN with garbage.

I may be wrong, it's been a while since I worried about stuff like this but Oracle have a long track record these days of warning people against bad application design even when it is really not the database manuals place to do so.

Edit to add clarity

[Updated on: Thu, 09 February 2017 05:58]

Report message to a moderator

Re: Transparent Database Encryption [message #660131 is a reply to message #660128]

Thu, 09 February 2017 06:34

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

jvanh wrote on Thu, 09 February 2017 11:27

Apologies herewith & thanks again for your prompt feedback.

Will start testing to take this concern away: "Even though Oracle Data Redaction is not designed to prevent data exposure to database users who run ad hoc queries directly against the database, it can provide an additional layer to reduce the chances of accidental data exposure."

This is referring to the fact that if (for example) emp.sal is redacted, you can determine people's salaries by inference if you run queries such as

Code: [Select all] [Show/ hide]

select * from emp where sal between 4000 and 5000

Redaction is not intended to protect against users of tools where you can do that: it is intended to, for example, replace credit card numbers with XXXX-XXXX-XXXX-XXXX if people are using your website, but not if they are using your internal applications. You are reversing that.
If you want to conceal data from SQL*Plus users, give them access to views, not to the tables.

Report message to a moderator

Re: Transparent Database Encryption [message #661551 is a reply to message #660131]

Thu, 23 March 2017 11:40

Caffeine+
Messages: 14
Registered: February 2017

Junior Member

In addition to tightly controlled views I would also recommend looking into the Virtual Private Database features. That would allow you to set a policy against a tables that would enforce a need-to-know strategy. You can redact the data by default (i.e. nobody can see the data) and selectively allow clear text visibility based on session and instance attributes. I used a VPD solution to redect the last 4 digits of CC to any query unless it originated from the application. This was prior to Oracle Database Vault, TDE and automatic data redaction features were available, all which I would recommend now instead of my hand-carved solution. VPD is included in your DB licenses so it wouldn't incur additional costs beyond your time.

HTH

Report message to a moderator

Re: Transparent Database Encryption [message #661554 is a reply to message #661551]

Thu, 23 March 2017 12:52

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I used a VPD solution to redect the last 4 digits of CC to any query unless it originated from the application.

And why did not you use a simple view for this?

Report message to a moderator

Re: Transparent Database Encryption [message #661559 is a reply to message #661554]

Thu, 23 March 2017 13:55

Caffeine+
Messages: 14
Registered: February 2017

Junior Member

For the sake of clarity I did not include that it was done for a packaged application that had its own implementation of access control lists. We just piggybacked the VDP context on top of the application tables to selectively unmask the data.

Report message to a moderator

Previous Topic:	How to software use database security?
Next Topic:	Secure Connection

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 09:43:08 CDT 2025