hi all

I have a table named "members" and it contains the following fields: member_id(key), username, pwdhash, email. The primary key is member_id and this id is used as foreign key to other tables.

In my web application, should the member_id be ever be exposed to the end user without encryption? For example www.jone.com?userid=1 will retreive records of member_id=1 in "members" table.

thanks

MemberID is a surrogate key, and as such you should avoid exposing it to the user because, of itself, it contains no information.
I'd assume that USERNAME is a unique value, and this is what you should display in your application.

ok. noted. thanks.

I agree with blindman, but would like to point out that there is nothing inherently insecure about showing "www.jone.com?userid=1" since it is just another piece of data (although meaningless to a user). The problem there is if you allowed them to change the userid to something else to try to view other users. This really isn't a database security issue so much as an application security issue. You application will have to check the permissions of any action you do to make sure the user is authorized to view the information.

the '1' should really just be a meaningless number to the user. Showing it shouldn't matter other than they won't know what it is. Using www.jone.com?username=amthomas instead of www.joine.com?userid=1 won't make yoru application any more secure.. you still have to do the same security checks.

If you are talking about showing it on a view form then there is no point.

except for the fact that this leaves you open to URL hacking. data passed along the query string will not pass the internal audits at the company i work for.

I mentioned that.

My point is hiding stuff does not automatically secure a program. The security should be there anyway whether shown or not. Your company can have a 'hide' policy but that doesn't automatically make it secure.

How do you load up the userid as in the above case? store everything in the session? There are some that would arguing that using the session like that is bad programming.

If USERNAME is unique, then why have you got a USERID?

because the database developer drank the surrogate key koolaid

In my case at least, because the data modeler is a skeptical, cynical type of person.

The following are my opinions, but they have served me well for decades and I'm not likely to give them up without a considerable fight. Attribute values that the user can see, they are tempted to change. Surrogate keys should exist for the convenience of the application/database only, and should NEVER be visible to the end user.

In this example, the user knows and uses the USERNAME, which is as it ought to be. In my world, the user will never see and should never even know that the USERID exists!

-PatP

okay, pat, but you didn't really answer george's question

what does cynicism and skepticism have to do with declaring an extra surrogate key when a perfectly good natural key exists?

DBForums uses a surrogate userID...
How come that is exposed?

I have no idea of best practice for this stuff - I only dev. internal ASP.NET stuff rather than secure, client facing sites but I do prefer to use sessions for this. Any references for why this might be considered bad practice?

As I observed: -PatP

okay, pat, i guess you don't understand what i'm asking, so i'll just let it slide, as i don't want you to get frustrated with me yet once again for persisting in asking a question that you aren't answering

I'm gonna hazard a guess based on Pat's answers in previous threads:
1) Users change stuff what they can
2) Based on 1), don't use natural keys

3) Based on one and two, use a surrogate
4) Don't expose your surrogate

1 & 2 are I think what Pat considers an answer to your question. 3 & 4 are a bonus.

Out of curiosity Pat - your problem is to do with referential integrity of natural keys? You presumably enforce uniqueness of your natural alternate key right?