"Real" Transparent Data Encryption
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
A solution using DBMS_CRYPTO and Instead-Of Triggers
Oracle sells an license that allows customers to utilize a featured name Transparent Data Encryption.
CREATE TABLE async_hashmap (
rid NUMBER(4,0),
checksums VARCHAR2(1000));

ALTER TABLE async_hashmap
ADD CONSTRAINT pkasync_hashmap
PRIMARY KEY(rid);

CREATE SEQUENCE seq;

-- load seed table
BEGIN
FOR i in 1 .. 1000 LOOP
INSERT INTO async_hashmap VALUES (seq.NEXTVAL, dbms_crypto.randombytes(500));
END LOOP;
COMMIT;
END;
/

ALTER TABLE async_hashmap READ ONLY;

DROP SEQUENCE seq;
L
CREATE OR REPLACE PACKAGE heliac AUTHID DEFINER IS
 FUNCTION get_checksum(pSrc IN VARCHAR2 DEFAULT NULL,
                       pOffset1 IN INTEGER DEFAULT NULL,
                       pOffset2 IN INTEGER DEFAULT NULL) RETURN RAW;

 FUNCTION set_checksum(pSrc IN RAW DEFAULT NULL,
                       pOffset1 IN RAW DEFAULT NULL,
                       pOffset2 IN BLOB DEFAULT NULL) RETURN VARCHAR2;
END heliac;
/

CREATE OR REPLACE PACKAGE BODY heliac IS
--------------------------------------------
FUNCTION get_checksum(pSrc IN VARCHAR2 DEFAULT NULL,
                      pOffset1 IN INTEGER DEFAULT NULL,
                      pOffset2 IN INTEGER DEFAULT NULL) RETURN RAW IS
 l_int INTEGER;
 l_key RAW(128);
 l_mod INTEGER := dbms_crypto.encrypt_aes256 + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5;

 InvalidSrc EXCEPTION;
 InvalidOffset EXCEPTION;
BEGIN
  IF (pSrc IS NULL) OR (pOffset1 IS NULL) OR (pOffset2 IS NULL) OR LENGTH(pSrc) NOT IN (15,16) THEN
    RAISE InvalidSrc;
  END IF;

  BEGIN
    l_int := TO_NUMBER(pSrc);
  EXCEPTION
    WHEN OTHERS THEN
      RAISE InvalidSrc;
  END;

  IF pOffset1 NOT BETWEEN 1 AND 1000 THEN
    RAISE InvalidOffset;
  END IF;

  IF pOffset2 NOT BETWEEN 1 AND 967 THEN
    RAISE InvalidOffset;
  END IF;

  SELECT utl_raw.cast_to_raw(SUBSTR(checksums, pOffset2, 32))
  INTO l_key
  FROM async_hashmap
  WHERE rid = pOffset1;

  RETURN dbms_crypto.encrypt(utl_raw.cast_to_raw(pSrc), l_mod, l_key);
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END get_checksum;
--------------------------------------------
FUNCTION set_checksum(pSrc IN RAW DEFAULT NULL,
                      pOffset1 IN RAW DEFAULT NULL,
                      pOffset2 IN BLOB DEFAULT NULL) RETURN VARCHAR2 IS
 l_mod INTEGER := dbms_crypto.encrypt_aes256 + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5;
BEGIN
  RETURN utl_raw.cast_to_varchar2(dbms_crypto.decrypt(src => pSrc, typ => l_mod, key => pOffset1));
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END set_checksum;
--------------------------------------------
END heliac;
/
L
SELECT heliac.get_checksum('4245071625964194', 634, 712) FROM dual;

SELECT utl_raw.cast_to_raw(SUBSTR(checksums, 712, 32)) from async_hashmap where rid = 634;

SELECT heliac.set_checksum('DA04EC78F2E105DFD912A49E6FCF89F801BBA7BA2A4AA9CC3ADF2601F9FD8F57', '3936364334363837383034394341443043384537383944393445464437444233') FROM dual;
L
e
A
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved