DBSecWorx: UTL

Oracle UTL_RAW Built-In Package Versions 7.3 - 21c

Security Advisory

This built-in package was first made available when dinosaurs still roamed the earth: Oracle version 7 and there was a need for utilities for working with the RAW data type.

Back then we didn't need to worry about such things as state and organized crime family attacks ... and Oracle granted EXECUTE on this package to PUBLIC. We would hope that were this package introduced today Oracle would have recognized the security implications and only granted EXECUTE to SYS and forced explicit grants to any application that requires its functionality.

We do, therefore, strongly advise granting EXECUTE on UTL_RAW to GSMADMIN_INTERNAL and revoking EXECUTE from PUBLIC: In that order. If your databases have specific requirements for accessing this package explicitly grant EXECUTE to the application prior to the revoke from PUBLIC.

Recommended Security Rules

NEVER

Let any user or schema without documented justification or escalated privileges gain access to this package by revoking EXECUTE from PUBLIC

WITH GREAT CARE

Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified schemas
Query the data dictionary after EXECUTE has been revoked from PUBLIC to verify the equivalence created is the equivalence approved by IT management and your CISO

CAUTIONS

Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES

How Oracle Works

The code demo, at right, demonstrates a reasonable use if UTL_RAW as part of an anonymous block used to encrypt credit cards set serveroutput on set linesize 121 DECLARE l_credit_card_no VARCHAR2(19) := '1612-1791-1809-2605'; l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no); l_key RAW(128) := utl_raw.cast_to_raw('abcdefgh'); l_encrypted_raw RAW(2048); l_decrypted_raw RAW(2048); BEGIN dbms_output.put_line('Original : ' || l_credit_card_no); l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw, dbms_crypto.des_cbc_pkcs5, l_key); dbms_output.put_line('Encrypted : ' || RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw))); l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw, typ => dbms_crypto.des_cbc_pkcs5, key => l_key); dbms_output.put_line('Decrypted : ' || utl_raw.cast_to_varchar2(l_decrypted_raw)); END; /

UTL_RAW Package Information

AUTHID DEFINER

Constants

Name	Data Type	Value
big_endian	PLS_INTEGER	1
little_endian	PLS_INTEGER	2
machine_endian	PLS_INTEGER	3

Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'UTL_RAW' UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'UTL_RAW'; -- returns 231 rows

Documented in Types & Packages No

First Available 7.3

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/utlraw.sql

Subprograms

BIT_AND	CAST_TO_BINARY_FLOAT	COPIES
BIT_COMPLEMENT	CAST_TO_BINARY_INTEGER	LENGTH
BIT_OR	CAST_TO_NUMBER	OVERLAY
BIT_XOR	CAST_TO_NVARCHAR2	REVERSE
CAST_FROM_BINARY_DOUBLE	CAST_TO_RAW	SUBSTR
CAST_FROM_BINARY_FLOAT	CAST_TO_VARCHAR2	TRANSLATE
CAST_FROM_BINARY_INTEGER	COMPARE	TRANSLITERATE
CAST_FROM_NUMBER	CONCAT	XRANGE
CAST_TO_BINARY_DOUBLE	CONVERT

BIT_AND

Perform bitwise logical "and" of the values in raw r1 with raw r2 and return the "anded" result raw utl_raw.bit_and(r1 IN RAW, r2 IN RAW) RETURN RAW;

SELECT utl_raw.bit_and('0102F3', 'F30201')

      FROM dual;

BIT_COMPLEMENT

Perform bitwise logical "complement" of the values in raw and return the "complement'ed" result raw utl_raw.bit_complement(r IN RAW) RETURN RAW;

SELECT utl_raw.bit_complement('0102F3')

      FROM dual;

BIT_OR

Perform bitwise logical "or" of the values in raw r1 with raw r2 and return the "or'd" result raw utl_raw.bit_or(r1 IN RAW, r2 IN RAW) RETURN RAW;

SELECT utl_raw.bit_or('0102F3', 'F30201')

      FROM dual;

BIT_XOR

Perform bitwise logical "exclusive or" of the values in raw r1 with raw r2 and return the "xor'd" result raw utl_raw.bit_xor(r1 IN RAW, r2 IN RAW) RETURN RAW;

SELECT utl_raw.bit_xor('0102F3', 'F30201')

      FROM dual;

CAST_FROM_BINARY_DOUBLE

Return the RAW representation of a binary_double value utl_raw.cast_from_binary_double( n IN BINARY_DOUBLE, endianess IN PLS_INTEGER DEFAULT 1) RETURN RAW;

SELECT utl_raw.cast_from_binary_double(123.45)

      FROM dual;

CAST_FROM_BINARY_FLOAT

Return the RAW representation of a binary_float value utl_raw.cast_from_binary_float( n IN BINARY_FLOAT, endianess IN PLS_INTEGER DEFAULT 1) RETURN RAW;

SELECT utl_raw.cast_from_binary_float(123.45)

      FROM dual;

CAST_FROM_BINARY_INTEGER

Return the RAW representation of a binary_integer value utl_raw.cast_from_binary_integer( n IN BINARY_INTEGER, endianess IN PLS_INTEGER DEFAULT 1) RETURN RAW;

SELECT utl_raw.cast_from_binary_integer(100)

      FROM dual;

CAST_FROM_NUMBER

Returns the binary representation of a NUMBER in RAW utl_raw.cast_from_number(n IN NUMBER) RETURN RAW;

SELECT utl_raw.cast_from_number(100)

      FROM dual;

CAST_TO_BINARY_DOUBLE

Perform a casting of the binary representation of the raw into a binary_double cast_to_binary_double( r IN RAW, endianess IN PLS_INTEGER DEFAULT 1) RETURN BINARY_DOUBLE;

SELECT utl_raw.cast_to_binary_double('405EDCCCCCCCCCCD')

      FROM dual;

CAST_TO_BINARY_FLOAT

Perform a casting of the binary representation of the raw into a binary_float utl_raw.cast_to_binary_float( n IN BINARY_FLOAT, endianess IN PLS_INTEGER DEFAULT 1) RETURN RAW;

SELECT utl_raw.cast_to_binary_float('42F6E666')

      FROM dual;

CAST_TO_BINARY_INTEGER

Perform a casting of the binary representation of the raw into a binary integer utl_raw.cast_to_binary_integer( r IN RAW, endianess IN PLS_INTEGER DEFAULT 1) RETURN BINARY_INTEGER;

SELECT utl_raw.cast_to_binary_integer('00000064')

      FROM dual;

CAST_TO_NUMBER

Perform a casting of the binary representation of the number (in RAW) into a NUMBER utl_raw.cast_to_number(r IN RAW) RETURN NUMBER;

SELECT utl_raw.cast_to_number('C202')

      FROM dual;

CAST_TO_NVARCHAR2

Converts a RAW represented using n data bytes into NVARCHAR2 with n data bytes utl_raw.cast_to_nvarchar2(r IN RAW) RETURN NVARCHAR2;

set serveroutput on

      

      BEGIN

        FOR i IN 100..200 LOOP

          dbms_output.put_line(utl_raw.cast_to_nvarchar2(TO_CHAR(i)));

        END LOOP;

      END;

      /

CAST_TO_RAW

Converts a VARCHAR2 represented using n data bytes into a RAW with n data bytes utl_raw.cast_to_raw(r IN VARCHAR2) RETURN RAW;

SELECT utl_raw.cast_to_raw('ABC')

      FROM dual;

CAST_TO_VARCHAR2

To extract a substring from a BLOB using a PL/SQL program use dbms_lob.substr(). The problem is that it returns a string in hexadecimal characters. CAST_TO_VARCHAR2 turns the hexadecimal string into readable ASCII format. utl_raw.cast_to_nvarchar2(r IN RAW) RETURN VARCHAR2;

set serveroutput on

      

      BEGIN

        FOR i IN 100..200 LOOP

          dbms_output.put_line(utl_raw.cast_to_varchar2(TO_CHAR(i)));

        END LOOP;

      END;

      /

Demo 2 SELECT rawtohex('!4!4!4!4!4!4') FROM dual; SELECT utl_raw.cast_to_varchar2('213421342134213421342134') FROM dual;

COMPARE

Compares raw r1 against raw r2. Returns 0 if r1 and r2 are identical, otherwise, returns the position of the first byte from r1 that does not match r2 utl_raw.compare(r1 IN RAW, r2 IN RAW, pad IN RAW DEFAULT NULL) RETURN NUMBER;

SELECT utl_raw.compare(utl_raw.cast_to_raw('ABC'), utl_raw.cast_to_raw('ACC'))

      FROM dual;

CONCAT

Concatenate a set of 12 raws into a single raw (up to 32K) utl_raw.concat(r1 IN RAW DEFAULT NULL, r2 IN RAW DEFAULT NULL, r3 IN RAW DEFAULT NULL, r4 IN RAW DEFAULT NULL, r5 IN RAW DEFAULT NULL, r6 IN RAW DEFAULT NULL, r7 IN RAW DEFAULT NULL, r8 IN RAW DEFAULT NULL, r9 IN RAW DEFAULT NULL, r10 IN RAW DEFAULT NULL, r11 IN RAW DEFAULT NULL, r12 IN RAW DEFAULT NULL) RETURN RAW;

SELECT utl_raw.concat('A','41','B','42')

      FROM dual;

CONVERT

Convert raw from one character to a different character set and return the resulting raw utl_raw.convert( r IN RAW, to_charset IN VARCHAR2, from_charset IN VARCHAR2) RETURN RAW;

DECLARE

       fr_charset CONSTANT VARCHAR2(30) := 

         SUBSTR(SYS_CONTEXT('USERENV', 'LANGUAGE'),

        INSTR(SYS_CONTEXT('USERENV', 'LANGUAGE'),'.')+1);

      

       to_charset VARCHAR2(30) := 'TR8EBCDIC1026S';

       rawvar     RAW(100);

      BEGIN

        dbms_output.put_line(fr_charset);

        dbms_output.put_line(to_charset);

      

        rawvar := utl_raw.convert(UTL_RAW.CAST_TO_RAW('Morgan'),

          'AMERICAN_AMERICA.'||to_charset,

          'AMERICAN_AMERICA.'||fr_charset);

      

        dbms_output.put_line(rawvar);

      END;

      /

COPIES

Return n copies of r concatenated together utl_raw.copies(r IN RAW, n IN NUMBER) RETURN RAW;

SELECT utl_raw.copies('A', 6)

      FROM dual;

LENGTH

Return the length in bytes of a raw utl_raw.length(r IN RAW) RETURN NUMBER;

SELECT utl_raw.length('ABC')

      FROM dual;

OVERLAY

Overlay the specified portion of target raw with overlay raw, starting from byte position pos of target and proceeding for "len" bytes utl_raw.overlay( overlay_str IN RAW, target IN RAW, pos IN BINARY_INTEGER DEFAULT 1, len IN BINARY_INTEGER DEFAULT NULL, pad IN RAW DEFAULT NULL) RETURN RAW;

SELECT utl_raw.overlay('1', 'AAABBBCCC', 4)

      FROM dual;

REVERSE

Reverse a byte sequence in raw r from end to end utl_raw.reverse(r IN RAW) RETURN RAW;

SELECT utl_raw.reverse('123')

      FROM dual;

col x format a12

      

      WITH q AS (

      SELECT HEXTORAW('93D90428') r FROM dual

      UNION ALL

      SELECT HEXTORAW('93D90428') r from dual)

      SELECT r, '0x' || rawtohex(utl_raw.reverse(r)) x

      FROM q;

SUBSTR

Return a substring portion of raw r beginning at pos for len bytes utl_raw.substr(r IN RAW, pos IN BINARY_INTEGER, len IN BINARY_INTEGER DEFAULT NULL) RETURN RAW;

set serveroutput on

      

      DECLARE

       tmp  VARCHAR2(250) := '';

       vraw RAW(200) := utl_raw.cast_to_raw('ABCDEABCDEABCDE');

      BEGIN

        FOR i IN 1 .. 15 LOOP

          tmp := tmp|| '0x' || utl_raw.substr(vraw, i, 1) || ' ';

          dbms_output.put_line(tmp);

        END LOOP;

      END;

      /

TRANSLATE

Translate the bytes in the input r raw according to the bytes in the translation raws, from_set and to_set utl_raw.translate( r IN RAW, from_set IN RAW, to_set IN RAW) RETURN RAW;

CREATE OR REPLACE FUNCTION trans_demo(pin IN VARCHAR2)

      RETURN VARCHAR2 IS

       r_in  RAW(2000);

       r_out      RAW(2000);

       r_ul  RAW(64);

       r_lu  RAW(64);

      BEGIN

        r_in := utl_raw.cast_to_raw(pin);

        r_ul := utl_raw.cast_to_raw('ABCDEFabcdef');

        r_lu := utl_raw.cast_to_raw('abcdefABCDEF');

      

        r_out := utl_raw.translate(r_in , r_ul, r_lu);

      

        return(utl_raw.cast_to_varchar2(r_out));

      END trans_demo;

      /

      

      SELECT trans_demo('FaDe') FROM dual;

      SELECT trans_demo('FAde') FROM dual;

TRANSLITERATE

Transliterate the bytes in the input r raw according to the bytes in the transliteration raws, from_set and to_set utl_raw.transliterate( r IN RAW, to_set IN RAW DEFAULT NULL, from_set IN RAW DEFAULT NULL, pad IN RAW DEFAULT NULL) RETURN RAW;

CREATE OR REPLACE FUNCTION tl_demo(pin IN VARCHAR2)

      RETURN VARCHAR2 AUTHID DEFINER IS

       r_in  RAW(2000);

       r_out RAW(2000);

       r_up  RAW(64);

       r_lo  RAW(64);

       r_un  RAW(64) := utl_raw.cast_to_raw('_');

      BEGIN

        r_in := utl_raw.cast_to_raw(pin);

        r_up := utl_raw.cast_to_raw('ABCDEF. ');

        r_lo := utl_raw.cast_to_raw('abcdef');

      

        r_out := utl_raw.transliterate(r_in , r_lo, r_up, r_un);

      

        return(utl_raw.cast_to_varchar2(r_out));

      END tl_demo;

      /

      

      SELECT tl_demo('AB C.D') FROM dual;

XRANGE

Returns a raw containing all valid 1-byte encodings in succession beginning with the value start_byte and ending with the value end_byte utl_raw.xrange( start_byte IN RAW DEFAULT NULL, end_byte IN RAW DEFAULT NULL) RETURN RAW;

SELECT utl_raw.xrange(utl_raw.cast_to_raw('A'), utl_raw.cast_to_raw('Z'))

      FROM dual;

      

      SELECT utl_raw.xrange(utl_raw.cast_to_raw('0A'), utl_raw.cast_to_raw('Z'))

      FROM dual;