TDE issues

From: William Wagman <wjwagman_at_ucdavis.edu>
Date: Tue, 22 May 2007 17:00:07 -0700
Message-ID: <FE043305B38A0F448F3924429D650C2A02A7A8FD@VEXBE2.ex.ad3.ucdavis.edu>

Greetings,  

 We have 2 databases devx, testx are on the same server and database links are created from devx to testx and vice versa. The following scenario clearly shows that there is an issue with queries when they run from encrypted database to an unencrypted database, but not the other way. Does anyone have any thoughts on this?    

Devx database, where TDE is implemented.  

Create table t1 (c1 varchar2(2));

Insert into t1 values ('1');

Insert into t1 values ('2');  

devx=SQL>alter table t2 modify (c1 encrypt);  

devx=SQL>desc t2;

Name                                      Null?    Type



----------------------------------------- --------

----------------------------


C1                                                 VARCHAR2(2) ENCRYPT

  

Testx database, where TDE is not implemented.  

Create table t2 (c1 varchar2(2));

Insert into t2 values ('2');

Insert into t2 values ('3');  

On testx, following sqls run and get the results as expected.  

---

---

testx=SQL>select * from t1 where exists (select 'x' from oracle.t2_at_devx a where a.c1 = t1.c1);  

C1

--

2  

1 row selected.  

testx=SQL>select * from t1 where not exists (select 'x' from oracle.t2_at_devx a where a.c1 = t1.c1);  

C1

--

1

---

---

 

On devx the following sqls run and the results are NOT what we expect to see.

---

---

 

devx=SQL>select * from t2 where not exists (select 'x' from oracle.t1_at_testx a where a.c1 = t2.c1 );  

C1

--

2

3  

2 rows selected.  

devx=SQL>select * from t2 where exists (select 'x' from oracle.t1_at_testx a where a.c1 = t2.c1);  

no rows selected

---

---

 

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208  

--

http://www.freelists.org/webpage/oracle-l Received on Tue May 22 2007 - 19:00:07 CDT