Re: user authentication

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, April 11, 2006 10:31 am, Alain Roger wrote:
> i have MySQL  with 2 users in the "mysql" database which are root and
> uimmense.
> i suppose that what i see in the password field is the sha1 crypted
> password.
>
> is it correct or not ?

It could be SHA1, or it could be something else...

It *IS* the output from the MySQL password() function which changed
its internals from version to version, and which you should avoid
using for that reason.

> but lets continue...
> in fact, my web application should use a database named "immense".
> in this database, there is a table named "profiles".
> in this table, the login "uimmense" and its MD5 password are stored.

Let's be clear, here.

Did you store:
password

Or did you store:
5f4dcc3b5aa765d61d8327deb882cf99
(I.E., md5('password')

You should probably NOT be storing the plain-text version in your
database, unless you are looking at a super-low security needs
application.

> the problem is :
> when i use mysql_connect('localhost','uimmense',myMD5password); to
> connect
> to MySQL database system, the connection is refused because the MD5
> password
> does not correspond to what is saved into users table within mysql
> database... :-(

If you are sending in:
5f4dcc3b5aa765d61d8327deb882cf99
as the password, that is not gonna work...

Here's what MySQL does (more or less):

$query = "SELECT pass FROM mysql.user WHERE user = '$user' AND host =
'$host'";
$valid_md5 = mysql_query($query) or die(mysql_error());
if (md5($pass) === $valid_md5){
  //valid user
}
else{
  //invalid user
}

The whole POINT of this process is that if somebody breaks into your
MySQL database somehow, and they've got:
5f4dcc3b5aa765d61d8327deb882cf99
they can't USE that because they don't know the original password.

So MySQL isn't looking for you to send: 5f4dcc3b5aa765d61d8327deb882cf99

You have to send the password whose md5() hash is
5f4dcc3b5aa765d61d8327deb882cf99
(Which happens to be 'password' in this case)

This same technique of storing ONLY a one-way encrypted value so that
the data itself is useless for logging in is used all over the place
in the computer industry.

> here is my question :
> all my users registered into my "immense" database, should be also
> registered as users of MySQL database system ? (which is stored into
> "users"
> table, into "mysql" database) ?

Probably not.

You've got TWO levels / meanings of "users" here:

MySQL 'user' which executes queries on behalf of your PHP scripts.

Your PHP application / website has 'users' which are allows to do
specific things within your PHP application -- based on whatever logic
/ rules YOU want to impose.

Now, sometimes, there will be a correlation between these two sets of
users.

For example, you might set up TWO MySQL users:
'admin'
'uimmense'

And your web appliation might have two different classes of users:
'visitor'
'admin'

And then, in the PHP code, in the '/admin/admin.php' scripts, you'd
use the 'admin' MySQL user, which has insert/update/delete
permissions.

But your homepage and other pages for normal visitors would use the
'visitor' user to connect, and they have only SELECT priveleges.

Of course, there are some wrinkles here:

Maybe the 'visitor' needs INSERT access to a 'guestbook' table so they
can add comments.

Or maybe the 'visitor' needs INSERT/UPDATE/DELETE on a 'session' table
so you can store PHP session data in MySQL.

Still, those would be exceptions to the general rule that 'visitor'
only does SELECT on most tables.

> is it clear ?
>
> i do not see really realistic if everytime that a new user is
> registered to
> my application, i have to create him a profile for MySQL database.

There might be specific applications where this would be true, like a
webhost Control Panel -- though that also probably would have an even
more complex MySQL-user/Site-user relationship.

But, no, in general, you should not be adding a new user into
mysql.user table for each registered user of a web application.

You DO need to sit down and work out what classes of user you need,
and what level of complexity you want to work with.

*MANY* "simple" sites just have one user to connect to MySQL with
SELECT/INSERT/UPDATE/DELETE privileges and that's it on the MySQL
side.

On the web application side, you'd have one or two classes of user:
'visitor' and 'admin' and only your PHP logic and authentication keeps
'admin' usage "safe" from all 'visitors'

This is, admittedly, not quite as good a defense in depth where you
have 'visitor' and 'admin' users in MySQL, but on most shared hosts,
you don't really have any other options.

So you test the PHP authentication very heavily, and make SURE the
passwords for 'admin' users are good passwords, and build in a bit of
extra protection defense in depth into the admin authentication logic.

Security is not binary.  It's a gradient.

You have to really think hard about it, all day, every day, if you
want anything more than token appearances of "security"

-- 
Like Music?
http://l-i-e.com/artists.htm

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux