Having trouble getting this to work ... running MySQL 5.0.51a on Ubuntu
8.04. I'm trying to create a stored procedure using phpMyAdmin. It
compiles fine without these statements:

PREPARE stmt FROM
CONCAT(
'SET PASSWORD FOR `', vUsername, '`@`localhost` = PASSWORD(\'',
vNewPwd, '\');' );
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

What am I doing wrong? Do I have to declare "stmt" before using it as above?

Thanks!

Robert Hairgrove wrote:
Looks like you have to do it this way:

SET @a := CONCAT(
'SET PASSWORD FOR `', vUsername, '`@`localhost`=PASSWORD(\'',
vNewPwd, '\');' );
PREPARE stmt FROM @a;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Hmmm ... I suppose the procedural language doesn't handle temporary
string objects very well?

Robert Hairgrove wrote:
Still, there seems to be a problem using phpMyAdmin. I can call the
procedure from the MySQL Query Browser OK, but I cannot fetch the return
values in that environment. The new password is set correctly, however.

When I call the procedure from phpMyAdmin, I get the "PROCEDURE: xxx.yyy
can't return a result set in this context" error; this seems to me to be
a bug in phpMyAdmin, however.

The function definition looks like this:

CREATE PROCEDURE `do_change_pwd`(
IN vToken VARCHAR(40),
IN vNewPwd VARCHAR(40),
OUT vSuccess INTEGER,
OUT vMsg VARCHAR(255) )
MODIFIES SQL DATA
BEGIN
-- -- --
END//

If I replace everything between BEGIN and END with trivial code which
merely sets the values of the OUT parameters, everything works as
expected. I am calling the procedure like this:

CALL schema.do_change_pwd('token','new_pwd', @x, @y);
SELECT @x,@y;

(although 'token' and 'new_pwd' obviously have different values...)

Thanks for any suggestions!

Robert Hairgrove wrote:
I'm wondering why you even bother creating a stored proc just to change
a password.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle wrote:
It is for a web application written in PHP. There are several different
users, all with very restricted access rights (i.e. only SELECT and
EXECUTE on certain specific objects). The procedure is to enable someone
to enter a new password if they have forgotten their old one, i.e. they
can't log in to change their own password if they have forgotten it.
They receive a confirmation e-mail with a link to the page which does
the job. It's more secure that way than just e-mailing the users a new
password in plain text. And if a user wants to change the password, they
have to use the same procedure.

Robert Hairgrove wrote:
I still don't understand why you are creating a stored proc just to
change a password.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle wrote:
I suppose it could be done with PHP, but it seems just as simple to use
a SP. The entire SP has only about 20 lines or so ... I'd probably have
to write at least that many lines of code in PHP -- not counting the
explanations given here...

Can you (or anyone else) contribute something towards answering my
original question now, please?

Robert Hairgrove wrote:
I am. It's just not the answer you want.

This is one line of code in PHP. You're making a mountain out of a
molehill.

Additionally, if this is a web application, there is no need to give
each user a MySQL id. I use a single id for web access to MySQL, and a
separate table for users and their privileges.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Tue, 22 Jun 2010 14:06:21 +0200, Robert Hairgrove wrote:

Jerry's asking why you are maintaining your application's security in
mysql's security, instead of in the application and its data tables,
where it belongs. And by "belongs", I mean "where it can get maintained
and backed up with all the *rest* of your application's data, and where
it ends up not being potentially intermingled with *other application's*
data". For example, you have a user of one application with the id of
"jsmith". A different jsmith wants to use the other application. Why bar
him from his usual user ID?

--
62. I will design fortress hallways with no alcoves or protruding
structural supports which intruders could use for cover in a
firefight.
--Peter Anspach's list of things to do as an Evil Overlord

Jerry Stuckle wrote:
Well, I do appreciate what you are saying and taking the time to do so.
However, the setup is a little more complicated which means that (IMHO)
it is actually better to use different MySQL accounts for accessing this
data.

The application will be used to track student absentee data at my school
and send out an automatic notification to the home room teacher when a
student is absent from any class. Each teacher needs to be able to log
into the system, fill out the form and click to send the notification.
The data is stored in the process; students are allowed a quota of
absent marks each semester. There is one table of student data for all
the teachers, but each teacher only needs to see their own students. So
I have one view which filters the data according to the user which is
currently logged in. Each user gets SELECT privilege on the view, but
not on the table itself.

It seems to me to be easier to manage separate user accounts for this
than setting up a different view for each user. I don't want to store
any login data in the PHP files because I have no control over the
server's file system and don't know who might be able to read my PHP
files. Besides, there have been several attempts to hack our school's
server in the past (fortunately, none were very successful, but you
never know when they will be).

Of course, there will have to be one user login stored in the PHP page
for changing the password because the regular user won't have their
password (i.e. will have forgotten it). But that PHP user only gets an
EXECUTE privilege on the SP which I am trying to set up. I want to use a
stored procedure for this because otherwise, I'd have to grant that user
too many privileges. And there are other mechanisms installed which make
it next to impossible to abuse that procedure, even if someone were to
read the username and password from the PHP file and log in with those
credentials through a terminal or with some tool such as phpMyAdmin.

I hope this makes things a little clearer. Perhaps there is a better
solution which guarantees the level of security we need here?