Bug #20939 root cannot create SPs and functions remotely
Submitted: 10 Jul 2006 13:04 Modified: 11 Jul 2006 16:46
Reporter: Andrey Kazachkov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.22 OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[10 Jul 2006 13:04] Andrey Kazachkov
Description:
Root cannot create functions and stored procedures if MySQL Server is accesses remotely though can create databases and tables. 
The problem doesn't occurs if MySQL Server is accesses locally. 

How to repeat:
Following code doesn't work if MySQL Server is accesses remotely. 

DELIMITER GO

USE `mysql`
GO

DROP DATABASE IF EXISTS `testdb`
GO

CREATE DATABASE `testdb` DEFAULT CHARACTER SET `ascii` COLLATE
`ascii_general_ci`
GO

USE `testdb`
GO

CREATE TABLE `table1` (
  `nId` int PRIMARY KEY auto_increment,
  `tmDate1` DATETIME NULL,
  `tmDate2` DATETIME NULL,
  `strName` varchar(256)
) ENGINE=InnoDB DEFAULT CHARSET = ascii
GO

CREATE FUNCTION AK_CAST2INT(
    nVal INT)
RETURNS int
    DETERMINISTIC
BEGIN
    RETURN nVal;
END
GO

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'testdb'
[10 Jul 2006 13:44] MySQL Verification Team
Thank you for the bug report. I was unable to repeat connecting remotely
from a Linux box:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on *.* to 'root'@'%' identified by 'theroot';
Query OK, 0 rows affected (0.09 sec)

mysql>

From the Linux box:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot -p -h192.168.0.33
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.22-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER GO
mysql> CREATE DATABASE `testdb` DEFAULT CHARACTER SET `ascii` COLLATE
    -> `ascii_general_ci`
    -> GO
Query OK, 1 row affected (0.08 sec)

mysql>
mysql> USE `testdb`
Database changed
mysql> GO
ERROR:
No query specified

mysql>
mysql> CREATE TABLE `table1` (
    ->   `nId` int PRIMARY KEY auto_increment,
    ->   `tmDate1` DATETIME NULL,
    ->   `tmDate2` DATETIME NULL,
    ->   `strName` varchar(256)
    -> ) ENGINE=InnoDB DEFAULT CHARSET = ascii
    -> GO
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql> CREATE FUNCTION AK_CAST2INT(
    ->     nVal INT)
    -> RETURNS int
    ->     DETERMINISTIC
    -> BEGIN
    ->     RETURN nVal;
    -> END
    -> GO
Query OK, 0 rows affected (0.07 sec)

mysql>
[10 Jul 2006 14:13] Peter Laursen
very strange.  Because I have the opposite experience.  Even If I remove the CREATE ROUTINE privilege from 'root'@'%', I can still create your function!

grant all on *.* to 'root'@'%';
show grants for 'root'@'%';
/* returns
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY ..etc */
revoke create routine on *.* from 'root'@'%';
show grants for 'root'@'%';
/* returns
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, ALTER ROUTINE, CREATE USER ON *.* TO 'root'@'%' IDENTIFIED BY ... etc */

DELIMITER $$
DROP FUNCTION IF EXISTS `test`.`df` $$

CREATE FUNCTION `df`(nVal INT)RETURNS int DETERMINISTIC
BEGIN
    RETURN nVal;
END$$

DELIMITER ;

.. and function is created!

SuSE 10.0 - MySQL 5.0.22
[10 Jul 2006 14:13] Andrey Kazachkov
MySQL Server and client are installed on two windows machines. MySQL Server was installed via Windows installer installer. my.ini file is attached
[10 Jul 2006 14:14] Andrey Kazachkov
my.ini file

Attachment: my.ini (application/octet-stream, text), 9.03 KiB.

[10 Jul 2006 16:48] Peter Laursen
@Andrey: does the REMOTE 'root' user have the CREATE ROUTINE privilege?
Have you explicitly GRANTed it to 'root'@'yourclienthost' or to 'root'@'%'?  Only 'root'@'localhost' has it by default.

@Miguel
I really experience the GRANT&REVOKE CREATE ROUTINE privilege has no effect unless server is restarted.  It should not be necessary to 'flush privileges' with GRANT&REVOKE .. and it does not help either!  But restarting server does!
[10 Jul 2006 17:24] Andrey Kazachkov
If I connect to MySQl Server remotely or locally via dns name I get following

SHOW GRANTS;
Grants for root@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*2309E4F5BC46EE2D3D77D5F90CCFEC6EA316E793' WITH GRANT OPTION

But if connect locally via 127.0.0.1 I get other result:
SHOW GRANTS;
1	GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2309E4F5BC46EE2D3D77D5F90CCFEC6EA316E793' WITH GRANT OPTION
[10 Jul 2006 17:39] Peter Laursen
@Andrey

Obviously your remote root-user does not have CREATE ROUTINE and ALTER ROUTINE privilege.

Privileges are host-specific in MySQL.  
http://dev.mysql.com/doc/refman/5.0/en/grant.html

If every root -user shall have ALL privileges (same privileges as 'root'@'localhost') then execute:

GRANT all on *.* to 'root'@'%' with grant option;

Instead of 'root'@'%' you may use 'root'@'hostname' or 'root'@'ip'. 
And CREATE ROUTINE and ALTER ROUTINE privileges can also be granted on a specific database like 

GRANT CREATE ROUTINE on mydb.* to 'root'@'ip1.ip2.ip3.ip4;

'MySQL Administrator' is no use here as it does not support CREATE ROUTINE and ALTER ROUTINE privileges as GLOBAL privileges (a MA bug)

.. and if it does not work immediately then try restarting the server.  
As I wrote I have problems with those privileges taking effect.
[11 Jul 2006 16:46] Andrey Kazachkov
Peter, thank you for explanation.