Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Encryption Decryption in Oracle

Posted on 2016-10-28
12
Medium Priority
?
255 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
  • 5
  • 5
12 Comments
 
LVL 78

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 78

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
Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

 

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 78

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 78

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 78

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

Put Machine Learning to Work--Protect Your Clients

Machine learning means Smarter Cybersecurity™ Solutions.
As technology continues to advance, managing and analyzing massive data sets just can’t be accomplished by humans alone. It requires huge amounts of memory and storage, as well as high-speed processing of the cloud.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

972 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