drop procedure if exists setup_grants;

delimiter $
create procedure setup_grants(p_num_users bigint,p_num_schemas bigint)
begin
  declare i_user bigint default 0;
  declare i_schema bigint default 0;
  declare time_start decimal(20,6) default 0;
  declare time_end decimal(20,6) default 0;
  select 'starting drop/create users',now();
  repeat
      set @q1:=concat('drop user if exists \'user',i_user,'\'@\'%\'');
      set @q2:=concat('create user if not exists \'user',i_user,'\'@\'%\'');
--      select @q1;
--      select @q2;  
      prepare s from @q1;
      execute s;
      prepare s from @q2;
      execute s;
      set i_user:=i_user+1;
  until i_user>p_num_users end repeat;

  select 'done with creating users',now();
  select 'starting drop/create schemas',now(); 
  repeat
    -- select i_schema,now();
    set @q3:=concat('drop database if exists `schema',i_schema,'`');
    set @q4:=concat('create database if not exists `schema',i_schema,'`');
    prepare s from @q3;
    execute s;
    prepare s from @q4;
    execute s;
    set i_schema:=i_schema+1;
  until i_schema>=p_num_schemas end repeat;
  select 'done with creating schemas',now();
  
  set i_user:=0;
  set i_schema:=0;
  select 'starting with grants of each user to each schema',now();
  repeat
    set i_user:=0;
    set time_start:=unix_timestamp(now(6));
    start transaction;
    repeat
      set @q5:=concat('grant select,update,insert,delete,show view,create temporary tables on `schema',i_schema,'`.* to \'user',i_user,'\'@\'%\'');
      -- select @q5;
      prepare s from @q5;
      execute s;
      set i_user:=i_user+1;
    until i_user>p_num_users end repeat;
    commit;
    set time_end:=unix_timestamp(now(6));
    
    select concat('schema',i_schema,' took ',time_end - time_start, ' seconds to grant privs')as 'status';
    
    set i_schema:=i_schema+1;
  until i_schema>p_num_schemas end repeat;
  select 'done with granting privs',now();
  
  
end $

delimiter ;

call setup_grants(/*users*/600,/*schemas*/600);

show grants for user1@'%';