Re: Table design for basic user management

On 2009-11-08, Michael Wood <esiotrot(at)gmail(dot)com> wrote:
> 2009/11/7 Jasen Betts <jasen(at)xnet(dot)co(dot)nz>:
>> On 2009-11-06, Thomas Løcke <thomas(dot)granvej6(at)gmail(dot)com> wrote:
> [...]
>>> I've come up with an initial design for this database:
>>> http://pastebin.com/f5255453e
> [...]
>> CREATE TABLE log (
>>    userid integer PRIMARY KEY REFERENCES REFERENCES user(id) ON DELETE CASCADE,
>>    registered timestamp  DEFAULT now() NOT NULL,
>>    lastvisit  timestamp  NOT NULL,
>>    visits integer DEFAULT 0 NOT NULL
>> );
>>
>> then you can log-in with a single query,
>>
>> update user set lastvisit=default where username='fred' and
>> password=md5('salt'||'password') returning id;
> [...]
>
> I agree with using a salt, but you appear to be advocating a fixed
> salt for everyone?

it's better than nothing, but you're right a variable salt is even
better.

with no salt at all you can sometimes google the MD5 to "decrypt" it!

> Normally the salt is stored along with the
> password hash, so you'd need one query to retrieve the salt and
> another query to calculate the hash and compare it to the stored hash.