Hi,
I have a created the DBMS_OBFUSCATION procedure to encrypt all Sensitive existing data in the table.
Following is the procedure for
CREATE OR REPLACE PROCEDURE "DESENCRYPT_ORACLE" IS
input_string CHAR(16);
key_string VARCHAR2(8):= '123ABC67';
pid NUMBER (11);
encrypted_string VARCHAR2(2048);
decrypted_string VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';
CURSOR c1 IS SELECT ssn, person_id FROM CPP_PERSON_TEST WHERE ssn IS NOT NULL AND ssn !='--' AND LENGTH(ssn)=11;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO input_string,pid;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Input string: '||input_string || 'Key string is ' || key_string || 'Encrypted string is ' || encrypted_string);
dbms_obfuscation_toolkit.Desencrypt(input_string => input_string ,key_string => key_string,encrypted_string => encrypted_string );
UPDATE CPP_PERSON_TEST
SET new_ssn=encrypted_string
WHERE person_id=pid;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN error_in_input_buffer_length THEN
DBMS_OUTPUT.PUT_LINE('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
/
Now, I am trying to create a procedure/trigger to encrypt any new incoming data. The procedure, should accept the value, encrypt it and insert the encrypted value in the table. Please advice.
Thanks

