Ferreteria/sql/user account: Difference between revisions
< Ferreteria | sql
Jump to navigation
Jump to search
(+EmailAddr field) |
m (11 revisions imported: moving this project here) |
||
| (5 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| | * '''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 9: | Line 10: | ||
* '''2013-09-25''' initial design | * '''2013-09-25''' initial design | ||
* '''2013-11-09''' renamed from '''core_users''' to '''user''' | * '''2013-11-09''' renamed from '''core_users''' to '''user''' | ||
* '''2013-12-26''' we need the email address too | * '''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 ` | <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 | `WhenCreated` DATETIME NOT NULL COMMENT "when user was created", | ||
UNIQUE KEY(`UserName`), | |||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) | ) | ||
ENGINE = | 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;