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:18:54+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:18:50+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:18:50+01:00, msvensson@neptunus.(none) +0 -0
mysql-test/r/fix_priv_tabs.result@stripped, 2006-11-23 18:18:50+01:00, msvensson@neptunus.(none) +0 -0
New BitKeeper file ``mysql-test/r/fix_priv_tabs.result''
mysql-test/r/fix_priv_tabs.result@stripped, 2006-11-23 18:18:50+01:00, msvensson@neptunus.(none) +0 -0
scripts/mysql_fix_privilege_tables.sql@stripped, 2006-11-23 18:18:50+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:19:00 +01:00
+++ 1.39/scripts/mysql_fix_privilege_tables.sql 2006-11-23 18:19:00 +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:18:50
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/r/fix_priv_tabs.result 06/11/23 18:18:50
| Thread |
|---|
| • bk commit into 5.0 tree (msvensson:1.2330) BUG#20589 | msvensson | 23 Nov |
