MySQL Bugs: #20939: root cannot create SPs and functions remotely

Bug #20939	root cannot create SPs and functions remotely
Submitted:	10 Jul 2006 15:04	Modified:	11 Jul 2006 18:46
Reporter:	Andrey Kazachkov
Status:	Can't repeat
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Microsoft Windows (Windows)
Assigned to:		Target Version:

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'

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>

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

MySQL Server and client are installed on two windows machines. MySQL Server was installed
via Windows installer installer. my.ini file is attached

my.ini file

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

@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!

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

@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.

Peter, thank you for explanation.