Error 1370 execute command denied to user

I’m working on right now so the error may not display or may show a different error.

I noticed in the ‘information_schema’ database in the column ‘Definer’ I have this ‘discoup9_vkimura@174.6.38 .231’ and this is the what is required for me to create the stored procedure:
CREATE DEFINER = ‘discoup9_vkimura’@’174.6. 38.231’

But when I connect via the php script I have the $hostname variable set to ‘localhost’; hence, the error. When I tried to change the $hostname to ‘174.6.38.231’ I still receive an error as:

Fatal error: Uncaught exception ‘Exception’ with message ‘Failed to connect: SQLSTATE[HY000] [2003] Can’t connect to MySQL server on ‘174.6.38.231’ (4)’ in /home/discoup9/public_html /omusicstu dios/admin /calendar/ classes/db Writer.php :25 Stack trace: #0 /home/discoup9/public_html /omusicstu dios/admin /calendar/ loop_hours _classroom _v8.php(47 ): DBWriter::__construct() #1 /home/discoup9/public_html /omusicstu dios/admin /calendar/ time_class _room_v8.p hp(111): include(‘/home/discoup9/.. .’) #2

thrown in /home/discoup9/public_html /omusicstu dios/admin /calendar/ classes/db Writer.php on line 25

Does anyone know how to resolve this? I have the script and stored procedure working fine on my vista local machine but not on my hosting provider (bluehost).

Any help is appreciated.

Can you pls post these

show create procedure discoup9_omusicstudios.sel ectCustDbI mport;

show grants for discoup9_vkimura;
show grants for discoup9_vkimura@localhost ;
show grants for discoup9_vkimura@’%’;

========================== ========== ========== ========== ========== ========== ========== ========== ========== ========== ========== ========== ========== ========
| Procedure | sql_mode | Create Procedure |
========================== ========== ========== ========== ========== ========== ========== ========== ========== ========== ========== ========== ========== ========
| selectCustDbImport | |CREATE DEFINER=`discoup9_vkimura` @`174.6.38 .231` |
| | |PROCEDURE `selectCustDbImport`(IN in_timestampCur |
| | | INT, IN in_classRoom_id INT) |
| | | SQL SECURITY INVOKER |
| | | BEGIN |
| | | DECLARE l_timestampCurHour_id INT DEFAULT |
| | | 1233244800; /* testing timestamp */ |
| | | DECLARE l_classRoom_id INT DEFAULT 1; /* |
| | | testing timestamp */ |
| | | |
| | | SELECT |
| | | custDbImport.splitYes, custDbImport.ID, |
| | | custDbImport.FIRSTNAME, custDbImport.LASTNAME, |
| | | teachers.firstName, teachers.lastName, |
| | | `classTime`.`timeStamp`, classSize.classSize, |
| | | classType.classType |
| | | FROM |
| | | dc_dt_s_t_Many |
| | | INNER JOIN custDbImport ON custDbImport.id = |
| | | dc_dt_s_t_Many.student_id |
| | | INNER JOIN teachers ON teachers.id = |
| | | dc_dt_s_t_Many.teacher_id |
| | | INNER JOIN classSize ON classSize.id = |
| | | dc_dt_s_t_Many.classSize_i d |
| | | INNER JOIN classType ON classType.id = |
| | | dc_dt_s_t_Many.classType_i d |
| | | INNER JOIN classTime ON |
| | | classTime.classTime_id = |
| | | dc_dt_s_t_Many.classTime_i d |
| | | INNER JOIN classRoom ON classRoom.id = |
| | | dc_dt_s_t_Many.classRoom_i d |
| | | WHERE |
| | | /*classTime.`timeStamp` = |
| | | l_timestampCurHour_id AND classRoom.`id` = |
| | | l_classRoom_id;*/ |
| | | classTime.`timeStamp` = in_timestampCur AND |
| | | classRoom.`id` = in_classRoom_id; |
| | | |
| | | END

The above is the first query.

Here is the third show:

Источник

Uncommon Sense MySQL – When EXPLAIN Can Trash Your Database

If I ask you if running EXPLAIN on the query can change your database, you will probably tell me NO; it is common sense. EXPLAIN should show us how the query is executed, not execute the query, hence it can’t change any data.

Unfortunately, this is the case where common sense does not apply to MySQL (at the time of this writing MySQL 8.0.21 and previous versions) – there are edge cases where EXPLAIN can actually change your database as this Bug illustrates:

The problem is EXPLAIN executes the cleanup() stored function… which is permitted to modify data. This is different from the more sane PostgreSQL behavior which will NOT execute stored functions while running EXPLAIN (it will if you run EXPLAIN ANALYZE).

This decision in the MySQL case comes from trying to do the right stuff and provide the most reliable explain (query execution plan may well depend on what stored function returns) but it looks like this security tradeoff was not considered.

While this consequence of the current MySQL EXPLAIN design is one of the most severe, you also have the problem that EXPLAIN – which a rational user would expect to be a fast way to check the performance of a query – can take unbound time to complete, for example:

This will run for more than an hour, creating an additional accidental (or not) Denial of Service attack vector.

Going Deeper Down the Rabbit Hole

While this behavior is unfortunate, it will happen only if you have unrestricted privileges. If you have a more complicated setup, the behavior may vary.

If the user lacks EXECUTE privilege, the EXPLAIN statement will fail.

If the user has EXECUTE privilege but the user executing the stored function lacks DELETE privilege, it will fail too:

Note: I’m saying user executing stored function, rather than the current user, as depending on the SECURITY clause in Stored Function definition it may be run either as definer or as invoker.

So what can you do if you want to improve EXPLAIN safety, for example, if you’re developing a tool like Percona Monitoring and Management which, among other features, allows users to run EXPLAIN on their queries?

Advise users to set up privileges for monitoring correctly. It should be the first line of defense from this (and many other) issues, however, it is hard to rely on. Many users will choose the path of simplicity and will use “root” user with full privileges for monitoring.
Wrap your EXPLAIN statement in BEGIN … ROLLBACK which will undo any damage EXPLAIN may have caused. The downside of course is the “work” of deleting the data and when undoing the work will be done. (Note: Of course this only works for Transactional tables, if you still run MyISAM…. Well in this case you have worse problems to worry about.)
Use “ set transaction read-only” to signal you’re not expecting any writes… EXPLAIN which tries to write data will fail in this case without doing any work.

While these workarounds can have tools running EXPLAIN safer, it does not help users running EXPLAIN directly, and I really hope this issue will be fixed by redesigning EXPLAIN in a way it is not trying to run stored functions, as PostgreSQL already does.

For those who want to know how the query is executed EXACTLY, there is now EXPLAIN ANALYZE.

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master’s Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Источник

Problem writing a MySQL Function

I’m trying to create a function in MySQL 5.5 which will encrypt any field passed to it. The final function will be more complex than this, but this is my first attempt:

CREATE DEFINER=`myaccount`@`local host` FUNCTION `Enc1`(`IN` VARCHAR(255)) RETURNS VARBINARY(255) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER RETURN AES_ENCRYPT(firstname, ‘mykey’)

If I then run this SQL command:
mysql> SELECT Enc1(firstname) FROM person WHERE >
I get this error:

ERROR 1370 (42000): execute command denied to user ‘myaccount_user’@’localhos t’ for routine ‘myaccount_mydb.Enc1’

What am I doing wrong?

Or should I be using a procedure?

ASKER CERTIFIED SOLUTION

Thanks for that, mankowitz!

I’m using cPanel and phpMyAdmin on a shared hosting environment, and I tried your command via phpMyAdmin’s «SQL» menu, like this:
GRANT EXECUTE ON PROCEDURE myaccount_mydb.Enc1 TO myaccount_user@localhost;
but got this error:
#1370 — grant command denied to user ‘myaccount’@’localhost’ for routine ‘myaccount_mydb.myaccount_ mydb.Enc1’
So I tried this:
GRANT EXECUTE ON PROCEDURE Enc1 TO myaccount_user@localhost;
and got this error:
#1370 — grant command denied to user ‘myaccount’@’localhost’ for routine ‘myaccount_mydb.Enc1’
Or should I have done:
GRANT EXECUTE ON FUNCTION . ?
Got the same error for that.

But after some experimentation, I found that under cPanel > Databases, if I gave the user for that database «EXECUTE» permission, then reran my SELECT command:
SELECT Enc1(firstname) FROM person WHERE > I then got this error:
ERROR 1054 (42S22): Unknown column ‘firstname’ in ‘order clause’
I don’t see any order clause. Any ideas?

Also, I’d like this command to be able to be run on any column, so how can I make it so that will work?

Источник

execute denied error

has anyone an idea, how I can get over this access denied error?

#1370 — execute command denied to user ‘kulturbeutel-lei’@’localhost’ for routine ‘kulturbeutel2.set_visits’

CREATE DEFINER=`kulturbeutel-lei`@`localhost` PROCEDURE set_visits(
IN par_ip VARCHAR(40),
IN par_type VARCHAR(10)
)
BEGIN
CALL count_timeout_ip(@num, par_ip, par_type);
IF
@num = 0
THEN
CALL new_ip_timeout(par_ip, par_type);
UPDATE
`myphpgb_statistic`
SET
`visits` = `visits`+1
WHERE
`date` = CURDATE();
END IF;
END

✓ answered by Atli

Perhaps an obvious question, but does this user have the permission to use the EXECUTE command on this database?

GRANT EXECUTE
ON kulturbeutel2.*
TO ‘kulturbeutel-lei’@’localhost’;

Perhaps an obvious question, but does this user have the permission to use the EXECUTE command on this database?

GRANT EXECUTE
ON kulturbeutel2.*
TO ‘kulturbeutel-lei’@’localhost’;

EDIT: that seems to have done the trick, thanks Atli.

I’m facing the same prob. But i’m not able to access as root. How can i do that?

Only users with the EXECUTE permission can call procedures, and only root-like users can grant other users that permission.

If you can’t login as root via your GUI program, have you tried just using the CLI on the server computer?
I always prefer doing admin tasks via the command line anyways. Feels a lot safer, somehow.

Post your reply

All 8 Replies

14 Years Ago

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.

14 Years Ago

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.

14 Years Ago

So what problem are you getting now?

14 Years Ago

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!

14 Years Ago

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

14 Years Ago

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…

14 Years Ago

on the second grant, use identified by password

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

after done, use

flush privileges;

13 Years Ago

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>

Edited

13 Years Ago
by nav33n because:

Fake Signature snipped. Please check Control Panel-> Edit signature to add a signature to your profile.

Reply to this topic

Be a part of the DaniWeb community

We’re a friendly, industry-focused community of developers, IT pros, digital marketers,
and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Источник

I’m receiving this error when trying to call a stored procedure:

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1370 execute command denied to user ‘discoup9_vkimura’@’localhost’ for routine ‘discoup9_omusicstudios.selectCustDbImport» in /home/discoup9/public_html/omusicstudios/admin/calendar/loop_hours_classroom_v8.php:50 Stack trace: #0 /home/discoup9/public_html/omusicstudios/admin/calendar/loop_hours_classroom_v8.php(50): PDOStatement->execute() #1 /home/discoup9/public_html/omusicstudios/admin/calendar/time_class_room_v8.php(111): include(‘/home/discoup9/…’) #2 {main} thrown in /home/discoup9/public_html/omusicstudios/admin/calendar/loop_hours_classroom_v8.php on line 50

Currently, located here:
http://omusicstudios.com/admin/calendar/time_class_room_v8.php

I’m working on right now so the error may not display or may show a different error.

I noticed in the ‘information_schema’ database in the column ‘Definer’ I have this ‘discoup9_vkimura@174.6.38.231’ and this is the what is required for me to create the stored procedure:
CREATE DEFINER = ‘discoup9_vkimura’@’174.6.38.231’

But when I connect via the php script I have the $hostname variable set to ‘localhost’; hence, the error. When I tried to change the $hostname to ‘174.6.38.231’ I still receive an error as:

Fatal error: Uncaught exception ‘Exception’ with message ‘Failed to connect: SQLSTATE[HY000] [2003] Can’t connect to MySQL server on ‘174.6.38.231’ (4)’ in /home/discoup9/public_html/omusicstudios/admin/calendar/classes/dbWriter.php:25 Stack trace: #0 /home/discoup9/public_html/omusicstudios/admin/calendar/loop_hours_classroom_v8.php(47): DBWriter::__construct() #1 /home/discoup9/public_html/omusicstudios/admin/calendar/time_class_room_v8.php(111): include(‘/home/discoup9/…’) #2 {main} thrown in /home/discoup9/public_html/omusicstudios/admin/calendar/classes/dbWriter.php on line 25

Does anyone know how to resolve this? I have the script and stored procedure working fine on my vista local machine but not on my hosting provider (bluehost).

Any help is appreciated.

Sincerely,
Vkimura

Источник

Uncommon Sense MySQL – When EXPLAIN Can Trash Your Database

Going Deeper Down the Rabbit Hole

Problem writing a MySQL Function

execute denied error

✓ answered by Atli

Post your reply

Similar topics

Recommended Answers

All 8 Replies