Ferreteria/sql/user account: Difference between revisions

From Woozle Writes Code
Jump to navigation Jump to search
(making UserName a unique key)
m (11 revisions imported: moving this project here)
 
(4 intermediate revisions by one other user not shown)
Line 1: Line 1:
==About==
==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.
* '''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.
* '''Used by''': {{l/same|user_x_ugroup}}
* '''Module''': {{l/ferreteria/module|users}}
* '''Used by''': {{l/same|uacct_x_ugroup}}
===Rules===
===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.
* 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.
Line 10: Line 11:
* '''2013-11-09''' renamed from '''core_users''' to '''user'''
* '''2013-11-09''' renamed from '''core_users''' to '''user'''
* '''2013-12-26''' (1) we need the email address too; (2) making UserName a unique key
* '''2013-12-26''' (1) we need the email address too; (2) making UserName a unique key
* '''2017-01-26''' moved to Ferreteria, renamed "user_account", switched from MYISAM to InnoDB; made "WhenCreated" NOT NULL
===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 16: Line 18:
* '''EmailAddr''': email address, for resetting password on existing account; see RFC 3696 for length  
* '''EmailAddr''': email address, for resetting password on existing account; see RFC 3696 for length  
==SQL==
==SQL==
<mysql>CREATE TABLE `user` (
<syntaxhighlight lang=mysql>CREATE TABLE `user_account` (
   `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",
Line 23: Line 25:
   `PassSalt`    VARBINARY(128)  NOT NULL COMMENT "random prefix for hashing password",
   `PassSalt`    VARBINARY(128)  NOT NULL COMMENT "random prefix for hashing password",
   `EmailAddr`    VARCHAR(255)    NOT NULL COMMENT "email address",
   `EmailAddr`    VARCHAR(255)    NOT NULL COMMENT "email address",
   `WhenCreated`  DATETIME                 COMMENT "when user was created",
   `WhenCreated`  DATETIME         NOT NULL COMMENT "when user was created",
   UNIQUE KEY(`UserName`),
   UNIQUE KEY(`UserName`),
   PRIMARY KEY(`ID`)
   PRIMARY KEY(`ID`)
)
)
ENGINE = MYISAM;</mysql>
ENGINE = InnoDB;</syntaxhighlight>

Latest revision as of 16:42, 22 May 2022

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.
  • Module: Template:L/ferreteria/module
  • Used by: uacct_x_ugroup

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
  • 2013-12-26 (1) we need the email address too; (2) making UserName a unique key
  • 2017-01-26 moved to Ferreteria, renamed "user_account", switched from MYISAM to InnoDB; made "WhenCreated" NOT NULL

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, which makes sense.
    • "Whirlpool" seems to be among the best of the hash algorithms available in PHP. Blowfish or bcrypt would be better, but availability is iffy.
  • EmailAddr: email address, for resetting password on existing account; see RFC 3696 for length

SQL

CREATE TABLE `user_account` (
  `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",
  `EmailAddr`    VARCHAR(255)     NOT NULL COMMENT "email address",
  `WhenCreated`  DATETIME         NOT NULL COMMENT "when user was created",
  UNIQUE KEY(`UserName`),
  PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;
Retrieved from "https://wooz.dev/mw/index.php?title=Ferreteria/sql/user_account&oldid=1762"