Randomize DB record IDs

Question

In our web application we want to randomize the record IDs. The reason is because we want to hide how many entries there are in the DB already and we have unlisted things. In case IDs would be simple incremental numbers it would be easy to guess the IDs of unlisted things.

As I see it there are three ways to do this:

Simple Random Numbers

Algorithm:

1. Create a random number on insert.
2. Check if the ID is already in use. If yes goto 1.
3. Use this ID.

Pro

• easy
• works with any size or type of the ID (32bit, 64bit, var. length, strings)

Contra

• needs a transaction for possible race conditions (algorithm is not atomic)

UUIDs

Pro

• the likelihood of collisions is so low that you can ignore it

Contra

• we want to have nice short URLs with the page title as an URL comment ("#{id}--#{page_title}), a UUID would shift this comment all the way to the right
• I guess UUIDs as primary keys would have a lower performance on joins?

Encrypted IDs

Algorithm:

1. Read number from a sequence using nextval (atomic!)
2. Encrypt the ID using a secret key and an encryption algorithm that works with the size used for the ID

Pro

• no race conditions (no transactions necessary)

Contra

• size of the ID column can never be changed
• if someone can crack/guess the key all was for nothing

Time-stamps

Suggested by @emboss

Pro

• easy
• will not run out of IDs

Contra

• might produce collisions (although one needs to test if it really happens)
• maybe somewhat guessable

Random Public ID/Name Based Public ID

Suggested by @viktor tron

A second ID for all things that occur in an URL only used to find the record. Internally normal IDs are used (for joins etc).

Pro

• internally everything stays sane
• a good random algorithm/the naming scheme should make URL guessing impossible (enough)

Contra

• change a lot of things that use the IDs in public interfaces
• a user might expect that they can cut down URLs with such titles in them, but in this case the URLs won't work anymore

I think I'll use the third option. Or are there more arguments against it? Is there an even better solution? We use Ruby on Rails 3.x and PostgreSQL 9.x.

Edit: Unlisted does not mean private! It is meant like unlisted videos on YouTube. They are normal videos that just aren't listed in searches or the uploader's profile. So you can't really find them (without trying every possible ID), but everyone who knows the URL can access them. Of course a user that makes something unlisted and sends the link to someone else has to be aware that it might not stay unknown (the URL may be passed on and through linking might end up in a search engine).

We also have another option to make things private. These are two different things. (I see that assuming that everyone knows what "unlisted" means was a mistake.)

Answer 1

Note: this answers the initial version of the question, from which it was not obvious that this is not a replacement for authorization logic.

---

This is a wrong solution to a wrong problem.

You think the problem is: users can guess ids of "unlisted" things and use them.

Actual problem is: users can access things without authorization.

Put authorization logic in place, allow user access only to items that he can legitimately access and forbid everything else.

Also

hide how many entries there are in the DB

I think there's no shame in being small, if this is the reason. Anyway, you can start your sequence from 100000 or increment it by N or employ another similar trick :)

Answer 2

I suggest a totally different way: simply do not show record IDs to users. You do not need to. Use another form of identification for url.

Since you say you want pretty urls, you could simply use a slugger/permalink gem, like https://github.com/norman/friendly_id

friendly_id's default slug generator offers functionality to check slug strings for uniqueness and, if necessary, appends a sequence to guarantee it.

Seriously, leave IDs alone :)