implement obscured unique identifiers for existing MySQL schema

I have an existing MySQL database schema in production for an PHP5 application. The application was built in a popular MVC framework (which one isn't important). We use Doctrine ORM 1.2.x as out ORM.

The default routing used a pr开发者_运维问答imary id, in our case simply an unsigned auto incremented integer. However some of the data is sensitive, and although we run under SSL, changing an ID value in the url could potentially give access to confidential data a user is not authorized to see.

The solution as I see it is to use some obscured value in place of the more obvious record ID.

Ideally we would just add a new column to affected table and generate some unique random or hashed value for that record right?

However, I can conceivably see a couple other tables/routes being in need of the same treatment sooner or later, and would like a reusable solution that can avoid a series of database updates. So I've been thinking about alternative methods and would like opinions on whether there are any major issues to be concerned about.

• simple obfuscating the value, i.e. shifting bits and/or base 64 encoding
• quick and nasty encryption
• using hmac to ensure the id given matches the given hmac

update As mentioned by Charles, ACLs would be a preferred solution, however some portions of the site are open to the public, so ACLs for these areas are not possible. We do however make extensive use of ACL in the applications backend.

changing an ID value in the url could potentially give access to confidential data a user is not authorized to see.

Shouldn't your code have access controls that would prevent this from happening? That's going to be a more sane solution than the "security through obscurity" that obfuscated identifiers is going to bring you.

Ideally we would just add a new column to affected table and generate some unique random or hashed value for that record right?

Yup! Just remember to mark the column as UNIQUE.

So I've been thinking about alternative methods and would like opinions on whether there are any major issues to be concerned about.

The three options you mentioned are silly in their own ways. All you need is a unique identifier for that row in that table. Pull some entropy out of thin air and encode enough of it, and bingo, you have your unique identifier. Don't get too clever, you'll find yourself with a self-inflicted bullet through your foot. Don't make the random data based on a hash of the contents or the row's existing sequential identifier. The HMAC could be good enough here.

On the other hand, I wouldn't have the faintest idea on how to hook your ORM up to the new identifier, or have it generate one for you when also creating new rows.