This discussion is archived
1 Reply Latest reply: Aug 13, 2012 9:07 AM by Justin Cave RSS

How to encrypt the Number(data type) column

954872 Newbie
Currently Being Moderated
h3. How to encrypt the number column and have to store the result in that same number column ?

I know number column wont accecpt encrypted values like *"A0ED23RT"*.

________________________________

create table TEST(name varchar2(20),amount number(20));

Insert into table TEST values('name',encrypt(20));

SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

________________________________

I am using DBMS.CRYPTO to encrypt.

There is only one option to encrypt number column by using TDE(Transparent Data Encryption) , But this can be only used in System Level, not in session level.

____________________________________

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "myPassword";
____________________________________

*** And also by using TDE its unable to hide the original value, as the name implies its transparent.

h3. Is there any DBMS package to encrypt the number column and to produce the output as encrypted number ?

I want to get the output like this.

Original Values:

insert into table TEST values('John',encrypt(20000000));


After Encryption:

select * from TEST;

Name Amount

John 46739528
  • 1. Re: How to encrypt the Number(data type) column
    Justin Cave Oracle ACE
    Currently Being Moderated
    I'm not sure that I understand the question.

    If you want to manually encrypt and decrypt data using DBMS_CRYPTO, the encrypted data should be stored in a RAW column. This is true whether the source data is a string, a number, or any other data type. Yes, you can often get away with storing encrypted data in a VARCHAR2 column up until you find yourself struggling with character set conversion issues at which point you'll need to convert to RAW anyway.

    You certainly can manually encrypt a number and store it in a RAW column. You can't manually encrypt anything and store it in a NUMBER column. Well, I guess you could, it just would be a lot of effort for no real benefit. It rarely makes sense to do your own manual encryption and decryption-- key management becomes a major issue at that point. Letting Oracle handle the encryption using TDE generally makes much more sense.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points