Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    should primary key be exposed?

    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
    Last edited by ytubber; 12-02-07 at 02:01.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2007
    Posts
    3
    ok. noted. thanks.

  4. #4
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    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.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Quote Originally Posted by amthomas
    I agree with blindman, but would like to point out that there is nothing inherently insecure about showing "www.jone.com?userid=1"
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    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.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    If USERNAME is unique, then why have you got a USERID?
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    If USERNAME is unique, then why have you got a USERID?
    because the database developer drank the surrogate key koolaid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by georgev
    If USERNAME is unique, then why have you got a USERID?
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    DBForums uses a surrogate userID...
    How come that is exposed?
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by amthomas
    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.
    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?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by r937
    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?
    As I observed:
    Quote Originally Posted by Pat Phelan
    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.
    -PatP

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •