Access denied executing routines

[clusher]

Hi all.

I've been making a site for my gymnastics group. I've got some background on both programming, mysql and PHP, mainly from university classes. However, all the projects where done in localhost, and now that I'm getting in the "real world" I'm having a few problems...

The two main problems right now are:
1. The "public" user isn't accepted... and it seems I lack the privileges to GRANT anything to public user. Any ideas why?

2. I've been developing the site on localhost and all was ready to go. When I uploaded the files and started testing the routines in a reserved area for members, it started giving the following error:
Error 1370 : execute command denied to user 'airfapt_clusher'@'localhost' for routine 'airfapt_airfa.inserir_noticia'.

I've read a few thing about a "definer" field, but couldn't understand how to use it... The code for the routine is:

create procedure airfapt_airfa.inserir_noticia(in modalidade char(20), in corpo text, in data_n date)
begin
  start transaction;
    insert into airfapt_airfa.noticias(data_n, modalidade, corpo) values(data_n, modalidade, corpo);
  commit;
end &&

I hope you guys can help me out...
Thanks in advance!
Carlos

[dickersonka]

The problem is your "public" user most likely does not have permissions to call the insert stored procedures.

A lot of times, you may have a separate user for searching or selecting data, than inserting data.

[clusher]

The problem is your "public" user most likely does not have permissions to call the insert stored procedures.

But these are two separate problems. When I call the procedure in the example I do it with a registered and full privileged user (airfapt_clusher), that's why I'm not getting the problem.

The "public" user problem I solved it using registered users with different privileges, just like you said.

[dickersonka]

So what problem are you getting now?

[clusher]

Like I said, when I try to execute a routine (a procedure or function) the error message: Error 1370 : execute command denied to user 'airfapt_clusher'@'localhost' for routine 'airfapt_airfa.inserir_noticia'

appears. This happens with the user "airfapt_clusher", which has all privileges in the database.
I searched, I googled, I read forums, but the closest thing to what is happening to me seems to have something to do with a DEFINER field that is used when creating a routine, but I'm not really sure how it works, I didn't find much info about it.

I need to get this working. A stable solution would be great, but anything that does the trick will do for now!

[dickersonka]

i don't think that user has permissions on it still

grant all on airfapt_airfa.* to
     "airfapt_clusher"@"localhost" identified
    by "password";

substitute password with your password

[clusher]

I'm hosting the site on a shared and payed server, and the administrators use CPanel.
I created both the database and the users on CPanel, and I gave GRANT ALL on both 'airfapt_clusher' and 'airfapt_publico'.

Just to make sure I went to phpMyAdmin and tried to run a few statements as 'airfapt' user (the login the administrators gave me). I first called SHOW GRANTS and it shows:
GRANT USAGE ON *.* TO 'airfapt'@'localhost' IDENTIFIED BY PASSWORD '*A92A921742981823341D74BCB29FCF66684D75E5'
GRANT ALL PRIVILEGES ON `airfapt\_airfa`.* TO 'airfapt'@'localhost'

So next I called SHOW GRANTS FOR 'airfapt_clusher'@'localhost' and I got:
#1044 - Access denied for user 'airfapt'@'localhost' to database 'mysql'

Just to experiment I ran the GRANT ALL using phpMyAdmin and it gave the same error:
#1044 - Access denied for user 'airfapt'@'localhost' to database 'airfapt_airfa'

I'm totally lost by now. In one hand it says I have all privileges, on the other it says I don't...

[dickersonka]

on the second grant, use identified by password

and also at the end of it, add with grant option

after done, use

flush privileges;

[webhostuk]

Hello,

I had exactly same problem to work on for a client ..I managed to fix it.
Cpanel does not allows user to give execute permission.. the issue can only be fixed by your webhost company having root access.

Ask them to go to root phpmyadmin>>your database>> privilages>> under that they will find table with local host ,your database user and permission allowed details ..ask them to edit it and allow execute permission to the user .. all is fixed for me now :)

Hope it helps you.

Regards
Max
<snip>