Re: TDE issues

Bill,
are you sure of your test case?
the second query on testx
select * from t1 where not exists (select 'x' from oracle.t2_at_devx a where a.c1 = t1.c1);

cannot return the result that you mention 1
as there is no such data in t1 in testx (otherwise, this is a 'wrong result' case).

rgds

On 5/23/07, William Wagman <wjwagman_at_ucdavis.edu> wrote:
>
> 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