Hi All,

We are using encryption - decryption functions as below:

The table itself is defined as follows.

drop table T;
create table T
( emp_number varchar(80) not null,
emp_full_name varchar(255),
emp_date_of_birth blob(128),
emp_bank_account_number blob(128),
emp_bank_bsb blob(128),
emp_next_of_kin_full_name blob(1024)
) in A;

-- Use ISO format dates? (YYYY-MM-DD-HH24:MIS). The below insertion is done through ETL or by Admin and the encryption key is only known to him and user who will use the report.
set encryption password 'donkey';
insert into T ('70120843', 'SH', ENCRYPT(TRIM('1900-10-15')), ENCRYPT(TRIM('303-092')),ENCRYPT(TRIM('333666999')), ENCRYPT(TRIM('JW')));

To return the resultset
set encryption password 'donkey'; -- This is set in the view in the schema and not visible to public.

we have a prompt which the user will have to answer with the encryption key to see the result of the Select statement

select emp_number, emp_full_name,
decrypt_char(cast(emp_date_of_birth as varchar(1024)),<prompt>),
decrypt_char(cast(emp_bank_account_number as varchar(1024)),<prompt>),
decrypt_char(cast(emp_bank_bsb as varchar(1024)),<prompt>),
decrypt_char(cast(emp_next_of_kin_full_name as varchar(1024)),<prompt>)
from T;


Now, we want that when a Select statement is given over this table T without the encryption key prompt being answered, then only the two un-encrypted column should be displayed. And when the key prompt is answered, all the columns encrypted and non-encrypted should be displayed.

Any suggestion to implement this is welcomed where the key remains known only to admin or the user but not the developer.