MySQL - Grants

Queries para exportar os grants de um determinado usuário:

set @usuario='\'nome_usuario\'@\'ip_usuario\'';

--criação usuário
select concat('GRANT USAGE ON *.* TO \'',user,'\'@\'',host,'\' identified by password \'',password,'\';') from mysql.user where @usuario=concat('\'',user,'\'@\'',host,'\'');

-- user_privileges
select concat('grant ',PRIVILEGE_TYPE,' on *.* to ',@usuario,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.user_privileges where GRANTEE = @usuario;

-- schema_privileges
select concat('grant ',PRIVILEGE_TYPE,' on ',TABLE_SCHEMA,'.* to ',@usuario,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.schema_privileges where GRANTEE = @usuario;

-- table_privileges
select concat('grant ',PRIVILEGE_TYPE,' on ',TABLE_SCHEMA,'.',TABLE_NAME,' to ',@usuario,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.table_privileges where GRANTEE = @usuario;

Queries para exportar os grants de todos os usuários:

--criação usuário

select concat('GRANT USAGE ON *.* TO \'',user,'\'@\'',host,'\' identified by password \'',password,'\';') from mysql.user;

-- user_privileges
select concat('grant ',PRIVILEGE_TYPE,' on *.* to ',GRANTEE,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.user_privileges;

-- schema_privileges
select concat('grant ',PRIVILEGE_TYPE,' on ',TABLE_SCHEMA,'.* to ',GRANTEE,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.schema_privileges;

-- table_privileges
select concat('grant ',PRIVILEGE_TYPE,' on ',TABLE_SCHEMA,'.',TABLE_NAME,' to ',GRANTEE,CASE IS_GRANTABLE WHEN 'YES' THEN ' with grant option;' WHEN 'NO' THEN ';' ELSE ';' END) from information_schema.table_privileges;