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,
) 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>)
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.