Permissions on Oracle package DBMS_CRYPTO
========================================
GRANT EXECUTE ON DBMS_CRYPTO TO TEST_USER;
========================================
Create a wrapper package.
p_text - is the encrypted value
p_key - a key passed from application
The encrypted value - is stored in database instead of the real value
The key - should be stored outside of the database.
========================================
CREATE OR REPLACE PACKAGE DBMS_CRYPT AS
FUNCTION encrypt_text (p_text IN VARCHAR2,
p_key IN VARCHAR2) RETURN VARCHAR2;
FUNCTION decrypt_text (p_text IN VARCHAR2,
p_key IN VARCHAR2) RETURN VARCHAR2;
END DBMS_CRYPT;
/
========================================
CREATE OR REPLACE PACKAGE BODY DBMS_CRYPT AS
FUNCTION encrypt_text (p_text IN VARCHAR2,
p_key IN VARCHAR2) RETURN VARCHAR2 IS
v_return_value VARCHAR2(1000);
BEGIN
SELECT DBMS_CRYPTO.encrypt(UTL_RAW.cast_to_raw(p_text), 4353, UTL_RAW.cast_to_raw(p_key))
INTO v_return_value
FROM DUAL;
RETURN v_return_value;
END;
FUNCTION decrypt_text (p_text IN VARCHAR2,
p_key IN VARCHAR2) RETURN VARCHAR2 IS
v_return_value VARCHAR2(1000);
BEGIN
SELECT UTL_RAW.cast_to_varchar2(SYS.DBMS_CRYPTO.decrypt(p_text, 4353, UTL_RAW.cast_to_raw(p_key)))
INTO v_return_value
FROM DUAL;
RETURN v_return_value;
END;
END DBMS_CRYPT;
/
Usage
========================================
SELECT DBMS_CRYPT.encrypt_text(p_text => TO_CHAR(1234), p_key => 'Apr2026@Tomia') FROM DUAL;
SELECT DBMS_CRYPT.decrypt_text(p_text => 'B067B800BC18271C', p_key => 'Apr2026@Tomia') FROM DUAL;
Usage with table
========================================
CREATE TABLE TOKEN_USAGE
(id NUMBER,
usage_name VARCHAR2(100),
usage_value VARCHAR2(1000),
ts_last_modified DATE DEFAULT SYSDATE
) TABLESPACE IGT_TABLE;
ALTER TABLE TOKEN_USAGE ADD CONSTRAINT TOKEN_USAGE_PK PRIMARY KEY (id) USING INDEX TABLESPACE IGT_INDEX;
INSERT INTO TOKEN_USAGE (id, usage_name, usage_value) VALUES (1,'Tomia Chat Bot',DBMS_CRYPT.encrypt_text(p_text => TO_CHAR(1234), p_key => 'Apr2026@Tomia'));
SELECT id, usage_name, usage_value FROM TOKEN_USAGE;
ID USAGE_NAME USAGE_VALUE
---------- -------------- -----------------
1 Tomia Chat Bot B067B800BC18271C
SELECT id, usage_name, DBMS_CRYPT.decrypt_text(p_text => usage_value, p_key => 'Apr2026@Tomia') as usage_value, ts_last_modified FROM TOKEN_USAGE
WHERE id=1;S
ID USAGE_NAME USAGE_VALUE
---------- -------------- -----------------
1 Tomia Chat Bot 1234
