Rename Table and user privileges

from the Artful MySQL Tips List

The Rename Table command in MySQL does not update user privileges for the renamed table and its columns (in SQL Server it does, in Oracle it does not). Here is a simple MySQL procedure that does so.

drop procedure if exists rename_table;
delimiter go
create procedure rename_table( 
  dbold varchar(64), tblold varchar(64), 
  dbnew varchar(64), tblnew varchar(64) 
  declare exit handler for sqlexception
      get diagnostics condition 1
      @sqlstate = returned_sqlstate, @sqlmessage = message_text;
      select @sqlstate state, @sqlmessage message;
  set @sql = concat( 'rename table ', '`', dbold, '`.`', tblold, 
                     '` to `', dbnew, '`.`', tblnew,'`');
  -- select @sql;
  prepare stmt from @sql;
  execute stmt;
  drop prepare stmt;
  select 'Rename complete' as 'Rename result';
  update mysql.tables_priv 
    set db=dbnew,table_name=tblnew 
    where db=dbold and table_name=tblold;
  select concat( 'Updated ', row_count(), ' mysql.tables_priv rows.' ) 
    as 'tables_priv result';
  update mysql.columns_priv 
    set db=dbnew,table_name=tblnew 
    where db=dbold and table_name=tblold;
  select concat( 'Updated ', row_count(), ' mysql.columns_priv rows.' ) 
    as 'columns_priv result';
  flush privileges;
  select 'Privileges flushed' as 'Final result';
delimiter ;

Here's a simple test case for the procedure:

drop table if exists test.t, test.t9;
create table test.t(i int);
show create table test.t;
call rename_table( 'test', 't', 'test', 't9' );
show create table test.t9;

Remember that any stored routine that references the table by name needs to be updated.

Return to the Artful MySQL Tips page