MySQL Bugs: #11396: Access denied when creating a function when using a remote connection

Bug #11396	Access denied when creating a function when using a remote connection
Submitted:	16 Jun 2005 20:16	Modified:	23 Nov 2005 14:52
Reporter:	Jan Bouwhuis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.7-beta-nt	OS:	Windows (Windows 2003 server/XP SP2)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Using a client connection other then local host (127.0.0.1) creating a function gives Error 1044: Access denied.

user account 'root' is used and authorized to connect remotely. All default settings are used. Other tasks as backup,restore,drop schema etc. work fine.

After an explicitly grat of userrights to the connected host every thing works fine.

How to repeat:
Connect to a server; use the local IP-address (not local host or 127.0.0.1); and open a database database. Be sure to open the root account for remote access.

Create a function, e.g.

CREATE FUNCTION testfunct(id INT UNSIGNED)
  RETURNS INT UNSIGNED
BEGIN
  RETURN id * 2;
END

Returns: 

Suggested fix:
user root should be able to create a function with default permissions when during installation is indicated that user root can connect from different systems.

To enable creation the CREATE_PRIVILEGE should be assignd to user root explicitly. To Drop the DROP privilege is needed explicitly.

I was unable to repeat with a server built from latest BK source:

c:\mysql\bin>mysql -uroot -h192.168.0.33 -p test
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.8-beta-nt

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

mysql> delimiter //
mysql> CREATE FUNCTION testfunct(id INT UNSIGNED)
    ->   RETURNS INT UNSIGNED
    -> BEGIN
    ->   RETURN id * 2;
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql>

Please correct me if I didn't understand what you meant.

Thanks in advance.

I need to reinstall and place a clean copy of the MySQL databases. The reason you cannot repeat might be it is because only with an old upgraded database this problem occurs. I will push new comment later.

I have been using version 5.0.7-beta.

I have uninstalled previous versions, database instances and files first.

Using the local IP-adres gives:

mysql -uroot -h10.0.0.100 -p test
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 123 to server version: 5.0.7-beta-nt

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

mysql> delimiter //
mysql> CREATE FUNCTION testfunct(id INT UNSIGNED)
    -> RETURNS INT UNSIGNED
    -> BEGIN
    ->   RETURN id * 2;
    -> END //
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'test'
mysql> CREATE TABLE `test`.`testtable` (
    ->   `testid` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY(`testid`)
    -> )
    -> TYPE = InnoDB //
Query OK, 0 rows affected, 1 warning (0.05 sec)

When I am using 127.0.0.1 I get the following result.

C:\Documents and Settings\JBOUWH>mysql -uroot -h127.0.0.1 -p test
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.7-beta-nt

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

mysql> delimiter //
mysql> CREATE FUNCTION testfunct(id INT UNSIGNED)
    -> RETURNS INT UNSIGNED
    -> BEGIN
    ->   RETURN id * 2;
    -> END //
Query OK, 0 rows affected (0.00 sec)

Im am using a default configuration and enabled the option 'Enable root access from remote machines'

Using mysql-administrator 1.0.21, when I explicitly grant user root@% to the privilege 'CREATE_ROUTINE' and 'DROP'. After that the create function statement works fine.

Using the root account, it should not be necessary to grant these privileges.

From our Manual: http://dev.mysql.com/doc/mysql/en/default-privileges.html

#

Two accounts are created with a username of root. These are superuser accounts that can do anything. The initial root account passwords are empty, so anyone can connect to the MySQL server as root without a password and be granted all privileges.

    *

      On Windows, one root account is for connecting from the local host and the other allows connections from any host.
    *

      On Unix, both root accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of localhost for one account, or the actual hostname or IP number for the other.

And verifying the root accounts in a fresh install:

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

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

mysql> select host from user where user="root";
+-----------+
| host      |
+-----------+
| localhost |
+-----------+
1 row in set (0.02 sec)

Then that need to be fixed either in the Manual or in the package release
setting one more root account for to connect from any host.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated 4.1/5.0/5.1 versions of Manual as suggested.