Database design for a login system

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jistanidiot@gmail.com - 16 May 2005 15:31 GMT

Hello comp.databases

I hope this is the right place to post this question. If not please
help me find the right place.

I need to create a login system for our site. What I've found on the
subject with Google seems to be overly simplistic.

They all basically suggest there be one table with the following fields

id int autoincrement
username varchar
password varchar
userlevel int
email varchar

A few suggest other fields like a timestamp and modified_by.

For our current needs this probably all we need. However I foresee in
the next 2 years that we'll require all of our visitors to get userids
not just the admins and that we'll have to grant some users access to
just certain parts of our site. I'm not sure such a simple number
based userlevel will be enough. How do I handle adding additional
levels? What if I want a user to have level 2 access for form X and
level 10 access for form Y?

I was thinking of adding a user_group field to control what sections of
the site a user can access. userlevel would then apply just to that
section. How should I handle access to multiple groups?

Is there something considered to be standard good design for such a
system? I really don't want to have to redesign the login system in the
next 3-5 years, so I want to make sure I do it right.

If it matters, I'll be developing this on a standard LAMP (Linux Apache
MySQL PHP) system. However we might be moving over to Oracle in the
near future.

Reply to this Message

jerry gitomer - 16 May 2005 17:26 GMT

> Hello comp.databases
>
[quoted text clipped - 33 lines]
> MySQL PHP) system. However we might be moving over to Oracle in the
> near future.

Since you already have (or are planning on) a username column a
separate userid would be redundant.

You are correct in assuming that a simple userlevel won't be
sufficient for your needs. But, have no fear, the solution
isn't very complicated. What you will have is a classic
many-to-many situation. What is needed is a simple table where
each row has three columns; the username, the formid, and the
userlevel of that user for the form in question.

If you want you can substitute a groupid for the formid.

HTH
Jerry

Reply to this Message

Lemming - 25 May 2005 02:09 GMT

>I need to create a login system for our site. What I've found on the
>subject with Google seems to be overly simplistic.
[quoted text clipped - 6 lines]
>userlevel int
>email varchar

That might even be overkill. I wouldn't bother with an "id" field if
you have username. In fact, if makes username non-unique, which I
suspect you don't want.

>For our current needs this probably all we need. However I foresee in
>the next 2 years that we'll require all of our visitors to get userids
[quoted text clipped - 3 lines]
>levels? What if I want a user to have level 2 access for form X and
>level 10 access for form Y?

The simpest way is by role. More difficult is per-user permissions.
Yet more difficult is a combination of the two.

>I was thinking of adding a user_group field to control what sections of
>the site a user can access. userlevel would then apply just to that
>section. How should I handle access to multiple groups?

The simples way is to say what pages/forms/sections of the site are
accessible by which roles, and which roles are assigned to which
users. An intersect of the object roles and the user roles should
tell you whether a particular user is authorised to view a particular
object (page/form/section/etc).

>Is there something considered to be standard good design for such a
>system? I really don't want to have to redesign the login system in the
[quoted text clipped - 3 lines]
>MySQL PHP) system. However we might be moving over to Oracle in the
>near future.

It probably doesn't matter, user-authority systems are for the most
part the same.

You might want to google "SQL Injection" for some thoughts about what
might go wrong.

Lemming

Signature

Curiosity *may* have killed Schrodinger's cat.

Reply to this Message

Damien - 25 May 2005 07:33 GMT

> >I need to create a login system for our site. What I've found on the
> >subject with Google seems to be overly simplistic.
[quoted text clipped - 10 lines]
> you have username. In fact, if makes username non-unique, which I
> suspect you don't want.

It might also be worth storing passwordSalt and passwordHash instead of
storing password. Advantages:

1) your front end system can accept passwords of any length,
2) even if someone gains access to this table, they cannot read other
peoples passwords

Disadvantages (although I never even give it a second thought, but in
the interests of being balanced):

1) More effort required in your login/change password screens,
2) You cannot retrieve someones password if they forget it.

Damien

Reply to this Message

--CELKO-- - 25 May 2005 16:30 GMT

>> I was thinking of adding a user_group field to control what sections of the site a user can access. userlevel would then apply just to that section. How should I handle access to multiple groups? <<

I set up a hierarchical access control system a few years ago based on
the nested set model. Each user was a leaf node and had access to the
things that were over him in the tree structure, so at the root you had
things like "company email" and as you went down, you were allowed
access to department/team/project level tools and data.

It took two days to design and test a 50,000 node prototype. Ran
great.

Reply to this Message