My database has around 100 tables and anywhere from 1,000 to 20,000 rows per table. Each of the tables has a primary key called id - at the moment when referencing rows in a table I am simply using id, for example www.domain.com/viewuser.php?id=3
I am pretty sure I have no hardcoded references to row ID's in my code, they all reference either a POST value or a session ID set when the user logs in.
Is it easy to retrospectively change the id from auto increment integer to a unique alphanumeric id for all my existing tables without causing extensive recoding or altering existing queries etc? If so, how would I go about it?
My tow thought were to manually update all my tables but this would also require changing every insert as well? Could I do something with a unique_id table which sits between though I think this may require updating of all queries? Hoping there is an easy way to do this...
Randomizing the id values (changing them from auto_increment integers to alphanumeric) in the database doesn't really address the underlying problem.
Even if you manage to randomize and obfuscate the id values, users are still going to be able to access other user's data through URL manipulation. All you are going to achieve is to make it it "less likely" that any particular URL manipulation attempt will succeed at returning forbidden data.
If your app needs to PREVENT some users from seeing some data, then your app needs to provide a real mechanism for doing that.
Obfuscating ID values is going to be whole lot of work, and in the end, it won't accomplish what really needs to be done.
(see the comments from Sabeen Malik, drew010, Justin Swartsel,,, they are all "right on".)
If your application already implements a mechanism to prevent access to forbidden data, the obfuscation of ID values is not necessary.
The only place these ID values really need to obfuscated would be in the URL (or POST), and that can be achieved WITHOUT changing the existing database. (See Sabeen Malik's comment; it's possible to encrypt/decrypt the id values sent to/from the browser... the big issue there is if there is javascript running in the browser that is depending on the id values being "orderable"...)
I understand this may not be the answer you were looking for in your original question. But I wouldn't undertake the effort to change from id INT AUTO_INCREMENT values absent a good reason to.