MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:November 4 2004 9:42pm
Subject:Re: Newbie question about web users
View as plain text  
It's not a simple question at all.

Your users actually DON'T talk to the database, your PHP application does. 
Your users converse with your PHP code. Your web server will have one 
layer of authentication just to allow the user to view the pages your site 
is composed of.  Assume for a second  that anyone can see any page on your 
site (no security), you can still set up application-level security by 
requiring your users to enter more information once they get to your site 
(like a login screen). Where you store your login information is up to you 
but since you already have a MySQL database at your disposal you can put 
it in there.

Since your application needs to be able to verify that a particular 
username/password combination is valid running a query against the 
database, it doesn't make it try to login with the user's credentials in 
order to verify those credentials. When I am designing web-based 
applications I usually need only 2 logins. One will be used by the 
application for read-only access. The second will be used only when the 
application needs to update something.  Notice I was talking about the 
application doing those things. Most users won't get direct access to your 
applicaiton data (usually) as it's only there to run the application. 

Now, within a MySQL database can be a set of tables (that you design) that 
contain everything your application needs to know about each user's 
account information, login permissions, and any other rights they have 
within the application (among other things).  This will be information 
used by your website to decide if a user has the correct privileges to 
perform some kind of action. Your application will use one of *its* logins 
to connect to that database in order to add, edit, or delete rows from 
those tables. The users don't interact with this data.

So, user security basically boils down to two layers. The first layer is 
determined by the security settings on the web server. The second is 
managed by your application code.  By keeping user security and 
application security separate, you improve the chances that your users 
will be unable to read your databases directly. Keep reading and 
researching. There are a lot of things to worry about when you start 
locking-down a web-based application.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Matthew Benton <shoppa67@stripped> wrote on 11/04/2004 03:53:57 PM:

> Hello all,
> Basic question about creating a database that will be accessed by 
> many users. Do I create one web user account with no password in the
> mysql.user table or add each user to the mysql.user table as they 
> join up to my site? (Lots of books and documentation says how to 
> create users, the privelege system etc, but none seem to address 
> this basic structural question - presumably because it's obvious to 
> anyone who already knows). I'm planning a mysql and PHP site.
> Taking a mundane example - lets say a second hand bookstore has a 
> site which lets users search through a list of books, and lets 
> subscribed users select favorite books, 0-n number of books, in a 
> table fav_books. Each fav_book tuple is then related to at least one
> user.  When the favourite book comes into stock the store sends a 
> message to the user to let them know it's available. (I said it was 
> a mundane example!).
> If the bookstore created a default user then I suppose anyone can 
> alter anyone elses data in the fav_book table (undesirable) ? Would 
> they create a table of authenticated_users for example instead of 
> putting them in the mysql.user table, then use PHP to retain a 
> record of which user is logged in at that time and so which tuples 
> in the fav_book table they can change? Alternatively if each user is
> listed in mysql.user table they would all get the same priveleges 
> and the bookstore would have to create a seperate fav_book table for
> each user to contain just their data. With as many tables as users 
> it would be surely impossible to run background processes which 
> check the new stock against books people are looking for?
> As I say it's a basic question about web use of a database - hope 
> the answer isn't too obvious.
> Thanks for any help
> matt
> ---------------------------------
>  ALL-NEW Yahoo! Messenger - all new features - even more fun! 
Newbie question about web usersMatthew Benton4 Nov
  • Re: Newbie question about web usersJohn McCaskey4 Nov
  • Re: Newbie question about web usersSGreen4 Nov