(randomnumber, randominteger are restricted cases of dbms_crypto_ffi.random(16) and dbms_crypto_ffi.random(4)).
dbms_crypto.randombytes function is based on the RSA X9.31 PRNG (Pseudo-Random Number Generator) ( DBMS_CRYPTO).
NIST wrote:
As of January 1, 2016, in accordance with the SP800-131A Revision 1 Transitions: Recommendation for
Transitioning the Use of Cryptographic Algorithms and Key Lengths, the use of RNGs specified
in FIPS 186-2, [X9.31], and the 1998 version of [X9.62] is no longer approved.
This list is provided for historical purposes only.
In this Blog, we will give a dbms_crypto.randombytes Enhancement Suggestion with draft code. Then we take UUID generations as a use case of random functions, and compare Plsql vs. Java implementations.
Note: Tested on Oracle 19.17.
1. Draft Implementation of dbms_crypto.randombytes Enhancement Suggestion
We can enhance dbms_crypto.randombytes to read /dev/random or urandom as something like following code:
create or replace directory DEV_RANDOM_DIR as '/dev';
create or replace function randombytes_new (p_mode varchar2, p_len number := 16) return raw as
l_random_bytes raw(30000);
l_file utl_file.file_type;
l_line varchar2(10000);
not_implmentation exception;
pragma exception_init (not_implmentation, -20001);
begin
case p_mode
when 'H' then
l_file := utl_file.fopen('DEV_RANDOM_DIR', 'random', 'R');
utl_file.get_line(l_file, l_line);
l_random_bytes := utl_raw.cast_to_raw(l_line);
when 'M' then
l_file := utl_file.fopen('DEV_RANDOM_DIR', 'urandom', 'R');
utl_file.get_line(l_file, l_line);
l_random_bytes := utl_raw.cast_to_raw(l_line);
when 'L' then
l_random_bytes := dbms_crypto.randombytes(p_len); -- no read of /dev/random or urandom
else raise_application_error(-20001,'no_implmentation');
end case;
if p_mode in ('H', 'M') then
utl_file.fclose(l_file);
end if;
l_random_bytes := lower(substr(l_random_bytes, 1, p_len*2));
dbms_output.put_line(l_random_bytes);
return l_random_bytes;
end;
/
-- Test
SQL > select randombytes_new('H'), randombytes_new('M') , randombytes_new('L') from dual;
67B9D40056FFF28D3FD625EF47E4FA97
9D07ED5D1EB537757B354F71242F5816
3B3567C46334C705030FA61A73DB921E
2. UUID Generation
Take UUID generations as a use case of random functions, we will compare Plsql vs. Java implementations.
Java java.util.UUID reads /dev/random (/dev/urandom) and provides a cryptographically strong random number generator (RNG) with high entropy, as described in RFC 1750: Randomness Recommendations for Security. (calls java.security.SecureRandom.nextBytes with selected security.provider)
sun Java security provider NativePRNG has 3 Variants: MIXED, BLOCKING, NONBLOCKING (default MIXED):
BLOCKING: seedFile = new File(NAME_RANDOM); nextFile = new File(NAME_RANDOM);
MIXED: seedFile = new File(NAME_RANDOM); nextFile = new File(NAME_URANDOM);
NONBLOCKING: seedFile = new File(NAME_URANDOM);nextFile = new File(NAME_URANDOM);
In internet, we can find different uuid implmentations with Oracle dbms_crypto and Java,
for example: How to generate a version 4 (random) UUID on Oracle?
Here our test code:
create or replace function dbms_crypto_uuid return varchar2 is
/* UUID Version 4 must be formatted as xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx
where x is any hexadecimal character (lower case only) and y is one of 8, 9, a, or b.*/
v_uuid_raw raw(16);
v_uuid varchar2(36);
v_y varchar2(1);
begin
v_uuid_raw := sys.dbms_crypto.randombytes(16);
v_uuid_raw := utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(v_uuid_raw, 7, 1), '0F'), '40'), v_uuid_raw, 7);
v_y := to_char(8 + round(dbms_random.value(0, 3)), 'fmx');
v_uuid_raw := utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(v_uuid_raw, 9, 1), '0F'), v_y || '0'), v_uuid_raw, 9);
v_uuid := substr(v_uuid_raw, 1, 8)||'-'||
substr(v_uuid_raw, 9, 4)||'-'||
substr(v_uuid_raw, 13, 4)||'-'||
substr(v_uuid_raw, 17, 4)||'-'||
substr(v_uuid_raw, 21, 12);
return v_uuid;
end;
/
--Java Variant-1
create or replace function java_uuid_v1 return varchar2 as language java
name 'java.util.UUID.randomUUID() return String'
;
/
--Java Variant-2
create or replace and compile java source named "RandomUUIDV2" as
import java.util.UUID;
public class RandomUUIDV2{
public static String create() {
return java.util.UUID.randomUUID().toString();
}
private static class Inner {
//overwrite Object.toString()
public String toString()
{
System.out.println("Call Stack");
StackTraceElement[] stackTraces = Thread.currentThread().getStackTrace();
for (int i = 1; i < stackTraces.length; i++) {
System.out.println(" " + (stackTraces.length - i) + " " + stackTraces[i]);
}
return "End";
}
}
public static String ToStringCompare() {
Inner inner = new Inner();
System.out.println("\n---------- Object toString implicit ----------\n");
System.out.println(inner);
System.out.println("\n---------- Class toString explicit ----------\n" );
System.out.println(inner.toString());
return "ToStringCompare";
}
};
/
create or replace function java_uuid_v2 return varchar2 as language java
name 'RandomUUIDV2.create() return String';
/
create or replace function to_string_compare return varchar2 as language java
name 'RandomUUIDV2.ToStringCompare() return String';
/
create or replace procedure test_proc_uuid(p_cnt number, p_use_method varchar2 := 'dbms_crypto', p_gc_limit number := null) as
l_uuid_str varchar2(200);
l_java_es_return varchar2(50);
l_gc_cnt_start number;
l_gc_cnt_end number;
l_heap_size_start number;
l_heap_size_end number;
l_endsess_cnt number := 0;
l_start_time number := dbms_utility.get_time;
begin
dbms_output.put_line('============== test_proc_uuid('||p_cnt||', '||p_use_method||', '||p_gc_limit||') ===========');
select s.value into l_gc_cnt_start from v$mystat s, v$statname n
where s.statistic#= n.statistic# and name in ('java call heap gc count');
select s.value into l_heap_size_start from v$mystat s, v$statname n
where s.statistic#= n.statistic# and name in ('java call heap used size');
for i in 1..p_cnt loop
case p_use_method
when 'dbms_crypto' then
l_uuid_str := dbms_crypto_uuid;
when 'java_v1' then
l_uuid_str := java_uuid_v1;
when 'java_v2' then
l_uuid_str := java_uuid_v2; -- with randomUUID().toString()
else raise_application_error(-20010, 'no implementation');
end case;
if mod(i, p_gc_limit)= 0 then
l_java_es_return := dbms_java.endsession;
l_endsess_cnt := l_endsess_cnt + 1;
end if;
end loop;
select s.value into l_gc_cnt_end from v$mystat s, v$statname n
where s.statistic#= n.statistic# and name in ('java call heap gc count');
select s.value into l_heap_size_end from v$mystat s, v$statname n
where s.statistic#= n.statistic# and name in ('java call heap used size');
dbms_output.put_line('dbms_java.endsession count = '||l_endsess_cnt||', Elpased_CS = '||(dbms_utility.get_time-l_start_time));
dbms_output.put_line('java call heap gc diff = '||(l_gc_cnt_end - l_gc_cnt_start)||' ('||l_gc_cnt_end||'-'||l_gc_cnt_start||')');
dbms_output.put_line('java call heap used size diff = '||(l_heap_size_end - l_heap_size_start)||
' ('||l_heap_size_end||'-'||l_heap_size_start||'), '||' End Size(KB) = '||(round(l_heap_size_end/1024)));
end;
/
SQL> set serveroutput on size 50000
SQL> exec dbms_java.set_output(50000);
SQL> select dbms_crypto_uuid, java_uuid_v1, java_uuid_v2 from dual;
DBMS_CRYPTO_UUID JAVA_UUID_V1 JAVA_UUID_V2
------------------------------------ ------------------------------------ ------------------------------------
F82C9C94-6BBA-47B0-AC1B-9593224FC974 4c5c9e82-4646-4f75-bf40-84f2c46479ab e85416af-3665-4053-96c2-b5b06aa7f6de
SQL> select to_string_compare from dual;
---------- Object toString implicit ----------
Call Stack
4 RandomUUIDV2$Inner.toString(RandomUUIDV2:12)
3 java.lang.String.valueOf(String.java:2994)
2 java.io.PrintStream.println(PrintStream.java:821)
1 RandomUUIDV2.ToStringCompare(RandomUUIDV2:23)
End
---------- Class toString explicit ----------
Call Stack
2 RandomUUIDV2$Inner.toString(RandomUUIDV2:12)
1 RandomUUIDV2.ToStringCompare(RandomUUIDV2:25)
End
// java.lang.String.valueOf
// public static String valueOf(Object obj) {
// return (obj == null) ? "null" : obj.toString();
// if the argument is null, then a string equal to "null"; otherwise, the value of obj.toString() is returned.
begin
test_proc_uuid(10000, 'dbms_crypto');
test_proc_uuid(10000, 'java_v1');
test_proc_uuid(10000, 'java_v2');
test_proc_uuid(10000, 'dbms_crypto', 1000);
test_proc_uuid(10000, 'java_v1', 1000);
test_proc_uuid(10000, 'java_v2', 1000);
end;
/
============== test_proc_uuid(10000, dbms_crypto, ) ===========
dbms_java.endsession count = 0, Elpased_CS = 23
java call heap gc diff = 0 (90-90)
java call heap used size diff = 0 (136352-136352), End Size(KB) = 133
============== test_proc_uuid(10000, java_v1, ) ===========
dbms_java.endsession count = 0, Elpased_CS = 112
java call heap gc diff = 1 (91-90)
java call heap used size diff = -61792 (74560-136352), End Size(KB) = 73
============== test_proc_uuid(10000, java_v2, ) ===========
dbms_java.endsession count = 0, Elpased_CS = 86
java call heap gc diff = 1 (92-91)
java call heap used size diff = 3903360 (3977920-74560), End Size(KB) = 3885
============== test_proc_uuid(10000, dbms_crypto, 1000) ===========
dbms_java.endsession count = 10, Elpased_CS = 18
java call heap gc diff = 0 (92-92)
java call heap used size diff = 0 (3977920-3977920), End Size(KB) = 3885
============== test_proc_uuid(10000, java_v1, 1000) ===========
dbms_java.endsession count = 10, Elpased_CS = 245
java call heap gc diff = 10 (102-92)
java call heap used size diff = -3903360 (74560-3977920), End Size(KB) = 73
============== test_proc_uuid(10000, java_v2, 1000) ===========
dbms_java.endsession count = 10, Elpased_CS = 114
java call heap gc diff = 10 (112-102)
java call heap used size diff = 61792 (136352-74560), End Size(KB) = 133
