Rename Table, 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) 
)
begin
  declare exit handler for sqlexception
    begin
      get diagnostics condition 1
      @sqlstate = returned_sqlstate, @sqlmessage = message_text;
      select @sqlstate state, @sqlmessage message;
    end;
  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';
end;
go
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.

Last updated 18 Sep 2013


Return to the Artful MySQL Tips page