From 838e89dea60b7215a39c80824764d92f951dd5c4 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 28 Feb 2007 14:26:58 +0100 Subject: Bug#20166 mysql-test-run.pl does not test system privilege tables creation - Split out initial data in mysql_system_tables.sql to it's own file - Use file from mysql_install_db and mysql-test-run scripts/mysql_system_tables_fix.sql: Rename: scripts/mysql_fix_privilege_tables.sql.in -> scripts/mysql_system_tables_fix.sql mysql-test/mysql-test-run.pl: - Add mysql_system_tables_data.sql as part of the bootstrap - Remove the addition of pid to end of bootstrap.sql, now to file used for bootstrap will be $opt_vardir/tmp/bootstrap.sql - Improve error message descibing how to find cause of a failed bootstrap scripts/Makefile.am: - Rename mysql_fix_privileg_tables.sql.in to mysql_system_tables_fix.sql - Build mysql_fix_privilege_tables from mysql_system_tables.sql and mysql_system_tables_fix.sql - Add mysql_system_tables_fix.sql to EXTRA_DIST scripts/mysql_install_db.sh: - Use mysql_system_tables_data.sql file when bootstrapping mysql, it will contain initial data for MysQL system tables scripts/mysql_system_tables.sql: Move initial data for system tables to it's own file scripts/mysql_system_tables_data.sql: Move initial data for system tables to it's own file --- scripts/Makefile.am | 16 +- scripts/mysql_fix_privilege_tables.sql.in | 394 ------------------------------ scripts/mysql_install_db.sh | 12 +- scripts/mysql_system_tables.sql | 10 - scripts/mysql_system_tables_data.sql | 13 + scripts/mysql_system_tables_fix.sql | 394 ++++++++++++++++++++++++++++++ 6 files changed, 427 insertions(+), 412 deletions(-) delete mode 100644 scripts/mysql_fix_privilege_tables.sql.in create mode 100644 scripts/mysql_system_tables_data.sql create mode 100644 scripts/mysql_system_tables_fix.sql (limited to 'scripts') diff --git a/scripts/Makefile.am b/scripts/Makefile.am index d1c3bd82050..cca81cd8818 100644 --- a/scripts/Makefile.am +++ b/scripts/Makefile.am @@ -65,15 +65,14 @@ EXTRA_DIST = $(EXTRA_SCRIPTS) \ mysqlaccess.conf \ mysqlbug \ make_win_bin_dist \ - mysql_fix_privilege_tables.sql.in + mysql_system_tables_fix.sql dist_pkgdata_DATA = fill_help_tables.sql \ mysql_fix_privilege_tables.sql \ mysql_system_tables.sql \ + mysql_system_tables_data.sql \ mysql_test_data_timezone.sql -# mysqlbug should be distributed built so that people can report build -# failures with it. CLEANFILES = @server_scripts@ \ make_binary_distribution \ make_sharedlib_distribution \ @@ -94,8 +93,11 @@ CLEANFILES = @server_scripts@ \ mysql_upgrade_shell \ mysqld_multi \ make_win_src_distribution \ - mysql_create_system_tables + mysql_create_system_tables \ + mysql_fix_privilege_tables.sql +# mysqlbug should be distributed built so that people can report build +# failures with it. DISTCLEANFILES = mysqlbug # We want the right version and configure comand line in mysqlbug @@ -104,10 +106,10 @@ mysqlbug: ${top_builddir}/config.status mysqlbug.sh # Build mysql_fix_privilege_tables.sql from the files that contain # the system tables for this version of MySQL plus any commands # needed to upgrade the system tables from an older version -mysql_fix_privilege_tables.sql: mysql_fix_privilege_tables.sql.in \ - mysql_system_tables.sql +mysql_fix_privilege_tables.sql: mysql_system_tables.sql \ + mysql_system_tables_fix.sql @echo "Building $@"; - @cat mysql_system_tables.sql $@.in > $@ + @cat mysql_system_tables.sql mysql_system_tables_fix.sql > $@ SUFFIXES = .sh diff --git a/scripts/mysql_fix_privilege_tables.sql.in b/scripts/mysql_fix_privilege_tables.sql.in deleted file mode 100644 index 205bc93efea..00000000000 --- a/scripts/mysql_fix_privilege_tables.sql.in +++ /dev/null @@ -1,394 +0,0 @@ -# This part converts any old privilege tables to privilege tables suitable -# for current version of MySQL - -# You can safely ignore all 'Duplicate column' and 'Unknown column' errors -# because these just mean that your tables are already up to date. -# This script is safe to run even if your tables are already up to date! - -# On unix, you should use the mysql_fix_privilege_tables script to execute -# this sql script. -# On windows you should do 'mysql --force mysql < mysql_fix_privilege_tables.sql' - -set storage_engine=MyISAM; - -ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; - -# Detect whether or not we had the Grant_priv column -SET @hadGrantPriv:=0; -SELECT @hadGrantPriv:=1 FROM user WHERE Grant_priv LIKE '%'; - -ALTER TABLE user add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; -ALTER TABLE host add Grant_priv enum('N','Y') NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; -ALTER TABLE db add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; - -# Fix privileges for old tables -UPDATE user SET Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; -UPDATE db SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; -UPDATE host SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; - -# -# The second alter changes ssl_type to new 4.0.2 format -# Adding columns needed by GRANT .. REQUIRE (openssl) - -ALTER TABLE user -ADD ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci 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; - -# -# tables_priv -# - -ALTER TABLE tables_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 -# - -# 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 - 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. -# - -ALTER TABLE func add type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL; - -# -# Change the user,db and host tables to current format -# - -# Detect whether we had Show_db_priv -SET @hadShowDbPriv:=0; -SELECT @hadShowDbPriv:=1 FROM user WHERE Show_db_priv LIKE '%'; - -ALTER TABLE user -ADD Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_priv, -ADD Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_db_priv, -ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Super_priv, -ADD Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_tmp_table_priv, -ADD Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv, -ADD Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Execute_priv, -ADD Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Repl_slave_priv; - -# Convert privileges so that users have similar privileges as before - -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<>"" AND @hadShowDbPriv = 0; - - -# 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 DEFAULT 0 AFTER x509_subject, -ADD max_updates int(11) unsigned NOT NULL DEFAULT 0 AFTER max_questions, -ADD max_connections int(11) unsigned NOT NULL DEFAULT 0 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') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, -ADD Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; -ALTER TABLE host -ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, -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 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'; - -# Convert all tables to UTF-8 with binary collation -# and reset all char columns to correct width -ALTER TABLE user - MODIFY Host char(60) NOT NULL default '', - MODIFY User char(16) NOT NULL default '', - ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; -ALTER TABLE user - MODIFY Password char(41) character set latin1 collate latin1_bin NOT NULL default '', - MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL; - -ALTER TABLE db - MODIFY Host char(60) NOT NULL default '', - MODIFY Db char(64) NOT NULL default '', - MODIFY User char(16) NOT NULL default '', - ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; -ALTER TABLE db - MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; - -ALTER TABLE host - MODIFY Host char(60) NOT NULL default '', - MODIFY Db char(64) NOT NULL default '', - ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; -ALTER TABLE host - MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, - MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; - -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; - -# -# Detect whether we had Create_view_priv -# -SET @hadCreateViewPriv:=0; -SELECT @hadCreateViewPriv:=1 FROM user WHERE Create_view_priv LIKE '%'; - -# -# Create VIEWs privileges (v5.0) -# -ALTER TABLE db ADD Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; -ALTER TABLE db MODIFY Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; - -ALTER TABLE host ADD Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; -ALTER TABLE host MODIFY Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; - -ALTER TABLE user ADD Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Repl_client_priv; -ALTER TABLE user MODIFY Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Repl_client_priv; - -# -# Show VIEWs privileges (v5.0) -# -ALTER TABLE db ADD Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; -ALTER TABLE db MODIFY Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; - -ALTER TABLE host ADD Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; -ALTER TABLE host MODIFY Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; - -ALTER TABLE user ADD Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; -ALTER TABLE user MODIFY Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; - -# -# 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; - -# -# -# -SET @hadCreateRoutinePriv:=0; -SELECT @hadCreateRoutinePriv:=1 FROM user WHERE Create_routine_priv LIKE '%'; - -# -# Create PROCEDUREs privileges (v5.0) -# -ALTER TABLE db ADD Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; -ALTER TABLE db MODIFY Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; - -ALTER TABLE host ADD Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; -ALTER TABLE host MODIFY Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; - -ALTER TABLE user ADD Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; -ALTER TABLE user MODIFY Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; - -# -# Alter PROCEDUREs privileges (v5.0) -# -ALTER TABLE db ADD Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; -ALTER TABLE db MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; - -ALTER TABLE host ADD Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; -ALTER TABLE host MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; - -ALTER TABLE user ADD Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; -ALTER TABLE user MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; - -ALTER TABLE db ADD Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; -ALTER TABLE db MODIFY Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; - -ALTER TABLE host ADD Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; -ALTER TABLE host MODIFY Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; - -# -# Assign create/alter routine privileges to people who have create privileges -# -UPDATE user SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv where user<>"" AND @hadCreateRoutinePriv = 0; -UPDATE db SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv, Execute_priv=Select_priv where user<>"" AND @hadCreateRoutinePriv = 0; -UPDATE host SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv, Execute_priv=Select_priv where @hadCreateRoutinePriv = 0; - -# -# Add max_user_connections resource limit -# -ALTER TABLE user ADD max_user_connections int(11) unsigned DEFAULT '0' NOT NULL AFTER max_connections; - -# -# user.Create_user_priv -# - -SET @hadCreateUserPriv:=0; -SELECT @hadCreateUserPriv:=1 FROM user WHERE Create_user_priv LIKE '%'; - -ALTER TABLE user ADD Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; -ALTER TABLE user MODIFY Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; -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'); - -# -# procs_priv -# - -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; - -# -# proc -# - -# Correct the name fields to not binary, and expand sql_data_access -ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL, - MODIFY specific_name char(64) DEFAULT '' NOT NULL, - MODIFY sql_data_access - enum('CONTAINS_SQL', - 'NO_SQL', - 'READS_SQL_DATA', - 'MODIFIES_SQL_DATA' - ) DEFAULT 'CONTAINS_SQL' NOT NULL, - MODIFY body longblob DEFAULT '' NOT NULL, - MODIFY sql_mode - set('REAL_AS_FLOAT', - 'PIPES_AS_CONCAT', - 'ANSI_QUOTES', - 'IGNORE_SPACE', - 'NOT_USED', - 'ONLY_FULL_GROUP_BY', - 'NO_UNSIGNED_SUBTRACTION', - 'NO_DIR_IN_CREATE', - 'POSTGRESQL', - 'ORACLE', - 'MSSQL', - 'DB2', - 'MAXDB', - 'NO_KEY_OPTIONS', - 'NO_TABLE_OPTIONS', - 'NO_FIELD_OPTIONS', - 'MYSQL323', - 'MYSQL40', - 'ANSI', - 'NO_AUTO_VALUE_ON_ZERO', - 'NO_BACKSLASH_ESCAPES', - 'STRICT_TRANS_TABLES', - 'STRICT_ALL_TABLES', - 'NO_ZERO_IN_DATE', - 'NO_ZERO_DATE', - 'INVALID_DATES', - 'ERROR_FOR_DIVISION_BY_ZERO', - 'TRADITIONAL', - 'NO_AUTO_CREATE_USER', - 'HIGH_NOT_PRECEDENCE' - ) DEFAULT '' NOT NULL, - DEFAULT CHARACTER SET utf8; - -# Correct the character set and collation -ALTER TABLE proc CONVERT TO CHARACTER SET utf8; -# Reset some fields after the conversion -ALTER TABLE proc MODIFY db - char(64) collate utf8_bin DEFAULT '' NOT NULL, - MODIFY definer - char(77) collate utf8_bin DEFAULT '' NOT NULL, - MODIFY comment - char(64) collate utf8_bin DEFAULT '' NOT NULL; - -# Activate the new, possible modified privilege tables -# This should not be needed, but gives us some extra testing that the above -# changes was correct - -flush privileges; diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh index ed5c8c97d6b..5dd5d86c666 100644 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@ -138,10 +138,12 @@ fi # Find SQL scripts needed for bootstrap fill_help_tables="fill_help_tables.sql" create_system_tables="mysql_system_tables.sql" +fill_system_tables="mysql_system_tables_data.sql" if test -n "$srcdir" then fill_help_tables=$srcdir/scripts/$fill_help_tables create_system_tables=$srcdir/scripts/$create_system_tables + fill_system_tables=$srcdir/scripts/$fill_system_tables else for i in $basedir/support-files $basedir/share $basedir/share/mysql \ $basedir/scripts `pwd` `pwd`/scripts @pkgdatadir@ @@ -154,6 +156,7 @@ else fill_help_tables=$pkgdatadir/$fill_help_tables create_system_tables=$pkgdatadir/$create_system_tables + fill_system_tables=$pkgdatadir/$fill_system_tables fi if test ! -f $create_system_tables @@ -170,6 +173,13 @@ then exit 1; fi +if test ! -f $fill_system_tables +then + echo "FATAL ERROR: Could not find help file '$fill_system_tables' in" + echo "@pkgdatadir@ or inside $basedir" + exit 1; +fi + # Find executables and paths mdata=$ldata/mysql mysqld=$execdir/mysqld @@ -273,7 +283,7 @@ mysqld_install_cmd_line="$mysqld_bootstrap $defaults $mysqld_opt --bootstrap \ # Pipe mysql_system_tables.sql to "mysqld --bootstrap" s_echo "Installing MySQL system tables..." -if `(echo "use mysql;"; cat $create_system_tables) | $mysqld_install_cmd_line` +if `(echo "use mysql;"; cat $create_system_tables $fill_system_tables) | $mysqld_install_cmd_line` then s_echo "OK" diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index 03a0b36b985..5f5aea20729 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -6,10 +6,6 @@ set storage_engine=myisam; CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; --- default grants for anyone to access database 'test' and 'test_%' -INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N'); -INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N'); - CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; @@ -57,9 +53,3 @@ CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NU 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) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; --- default users allowing root access from local machine -INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); -REPLACE INTO user VALUES (@@hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); -REPLACE INTO user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); - - diff --git a/scripts/mysql_system_tables_data.sql b/scripts/mysql_system_tables_data.sql new file mode 100644 index 00000000000..a9c816f2896 --- /dev/null +++ b/scripts/mysql_system_tables_data.sql @@ -0,0 +1,13 @@ +-- +-- The inital data for system tables of MySQL Server +-- + +-- default grants for anyone to access database 'test' and 'test_%' +INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N'); +INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N'); + +-- default users allowing root access from local machine +INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); +REPLACE INTO user VALUES (@@hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); +REPLACE INTO user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); + diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql new file mode 100644 index 00000000000..205bc93efea --- /dev/null +++ b/scripts/mysql_system_tables_fix.sql @@ -0,0 +1,394 @@ +# This part converts any old privilege tables to privilege tables suitable +# for current version of MySQL + +# You can safely ignore all 'Duplicate column' and 'Unknown column' errors +# because these just mean that your tables are already up to date. +# This script is safe to run even if your tables are already up to date! + +# On unix, you should use the mysql_fix_privilege_tables script to execute +# this sql script. +# On windows you should do 'mysql --force mysql < mysql_fix_privilege_tables.sql' + +set storage_engine=MyISAM; + +ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; + +# Detect whether or not we had the Grant_priv column +SET @hadGrantPriv:=0; +SELECT @hadGrantPriv:=1 FROM user WHERE Grant_priv LIKE '%'; + +ALTER TABLE user add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; +ALTER TABLE host add Grant_priv enum('N','Y') NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; +ALTER TABLE db add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; + +# Fix privileges for old tables +UPDATE user SET Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; +UPDATE db SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; +UPDATE host SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; + +# +# The second alter changes ssl_type to new 4.0.2 format +# Adding columns needed by GRANT .. REQUIRE (openssl) + +ALTER TABLE user +ADD ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci 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; + +# +# tables_priv +# + +ALTER TABLE tables_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 +# + +# 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 + 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. +# + +ALTER TABLE func add type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL; + +# +# Change the user,db and host tables to current format +# + +# Detect whether we had Show_db_priv +SET @hadShowDbPriv:=0; +SELECT @hadShowDbPriv:=1 FROM user WHERE Show_db_priv LIKE '%'; + +ALTER TABLE user +ADD Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_priv, +ADD Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_db_priv, +ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Super_priv, +ADD Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_tmp_table_priv, +ADD Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv, +ADD Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Execute_priv, +ADD Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Repl_slave_priv; + +# Convert privileges so that users have similar privileges as before + +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<>"" AND @hadShowDbPriv = 0; + + +# 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 DEFAULT 0 AFTER x509_subject, +ADD max_updates int(11) unsigned NOT NULL DEFAULT 0 AFTER max_questions, +ADD max_connections int(11) unsigned NOT NULL DEFAULT 0 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') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, +ADD Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; +ALTER TABLE host +ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, +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 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'; + +# Convert all tables to UTF-8 with binary collation +# and reset all char columns to correct width +ALTER TABLE user + MODIFY Host char(60) NOT NULL default '', + MODIFY User char(16) NOT NULL default '', + ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; +ALTER TABLE user + MODIFY Password char(41) character set latin1 collate latin1_bin NOT NULL default '', + MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL; + +ALTER TABLE db + MODIFY Host char(60) NOT NULL default '', + MODIFY Db char(64) NOT NULL default '', + MODIFY User char(16) NOT NULL default '', + ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; +ALTER TABLE db + MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; + +ALTER TABLE host + MODIFY Host char(60) NOT NULL default '', + MODIFY Db char(64) NOT NULL default '', + ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; +ALTER TABLE host + MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; + +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; + +# +# Detect whether we had Create_view_priv +# +SET @hadCreateViewPriv:=0; +SELECT @hadCreateViewPriv:=1 FROM user WHERE Create_view_priv LIKE '%'; + +# +# Create VIEWs privileges (v5.0) +# +ALTER TABLE db ADD Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; +ALTER TABLE db MODIFY Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; + +ALTER TABLE host ADD Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; +ALTER TABLE host MODIFY Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; + +ALTER TABLE user ADD Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Repl_client_priv; +ALTER TABLE user MODIFY Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Repl_client_priv; + +# +# Show VIEWs privileges (v5.0) +# +ALTER TABLE db ADD Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; +ALTER TABLE db MODIFY Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; + +ALTER TABLE host ADD Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; +ALTER TABLE host MODIFY Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; + +ALTER TABLE user ADD Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; +ALTER TABLE user MODIFY Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; + +# +# 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; + +# +# +# +SET @hadCreateRoutinePriv:=0; +SELECT @hadCreateRoutinePriv:=1 FROM user WHERE Create_routine_priv LIKE '%'; + +# +# Create PROCEDUREs privileges (v5.0) +# +ALTER TABLE db ADD Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; +ALTER TABLE db MODIFY Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; + +ALTER TABLE host ADD Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; +ALTER TABLE host MODIFY Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; + +ALTER TABLE user ADD Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; +ALTER TABLE user MODIFY Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; + +# +# Alter PROCEDUREs privileges (v5.0) +# +ALTER TABLE db ADD Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; +ALTER TABLE db MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; + +ALTER TABLE host ADD Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; +ALTER TABLE host MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; + +ALTER TABLE user ADD Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; +ALTER TABLE user MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; + +ALTER TABLE db ADD Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; +ALTER TABLE db MODIFY Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; + +ALTER TABLE host ADD Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; +ALTER TABLE host MODIFY Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; + +# +# Assign create/alter routine privileges to people who have create privileges +# +UPDATE user SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv where user<>"" AND @hadCreateRoutinePriv = 0; +UPDATE db SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv, Execute_priv=Select_priv where user<>"" AND @hadCreateRoutinePriv = 0; +UPDATE host SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv, Execute_priv=Select_priv where @hadCreateRoutinePriv = 0; + +# +# Add max_user_connections resource limit +# +ALTER TABLE user ADD max_user_connections int(11) unsigned DEFAULT '0' NOT NULL AFTER max_connections; + +# +# user.Create_user_priv +# + +SET @hadCreateUserPriv:=0; +SELECT @hadCreateUserPriv:=1 FROM user WHERE Create_user_priv LIKE '%'; + +ALTER TABLE user ADD Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; +ALTER TABLE user MODIFY Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; +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'); + +# +# procs_priv +# + +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; + +# +# proc +# + +# Correct the name fields to not binary, and expand sql_data_access +ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL, + MODIFY specific_name char(64) DEFAULT '' NOT NULL, + MODIFY sql_data_access + enum('CONTAINS_SQL', + 'NO_SQL', + 'READS_SQL_DATA', + 'MODIFIES_SQL_DATA' + ) DEFAULT 'CONTAINS_SQL' NOT NULL, + MODIFY body longblob DEFAULT '' NOT NULL, + MODIFY sql_mode + set('REAL_AS_FLOAT', + 'PIPES_AS_CONCAT', + 'ANSI_QUOTES', + 'IGNORE_SPACE', + 'NOT_USED', + 'ONLY_FULL_GROUP_BY', + 'NO_UNSIGNED_SUBTRACTION', + 'NO_DIR_IN_CREATE', + 'POSTGRESQL', + 'ORACLE', + 'MSSQL', + 'DB2', + 'MAXDB', + 'NO_KEY_OPTIONS', + 'NO_TABLE_OPTIONS', + 'NO_FIELD_OPTIONS', + 'MYSQL323', + 'MYSQL40', + 'ANSI', + 'NO_AUTO_VALUE_ON_ZERO', + 'NO_BACKSLASH_ESCAPES', + 'STRICT_TRANS_TABLES', + 'STRICT_ALL_TABLES', + 'NO_ZERO_IN_DATE', + 'NO_ZERO_DATE', + 'INVALID_DATES', + 'ERROR_FOR_DIVISION_BY_ZERO', + 'TRADITIONAL', + 'NO_AUTO_CREATE_USER', + 'HIGH_NOT_PRECEDENCE' + ) DEFAULT '' NOT NULL, + DEFAULT CHARACTER SET utf8; + +# Correct the character set and collation +ALTER TABLE proc CONVERT TO CHARACTER SET utf8; +# Reset some fields after the conversion +ALTER TABLE proc MODIFY db + char(64) collate utf8_bin DEFAULT '' NOT NULL, + MODIFY definer + char(77) collate utf8_bin DEFAULT '' NOT NULL, + MODIFY comment + char(64) collate utf8_bin DEFAULT '' NOT NULL; + +# Activate the new, possible modified privilege tables +# This should not be needed, but gives us some extra testing that the above +# changes was correct + +flush privileges; -- cgit v1.2.1