[thelist] Where to Store Username and Password in SQL Tables

[JSchell at doeanderson.com]

This seems to be an ongoing issue that everyone confronts.  When we looked
to address it we considered a larger approach and I've abbreviated it a bit
here for demonstration purposes.  In this architecture you can create as
many sites, users and permissions as you like within the database and have
your web site code refer to the db to determine the appropriate access.

tbl_sites
(
id  PRIMARY KEY
name
url
securekey #this is used as a random encrypted key that must be passed with
each access request so someone could not hack into a site from another on
the same db.
secureurl #provided from the requesting site, it must match with the
securekey to insure proper access.
active
)

The tbl_sites table lists all the potential sites, portals, applications,
etc that would access the server for authentication.  Add any additional
descriptive fields as needed.

tbl_users
(
id PRIMARY KEY
firstname
lastname
email
address
company
city
state
zip
username
password
active
)

The tbl_users table lists all possible users who could access a site on the
server.  Add any additional contact fields as needed.

tbl_sitelink
(
sid
uid
)

The tbl_sitelink tables links users to their appropriate sites which they
can access.

tbl_permission
(
id
sid
name
)

The tbl_permission table lists all possible permissions for each site
linked to the ID field in the tbl_sites table by the SID field in the
tbl_permission table.

tbl_permissionlink
(
uid
pid
)

The tbl_permissionlink table links users to their available permissions.



Jeremy Schell
Senior Internet Developer/Technical Lead, Interactive
Doe Anderson, Inc.
(502) 815-3339


|---------+----------------------------->
|         |           Boris Mann        |
|         |           <boris at bmannconsul|
|         |           ting.com>         |
|         |           Sent by:          |
|         |           thelist-admin at list|
|         |           s.evolt.org       |
|         |                             |
|         |                             |
|         |           01/27/2003 04:02  |
|         |           PM                |
|         |           Please respond to |
|         |           thelist           |
|         |                             |
|---------+----------------------------->
  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |                                                                                                                                                  |
  |       To:       thelist at lists.evolt.org                                                                                                          |
  |       cc:                                                                                                                                        |
  |       Subject:  Re: [thelist] Where to Store Username and Password in SQL Tables                                                                 |
  >--------------------------------------------------------------------------------------------------------------------------------------------------|




I would think that the main pitfall is if you want to add a third type
of user. Maybe each manufacturer wants to designate an "editor" contact
that  can access stuff in their row, but not delete it or change
critical info (like the main contact email). Nopw you're stuck having
to add another table, create more SQL queries to access it, etc.

I would think that a "users" table that has a "role_id" field, as well
as an associated "roles" table would be the way to go. And, even if you
initially have two types/roles of users, it's still only two tables.

Your logic could be a case statement on the value of the "role_id"
field -- you can add as many different roles as you want, each with
their own case statement for what their "permissions" are.

Even better, make a function or class method called
userHasPermission(user_id) which just returns true or false, and you
can write it any way you want on the inside.

--
Boris Mann
http://www.bmannconsulting.com

On Monday, January 27, 2003, at 03:45 PM, RUST Randal wrote:

> I have two types of users in my application: Admins and Widget
> Manufacturers.  Admins have access to everything, Manufactuers can only
> access their particular row in the Manufacturers table.
>
> It seems really easy to just store the Username and Password in each
> table accordingly.  Like this...
>

<snip>separate table for each user type</snip>

> But are there any pitfalls to this approach?  Should I create a Users
> table?
>
> ----------
> Randal Rust
> Covansys Corp.
> Columbus, OH

--
* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !