Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Encryption Decryption in Oracle

Posted on 2016-10-28
12
Medium Priority
?
222 Views
Last Modified: 2016-11-16
Hello experts,

I've an application built on VB.NET and it's using Oracle DB.  The application was in use since couple of year and database have records in it.

I like to encrypt few sensitive data in database and decrypt   it while accessing in application. here's few questions that i've. could someone answer?

I don't want to purchase any additional oracle license for encryption, rather like to use any existing feature to achieve this.

1. Is there a way to use oracle package or query to encrypt, decrypt the data without changing the application code?
2. If it's not possible in Oracle query, what is the best way to encrypt and decrypt the data with minimal code change?
3.  How do i encrypt the Date column in database? I think other data type can be encrypted using either oracle query or .net code but I've doubt on Date column.

Any input/suggestion will be greatly appreciated.
0
Comment
Question by:Arikkan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41864782
A lot of the answers here depend on what and when you need encryption.  Most of the time this is defined by policy.  What are your exact requirements?

Do you need encryption at rest when the database is down?
Do you need encryption in transit?
Do you want only specific columns encrypted and only decrypted at the application level?

Each requirement has different implementation and different license implications.

>>I don't want to purchase any additional oracle license

For us to answer that, we would need to know your current license.  Personally, I would NEVER post that on a public website...

I would contact your account team/sales for exactly what you need.

Data at rest and available to apps sounds like Transparent Data Encryption (TDE) and normally requires additional licenses.

There is Vault that can protect data from even the DBA, again, probably an extra license.

If you want to write your own at the column level, DBMS_CRYPTO is an available package that comes with most installs.  I believe it is free in most versions but only Oracle can confirm that for sure.

There are several options in between those.

I would really work directly with Oracle on what you really need and what your options are.
2
 

Author Comment

by:Arikkan
ID: 41868901
Thanks for your suggestion.

I like to implement this to specific columns and these should be decrypted at the application level.

Do you have any example of DBMS_CRYPTO that you mentioned in your post?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41868949
>>Do you have any example of DBMS_CRYPTO that you mentioned in your post?

Best place to start is always the online documentation:
http://docs.oracle.com/database/121/ARPLS/d_crypto.htm

There are some examples in there.

There are also examples on the Internet.



What I'm not sure of and have never tried is encrypting with DBMS_CRYPTO and decrypting with .Net.

You might be better off writing an initial encryption program with .Net that updates all the existing data.  Then let your application take care of everything new.  That way it is the same encryption code libraries doing it.
1
Automating Your MSP Business

The road to profitability.
Delivering superior services is key to ensuring customer satisfaction and the consequent long-term relationships that enable MSPs to lock in predictable, recurring revenue. What's the best way to deliver superior service? One word: automation.

 

Author Comment

by:Arikkan
ID: 41870795
Thanks, slightwv !

I've decided to use encrypt and decrypt at database level only. that way I'll not have to worry about encrypting in application. I'll read trough the documents and circle back if need any further help
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41870825
>>I've decided to use encrypt and decrypt at database level only.

What benefit does that give you?

You encrypt things for security.  If it is all stored in the database, there really isn't a need to encrypt it.  If I get access to your database, I have access to the encryption keys.
0
 

Author Comment

by:Arikkan
ID: 41870874
If i use encryption/decryption at database level (in packages and procedure/Queries) then I don't have to worry about changing the application code. Also, there are numerous logic in system to compare fields in query, and some of complex logic performs certain arithmetic operation like subtract the dates to get the result. So, It will be wiser to implement in Oracle only and not worry about changing the application code.

I think we can restrict the access of encryption key to certain user and roles.

Do you have any suggestion or best practice to achieve this whole requirement?

Appreciate your help
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41870889
>> Do you have any suggestion or best practice to achieve this whole requirement?

I don't know your exact requirements driving the need to encrypt certain fields.

>>I think we can restrict the access of encryption key to certain user and roles.

This may or may not meet the requirements.  

Your app connects to the database.  If I connect to the database as the application database user, I have everything I need to decrypt the data.

If I connect as the DBA, I have everything I need.

What other users do you have that connects directly to the database?
0
 

Author Comment

by:Arikkan
ID: 41871043
Just the application database user and few from DBA Team.

Also, the requirement is pretty simple that I want to encrypt few sensitive data in database and need a way to decrypt when reading back. Application is using in-line query as well as procedures for DML statements.

Do you have any working example of encryption/decryption in oracle?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 41871166
>>Just the application database user and few from DBA Team.

Which brings me to my point:  I can connect as any allosed user and I have everything I need to decrypt your data.

How are you going to restrict that through roles?

>>Do you have any working example of encryption/decryption in oracle?

Back to my first post:  That is a very broad topic.  If you mean DBMS_CRYPTO, I posted the documentation link that has an example.  I'm not sure I could come up with anything that isn't already on the Internet.

>> the requirement is pretty simple that I want to encrypt few sensitive data in database

That by itself isn't a complete requirement.  You encrypt data to protect it.  If everything I need to decrypt it is available to every user that can connect to the database, do I really need to encrypt it to start with?

Most encryption requirements have things like "data at rest",  "data in transit", etc...  

Each of these can be achieved in different ways and with different products.
0
 

Author Comment

by:Arikkan
ID: 41871353
Thanks for your suggestion.

Can other experts throw some light to this discussion?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41871504
Slightwv has given you some very good suggestions and questions.  You haven't told us what you may have tried, or how exactly you expect encryption to help you, or protect you from.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Worried about if Apple can protect your documents, photos, and everything else that gets stored in iCloud? Read on to find out what Apple really uses to make things secure.
Will you be ready when the clock on GDPR compliance runs out? Is GDPR even something you need to worry about? Find out more about the upcoming regulation changes and download our comprehensive GDPR checklist today !
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Suggested Courses

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question