locked
UserID vs Username - Security vs Speed? RRS feed

  • Question

  • Hi,
      This may be a moot point, but is it better to be dealing with UserID (guid) than Usernames (varchar)? I have the membership user table, which has the UserID and Username. Throughout my project, is it better if I have UserID's in my table, and call the Username via that or is that unneccessary calls to the database and inefficient? I'm not sure if hackers can do much with just the username (I'm securing the app as much as I know how against XSS, SQL Injections, Session hijacking and running in medium trust).
    Thanks for any advice
    Friday, September 25, 2009 12:51 AM

Answers

  • It's just data to the app.  Either one is going to work.  From an efficiency standpoint, you want to construct the application such that the only calls to the database are those that are absolutely necessary.

    I design by what I'd call "common sense".  The first step of which is to get out of the technobabble, technology gobbledygook.  Put the design flow in terms of real world activities that a 2 year old would be able to tell you which way is better.  For example - application X pulls a list of keys back and then loops across the result set executing 1 query for each of the keys returned.  Translation into real world - I create a shopping list and go to the store, I walk in the store, go to the shelf and pull down the first item in the list, I got to the checkout counter, pay for that item, walk out to the car, put the item in the car, then walk back into the store to look for item #2 on the list.  Repeat for each item in the list.  Now, does that leave any doubt whatsoever that the looping across a key set is an extremely bad application design when you could have just pulled the entire result set in one shot?  Example #2; My application runs a query and pulls back 17 million rows of data with 123 columns in each row.  At most any given user will only utilize 20 columns of data across a maximum of 250 rows.  Translation to real world - My shopping list has 3 items on it.  I walk into the store, put those 3 items in my shopping cart, then proceed to wander around the store adding products until my shopping cart can't hold anything else.  I pay for all of my items, get home, put the 3 items I actually needed into the cupboard, and then throw away everything else that I just bought.  Any doubt as to whether this particular application design makes any sense?

    Now, just apply the same principle to everything you design and you'll probably be quite surprised at the efficiency of the resulting application.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Friday, September 25, 2009 6:55 AM
  • Hi,

    I think the important is that how we validate our user's membership and which authentication we use. The users usually login the application using username(a unique label, for example: email) and password, if you are using a GUID column as a userID, and one username maps a userID, both of the them are unique, it can be benifit if you are allowing user change his/her username/email.

    Hope this helps, thanks!
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 29, 2009 8:07 AM

All replies

  • It's just data to the app.  Either one is going to work.  From an efficiency standpoint, you want to construct the application such that the only calls to the database are those that are absolutely necessary.

    I design by what I'd call "common sense".  The first step of which is to get out of the technobabble, technology gobbledygook.  Put the design flow in terms of real world activities that a 2 year old would be able to tell you which way is better.  For example - application X pulls a list of keys back and then loops across the result set executing 1 query for each of the keys returned.  Translation into real world - I create a shopping list and go to the store, I walk in the store, go to the shelf and pull down the first item in the list, I got to the checkout counter, pay for that item, walk out to the car, put the item in the car, then walk back into the store to look for item #2 on the list.  Repeat for each item in the list.  Now, does that leave any doubt whatsoever that the looping across a key set is an extremely bad application design when you could have just pulled the entire result set in one shot?  Example #2; My application runs a query and pulls back 17 million rows of data with 123 columns in each row.  At most any given user will only utilize 20 columns of data across a maximum of 250 rows.  Translation to real world - My shopping list has 3 items on it.  I walk into the store, put those 3 items in my shopping cart, then proceed to wander around the store adding products until my shopping cart can't hold anything else.  I pay for all of my items, get home, put the 3 items I actually needed into the cupboard, and then throw away everything else that I just bought.  Any doubt as to whether this particular application design makes any sense?

    Now, just apply the same principle to everything you design and you'll probably be quite surprised at the efficiency of the resulting application.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Friday, September 25, 2009 6:55 AM
  • Hi,

     Thanks for the reply. I've gone over the system design to ensure that the database calls are limited to only when they're required. However, does it take any more resources if I retrieve the username via the userID (the userID is in a table where the username is not, so I need to search the 'Users' table to find the username belonging to the userID). Or, should I just replace the userID with the username in the table so no lookup is required? To me, the latter seems more efficient, but I'm not sure if SQL does any optimisation that makes this process negligible, since it's a very common call. The only reason I am hesitant of using username directly in the table, was that any would-be hacker may compromise the table and have username information directly instead of random GUIDs. Is this a genuine threat, or is it so unlikely to happen, the efficiency concern outweighs the security risk?

    Thanks again for the help.
    Friday, September 25, 2009 11:01 AM
  • Hi,

    I think the important is that how we validate our user's membership and which authentication we use. The users usually login the application using username(a unique label, for example: email) and password, if you are using a GUID column as a userID, and one username maps a userID, both of the them are unique, it can be benifit if you are allowing user change his/her username/email.

    Hope this helps, thanks!
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 29, 2009 8:07 AM