Home » SQL & PL/SQL » SQL & PL/SQL » encrypt a few filds in the database (10204)
encrypt a few filds in the database [message #442945] Thu, 11 February 2010 09:33 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
Hi,.

We have a requirement to encrypt a few columns in database. While support can currently view all the data in production for a table. We do not want to revoke access to the table, but just encrypt information like address, names etc,.

How can we apply encryption to only 2-3 fields in a table, without affecting our selects from the table.

any ideas?
Re: encrypt a few filds in the database [message #442950 is a reply to message #442945] Thu, 11 February 2010 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to hide some columns to some users then do not encrypt, use view and grant access to the view instead of the table.

Regards
Michel
Re: encrypt a few filds in the database [message #442951 is a reply to message #442945] Thu, 11 February 2010 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>How can we apply encryption to only 2-3 fields in a table, without affecting our selects from the table.
I do not clearly understand above.

Oracle DBMS does not know or care about any "encrypted" columns.
To Oracle bits are bits.
It is up to the application to handle as needed the encryption/decryption of the contents of these columns.
Re: encrypt a few filds in the database [message #442952 is a reply to message #442945] Thu, 11 February 2010 10:20 Go to previous messageGo to next message
John Watson
Messages: 6564
Registered: January 2010
Location: Global Village
Senior Member
Encryption is straightforward with 10.2.0.4, use transparent data encryption (TDE) for the necessary columns. Your software will not know it has happened, except for a very few issues with indexes and constraints I think. But that won't stop your staff from seeing the data, because it is transparent! Do you mean something more like Data Vault, where you set up realms within which no-one (not even SYS) can see certain data?
Re: encrypt a few filds in the database [message #442953 is a reply to message #442945] Thu, 11 February 2010 10:39 Go to previous messageGo to next message
kwalz58
Messages: 13
Registered: February 2006
Location: SC
Junior Member
You could look at doing something like VPD - that doesn't do encryption but you can use it to hide column values.

If you want encrypted data you would probably have to define a view over the table that uses one of the encryption packages for the columns you want to protect.

Either case you go with, however, will have a performance impact as you are now applying some kind of function to each row of a result set that contains these columns.

The way of least impact would be the view mentioned earlier that simply does not contain the columns to be protected.
Re: encrypt a few filds in the database [message #442955 is a reply to message #442953] Thu, 11 February 2010 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@John Watson and @kwalz58

Why starting to envisage complex and sophisticated features when a simple view can do it?

I see this all the time in my job, "we want VPD", "we want encryption" and when you ask why the answer is always "some data must not be seen by some users", well there is nothing simpler, faster and more performant than view. No side effect on performances, no side effect on optimizer, no need to create context or trigger, no need to use CPU to encrypt and decrypt, no need to change organization (including backups) to manage cypher key...

Regards
Michel
Re: encrypt a few filds in the database [message #442958 is a reply to message #442955] Thu, 11 February 2010 11:20 Go to previous messageGo to next message
John Watson
Messages: 6564
Registered: January 2010
Location: Global Village
Senior Member
Hi, Michel. I'm interested in this at the moment, because we are trying to sell the Security Options to a number of clients. The PCI rules for encrypting credit card numbers apply to every company that uses credit cards, and they are really expensive to implement, unless you use TDE. And I see great use for Data Vault: it becomes possible to outsource all your DBA work, without letting anyone (not even me, AS SYSDBA) see sensitive data. You can't do that with views! The DAta Masking option is powerful, too.
With the current set of security options, I think that as a DBA I can provide some very cost-effective solutions. Don't you think it is important that people should consider using them, instead of investing in code?
Re: encrypt a few filds in the database [message #442959 is a reply to message #442955] Thu, 11 February 2010 11:26 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The view solution has been used for ages and is super-simple, but it breaks down as soon your Developers/DBAs stop being dilligent. Copies of prod getting copied to dev & QA where grants may be less well controlled can quickly expose sensitive data. When you're trying to protect plain-text passwords, salary info, credit card numbers or whatever, you may need to consider encryption.

VPD has never been attractive to me for many reasons, including the need for careful schema design up-front and the lack (or great difficulty) in being able to maintain that logically separate database independently of the others that share the same tables (truncate, backup & restore etc).
Re: encrypt a few filds in the database [message #442983 is a reply to message #442955] Thu, 11 February 2010 19:48 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Michel Cadot wrote on Thu, 11 February 2010 10:45
@John Watson and @kwalz58

Why starting to envisage complex and sophisticated features when a simple view can do it?

.....
.....

Regards
Michel



I Agree.
Re: encrypt a few filds in the database [message #443037 is a reply to message #442958] Fri, 12 February 2010 03:42 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John Watson wrote on Thu, 11 February 2010 18:20
Hi, Michel. I'm interested in this at the moment, because we are trying to sell the Security Options to a number of clients. The PCI rules for encrypting credit card numbers apply to every company that uses credit cards, and they are really expensive to implement, unless you use TDE. And I see great use for Data Vault: it becomes possible to outsource all your DBA work, without letting anyone (not even me, AS SYSDBA) see sensitive data. You can't do that with views! The DAta Masking option is powerful, too.
With the current set of security options, I think that as a DBA I can provide some very cost-effective solutions. Don't you think it is important that people should consider using them, instead of investing in code?

You mix up many solutions with different (and maybe exclusive) purpose.
I didn't say you should not consider them, I said you should FIRST consider simple solution.

TDE is protection against file theft, not SQL access.
Encryption protects data but leads to code development and performances deterioration. Most of the time, if you really need encryption, it is better to do it OUTSIDE the database, in the application server/program.
Database Vault protects against DBA using SQL but not from direct access to the files and it leads to modifications in entreprise organization.
Data Masking is to hide data when replicating data, nothing about the production database.
Views do not lead to any code development but do not protect against file theft nor DBA access (although you can restrict DBA accesses, for instance they do not need to have SELECT ANY TABLE privilege).

A DBA should not provide ALL "solutions", but the most efficient one(s) to fit the need requirements (which is the most difficult thing to get).
So the first answer is: "what do you want to protect, against who or what?" not the list of high level features. It is surely less sexy but more efficient.

Regards
Michel
Previous Topic: Matrix report problem
Next Topic: Performance Issues
Goto Forum:
  


Current Time: Sat Dec 03 12:28:43 CST 2016

Total time taken to generate the page: 0.08738 seconds