Loading an Oracle table with an encrypted LOB file and then decrypting it

Question

I am being given a large encrypted file containing 10 million rows of data.

I need to load this into an oracle database (in it's encrypted form) and then decrypt it in the database using pl/sql and the oracle built-in dbms_crypto.

I then need to process the unencrypted LOB to seperate out the 10 Million rows.

I will have the public key for the file. The file will be about 5GB in size. Is this possible using just pl/sql? (and the oracle built-ins)

Has anyone had any experience of doing this sort of thing? - any pointers will be most welcome.

Thanks

Answer 1

this works for me.

SYS has to:

GRANT EXECUTE ON DBMS_CRYPTO TO <user>

---

in user :

CREATE OR REPLACE DIRECTORY
CRYPTDIR AS
'<crypted files directory>';

---

CREATE TABLE TESTCRYPT (ID INTEGER, E BLOB, D BLOB);

---

CREATE OR REPLACE FUNCTION load_Blob_FromFile(p_file_name VARCHAR2) 
RETURN BLOB
AS
   dest_loc  BLOB := empty_blob();
   src_loc   BFILE := BFILENAME('CRYPTDIR', p_file_name);
BEGIN
   -- Open source binary file from OS
   DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

   -- Create temporary LOB object
   DBMS_LOB.CREATETEMPORARY(
         lob_loc => dest_loc
       , cache   => true
       , dur     => dbms_lob.session
   );

   -- Open temporary lob
   DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);

   -- Load binary file into temporary LOB
   DBMS_LOB.LOADFROMFILE(
         dest_lob => dest_loc
       , src_lob  => src_loc
       , amount   => DBMS_LOB.getLength(src_loc));

   -- Close lob objects
   DBMS_LOB.CLOSE(dest_loc);
   DBMS_LOB.CLOSE(src_loc);

   -- Return temporary LOB object
   RETURN dest_loc;
END;
/

---

INSERT INTO TESTCRYPT (ID, E) SELECT 1, LOAD_BLOB_FROMFILE('EncryptedFile') FROM DUAL;

---

CREATE OR REPLACE FUNCTION DCRYPT2(TO_DECRYPT IN BLOB) RETURN BLOB
IS
  DECRYPTED BLOB;
  v_key  PLS_INTEGER :=
      DBMS_CRYPTO.ENCRYPT_AES128 +
      DBMS_CRYPTO.CHAIN_ECB +
      DBMS_CRYPTO.PAD_PKCS5;
BEGIN
dbms_lob.createtemporary(DECRYPTED,true);
DBMS_CRYPTO.DECRYPT(DECRYPTED,
                    TO_DECRYPT,
                     v_key,
                     '<Hex-Key>'
                    );
RETURN DECRYPTED;
END;

---

UPDATE TESTCRYPT SET D = DCRYPT2(E) WHERE ID=1;

---

CREATE OR REPLACE FUNCTION PADIS_MASTER.blob2clob (p_in blob) RETURN clob IS
     v_clob    clob;
     v_varchar VARCHAR2(32767);
     v_start   PLS_INTEGER := 1;
     v_buffer  PLS_INTEGER := 32767;
   BEGIN
     dbms_lob.createtemporary(v_clob, TRUE);
     FOR i IN 1..CEIL(dbms_lob.getlength(p_in) / v_buffer)
     LOOP
       v_varchar := utl_raw.cast_to_varchar2(dbms_lob.SUBSTR(p_in, v_buffer, v_start));
       dbms_lob.writeappend(v_clob, LENGTH(v_varchar), v_varchar);
       v_start := v_start + v_buffer;
     END LOOP;
     RETURN v_clob;
   END;
/

---

SELECT BLOB2CLOB(D) FROM TESTCRYPT WHERE ID = 1;

---

for example Oracle 11 compiles with this key: 'b1b7adc285e82db81ea17f7be706e4f7'

at last the encryption function:

CREATE OR REPLACE FUNCTION ECRYPT(TO_CRYPT IN BLOB) RETURN BLOB
IS
 CRYPTED BLOB;
 v_key  PLS_INTEGER :=  DBMS_CRYPTO.ENCRYPT_AES128 
                      + DBMS_CRYPTO.CHAIN_ECB 
                      + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
 dbms_lob.createtemporary(AUSGABE,true);
 DBMS_CRYPTO.ENCRYPT(CRYPTED,
                     TO_CRYPT,
                     v_key,
                     'b1b7adc285e82db81ea17f7be706e4f7'
                    );
 RETURN CRYPTED;
END;