Home » RDBMS Server » Security » Encryption (oracle 11.2.0.1.0, windows)
Encryption [message #679300] Thu, 20 February 2020 07:20 Go to next message
shawaj
Messages: 85
Registered: January 2016
Member
Hi All,

i need to store encrypted data(social security or PAN) in database table for security purpose so i tried the following command

CREATE  TABLE customer(
    cust_id      NUMBER,
    cust_name    VARCHAR2(100),
    so_securityno   VARCHAR2(30) encrypt,
    cust_phone   NUMBER encrypt,
    cust_pan VARCHAR2(20) encrypt
  );
but got error - ERROR at line 1:
ORA-00439: feature not enabled: Transparent Data Encryption
Re: Encryption [message #679301 is a reply to message #679300] Thu, 20 February 2020 07:27 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
You are trying to use Transparent Data Encryption, which needs to be separately licensed option on top of Enterprise Edition. It also requires some configuration: you need to create a wallet to store the keys. Have you done all that?
Re: Encryption [message #679306 is a reply to message #679301] Thu, 20 February 2020 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67148
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... In addition, once the wallet is opened the data are visible to everyone who has access to the table.
TDE only protects against disk or backup thief not against database intruders.

You have to first define what you want to protect, against what/who and when.
You have to then define who/what is allowed to see the data, how and when.
Only then you can define which strategy you may implement (which may not be encryption).

[Updated on: Fri, 21 February 2020 00:56]

Report message to a moderator

Re: Encryption [message #679311 is a reply to message #679306] Fri, 21 February 2020 00:41 Go to previous messageGo to next message
shawaj
Messages: 85
Registered: January 2016
Member
Thanks for your response,

i need to protect some columns of a table, against disk or backup and against database intruders too.

Please suggest a way..

Re: Encryption [message #679312 is a reply to message #679301] Fri, 21 February 2020 00:47 Go to previous messageGo to next message
shawaj
Messages: 85
Registered: January 2016
Member
Thanks John Watson,

i don't have additional licence for TDE and configuration.
Re: Encryption [message #679313 is a reply to message #679312] Fri, 21 February 2020 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67148
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

One way is to encrypt at application level... if this fits the other requirements and you can trust the application vendor/developers...
As I said there are many questions to answer.

Re: Encryption [message #679314 is a reply to message #679312] Fri, 21 February 2020 01:33 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
shawaj wrote on Fri, 21 February 2020 06:47
Thanks John Watson,

i don't have additional licence for TDE and configuration.
So use DBMS_CRYPTO. To make the encryption and decryption transparent, cover the tables with views that will decrypt when you SELECT and have INSTEAD OF triggers to encrypt when you INSERT or UPDATE.
Re: Encryption [message #679315 is a reply to message #679314] Fri, 21 February 2020 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67148
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... but take care that when you use it the encryption algorithm and key are visible in the SGA (so to the DBA and then it depends against who you want to protect, back to my questions).

Re: Encryption [message #679358 is a reply to message #679315] Tue, 25 February 2020 06:58 Go to previous messageGo to next message
shawaj
Messages: 85
Registered: January 2016
Member
Thanks to all,

my problem solved by using DBMS_CRYPTO package.
Re: Encryption [message #679946 is a reply to message #679358] Tue, 14 April 2020 09:08 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 25
Registered: September 2018
Location: USA
Junior Member
shawaj,

Would you share how you encrypt cust_phone, i.e. number, field?

Thanks.


[Updated on: Tue, 14 April 2020 09:10]

Report message to a moderator

Re: Encryption [message #679952 is a reply to message #679946] Tue, 14 April 2020 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67148
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can use UTL_RAW to convert a NUMBER to a RAW then DBMS_CRYPTO to encrypt the NUMBER field (and possibly use UTL_RAW to convert the RAW value from DBMS_CRYPTO to a NUMBER).

Re: Encryption [message #679954 is a reply to message #679952] Tue, 14 April 2020 11:27 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 25
Registered: September 2018
Location: USA
Junior Member
Alas. Proposed way doesn't allow to insert encrypted data into number field.
Re: Encryption [message #679955 is a reply to message #679954] Tue, 14 April 2020 11:39 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
Olexandr Siroklyn wrote on Tue, 14 April 2020 17:27
Alas. Proposed way doesn't allow to insert encrypted data into number field.
Well, no - the column (please don't say "field" when you mean "column"!) has to be data type raw. What you probably need to do (as I suggested before) is cover the table with a view that does all the work.
Re: Encryption [message #679956 is a reply to message #679955] Tue, 14 April 2020 11:47 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 25
Registered: September 2018
Location: USA
Junior Member
Thanks. Solution, not based on DBMS_CRYPTO, to encrypt date, timestamp, number and bfile can be found on forum's marketplace.
Re: Encryption [message #679957 is a reply to message #679956] Tue, 14 April 2020 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67148
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you are saying you hijacked this topic to spam for your product?

Re: Encryption [message #679958 is a reply to message #679957] Tue, 14 April 2020 12:43 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 25
Registered: September 2018
Location: USA
Junior Member
If you find out my last response as inappropriate to this topic I have no objections to have that response as deleted.
Re: Encryption [message #679959 is a reply to message #679958] Tue, 14 April 2020 12:50 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
I suppose, Olexandr, it would have been better to make your product affiliation clear earlier.

Suggestion - you could redeem yourself! Create another Marketplace topic describing your product in greater detail. This comment on your websiteQuote:
Restrictions

There are no ways to store asymmetrically encrypted data in Oracle table data types like date, timestamp, float, number.
is interesting, you could explain a bit more about how you actually do this.
Re: Encryption [message #679965 is a reply to message #679958] Tue, 14 April 2020 14:47 Go to previous message
Michel Cadot
Messages: 67148
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Olexandr Siroklyn wrote on Tue, 14 April 2020 19:43
If you find out my last response as inappropriate to this topic I have no objections to have that response as deleted.

What I found inappropriate is that you introduced yourself in the topic asking how OP did it, ignored the answers that have been provided to you and claimed there is a solution in the Marketplace forum.
In short, you came masked to promote your product.
There won't be no problem if you just posted something like "a solution might be this product" with a link to your post in Marketplace forum.

And to add to John's post, you can give a short description in our Wiki Commercial products page and create a page in the Wiki describing in details your product. You can also create your user page.

Previous Topic: Store and retrieve a key from db wallet
Next Topic:
Goto Forum:
  


Current Time: Tue Jun 02 13:00:00 CDT 2020