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: | |
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
[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.