MySQL Bugs: #17303: Create View privileges not available for remote root user

Bug #17303	Create View privileges not available for remote root user
Submitted:	10 Feb 2006 15:52	Modified:	3 Mar 2008 20:04
Reporter:	Robert Verheus	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	MySQL 5.0.18	OS:	Windows (Windows 2000 SP4)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
The priviliges 'CREATE VIEW', 'SHOW VIEW', 'CREATE ROUTINE', 'ALTER ROUTINE' and 'CREATE USER' are missing from the generated privileges for root@%. These priviliges should be generated by the Instance Configuration Wizard when the setting 'Enable root access from remote machines' is set.

How to repeat:
After installing MySQL 5.0.18 the MYSQL instance is configured with the wizard with the following options:

* Detailed configuration
* Server Machine
* Multifunctional Database
* No changes made to InnoDB Tablespace Settings
* Online Transaction Processing
* Enable TCP/IP Networking with port number 3306
* Enable Strict Mode
* Best Support For Multilingualism
* Install As Windows Service with Service name MySQL and launch automatically
* Include Bin Directory in Windows Path
* A root password is entered
* The 'Enable root access from remote machines' is set

According to this latest setting there should have been created privileges for root@localhost AND root@%

The privileges 'CREATE VIEW', 'SHOW VIEW', 'CREATE ROUTINE', 'ALTER ROUTINE' and 'CREATE USER' are created for root@localhost but not for root@%

Suggested fix:
Also create the privileges 'CREATE VIEW', 'SHOW VIEW', 'CREATE ROUTINE', 'ALTER ROUTINE' and 'CREATE USER' for root@%.

Thank you for a problem report. Verified just as described:

mysql> select * from user\G
*************************** 1. row ***************************
                 Host: localhost
                 User: root
             Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
*************************** 2. row ***************************
                 Host: %
                 User: root
             Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
     Create_user_priv: N
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
2 rows in set (0.00 sec)

Tested revised .msi for 5.0.52.

Verified remote user has missing privileges when "Enable root access from remote machines"and "Create An Anonymous Account" are selected -- no SQL error and privileges also verified via examination of INFORMATION_SCHEMA.USER_PRIVILEGES table.

Also verified that these privileges do not exist when these options are not chosen.

Tested on Windows XP 32bit VMWare image.

Ok to push.

This should be available the next time an MSI is created.

Noted in 5.0.51a, 5.1.23 changelogs.

ceConfig.exe failed to grant certain privileges to the
'root'@'%' account.