| encrypt a few filds in the database [message #442945] |
Thu, 11 February 2010 09:33  |
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 #442952 is a reply to message #442945] |
Thu, 11 February 2010 10:20  |
John Watson
Messages: 8960
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 |
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
John Watson
Messages: 8960
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 |
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 #443037 is a reply to message #442958] |
Fri, 12 February 2010 03:42 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
John Watson wrote on Thu, 11 February 2010 18:20Hi, 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
