Alexander, > - After having set up both machines completely, I installed the Win32 > binary distribution of MySQL 4.0.7-gamma. Although I did not upgrade > from an earlier version, the privileges tables still have the old > structure without the new columns introduced with MySQL 4.0.2-beta! > Furthermore, the script menioned in the documentation > (mysql_fix_privilege_tables) is obiously not included in the > distribution. Because of this, there is no way to use the new privilege > system but fixing the tables manually...
I encountered the same issue, and Miguel Solórzano from MySQL AB replied that it was his fault, simply forgetting the new privilege tables in the Win32 dist. mysql_fix_privilege_tables is nowhere to find, but a list member who had pulled the 4.1 source found it in there and sent it to me. I extracted the SQL statements, ran the script, and now my 4.0.7 has all the new privileges. You'll find it at the bottom of this mail. Maybe this can fix the other bugs you reported, too. Regards, -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 ----- Original Message ----- From: "Alexander M. Turek" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, January 05, 2003 4:23 PM Subject: MySQL 4.0.7-gamma: Bugs affecting the privilege system > Hi MySQL team, > > > I'm not sure, if this is the right place for bug reports, so, if it > isn't, please forward this to those who are resposible. Running MySQL > 4.0.7 on a Windows NT 5.2 RC2 machine and on a Windows NT 5.1 one as > well, I encoutered some bugs: > > > - After having set up both machines completely, I installed the Win32 > binary distribution of MySQL 4.0.7-gamma. Although I did not upgrade > from an earlier version, the privileges tables still have the old > structure without the new columns introduced with MySQL 4.0.2-beta! > Furthermore, the script menioned in the documentation > (mysql_fix_privilege_tables) is obiously not included in the > distribution. Because of this, there is no way to use the new privilege > system but fixing the tables manually... > > > - If I create a user by > > GRANT USAGE ON *.* TO 'foo'@'foohost'; > > and try to get the GRANT query by > > SHOW GRANTS FOR 'foo'@'foohost'; > > afterwards, MySQL returns an empty result set. > If I add a query like > > SET PASSWORD FOR 'foo'@'foohost' = PASSWORD('foobar'); > > and try the SHOW GRANTS query menitioned above again, MySQL correctly > returns: > > +----------------------------------------------------------------------- > ----------+ > | Grants for foo@foohost > | > +----------------------------------------------------------------------- > ----------+ > | GRANT USAGE ON *.* TO 'foo'@'foohost' IDENTIFIED BY PASSWORD > '4655c05b05f11fab' | > +----------------------------------------------------------------------- > ----------+ > > > - If I create a user by > > GRANT USAGE ON `footable`.* TO 'foo'@'foohost' IDENTIFIED BY 'foobar' > WITH GRANT OPTION; > > and try to get the GRANT query again, MySQL returns this: > > +----------------------------------------------------------------------- > ----------+ > | Grants for foo@foohost > | > +----------------------------------------------------------------------- > ----------+ > | GRANT USAGE ON *.* TO 'foo'@'foohost' IDENTIFIED BY PASSWORD > '4655c05b05f11fab' | > | GRANT ON `footable`.* TO 'foo'@'foohost' WITH GRANT OPTION > | > +----------------------------------------------------------------------- > ----------+ > > While the first query is completly correct, the second one would result > in an error... > > > Happy fixing, > > Alexander M. Turek > <[EMAIL PROTECTED]> > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- ATTACHMENT: mysql_fix_privilege_tables -- /* THIS IS AN UNOFFICIAL EXTRACT FROM THE mysql_fix_privilege_tables SHELL SCRIPT. MADE TO WORK (well, at least for me ;-) WHEN UPDATING FROM MySQL 4.0.3 TO 4.0.7 BY Stefan Hinz, [EMAIL PROTECTED], 2002-12-31 (yes, right, New Year's Eve :) Copy this file to: c:\mysql\bin (or whereever you installed MySQL) and run it like follows (example is on Win2K): c:\mysql\bin> mysql -f < mysql_fix_privilege_tables.sql mysql (The -f option will make it continue even if there are errors.) */ # Fix old password format, add File_priv and func table ALTER TABLE user CHANGE password password CHAR(16) NOT NULL; ALTER TABLE user ADD File_priv ENUM('N','Y') NOT NULL; CREATE TABLE IF NOT EXISTS func ( name CHAR(64) DEFAULT '' NOT NULL, ret TINYINT(1) DEFAULT '0' NOT NULL, dl CHAR(128) DEFAULT '' NOT NULL, type ENUM ('function','aggregate') NOT NULL, PRIMARY KEY (name) ); # Add the new grant colums ALTER TABLE user ADD Grant_priv ENUM('N','Y') NOT NULL, ADD References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add Alter_priv enum('N','Y') NOT NULL; ALTER TABLE host ADD Grant_priv enum('N','Y') NOT NULL,add References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add Alter_priv enum('N','Y') NOT NULL; ALTER TABLE db add Grant_priv enum('N','Y') NOT NULL,add References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add Alter_priv enum('N','Y') NOT NULL; # Copy existing entries to the new columns UPDATE user SET Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv, Alter_priv=Create_priv; UPDATE db SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_pri v; UPDATE host SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_pri v; # The second alter changes ssl_type to new 4.0.2 format ALTER TABLE user ADD ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL, ADD ssl_cipher BLOB NOT NULL, ADD x509_issuer BLOB NOT NULL, ADD x509_subject BLOB NOT NULL; ALTER TABLE user MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL; # Create tables_priv and columns_priv if they don't exists CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) DEFAULT '' NOT NULL, Db char(60) DEFAULT '' NOT NULL, User char(16) DEFAULT '' NOT NULL, Table_name char(60) DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp(14), Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','Referen ces','Index','Alter') DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name) ); CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) DEFAULT '' NOT NULL, Db char(60) DEFAULT '' NOT NULL, User char(16) DEFAULT '' NOT NULL, Table_name char(60) DEFAULT '' NOT NULL, Column_name char(59) DEFAULT '' NOT NULL, Timestamp timestamp(14), Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ); # Name change of Type -> Column_priv from MySQL 3.22.12 ALTER TABLE columns_priv change Type Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL; # Add the new 'type' column to the func table. # alter table func add type enum ('function','aggregate') NOT NULL; # Change the user,db and host tables to MySQL 4.0 format alter table user add Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER alter_priv, add Super_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Show_db_priv, add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Super_priv, add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Create_tmp_table_priv, add Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Lock_tables_priv, add Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Execute_priv, add Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Repl_slave_priv; update user set show_db_priv= select_priv, super_priv=process_priv, execute_priv=process_priv, create_tmp_table_priv='Y', Lock_tables_priv='Y', Repl_slave_priv=file_priv, Repl_client_priv=file_priv where user<>""; # Add fields that can be used to limit number of questions and connections # for some users. alter table user add max_questions int(11) NOT NULL AFTER x509_subject, add max_updates int(11) unsigned NOT NULL AFTER max_questions, add max_connections int(11) unsigned NOT NULL AFTER max_updates; # Add Create_tmp_table_priv and Lock_tables_priv to db and host alter table db add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL, add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL; alter table host add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL, add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL; --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
