Arikkan
asked on
Encryption Decryption in Oracle
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.
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.
ASKER
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?
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?
>>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.
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.
ASKER
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
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
>>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.
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.
ASKER
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
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
>> 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?
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?
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your suggestion.
Can other experts throw some light to this discussion?
Can other experts throw some light to this discussion?
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.
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.