Advice on Php/Mysql application - User Validation

[Ben Binskin]

Im rather new to developing mysql/php applications and am after some advice
on handling user validation for a web based system, ive implimented a number
of ways and would like to know which way is better in regards to security
etc, here are the following ways i have implimented this in the past:
a) user submits via form login/pass, mssql db is accessed via a no login
no pass account, and a basic "select from subscribers where user=$blah and
pass=$boo" is implimented, if theirs a result match the user can proceed, if
not they are booted back to the login with an error message.
b) each user of the system has an account in the mysql/user table, set
up via grant statements via a system admin, so then you can do a direct
login to the database with the users submitted info, if the connection is
granted then the rest of the subscriber info is pulled out of a second table
in the applications own database, linking the two tables on unique login
names. if allowed to make a database connection, the user can access the
rest of the site, if access is denied, they are booted back to the login
screen.

for both of these methods i store the submitted user/pass info in session
variables, and this info is verified on every page by an include file, is
this a good idea? or would it be wiser to use a variable that can only be
set when a successful login in is made, then to check if that variable
exists instead? (doing this so that no one can simply go
http://www.etc -etc/mypage.php)

My questions on these methods are
for a) is it a good idea to set up a database that doesnt require a user
to actually log into it, ie to check to make sure the users info is correct,
an account must be setup for anyone to access to check if the login info is
correct, because if they cant access the database how can they have their
login/pass validated? I have a feeling that employing that method simply
isnt very secure.
b) is having a large number of accounts in the user table of the
mysql db a safe way to go? this way i can enforce security through mysql
itself ... if the user doesnt have 'granted' access to the database, they
get no access, mysql is performing this validation for itself.

Am i on the right track with iether method? or is their a better way that i
am totally missing, any help would be greatly appreciated.

Cheers -Ben.

[Jean-Baptiste Nizet]

Re: Advice on Php/Mysql application - User Validation

Ben Binskin wrote:

The classical method, particularly with ISPs hosting a large number of
user web sites, is to have a database with only one user/password. All
the requests to the DB are performed using this user/password. The web
site users are stored in a DB table, with their password. When a login
succeeds, a "loggedIn" variable is set to TRUE in the session. When the
user logs out, the session is destroyed, or the "loggedIn" variable is
removed (depending on your use of the session).
I'm using this technique, and it works like a charm.
Be careful about your request for checking login/passwords, though:
mysql is case-insensitive.
JB.

[Ben Binskin]

Re: Advice on Php/Mysql application - User Validation

Thanks for your help, my only concern now is setting the user/pass to the db
as being static on the site, i was led to believe that was a bad way to
permit access.
I assume that if you were to set up this default access that you would only
grant it select permissions? but this account would also require add
permissions for my application, Or would you grant this user all
permissions, instead opt for the site to only allow certain users to access
the areas of the site that require additional permissons, delete, update etc
.... for my app theirs basically two user types, a data entry person (many)
and a system admin who needs all permissions, im just very concerned about
security of the db, statically setting up access worries me to an extent ...
but if it works it works i guess.

Thanks again - Ben.

[Jean-Baptiste Nizet]

Re: Advice on Php/Mysql application - User Validation

Ben Binskin wrote:
[color=blue]
> Thanks for your help, my only concern now is setting the user/pass to the db
> as being static on the site, i was led to believe that was a bad way to
> permit access.
> I assume that if you were to set up this default access that you would only
> grant it select permissions? but this account would also require add
> permissions for my application, Or would you grant this user all
> permissions, instead opt for the site to only allow certain users to access
> the areas of the site that require additional permissons, delete, update etc
> ... for my app theirs basically two user types, a data entry person (many)
> and a system admin who needs all permissions, im just very concerned about
> security of the db, statically setting up access worries me to an extent ...
> but if it works it works i guess.
>
> Thanks again - Ben.
>
>[/color]

I'm not sure I fully understand your question.
In addition to the basic user/password authentication, I also use
authorization groups. Each user may be part of 0-N groups. Each group
has a defined set of permissions.
In the database this is modeled with a "group" table, and an "access"
table. The group table contains an group_id and a description, and the
access table contains a user_id and a group_id.

JB.