Bug #59766 ODBC - GRANT EXECUTE ON PROCEDURE or REVOKE FILE fails with root
Submitted: 27 Jan 2011 0:53 Modified: 30 May 2013 6:21
Reporter: bill bill Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:ODBC 5.1.8 -> 5.5.8 OS:Microsoft Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: GRANT EXECUTE PROCEDURE, ODBC, REVOKE FILE

[27 Jan 2011 0:53] bill bill
Description:
When connecting via odbc, grant execute on procedure or revoke file fails

using the current 5.5.8 mysql with 5.8 ODBC connector

This worked fine with earlier versions.

first you get the message access denied for grant execute on proc or for revoke, after that you get  access denied for root@127.0.0.1

Also, it appears to be related to the strange XP too many connections issue that's been around since ODBC 5.1 that we don't see on linux
after getting one of those, you then will get

access denied for root@127.0.0.1

wait a while and it works again.

The exact same grant statements work perfectly well from the mysql command prompt.

How to repeat:
create a database from command prompt

create a user1

create a stored proc

using ADODB connecting with root@127.0.0.1, 

set cn1 = new ADODB.Connection 
cn1.open "DRIVER={MySQL ODBC 5.1 Driver};PWD=somepass;SERVER=127.0.0.1;PORT=3306;

create user1 by executing a grant works

cn1.execute "REVOKE FILE ON *.* from 'user1'@'localhost'"

initial install of 5.5.8 on windows
root access denied for root@127.0.0.1
solved by adding the record

then, created user with select, insert, update, execute 

created database testdb1 with a stored proc

could not create users from ODBC before adding these rows FOR ROOT USER!

insert Db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv, 
Drop_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Create_tmp_table_priv,
Lock_tables_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,
Execute_priv,Event_priv,Trigger_priv)
values ('localhost','%','root','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

insert Db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv, 
Drop_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Create_tmp_table_priv,
Lock_tables_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,
Execute_priv,Event_priv,Trigger_priv)
values ('127.0.0.1','%','root','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> select * from db;
+-----------+----+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+---------
---+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+------
--------+
| Host      | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_pr
iv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigg
er_priv |
+-----------+----+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+---------
---+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+------
--------+
| localhost | %  | root | Y           | Y           | Y           | Y           | Y           | Y         | Y          | Y               | Y          | Y
   | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y
        |
| 127.0.0.1 | %  | root | Y           | Y           | Y           | Y           | Y           | Y         | Y          | Y               | Y          | Y
   | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y
        |
+-----------+----+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+---------
---+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+------
--------+

-------------------------------------------
prior to ODBC connection from command line
-------------------------------------------
create database if not exists testdb1;

use testdb1;

grant select,insert,Update,delete,execute on testdb1.* to pUserX@'localhost' 
identified by 'somethingelse';

flush privileges;

create table if not exists tickets ( 
id bigint not null auto_increment primary key, 
tickettype int not null default 0, ticket varchar(255) not null )engine=innodb character set utf8;

DROP PROCEDURE IF EXISTS `testdb1`.`SetTicketOneProp` $$
CREATE DEFINER=`pUserX`@`localhost` 
PROCEDURE `SetTicketOneProp`(IN SourceTicket varchar(255), IN CustomerIP varchar(54), 
IN pWhichField integer, IN pValue integer)
BEGIN
	update tickets set tickettype = pValue where ticket=SourceTicket and ipaddress=CustomerIP;

END $$

DELIMITER ;

-------------------------------------------
Call this from an ODBC connection 

set cn1 = new ADODB.Connection 
cn1.open "DRIVER={MySQL ODBC 5.1 Driver};PWD=somepass;charset=UTF8;SERVER=127.0.0.1;PORT=3306;DATABASE=mysql;USER=root;"

cn1.execute "REVOKE FILE ON *.* from 'user1'@'localhost'"

-------------------------------------------

set sql_log_off = 'ON'; 

GRANT SELECT ON `testdb1`.* TO 'user1'@'127.0.0.1'
IDENTIFIED BY '(wvufvapsp8hkgmczrd1s(qXul1omaypwv_on1n2'
 
GRANT SELECT ON `testdb1`.* TO 'user1'@'localhost'
IDENTIFIED BY '(wvufvapsp8hkgmczrd1s(qXul1omaypwv_on1n2'
 
GRANT  SELECT, INSERT, EXECUTE  on `testdb1`.* to 'pUserX'@'localhost' identified by 'BE15841A190818' ;
GRANT  SELECT, INSERT, EXECUTE  on `testdb1`.* to 'pUserX'@'127.0.0.1' identified by 'BE15841A190818' ;

--------failures begin here as I execute this series of commands using ADODB connection using ODBC 5.1.8
--------same code worked fine with earlier version of ODBC 5.1---------------------

GRANT EXECUTE on PROCEDURE `testdb1`.`SetTicketOneProp` TO 'user1'@'127.0.0.1';
GRANT EXECUTE on PROCEDURE `testdb1`.`SetTicketOneProp` TO 'user1'@'localhost';

REVOKE FILE ON *.* from 'user1'@'localhost';
REVOKE FILE ON *.* from 'user1'@'127.0.0.1';

--- access denied  root@127.0.0.1

 flush privileges; 

 set sql_log_off = 'OFF';

Suggested fix:
Perhaps it is a new "security feature", but it would be nice to know grant and revoke are eliminated from ODBC calls, since I have admininstrative code that is used to generate and revoke accounts.
[27 Jan 2011 0:59] bill bill
trying to simplify the example, I erroneously pasted incorrect grant statements, but that's not the issue, as calling the procedure would result in no update rights.

The issue is the access denied to    root@127.0.0.1  

for grant execute on procedure ... statements 
and for revoke file on *.* ...  statements
[27 Jan 2011 1:28] bill bill
consistent failure for revoke file on ...
intermittent failure for grant execute on procedure

# so what's the problem?
# 1. it fails on revoke file ON *.* ...  WHY? it is the root user.
# 2. it fails on grant execute on procedure sometimes ...

# have mysql 5.5.8 for windows and ODBC 5.1.8 connector installed
# from the mysql command prompt past the following to create a database, user, and procedure

create database if not exists testdb1;

use testdb1;

grant select,insert,Update,delete,execute on testdb1.* to userProcsX@'localhost' identified by 'test';

create table if not exists tickets ( 
 id bigint not null auto_increment primary key, 
 ticket varchar(255) not null
)engine=innodb character set utf8;

DELIMITER $$

DROP PROCEDURE IF EXISTS `testdb1`.`SetTicket` $$
CREATE DEFINER=`userProcsX`@`localhost` 
PROCEDURE `SetTicket`(IN SourceTicket varchar(255))

BEGIN
insert tickets(ticket) values(SourceTicket);
select last_insert_id();
	
END $$

DELIMITER ;

call SetTicket('test');

+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> select * from tickets;
+----+--------+
| id | ticket |
+----+--------+
|  1 | test   |
+----+--------+
1 row in set (0.00 sec)

mysql> delete from mysql.procs_priv;
Query OK, 282 rows affected (0.00 sec)

mysql> select * from mysql.procs_priv;
Empty set (0.00 sec)

'Open a standard VB6 project
'add a reference to ADODB
'add a command button to your vb6 form and paste in this test code

Option Explicit
Private Sub Command_Click()

Dim cn As ADODB.Connection, lp As Long, sql As String, sql_cmds() As String, lmax As Long, lp2 As Long

sql = "set sql_log_off = 'ON';"
sql = sql & vbCrLf & "GRANT SELECT ON `testdb1`.* TO 'user1'@'127.0.0.1' IDENTIFIED By 'something';"
sql = sql & vbCrLf & "GRANT SELECT ON `testdb1`.* TO 'user1'@'localhost' IDENTIFIED By 'something';"
sql = sql & vbCrLf & "GRANT  SELECT, INSERT, EXECUTE  on `testdb1`.* to 'pUserX'@'localhost' identified by 'test' ;"
sql = sql & vbCrLf & "GRANT  SELECT, INSERT, EXECUTE  on `testdb1`.* to 'pUserX'@'127.0.0.1' identified by 'test' ;"
sql = sql & vbCrLf & "GRANT EXECUTE on PROCEDURE `testdb1`.`SetTicket` TO 'user1'@'127.0.0.1';"
sql = sql & vbCrLf & "GRANT EXECUTE on PROCEDURE `testdb1`.`SetTicket` TO 'user1'@'localhost';"
sql = sql & vbCrLf & "REVOKE FILE ON *.* from 'user1'@'localhost';"
sql = sql & vbCrLf & "REVOKE FILE ON *.* from 'user1'@'127.0.0.1';"
sql = sql & vbCrLf & "flush privileges;"
sql = sql & vbCrLf & "set sql_log_off = 'OFF';"

 
sql_cmds = Split(sql, vbCrLf)

Set cn = New ADODB.Connection
cn.Open "DRIVER={MySQL ODBC 5.1 Driver};PWD=somepass;charset=UTF8;SERVER=127.0.0.1;PORT=3306;DATABASE=mysql;USER=root;"
lmax = UBound(sql_cmds)
For lp2 = 1 To 5
    For lp = 0 To lmax
        On Error Resume Next
        cn.Execute sql_cmds(lp)
        If Err Then
            MsgBox Err.Description & vbCrLf & sql_cmds(lp)
        End If
    
    Next lp
Next lp2
cn.Close
Set cn = Nothing

End Sub

# below I test user1 created above with ODBC calls through ADODB vb6 app
# works fine. can access db, can call stored procedure fine too.

>mysql -u user1 -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 179
Server version: 5.5.8 MySQL Community Server (GPL)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb1            |
+--------------------+
2 rows in set (0.03 sec)

mysql> use testdb1;
Database changed

mysql> call setTicket('hello');
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)
[27 Jan 2011 1:37] bill bill
NOTE THAT THE COMMAND WORKS FROM THE COMMAND LINE

and I realize there is nothing to revoke

This is for an administrative tool to verify user rights are correct to comply with a IT policy.

I'll bet it is easy to figure out and correct, since it does it every time. 

Thank you.
[4 Feb 2011 9:02] Bogdan Degtyariov
Hi b2010,

It seems that all problems you reported have a single cause: not granting all needed privileges to the user 'root'@'127.0.0.1'.

Please note that 'root'@'127.0.0.1' and 'root'@'localhost' are two absolutely different users. It is even possible to have privileges that do not overlap (I mean something which allowed to 'root'@'localhost' is not allowed to 'root'@'127.0.0.1' and vice versa). Giving user name 'root' does not mean that the server treats it as user with root privileges.

You should log in as 'root'@'localhost' and execute the following statement from mysql command line:

grant all privileges on *.* to 'root'@'127.0.0.1' identified by '*******' with grant option;

More information about GRANT syntax is available here:

http://dev.mysql.com/doc/refman/5.5/en/grant.html
[5 Mar 2011 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[30 May 2013 6:21] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.