Solved

Encryption Decryption in Oracle

Posted on 2016-10-28
12
60 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:SHANCHAT972
  • 5
  • 5
12 Comments
 
LVL 76

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:SHANCHAT972
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 76

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
 

Author Comment

by:SHANCHAT972
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 76

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:SHANCHAT972
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 76

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:SHANCHAT972
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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:SHANCHAT972
ID: 41871353
Thanks for your suggestion.

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

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
SSL stands for “Secure Sockets Layer” and an SSL certificate is a critical component to keeping your website safe, secured, and compliant. Any ecommerce website must have an SSL certificate to ensure the safe handling of sensitive information like…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now