Ferreteria/sql/user account: Difference between revisions

From Woozle Writes Code
Jump to navigation Jump to search
(rules)
(renaming)
Line 7: Line 7:
* '''History''':
* '''History''':
** '''2013-09-25''' initial design
** '''2013-09-25''' initial design
** '''2013-11-09''' renamed from '''core_users''' to '''user'''
* '''Fields''':
* '''Fields''':
** '''PassSalt''': random data prepended to the password before hashing in order to prevent [[wikipedia:rainbow table|precomputed lookup]] attacks if any hashed passwords are revealed (e.g. if the database is cracked)
** '''PassSalt''': random data prepended to the password before hashing in order to prevent [[wikipedia:rainbow table|precomputed lookup]] attacks if any hashed passwords are revealed (e.g. if the database is cracked)
Line 12: Line 13:
*** "Whirlpool" seems to be among the best of the hash algorithms available in PHP. Blowfish or bcrypt would be better, but availability is iffy.
*** "Whirlpool" seems to be among the best of the hash algorithms available in PHP. Blowfish or bcrypt would be better, but availability is iffy.
==SQL==
==SQL==
<mysql>CREATE TABLE `core_users` (
<mysql>CREATE TABLE `user` (
   `ID`          INT              NOT NULL AUTO_INCREMENT,
   `ID`          INT              NOT NULL AUTO_INCREMENT,
   `UserName`    VARCHAR(31)      NOT NULL COMMENT "log in ID",
   `UserName`    VARCHAR(31)      NOT NULL COMMENT "log in ID",

Revision as of 17:50, 9 November 2013

About

  • Purpose: tracks site users as a separate concept from customers. A user may or may not be a customer; a customer only becomes a user if they select a login ID. Later, users might also be admins or vendors.
  • Rules:
    • A user may have zero or more customers; a customer may have one user or none. This was initially done as a quick way to implement users within the existing structure; we'll have to decide later if it makes sense -- but it does seem to offer some advantages to the users.
    • When checking out, a logged-in user has the option of choosing to use shipping and payment data from their existing customer records or entering new data for either (which will then be added as a new customer record).
    • If the user is not logged in, new customer records (with no user ID) will be added regardless.
  • History:
    • 2013-09-25 initial design
    • 2013-11-09 renamed from core_users to user
  • Fields:
    • PassSalt: random data prepended to the password before hashing in order to prevent precomputed lookup attacks if any hashed passwords are revealed (e.g. if the database is cracked)
      • One source advised that the salt should have at least as many bits as the hash result.
      • "Whirlpool" seems to be among the best of the hash algorithms available in PHP. Blowfish or bcrypt would be better, but availability is iffy.

SQL

<mysql>CREATE TABLE `user` (

 `ID`           INT              NOT NULL AUTO_INCREMENT,
 `UserName`     VARCHAR(31)      NOT NULL COMMENT "log in ID",
 `FullName`     VARCHAR(127) DEFAULT NULL COMMENT "optional 'real' name",
 `PassHash`     VARBINARY(128) NOT NULL   COMMENT "hash for [password+salt]",
 `PassSalt`     VARBINARY(128) NOT NULL   COMMENT "random prefix for hashing password",
 `WhenCreated` DATETIME                   COMMENT "when user was created",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>