russ -- Thanks for the question regarding "Need Unique Obfuscated/Hashed PK to expose in public URL", version Apex 3.2.1

You Asked

Tom,

Long time reader, first time poster...

Developing in Apex 3.2.1 (side note... thanks for sharing "ask the expert" with the Apex community... very helpful "how to" in various areas :-), Database Server 10g R2 (10.2.0.3), Windows for now, but Linux later.

Summary: I'm trying to write a DB trigger (Before Insert) that will generate a unique obfuscated ID so that I can expose the obfuscated ID in a public URL.

Specifically, I have a table (A) with a trigger populated PK (sequence or sys_guid() ). I want to email a URL allowing the display of the newly created row, but I can't send the PK since the next/previous PK value is easily guessable and would allow the bad guys to troll other peoples data. My idea is to have a DB trigger take the PK value and obfuscate it (but the result needs to be unique), store the obfuscated PK in a column (Hidden_in_plain_sight_ID), and use Hidden_in_plain_sight_ID in the public URL. Then my Apex page could act upon the one row uniquely identified by the Hidden_in_plain_sight_ID. I know this does not prevent a brute force attack, I just want to make it "smarter than a fifth grader" to break.

I've thought of sys_guid(), but that is sequential on some platforms (or is there a way to force "psuedo-random" ?), and I've thought of dbms_obfuscation_toolkit.md5( PK ), but I think I read that md5 is not guaranteed to be unique even thought each PK is unique;

I suspect I have just missed an obvious answer, but I hope I have described the goal clearly enough.

Thanks,
Russ

and we said...

"ask the expert" does that itself - the unique, but unguessable key (unguessable in that you cannot guess what a valid ID would be - they jump all over the place, definitely not sequential)

... but I can't send the PK since the next/previous PK value is easily guessable and would allow the bad guys to troll other peoples data.....

I do this for that reason, no one logs in here, and I'd rather not be able to have people guess the primary keys and see an answer I did not publish. It would not be fatal for me if they did - so it is just 'secure enough', it would be hard to find an ID that I've used for an answer that is flagged as "not published". I send that private ID to a user so they can read it but the rest of the world cannot.

    select  to_number(
                 to_char(ate_seq.nextval) ||
                      lpad( substr( abs(ate_random.rand), 1, 5 ),5, '0' ) ||
                      ltrim(to_char(mod(abs(dbms_utility.get_time),1000000),'000000'))
                 )
    into    g_curr_val
    from   dual;

is what I use, nextval makes it unique

5 digits of randomness makes it less-guessable

get_time - an offset from some epoch that eventually resets - makes it so you would have to not only know what time I did the operation - but you'd have to know my epoch as well.

Reviews

Brilliant...   October 29, 2009 - 2pm Central time zone Bookmark | Bottom | Top Reviewer: russ clyde from Dallas, TX I figured (yep, I'm from Texas) it did something like that cause after I submitted my question I tried to guess your question_ID... obviously without success. Believe it or not, I did look at "ask the expert" database triggers and saw the use of ati_id.next_val, but had (unfortunately) assumed it was just a sequence number. If I'm not mistaken, your method even minimizes the indexing of a random number issue since the leading edge of your ID is in sequential order (until you bust into a new digit). This solution exactly matches what I need. As a bonus, the method appears to act as a sys_guid() and "almost" always be unique across multiple databases if I need to combine or transfer data to a new instance. Thanks.

---

Write a Review