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

[10 Feb 2006 15:52] Robert Verheus
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@%.
[19 Feb 2006 15:49] Valeriy Kravchuk
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)
[19 Dec 2007 18:23] Patrick Crews
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.
[19 Dec 2007 22:23] Iggy Galarza
This should be available the next time an MSI is created.
[3 Mar 2008 20:04] Paul DuBois
Noted in 5.0.51a, 5.1.23 changelogs.

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