bk commit into 5.0 tree (msvensson:1.2330) BUG#20589

[msvensson]

Below is the list of changes that have just been committed into a local
5.0 repository of msvensson. When msvensson does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-11-23 18:43:12+01:00, msvensson@neptunus.(none) +3 -0
  Bug#20589 Missing some table level privileges after upgrade
   - The table_priv column of table_privs table was altered to a enum type
     with fewer enums causing the SHOW/CREATE VIEW grants to be truncated.
   - Improved comments and moved all declarations for table_privs, column_privs
     and proc_privs to one section for each table making it easy to see hat alterations
     are performed on each table

  mysql-test/r/fix_priv_tables.result@stripped, 2006-11-23 18:43:10+01:00,
msvensson@neptunus.(none) +36 -0
    New BitKeeper file ``mysql-test/r/fix_priv_tables.result''

  mysql-test/r/fix_priv_tables.result@stripped, 2006-11-23 18:43:10+01:00,
msvensson@neptunus.(none) +0 -0

  mysql-test/t/fix_priv_tables.test@stripped, 2006-11-23 18:43:10+01:00,
msvensson@neptunus.(none) +66 -0
    New BitKeeper file ``mysql-test/t/fix_priv_tables.test''

  mysql-test/t/fix_priv_tables.test@stripped, 2006-11-23 18:43:10+01:00,
msvensson@neptunus.(none) +0 -0

  scripts/mysql_fix_privilege_tables.sql@stripped, 2006-11-23 18:43:10+01:00,
msvensson@neptunus.(none) +80 -60
    Collect everything for tables_priv, columns_priv and procs_priv in one section for
each table
    Remove duplicate ALTERS
    Remove the ALTERS that truncated "SHOW VIEW" and "CREATE VIEW" from
    the enum type for Table_priv.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	msvensson
# Host:	neptunus.(none)
# Root:	/home/msvensson/mysql/bug20589/my50-bug20589

--- 1.38/scripts/mysql_fix_privilege_tables.sql	2006-11-23 18:43:18 +01:00
+++ 1.39/scripts/mysql_fix_privilege_tables.sql	2006-11-23 18:43:18 +01:00
@@ -46,9 +46,8 @@ 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
+-- tables_priv
 --
-
 CREATE TABLE IF NOT EXISTS tables_priv (
   Host char(60) binary DEFAULT '' NOT NULL,
   Db char(64) binary DEFAULT '' NOT NULL,
@@ -56,22 +55,40 @@ CREATE TABLE IF NOT EXISTS tables_priv (
   Table_name char(64) binary DEFAULT '' NOT NULL,
   Grantor char(77) DEFAULT '' NOT NULL,
   Timestamp timestamp(14),
-  Table_priv
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter')
COLLATE utf8_general_ci DEFAULT '' NOT NULL,
-  Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
+  Table_priv set('Select','Insert','Update','Delete','Create',
+                 'Drop','Grant','References','Index','Alter')
+    COLLATE utf8_general_ci DEFAULT '' NOT NULL,
+  Column_priv set('Select','Insert','Update','References')
+    COLLATE utf8_general_ci DEFAULT '' NOT NULL,
   PRIMARY KEY (Host,Db,User,Table_name)
 ) CHARACTER SET utf8 COLLATE utf8_bin;
--- Fix collation of set fields
+
 ALTER TABLE tables_priv
-  modify Table_priv
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter')
COLLATE utf8_general_ci DEFAULT '' NOT NULL,
-  modify Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci
DEFAULT '' NOT NULL;
-ALTER TABLE procs_priv ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
-ALTER TABLE procs_priv
-  modify Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT
'' NOT NULL;
-ALTER TABLE procs_priv
-  add Routine_type enum('FUNCTION','PROCEDURE') COLLATE utf8_general_ci NOT NULL AFTER
Routine_name;
-ALTER TABLE procs_priv
-  modify Timestamp timestamp(14) AFTER Proc_priv;
+  ADD KEY Grantor (Grantor);
+
+ALTER TABLE tables_priv comment='Table privileges';
 
+ALTER TABLE tables_priv
+  MODIFY Host char(60) NOT NULL default '',
+  MODIFY Db char(64) NOT NULL default '',
+  MODIFY User char(16) NOT NULL default '',
+  MODIFY Table_name char(64) NOT NULL default '',
+  MODIFY Grantor char(77) NOT NULL default '',
+  ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
+
+ALTER TABLE tables_priv
+  MODIFY Column_priv set('Select','Insert','Update','References')
+    COLLATE utf8_general_ci DEFAULT '' NOT NULL;
+
+ALTER TABLE tables_priv
+  MODIFY Table_priv set('Select','Insert','Update','Delete','Create',
+                        'Drop','Grant','References','Index','Alter',
+                        'Create View','Show view')
+    COLLATE utf8_general_ci DEFAULT '' NOT NULL;
+
+--
+-- columns_priv
+--
 CREATE TABLE IF NOT EXISTS columns_priv (
   Host char(60) DEFAULT '' NOT NULL,
   Db char(64) DEFAULT '' NOT NULL,
@@ -82,16 +99,26 @@ CREATE TABLE IF NOT EXISTS columns_priv 
   Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
   PRIMARY KEY (Host,Db,User,Table_name,Column_name)
 ) CHARACTER SET utf8 COLLATE utf8_bin;
--- Fix collation of set fields
+
+-- Name change of Type -> Column_priv from MySQL 3.22.12
 ALTER TABLE columns_priv
-  MODIFY Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci
DEFAULT '' NOT NULL;
+  CHANGE Type Column_priv set('Select','Insert','Update','References')
+    COLLATE utf8_general_ci DEFAULT '' NOT NULL;
 
+ALTER TABLE columns_priv comment='Column privileges';
 
---
--- Name change of Type -> Column_priv from MySQL 3.22.12
---
+ALTER TABLE columns_priv
+  MODIFY Host char(60) NOT NULL default '',
+  MODIFY Db char(64) NOT NULL default '',
+  MODIFY User char(16) NOT NULL default '',
+  MODIFY Table_name char(64) NOT NULL default '',
+  MODIFY Column_name char(64) NOT NULL default '',
+  ENGINE=MyISAM,
+  CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
 
-ALTER TABLE columns_priv change Type Column_priv
set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL;
+ALTER TABLE columns_priv
+  MODIFY Column_priv set('Select','Insert','Update','References')
+    COLLATE utf8_general_ci DEFAULT '' NOT NULL;
 
 --
 --  Add the new 'type' column to the func table.
@@ -142,14 +169,12 @@ ADD Create_tmp_table_priv enum('N','Y') 
 ADD Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
 
 alter table user change max_questions max_questions int(11) unsigned DEFAULT 0  NOT NULL;
-alter table tables_priv add KEY Grantor (Grantor);
+
 
 alter table db comment='Database privileges';
 alter table host comment='Host privileges;  Merged with database privileges';
 alter table user comment='Users and global privileges';
 alter table func comment='User defined functions';
-alter table tables_priv comment='Table privileges';
-alter table columns_priv comment='Column privileges';
 
 -- Convert all tables to UTF-8 with binary collation
 -- and reset all char columns to correct width
@@ -223,25 +248,6 @@ ALTER TABLE func
   ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
 ALTER TABLE func
   MODIFY type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL;
-ALTER TABLE columns_priv
-  MODIFY Host char(60) NOT NULL default '',
-  MODIFY Db char(64) NOT NULL default '',
-  MODIFY User char(16) NOT NULL default '',
-  MODIFY Table_name char(64) NOT NULL default '',
-  MODIFY Column_name char(64) NOT NULL default '',
-  ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
-ALTER TABLE columns_priv
-  MODIFY Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci
DEFAULT '' NOT NULL;
-ALTER TABLE tables_priv
-  MODIFY Host char(60) NOT NULL default '',
-  MODIFY Db char(64) NOT NULL default '',
-  MODIFY User char(16) NOT NULL default '',
-  MODIFY Table_name char(64) NOT NULL default '',
-  MODIFY Grantor char(77) NOT NULL default '',
-  ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
-ALTER TABLE tables_priv
-  MODIFY Table_priv
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter')
COLLATE utf8_general_ci DEFAULT '' NOT NULL,
-  MODIFY Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci
DEFAULT '' NOT NULL;
 
 #
 # Detect whether we had Create_view_priv
@@ -274,11 +280,6 @@ ALTER TABLE user ADD Show_view_priv enum
 ALTER TABLE user MODIFY Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N'
NOT NULL AFTER Create_view_priv;
 
 #
-# Show/Create views table privileges (v5.0)
-#
-ALTER TABLE tables_priv MODIFY Table_priv
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create
View','Show view') COLLATE utf8_general_ci DEFAULT '' NOT NULL;
-
-#
 # Assign create/show view privileges to people who have create provileges
 #
 UPDATE user SET Create_view_priv=Create_priv, Show_view_priv=Create_priv where
user<>"" AND @hadCreateViewPriv = 0;
@@ -344,22 +345,41 @@ UPDATE user LEFT JOIN db USING (Host,Use
   WHERE @hadCreateUserPriv = 0 AND
         (user.Grant_priv = 'Y' OR db.Grant_priv = 'Y');
 
-#
-# Create some possible missing tables
-#
+--
+-- procs_priv
+--
 CREATE TABLE IF NOT EXISTS procs_priv (
-Host char(60) binary DEFAULT '' NOT NULL,
-Db char(64) binary DEFAULT '' NOT NULL,
-User char(16) binary DEFAULT '' NOT NULL,
-Routine_name char(64) binary DEFAULT '' NOT NULL,
-Routine_type enum('FUNCTION','PROCEDURE') NOT NULL,
-Grantor char(77) DEFAULT '' NOT NULL,
-Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT
NULL,
-Timestamp timestamp(14),
-PRIMARY KEY (Host,Db,User,Routine_name,Routine_type),
-KEY Grantor (Grantor)
+  Host char(60) binary DEFAULT '' NOT NULL,
+  Db char(64) binary DEFAULT '' NOT NULL,
+  User char(16) binary DEFAULT '' NOT NULL,
+  Routine_name char(64) binary DEFAULT '' NOT NULL,
+  Routine_type enum('FUNCTION','PROCEDURE') NOT NULL,
+  Grantor char(77) DEFAULT '' NOT NULL,
+  Proc_priv set('Execute','Alter Routine','Grant')
+    COLLATE utf8_general_ci DEFAULT '' NOT NULL,
+  Timestamp timestamp(14),
+  PRIMARY KEY (Host, Db, User, Routine_name, Routine_type),
+  KEY Grantor (Grantor)
 ) CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges';
 
+ALTER TABLE procs_priv
+  ENGINE=MyISAM,
+  CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
+
+ALTER TABLE procs_priv
+  MODIFY Proc_priv set('Execute','Alter Routine','Grant')
+    COLLATE utf8_general_ci DEFAULT '' NOT NULL;
+
+ALTER TABLE procs_priv
+  ADD Routine_type enum('FUNCTION','PROCEDURE')
+    COLLATE utf8_general_ci NOT NULL AFTER Routine_name;
+
+ALTER TABLE procs_priv
+  modify Timestamp timestamp(14) AFTER Proc_priv;
+
+--
+-- help_topic
+--
 CREATE TABLE IF NOT EXISTS help_topic (
 help_topic_id int unsigned not null,
 name varchar(64) not null,
--- New file ---
+++ mysql-test/r/fix_priv_tables.result	06/11/23 18:43:10
drop table if exists t1,t1aa,t2aa;
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
CREATE TABLE testdb.t1 (
c1 INT,
c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE VIEW testdb.v1 AS
SELECT * FROM testdb.t1;
GRANT CREATE VIEW, SHOW VIEW ON testdb.v1 TO 'show_view_tbl'@'localhost';
SHOW GRANTS FOR 'show_view_tbl'@'localhost';
Grants for show_view_tbl@localhost
GRANT USAGE ON *.* TO 'show_view_tbl'@'localhost'
GRANT CREATE VIEW, SHOW VIEW ON `testdb`.`v1` TO 'show_view_tbl'@'localhost'

GRANT SELECT(c1) on testdb.v1 to 'select_only_c1'@localhost;
SHOW GRANTS FOR 'select_only_c1'@'localhost';
Grants for select_only_c1@localhost
GRANT USAGE ON *.* TO 'select_only_c1'@'localhost'
GRANT SELECT (c1) ON `testdb`.`v1` TO 'select_only_c1'@'localhost'

"after fix privs"
SHOW GRANTS FOR 'show_view_tbl'@'localhost';
Grants for show_view_tbl@localhost
GRANT USAGE ON *.* TO 'show_view_tbl'@'localhost'
GRANT CREATE VIEW, SHOW VIEW ON `testdb`.`v1` TO 'show_view_tbl'@'localhost'

SHOW GRANTS FOR 'select_only_c1'@'localhost';
Grants for select_only_c1@localhost
GRANT USAGE ON *.* TO 'select_only_c1'@'localhost'
GRANT SELECT (c1) ON `testdb`.`v1` TO 'select_only_c1'@'localhost'

DROP USER 'show_view_tbl'@'localhost';
DROP USER 'select_only_c1'@'localhost';
DROP VIEW testdb.v1;
DROP TABLE testdb.t1;
DROP DATABASE testdb;

--- New file ---
+++ mysql-test/t/fix_priv_tables.test	06/11/23 18:43:10
# Embedded server doesn't support external clients
--source include/not_embedded.inc

#
# This is the test for mysql_fix_privilege_tables
# It checks that a system tables from mysql 4.1.23
# can be upgraded to current system table format
#
# Note: If this test fails, don't be confused about the errors reported
# by mysql-test-run This shows warnings generated by
# mysql_fix_system_tables which should be ignored.
# Instead, concentrate on the errors in r/system_mysql_db.reject

--disable_warnings
drop table if exists t1,t1aa,t2aa;
--enable_warnings

#
# Bug #20589 Missing some table level privileges after upgrade
#
# Add some grants that should survive the "upgrade"

--disable_warnings
DROP DATABASE IF EXISTS testdb;
--enable_warnings
CREATE DATABASE testdb;
CREATE TABLE testdb.t1 (
  c1 INT,
  c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE VIEW testdb.v1 AS
        SELECT * FROM testdb.t1;

GRANT CREATE VIEW, SHOW VIEW ON testdb.v1 TO 'show_view_tbl'@'localhost';
SHOW GRANTS FOR 'show_view_tbl'@'localhost';
echo;

# Some extra GRANTS for more complete test
GRANT SELECT(c1) on testdb.v1 to 'select_only_c1'@localhost;
SHOW GRANTS FOR 'select_only_c1'@'localhost';
echo;

-- disable_result_log
-- disable_query_log

# Run the mysql_fix_privilege_tables.sql using "mysql --force"
--exec $MYSQL --force mysql < $MYSQL_FIX_PRIVILEGE_TABLES >
$MYSQLTEST_VARDIR/log/fix_priv_tables.log 2>&1

-- enable_query_log
-- enable_result_log

echo "after fix privs";

SHOW GRANTS FOR 'show_view_tbl'@'localhost';
echo;
SHOW GRANTS FOR 'select_only_c1'@'localhost';
echo;

DROP USER 'show_view_tbl'@'localhost';
DROP USER 'select_only_c1'@'localhost';

DROP VIEW testdb.v1;
DROP TABLE testdb.t1;
DROP DATABASE testdb;

# End of 4.1 tests