Grant Execute Rights on a Stored Procedures

[cmalvido]

Hi, I’m trying to grant execute permission to all stored procedures in my database using phpMyAdmin, but I’m getting an error:

#1044 - Access denied for user 'cmalvido'@'int.starka.x10hosting.com' to database 'cmalvido_dtr'

SQL Query I’m using: GRANT EXECUTE ON PROCEDURE cmalvido_dtr.* TO cmalvido_dtr

 

[calistoy]

Here's a relevant previous support thread about this. http://x10hosting.com/forums/free-hosting/124658-php-storedprocedure-mysql.html

 

[dlukin]

None of those threads give a clear answer, possibly because nobody knows the answer.

Ok, here goes.

cPanel + PHPMyAdmin messes up a lot of things.
When you are in PHPMyAdmin, you are as user 'cmalvido' .
Your scripts will run as user cmalvido_yadda or similar. But cPanel will not let you give Execute privileges to him.
And cmalvido apparently cannot GRANT them in PHPMyAdmin.

So, from what I have gathered ....

Create a PHP (Python/Perl) script that has user cmalvido_yadda sign on to database cmalvido_database and create the Strored Procedures you want.

Make sure cmalvido_yadda has CREATE_ROUTINE permissions on the database.

Run the script from the web.

Remove CREATE_ROUTINE permissions and/or disable the script.

MySQL automatically grants EXECUTE privileges to the creator of the Stored Procedure.

No guarantees. Has not been tested. Try out on a dummy DB first if you are worried.

__________________________

If you try this, please post back with the results. Good or Bad.

 

[cmalvido]

@dlukin It worked thanks. Below is the example:

<?php
$link = mysql_pconnect("localhost", "cmalvido_username", "password") or die("Could not connect");
mysql_select_db("cmalvido_dtr") or die("Could not select database");

$q = <<<EOF

CREATE PROCEDURE test()
BEGIN
// code ...
END;
EOF;

$r = mysql_query($q);
if (!$r) die('Invalid query: '.mysql_error());
else echo 'Valid query: '.$r;

$q2 = 'CALL test()';
$r2 = mysql_query($q2);
if (!$r2) die('Invalid query: '.mysql_error());
else echo 'Valid query: '.$r;
?>

 

[calistoy]

None of those threads give a clear answer, possibly because nobody knows the answer.

Actually, they did know the answer. A click through the links in the thread I linked to here reveals this one http://markcordell.blogspot.com/2008/11/mysql-stored-procedure-permissions-and.html

Thank you for posting the info from that site here in a clearer way to help. I'm glad it worked.