encrypt Oracle data

[helloun]

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

[PAVB]

I would go with a trigger.

[LKBrwn_DBA]

And where is the code of this trigger/procedure?
Are you getting any errors? -- Post the errors...

[helloun]

Hi,
I didn't write the code for trigger, I was looking for sample trigger codes. Do I have to write a new code or make a call to the existing procedure from trigger.

Thanks

[LKBrwn_DBA]

Yes, you need to write the code, perhaps:

1) A procedure you can call to encrypt/decrypt any data
2) A trigger as suggested by PAVB that calls the above "encrypt" procedure to obfuscate the column(s) you want secret.

[PAVB]

I didn't write the code for trigger

A trigger is nothing but a storedproc that fires on certain circumstances - plenty of examples in Oracle documentation and on the web.

The beauty of addessing the issue via a trigger is that no matter how many pieces of code you may have out there touching that table you can go home and sleep well knowing that every single row will be encrypted.

Just to do things cleanly I would pack the obfuscation code as a function rather than as a storedproc.