This discussion is archived
5 Replies Latest reply: Dec 28, 2012 7:00 AM by Bolaji80 RSS

Oracle Wallet with encrypted column values

Saurabh Gupta-OC Newbie
Currently Being Moderated
HI,

We have an requirement to encrypt sensitive data of some of the table's columns for security compliance purpose. We are on Oracle 11gR2 on Linux platform.

We tried to test it out by using TDE - Oracle Wallet. Below is the detail steps:

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "SauPass";
System altered.

SQL> CREATE TABLE TEST_USER
(
USER VARCHAR2(10),
PWD VARCHAR2(30)ENCRYPT,
CREATE_DATE_TIME DATE,
MOD_DATE_TIME DATE
);
Table created.

SQL> INSERT INTO TEST_USER VALUES(‘abcde’,'xyzer’,sysdate,sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test_user;
USER PWD CREATE_DA MOD_DATE_
---------- ------------------------------ --------- ---------
abcde xyzer 06-SEP-12 06-SEP-12

Connect as sys:
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "SauPass";
System altered.

Connect saurabh:
SQL> select * from test_user;
select * from test_user
*
ERROR at line 1:
ORA-28365: wallet is not open

SQL> SELECT USER FROM TEST_USER;
USER
----------
abcde

But the problem is when I try to retrieve all the records from that table its throwing error "ORA-28365: wallet is not open". I want that when I fire query "select * from table_name" then it should show any encrypted values for encrypted column name along with rest of column values.

Actually I dont know this is possible with wallet or not, if anyone knows any alternative or solution, please reply.

Thanks,
Saurabh

Edited by: 877938 on Sep 6, 2012 3:08 AM
  • 1. Re: Oracle Wallet with encrypted column values
    sybrand_b Guru
    Currently Being Moderated
    When All Else Fails Read the Fine Manual http://docs.oracle.com/cd/E11882_01/network.112/e10746/asotrans.htm#ASOAG10137

    Yes, you need to have a wallet open, so the error message shouldn't have been a surprise to you.

    ------------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: Oracle Wallet with encrypted column values
    Dom Brooks Guru
    Currently Being Moderated
    ORA-28365: wallet is not open
    This command closes the wallet:
    ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "SauPass";
    Have you tried opening the wallet?
     ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "SauPass";
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2014.htm#sthref3351
  • 3. Re: Oracle Wallet with encrypted column values
    Saurabh Gupta-OC Newbie
    Currently Being Moderated
    Hi Dom,

    I think you did not understand my question.

    If I open the wallet then actual value of the column can be seen, I know this & then there is no meaning of encrypting the column.

    But in close wallet case, If I query for all the columns in the table, it should show any hash/encrypted value for the encrypted column, this is my requirement.

    Please let me know if its clear to you.

    Regards.
  • 4. Re: Oracle Wallet with encrypted column values
    Dom Brooks Guru
    Currently Being Moderated
    I don't believe that your requirement matches the [url http://docs.oracle.com/cd/E18283_01/network.112/e10746/asotrans.htm]concepts of TDE.

    You want obfuscation / data masking - See [url http://docs.oracle.com/cd/E11882_01/network.112/e16543/vpd.htm#i1014682]VPD or [url http://docs.oracle.com/cd/E11882_01/server.112/e23090/dba.htm#BABGBJAF]Data Vault or [url http://docs.oracle.com/cd/E11882_01/network.112/e16543/data_encryption.htm#DBSEG80084]DBMS_CRYPTO.
  • 5. Re: Oracle Wallet with encrypted column values
    Bolaji80 Newbie
    Currently Being Moderated
    @877938

    What you presently have is TDE. You need to open the wallet by issuing this command:

    alter system set encryption wallet open;

    or

    alter system set encryption wallet open identified by "zaq12wsx";


    However, I understand what you're actually looking for. I am also working on a similar requirement on 10gR2. I want to be able to encrypt data in SSN and Credit Card columns such that the scenario will look like this:

    original value prior to encryption:

    Name SSN Credit Card
    Jane 212-20-2211 4311234250110012


    select * from CUSTOMER_INFO
    Name SSN Credit Card
    Jane zerhs7ms fgs753bndklw9wasd7f

    Please share your findings so that I can also benefit from it. Thanks.

    BJ

Legend

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