Also if you store multiple encrypted string with a different set of security keys .

In that case you can get back your decrypted string corresponding to your security key.

It means you only able to decrypt the string which will match your security key

 

Let us demonstrate this as follows :-

------------------------------------------------------------

Step 1:-

Let us recreate the security table with a unique keu as follows

 

SQL> desc security_table

 Name                                      Null?    Type

 ----------------------------------------- -------- -----------------

 USERNAME                                           VARCHAR2(30)

 INPUT_DATE                                         DATE

 SECURE_DATA                                        VARCHAR2(80)

 UNQNO                                     NOT NULL NUMBER(5)

 

/* UNQNO is a Primary Key */

 

----------------------------------------------------------------

Step 2:-

Modify the Encrypt Procedure has follows :-

 

 

CREATE OR REPLACE PROCEDURE

Insert_encrypt_data(l_string in  VARCHAR2,

                    l_key in varchar2,

                    UnqKey in number)

as

l_data varchar2(2000);

Key_check_flag number;

l_encrypted_string varchar2(2000);

BEGIN

            --- the key and the input data must have a length

            -- divisible by eight (the key must be exactly 8 bytes long).

            --

            l_data := RPAD(l_string,(TRUNC(LENGTH(l_string)/8)+1)*8,CHR(0));

            key_check_flag := mod(length(l_key),8);

                  if key_check_flag != 0 then

                   Raise_application_error(-20199,'Key should be 8 char long');

                  end if;

            --

            -- Encrypt the input string

            --

                  DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT

                     (input_string => l_data,

                     key_string => l_key,

                   encrypted_string => l_encrypted_string);

            --

            --DBMS_OUTPUT.PUT_LINE('l_string ENCRYPTED: ' || l_encrypted_string);

            --

            --

            insert into security_table values(user,sysdate,l_encrypted_string,unqkey);

            commit;

            -- Dbms_output.put_line('Encrypted data inserted');

            END;

/

 

----------------------------------------------------------------

Step 3:-

Modify the Decrypt Procedure has follows :-

 

CREATE OR REPLACE PROCEDURE

Decrypt_encrypted_data(v_unqkey in number,l_key in varchar2,decrypt out varchar2)

as

cursor c1 is select secure_data from security_table where unqno=v_unqkey;

l_data varchar2(2000);

        Key_check_flag number;

l_decrypted_string varchar2(2000);

BEGIN

for c1rec in c1 loop

            --

            -- Both the key and the input data must have a length

            -- divisible by eight (the key must be exactly 8 bytes long).

            --

            key_check_flag := mod(length(l_key),8);

                  if key_check_flag != 0 then

                   Raise_application_error(-20199,'Key should be 8 char long');

                  end if;

                  DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT

                     (input_string => c1rec.secure_data,

                     key_string => l_key,

                     decrypted_string => l_decrypted_string);

            -- DBMS_OUTPUT.PUT_LINE('l_string DECRYPT: ' || l_decrypted_string);

             decrypt := l_decrypted_string;

            end loop;

            END;

/

 

------------------------------------------------------------------

Step 4:-

Now insert the data with different security Keys

 

SQL> execute insert_encrypt_data('YOU ARE INSERTING ENCRYPTED INFORMATION IN SECURITY TABLE','SECURITY',1);

SQL> execute insert_encrypt_data('YOU ARE INSERTING ENCRYPTED INFORMATION IN SECURITY TABLE WITH KEY 2','SECURIT2',2);

SQL> execute insert_encrypt_data('YOU ARE INSERTING ENCRYPTED INFORMATION IN SECURITY TABLE WITH KEY 3','SECURIT3',3);

 

PL/SQL procedure successfully completed.

 

 

SQL> select * from security_table;

 

USERNAME   INPUT_DATE           SECURE_DATA                                                       UNQNO

---------- -------------------- ------------------------------------------------------------ ----------

SCOTT      22-JUL-2002 03:57:38 9-£áçß┘µ╫╧ñ¥F▓«Sq↕╧u≡Σƒ+¿ôq√'╗s╫_┼╢5~ ╣àT↨Ü╩♂ò$┬oKαI6#∞╠±↓╥Σ          3

                                ▐M☼ç│├╫/K║¼σ

 

SCOTT      22-JUL-2002 03:56:40 ñ}vx≥£w¼├║╥)│ƒ_y╒╝8tσ♠∙k§ÿà#≡╞½<φm╟c█T$>1·í^v½╜♠◄Rⁿ↔WQ&♫╓_            1

                                ∙-k

 

SCOTT      22-JUL-2002 03:57:28 9-£áçß┘µ╫╧ñ¥F▓«Sq↕╧u≡Σƒ+¿ôq√'╗s╫_┼╢5~ ╣àT↨Ü╩♂ò$┬oKαI6#∞╠±↓╥Σ          2

                                ▐M☼ça→ñ5→q

 

 

 

-----------------------------------------------------------------------------

Step 5:-

Now Reterieve the Data  with different security Keys

 

ora816 SamSQL :> set serveroutput on

 

ora816 SamSQL :> set autoprint on

 

ora816 SamSQL :> var decrypt_String varchar2(2000)

 

 

 

SQL> execute Decrypt_encrypted_data('1','SECURITY',:decrypt_string);

 

PL/SQL procedure successfully completed.

 

 

DECRYPT_STRING

------------------------------------------------------------------------

YOU ARE INSERTING ENCRYPTED INFORMATION IN SECURITY TABLE

 

SQL> execute Decrypt_encrypted_data('2','SECURIT2',:decrypt_string);

 

PL/SQL procedure successfully completed.

 

 

DECRYPT_STRING

------------------------------------------------------------------------

YOU ARE INSERTING ENCRYPTED INFORMATION IN SECURITY TABLE WITH KEY 2

 

SQL> execute Decrypt_encrypted_data('3','SECURIT3',:decrypt_string);

 

PL/SQL procedure successfully completed.

 

 

DECRYPT_STRING

------------------------------------------------------------------------

YOU ARE INSERTING ENCRYPTED INFORMATION IN SECURITY TABLE WITH KEY 3

 

--------------------------------------------------------------------------------------

Copyright (c) Sameer Wadhwa All right Reserved