diff options
| -rw-r--r-- | mysql-test/r/fix_priv_tables.result | 36 | ||||
| -rw-r--r-- | mysql-test/r/fix_priv_tabs.result | 0 | ||||
| -rw-r--r-- | mysql-test/t/fix_priv_tables.test | 66 | ||||
| -rw-r--r-- | scripts/mysql_fix_privilege_tables.sql | 139 |
4 files changed, 180 insertions, 61 deletions
diff --git a/mysql-test/r/fix_priv_tables.result b/mysql-test/r/fix_priv_tables.result new file mode 100644 index 00000000000..e54330a5520 --- /dev/null +++ b/mysql-test/r/fix_priv_tables.result @@ -0,0 +1,36 @@ +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; diff --git a/mysql-test/r/fix_priv_tabs.result b/mysql-test/r/fix_priv_tabs.result new file mode 100644 index 00000000000..e69de29bb2d --- /dev/null +++ b/mysql-test/r/fix_priv_tabs.result diff --git a/mysql-test/t/fix_priv_tables.test b/mysql-test/t/fix_priv_tables.test new file mode 100644 index 00000000000..3a91f41dfcc --- /dev/null +++ b/mysql-test/t/fix_priv_tables.test @@ -0,0 +1,66 @@ +# 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 diff --git a/scripts/mysql_fix_privilege_tables.sql b/scripts/mysql_fix_privilege_tables.sql index 287d51379e2..5709fb96793 100644 --- a/scripts/mysql_fix_privilege_tables.sql +++ b/scripts/mysql_fix_privilege_tables.sql @@ -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,38 @@ 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 + 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, + 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, + COMMENT='Table privileges'; + +-- +-- columns_priv +-- CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) DEFAULT '' NOT NULL, Db char(64) DEFAULT '' NOT NULL, @@ -82,16 +97,25 @@ 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 -ALTER TABLE columns_priv - MODIFY Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL; - --- -- Name change of Type -> Column_priv from MySQL 3.22.12 --- +ALTER TABLE columns_priv + CHANGE Type Column_priv set('Select','Insert','Update','References') + COLLATE utf8_general_ci DEFAULT '' NOT NULL; -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 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, + COMMENT='Column privileges'; + +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 +166,12 @@ ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT 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 +245,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 +277,6 @@ ALTER TABLE user ADD Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAUL 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 +342,41 @@ UPDATE user LEFT JOIN db USING (Host,User) SET Create_user_priv='Y' 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, |