| 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: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | ODBC 5.1.8 -> 5.5.8 | OS: | Windows |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
| Tags: | GRANT EXECUTE PROCEDURE, ODBC, REVOKE FILE | ||
[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.
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.