Oracle Obfuscated Sequences

Obfuscating Sequences

CREATE OR REPLACE PACKAGE obfuscate_seq IS

  FUNCTION obfuscate_seq(i_seq IN NUMBER) RETURN NUMBER;
  FUNCTION deobfuscate_seq(i_nextval IN NUMBER) RETURN NUMBER;

END obfuscate_seq;
/
CREATE OR REPLACE PACKAGE BODY obfuscate_seq IS

  g_crypto_mod NUMBER := dbms_crypto.des3_cbc_pkcs5;

  g_seq_key RAW(128) := hextoraw('082748387234987899239487972395030981208309823098');

  FUNCTION obfuscate_seq(i_seq IN NUMBER) RETURN NUMBER IS

    l_seed_raw    RAW(7); -- max 7 byte
    l_nextval     NUMBER(19); -- can hold 64bit signed integer
    l_nextval_raw RAW(8); -- max 8 byte
    l_salt        NUMBER(19);

  BEGIN
    -- max      = 2^56 = 2^(7*8) = 72057594037927936 = 0xFFFFFFFFFFFFFF
    -- max seq  = 2^32 = 2^(4*8) = 4294967296        =       0xFFFFFFFF
    -- max salt = 2^24 = 2^(3*8) = 16777216          = 0xFFFFFF

    l_salt := trunc(dbms_random.VALUE(1,
                                      16777216 + 1));

    l_seed_raw := to_char(l_salt,
                          'FM00000X') || to_char(i_seq,
                                                 'FM0000000X');

    dbms_output.put_line('   seed:   0x' || l_seed_raw || ' len: ' || utl_raw.length(l_seed_raw) || ' decimal: ' ||
                         to_number(l_seed_raw,
                                   'XXXXXXXXXXXXXX'));

    l_nextval_raw := dbms_crypto.encrypt(l_seed_raw,
                                         g_crypto_mod,
                                         g_seq_key);

    IF (to_number(substr(l_nextval_raw,
                         1,
                         1),
                  'X') > 7)
    THEN
      l_nextval := to_number(l_nextval_raw,
                             'XXXXXXXXXXXXXXXX') - 18446744073709551616; --  number - 2^64 (2 complement)
    ELSE
      l_nextval := to_number(l_nextval_raw,
                             'XXXXXXXXXXXXXXXX');
    END IF;

    dbms_output.put_line('nextval: 0x' || l_nextval_raw || ' len: ' || utl_raw.length(l_nextval_raw) || ' decimal: ' ||
                         l_nextval);

    RETURN l_nextval;

  END;

  FUNCTION deobfuscate_seq(i_nextval IN NUMBER) RETURN NUMBER IS

    l_nextval_raw RAW(8); -- max 8 byte
    l_seed_raw    RAW(7); -- max 7 byte
    l_seq         NUMBER(10); -- max 4 byte

  BEGIN

    IF (i_nextval < 0)
    THEN

      l_nextval_raw := to_char(i_nextval + 18446744073709551616,
                               'FM000000000000000X');

    ELSE
      l_nextval_raw := to_char(i_nextval,
                               'FM000000000000000X');
    END IF;

    dbms_output.put_line('nextval: 0x' || l_nextval_raw || ' len: ' || utl_raw.length(l_nextval_raw) || ' decimal: ' ||
                         i_nextval);

    l_seed_raw := dbms_crypto.decrypt(l_nextval_raw,
                                      g_crypto_mod,
                                      g_seq_key);

    dbms_output.put_line('   seed:   0x' || l_seed_raw || ' len: ' || utl_raw.length(l_seed_raw) || ' decimal: ' ||
                         to_number(l_seed_raw,
                                   'XXXXXXXXXXXXXX'));

    l_seq := to_number(substr(to_char(l_seed_raw),
                              7),
                       'XXXXXXXXXXXXXX');

    RETURN l_seq;
  END;

END obfuscate_seq;
/

Test Script

DECLARE
  seq_original   NUMBER(10);
  seq_obfuscated NUMBER;
  seq_restored   NUMBER;
BEGIN
  seq_original   := 1;
  seq_obfuscated := obfuscate_seq.obfuscate_seq(seq_original);
  seq_restored   := obfuscate_seq.deobfuscate_seq(seq_obfuscated);
  dbms_output.put_line('seq_original : ' || seq_original);
  dbms_output.put_line('seq_restored : ' || seq_restored);

  seq_original   := 4294967295;
  seq_obfuscated := obfuscate_seq.obfuscate_seq(seq_original);
  seq_restored   := obfuscate_seq.deobfuscate_seq(seq_obfuscated);
  dbms_output.put_line('seq_original : ' || seq_original);
  dbms_output.put_line('seq_restored : ' || seq_restored);

  seq_original   := 0;
  seq_obfuscated := obfuscate_seq.obfuscate_seq(seq_original);
  seq_restored   := obfuscate_seq.deobfuscate_seq(seq_obfuscated);
  dbms_output.put_line('seq_original : ' || seq_original);
  dbms_output.put_line('seq_restored : ' || seq_restored);
END;