release/src/router/mysql/netware/mysql_fix_privilege_tables.pl

   1 #-----------------------------------------------------------------------------
   2 # Copyright (c) 2002-2004 MySQL AB
   3 #
   4 # This program is free software; you can redistribute it and/or modify
   5 # it under the terms of the GNU General Public License as published by
   6 # the Free Software Foundation; version 2 of the License.
   7 #
   8 # This program is distributed in the hope that it will be useful,
   9 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  10 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  11 # GNU General Public License for more details.
  12 #
  13 # You should have received a copy of the GNU General Public License
  14 # along with this program; if not, write to the Free Software
  15 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
  16 #-----------------------------------------------------------------------------
  17
  18 #-----------------------------------------------------------------------------
  19 # This notice applies to changes, created by or for Novell, Inc.,
  20 # to preexisting works for which notices appear elsewhere in this file.
  21
  22 # Copyright (c) 2003 Novell, Inc. All Rights Reserved.
  23
  24 # This program is free software; you can redistribute it and/or modify
  25 # it under the terms of the GNU General Public License as published by
  26 # the Free Software Foundation; either version 2 of the License, or
  27 # (at your option) any later version.
  28
  29 # This program is distributed in the hope that it will be useful,
  30 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  31 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  32 # GNU General Public License for more details.
  33
  34 # You should have received a copy of the GNU General Public License
  35 # along with this program; if not, write to the Free Software
  36 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
  37 #-----------------------------------------------------------------------------
  38
  39 #use strict;
  40 use Mysql;
  41
  42 print "MySQL Fix Privilege Tables Script\n\n";
  43
  44 print "NOTE: This script updates your privilege tables to the lastest\n";
  45 print "      specifications!\n\n";
  46
  47 #-----------------------------------------------------------------------------
  48 # get the current root password
  49 #-----------------------------------------------------------------------------
  50
  51 print "In order to log into MySQL to update it, we'll need the current\n";
  52 print "password for the root user.  If you've just installed MySQL, and\n";
  53 print "you haven't set the root password yet, the password will be blank,\n";
  54 print "so you should just press enter here.\n\n";
  55
  56 print "Enter the current password for root: ";
  57 my $password = <STDIN>;
  58 chomp $password;
  59 print "\n";
  60
  61 my $conn = Mysql->connect("localhost", "mysql", "root", $password)
  62   || die "Unable to connect to MySQL.";
  63
  64 print "OK, successfully used the password, moving on...\n\n";
  65
  66
  67 #-----------------------------------------------------------------------------
  68 # MySQL 4.0.2
  69 #-----------------------------------------------------------------------------
  70
  71 #-- Detect whether or not we had the Grant_priv column
  72 print "Fixing privileges for old tables...\n";
  73 $conn->query("SET \@hadGrantPriv:=0;");
  74 $conn->query("SELECT \@hadGrantPriv:=1 FROM user WHERE Grant_priv LIKE '%';");
  75
  76 #--- Fix privileges for old tables
  77 $conn->query("UPDATE user SET Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE \@hadGrantPriv = 0;");
  78 $conn->query("UPDATE db SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE \@hadGrantPriv = 0;");
  79 $conn->query("UPDATE host SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE \@hadGrantPriv = 0;");
  80
  81
  82 # Detect whether we had Show_db_priv
  83 $conn->query("SET \@hadShowDbPriv:=0;");
  84 $conn->query("SELECT \@hadShowDbPriv:=1 FROM user WHERE Show_db_priv LIKE '%';");
  85
  86 print "Adding new fields used by MySQL 4.0.2 to the privilege tables...\n";
  87 print "NOTE: You can ignore any Duplicate column errors.\n";
  88 $conn->query(" \
  89 ALTER TABLE user \
  90 ADD Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER alter_priv, \
  91 ADD Super_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Show_db_priv, \
  92 ADD Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Super_priv, \
  93 ADD Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Create_tmp_table_priv, \
  94 ADD Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Lock_tables_priv, \
  95 ADD Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Execute_priv, \
  96 ADD Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Repl_slave_priv; \
  97 ") && $conn->query(" \
  98 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; \
  99 ");
 100
 101 #-- The above statement converts privileges so that users have similar privileges as before
 102
 103 #-----------------------------------------------------------------------------
 104 # MySQL 4.0 Limitations
 105 #-----------------------------------------------------------------------------
 106
 107 print "Adding new fields used by MySQL 4.0 security limitations...\n";
 108
 109 $conn->query(" \
 110 ALTER TABLE user \
 111 ADD max_questions int(11) NOT NULL AFTER x509_subject, \
 112 ADD max_updates   int(11) unsigned NOT NULL AFTER max_questions, \
 113 ADD max_connections int(11) unsigned NOT NULL AFTER max_updates; \
 114 ");
 115
 116 #-- Change the password column to suite the new password hashing used
 117 #-- in 4.1.1 onward
 118 $conn->query("ALTER TABLE user change Password Password char(41) binary not null;");
 119
 120 #-- The second alter changes ssl_type to new 4.0.2 format
 121 #-- Adding columns needed by GRANT .. REQUIRE (openssl)"
 122 print "Adding new fields to use in ssl authentication...\n";
 123
 124 $conn->query(" \
 125 ALTER TABLE user \
 126 ADD ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL, \
 127 ADD ssl_cipher BLOB NOT NULL, \
 128 ADD x509_issuer BLOB NOT NULL, \
 129 ADD x509_subject BLOB NOT NULL; \
 130 ");
 131
 132 #-----------------------------------------------------------------------------
 133 # MySQL 4.0 DB and Host privs
 134 #-----------------------------------------------------------------------------
 135
 136 print "Adding new fields used by MySQL 4.0 locking and temporary table security...\n";
 137
 138 $conn->query(" \
 139 ALTER TABLE db \
 140 ADD Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL, \
 141 ADD Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL; \
 142 ");
 143
 144 $conn->query(" \
 145 ALTER TABLE host \
 146 ADD Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL, \
 147 ADD Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL; \
 148 ");
 149
 150 #
 151 # Change the Table_name column to be of char(64) which was char(60) by mistake till now.
 152 #
 153 $conn->query("alter table tables_priv change Table_name Table_name char(64) binary DEFAULT '' NOT NULL;");
 154
 155
 156 #
 157 # Create some possible missing tables
 158 #
 159 print "Adding online help tables...\n";
 160
 161 $conn->query(" \
 162 CREATE TABLE IF NOT EXISTS help_topic ( \
 163 help_topic_id int unsigned not null, \
 164 name varchar(64) not null, \
 165 help_category_id smallint unsigned not null, \
 166 description text not null, \
 167 example text not null, \
 168 url varchar(128) not null, \
 169 primary key (help_topic_id), unique index (name) \
 170 ) comment='help topics'; \
 171 ");
 172
 173 $conn->query(" \
 174 CREATE TABLE IF NOT EXISTS help_category ( \
 175 help_category_id smallint unsigned not null, \
 176 name varchar(64) not null, \
 177 parent_category_id smallint unsigned null, \
 178 url varchar(128) not null, \
 179 primary key (help_category_id), \
 180 unique index (name) \
 181 ) comment='help categories'; \
 182 ");
 183
 184 $conn->query(" \
 185 CREATE TABLE IF NOT EXISTS help_relation ( \
 186 help_topic_id int unsigned not null references help_topic, \
 187 help_keyword_id  int unsigned not null references help_keyword, \
 188 primary key (help_keyword_id, help_topic_id) \
 189 ) comment='keyword-topic relation'; \
 190 ");
 191
 192 $conn->query(" \
 193 CREATE TABLE IF NOT EXISTS help_keyword ( \
 194 help_keyword_id int unsigned not null, \
 195 name varchar(64) not null, \
 196 primary key (help_keyword_id), \
 197 unique index (name) \
 198 ) comment='help keywords'; \
 199 ");
 200
 201
 202 #
 203 # Filling the help tables with contents.
 204 #
 205 print "Filling online help tables with contents...\n";
 206 # Generate the path for "fill_help_tables.sql" file which is in different folder.
 207 $fill_help_table=$0;
 208 $fill_help_table =~ s/scripts[\\\/]mysql_fix_privilege_tables.pl/share\\fill_help_tables.sql/;
 209
 210 #read all content from the sql file which contains recordsfor help tables.
 211 open(fileIN,$fill_help_table) or die("Cannot open $fill_help_table: $!");
 212 @logData = <fileIN>;
 213 close(fileIN);
 214 foreach $line (@logData) {
 215 # if the line is not empty, insert a record in the table.
 216     if( ! ($line =~ /^\s*$/) ) {
 217         $conn->query("$line");
 218     }
 219 }
 220
 221 #-----------------------------------------------------------------------------
 222 # done
 223 #-----------------------------------------------------------------------------
 224
 225 print "\n\nAll done!\n\n";
 226
 227 print "Thanks for using MySQL!\n\n";