user password self management -- how?

[Tom Emerson]

This is actually a bit of a general-database question, since I'm sure it has
the same or similar repercussions regardless of which RDBMS you use, but I'm
curious: is it possible to safely allow users to maintain their own
passwords to a database?  [and if so, how?]

I'm sure I can do this programmatically by having my program connect to
the database using the root user/password, but that means embedding the
password in the program [i.e., viewable with string...] so once a user
deciphers that, they are free to run mysql -u root from anywhere the
application could run.

maybe I'm thinking in a deranged way, but it seems that granting update
access WITHOUT granting select access to the user table in mysql [and
perhaps even limited to the password column] would enable a user to change
their password without being able to read anyone else's (encrypted) password
value; HOWEVER that would also allow them to CHANGE anyone ELSE'S password
[even root] -- is it possible to limit access at the ROW level?  (i.e.,
user x can only select/update records with user='x')

The reason I'm doing this is that I'm designing an application that logs on
with a fixed username and uses it's own user/password table to validate
end-users.  The program notes if a password in the database is blank -- if
so, it compares the user-supplied password with a (eventually well-known)
first-time password and forces the user to set a new password, so
obviously the fixed user ID needs the ability to udpate the application's
password table, which has the same problems as the system level table, but
limits the damage that can be caused to just the application and not all
of mysql...  (i.e., crackers could determine the fixed user's password by
scanning the executable, but that won't let them harm anything other than
the application itself...)

Ultimately, I'd like to use mysql's user/password (grant) facillities
directly, but since the application would be distributed to offices
nationwide [all phoning home to the central database via the internet], I
don't want to burden the one system manager/DBA with the responsibility of
maintaining every single clerk and technician that can use the system -- I'm
willing to let the office manager at each location enter and maintain
employees local to that office [which makes sense because that person also
does the hiring/firing...] but for obvious reasons, the managers cannot
manipulate values for other offices [though, I suppose, I have to trust SOME
of them... ;) ]

If I could guarantee that the only access to the database was via my
program, then I'm certain I could create logic to maintain passwords safely;
however this is not the case -- not only is mysql available as a simple
client, but anyone with a smattering of knowledge of Access and the myodbc
connector could create their own manipulation program and circumvent any
safety protocols I establish.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: user password self management -- how?

[Benjamin Pflugmann]

Hello.

On Thu 2002-08-29 at 11:38:17 -0700, [EMAIL PROTECTED] wrote:
[...]
 I'm sure I can do this programmatically by having my program connect to
 the database using the root user/password, but that means embedding the
 password in the program [i.e., viewable with string...] so once a user
 deciphers that, they are free to run mysql -u root from anywhere the
 application could run.

IIRC, mysqladmin allows you to change your password yourself, so this
should be possible for any application. I guess it uses SET:

  SET PASSWORD FOR jeffrey@% = PASSWORD('biscuit');

The manual says about this Set the password for the current user. Any
non-anonymous user can change his own password!
(http://www.mysql.com/doc/en/SET_OPTION.html)

 maybe I'm thinking in a deranged way, but it seems that granting update
 access WITHOUT granting select access to the user table in mysql [and
 perhaps even limited to the password column] would enable a user to change
 their password without being able to read anyone else's (encrypted) password
 value;

You need SELECT privilege to use restrict your query via WHERE clause
(because the WHERE has to do an equivalent of an SELECT implicitly).

Even if you could have it that way (allowing WHERE on UPDATE without
select privelege), this would only provide a small threshold. This
came up some time ago (oops, July 1999 ;), and my answer was this:

  http://marc.theaimsgroup.com/?l=mysqlm=93121950516543w=2

The summary is that you could figure out the password with less than
100 queries, so, it would be quite useless.

 HOWEVER that would also allow them to CHANGE anyone ELSE'S password
 [even root] -- is it possible to limit access at the ROW level?  (i.e.,
 user x can only select/update records with user='x')
 
 The reason I'm doing this is that I'm designing an application that logs on
 with a fixed username and uses it's own user/password table to validate
 end-users.  The program notes if a password in the database is blank -- if
 so, it compares the user-supplied password with a (eventually well-known)
 first-time password and forces the user to set a new password, so
 obviously the fixed user ID needs the ability to udpate the application's
 password table, which has the same problems as the system level table, but
 limits the damage that can be caused to just the application and not all
 of mysql...

Oh. Okay, in this case SET PASSWORD is not appliable to your case. :-(
Thought you were talking about pure database users.

[...]
 Ultimately, I'd like to use mysql's user/password (grant) facillities
 directly, but since the application would be distributed to offices
 nationwide [all phoning home to the central database via the internet], I
 don't want to burden the one system manager/DBA with the responsibility of
 maintaining every single clerk and technician that can use the system -- I'm
[...]

Hm. If you change to GRANT, and create a database user for each real
user, I think SET PASSWORD could do what you want.

However, maybe I completely misunderstand your requirements.

Regards,

Benjamin.


-- 
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php