Re: Table schema for user login system?

On Sep 28, 5:39 pm, "Sanders Kaufman" <bu...@xxxxxxxxxxx> wrote:
"klenwell" <klenw...@xxxxxxxxx> wrote in message

news:1191012645.721457.124010@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


I went up and down that development path for years before I realized that,
as you mentioned, the login accounts table should be minimal... more minimal
than you described, certainly.

I try not to make it any bigger than userid and password - putting all that
other stuff into linked tables.
That way, later, if you cange the other stuff, you don't have to mess with
the actual login table.

Interesting approach. What parts of a user's account or identity
would you consider fixed or essential?

I always thought of a user's email as the binding factor as that's
usually how significant account changes get validated. And I assumed
that either the email address or the user name would have to remain
fixed to maintain a continuous record.

But I guess the primary integer key (what I term uid) would be the
account constant and any other variable could be changed as long as it
maintained an association with that.

By way of reference, if anyone's curious, here are the user tables for
drupal and wikimedia:

CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL default '0',
`name` varchar(60) NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`mail` varchar(64) default '',
`mode` tinyint(4) NOT NULL default '0',
`sort` tinyint(4) default '0',
`threshold` tinyint(4) default '0',
`theme` varchar(255) NOT NULL default '',
`signature` varchar(255) NOT NULL default '',
`created` int(11) NOT NULL default '0',
`access` int(11) NOT NULL default '0',
`login` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`timezone` varchar(8) default NULL,
`language` varchar(12) NOT NULL default '',
`picture` varchar(255) NOT NULL default '',
`init` varchar(64) default '',
`data` longtext,
PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`),
KEY `created` (`created`),
KEY `access` (`access`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
`user_id` int(10) unsigned NOT NULL auto_increment,
`user_name` varchar(255) character set latin1 collate latin1_bin NOT
NULL default '',
`user_real_name` varchar(255) character set latin1 collate
latin1_bin NOT NULL default '',
`user_password` tinyblob NOT NULL,
`user_newpassword` tinyblob NOT NULL,
`user_newpass_time` varbinary(14) default NULL,
`user_email` tinytext NOT NULL,
`user_options` blob NOT NULL,
`user_touched` varbinary(14) NOT NULL default '',
`user_token` varbinary(32) NOT NULL default '',
`user_email_authenticated` varbinary(14) default NULL,
`user_email_token` varbinary(32) default NULL,
`user_email_token_expires` varbinary(14) default NULL,
`user_registration` varbinary(14) default NULL,
`user_editcount` int(11) default NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`),
KEY `user_email_token` (`user_email_token`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Tom

.

Relevant Pages

  • Re: Table schema for user login system?
    ... the login accounts table should be minimal... ... usually how significant account changes get validated. ... `user_newpassword` tinyblob NOT NULL, ... PRIMARY KEY, ...
    (comp.lang.php)
  • Weakness introduced by denying remote logins on AIX, possibly others
    ... AIX 4.3.3 and AIX 5.1, ... is possible to remotely enumerate the passwords of a known AIX account. ... believed to be in the response from the login program after authentication ... Give accounts that have been restricted from remote logins strong passwords. ...
    (Security-Basics)
  • Re: Please! Doesnt anyone know a better way to do this?
    ... account, they need to automatically be directed to the page to enter data ... session variable on the Account page. ... I assume here that you're checking a database when the user attempts to ... When a new user attempts to login or clicks to register, ...
    (microsoft.public.dotnet.framework.aspnet)
  • WinXP laptop, simple-style login conn to Win2000 share, error
    ... So, to simplify matters, add all machines to the domain. ... local machine accounts) to keep track of... ... the local account information. ... the "pushbutton login") and configure the Laptops to auto ...
    (microsoft.public.windowsxp.security_admin)
  • Dexia website security alert
    ... A few days ago I sent a mail to the Dexia bank about their ... one is for the online banking account and one is for some ... The problem with the "members' login" was that a) it was ... selected the wrong login by mistake your username and password were ...
    (Security-Basics)