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 |
|
