No mundo do gerenciamento de banco de dados, controlar o acesso do usuário é crucial para manter a integridade e a segurança dos dados. Esta postagem do blog irá guiá-lo por um cenário real de configuração de permissões de usuário do MySQL, incluindo o processo, possíveis armadilhas e etapas de depuração.

O Cenário

Imagine que você é administrador de banco de dados de uma empresa que possui vários bancos de dados:

• Bancos de dados originais: produtos, clientes, pedidos, análises
• Copiar bancos de dados: products_copy, clients_copy, order_copy, analytics_copy

Sua tarefa é configurar permissões para um usuário chamado 'analista' com os seguintes requisitos:

1. O analista deve ser capaz de visualizar bancos de dados e tabelas dos bancos de dados originais.
2. O analista não deve ser capaz de editar os bancos de dados originais.
3. O analista não deve ser capaz de criar novos bancos de dados.
4. O analista deve ter acesso total (visualizar, editar, excluir, criar tabelas) aos bancos de dados '_copy'.

Vamos ver como podemos conseguir isso usando as instruções GRANT e REVOKE do MySQL.

Etapa 1: configuração inicial

Primeiro, precisamos nos conectar ao servidor MySQL com uma conta administrativa:

mysql -h hostname -P port -u admin -p

Substitua 'hostname', 'port' e 'admin' pelos detalhes reais do servidor e nome de usuário do administrador.

Etapa 2: crie o usuário

Se o usuário ainda não existir, precisamos criá-lo:

CREATE USER 'analyst'@'%' IDENTIFIED BY 'password';

Substitua 'senha' por uma senha forte e segura.

Etapa 3: conceder as permissões necessárias

Agora, vamos conceder as permissões necessárias:

-- Grant SELECT on original databases
GRANT SELECT ON products.* TO 'analyst'@'%';
GRANT SELECT ON customers.* TO 'analyst'@'%';
GRANT SELECT ON orders.* TO 'analyst'@'%';
GRANT SELECT ON analytics.* TO 'analyst'@'%';

-- Grant all privileges on copy databases
GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';

-- Grant global privileges
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

Etapa 4: verificar as permissões

Depois de configurar as permissões, é crucial verificá-las:

SHOW GRANTS FOR 'analyst'@'%';

Depuração e solução de problemas

Problema 1: Muitos privilégios

Em nosso cenário, inicialmente encontramos um problema em que o 'analista' tinha muitos privilégios:

mysql> SHOW GRANTS FOR 'analyst'@'%';
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Grants for analyst@%                                                                                                                                                                                                                                                                                                              |
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO "analyst"@"%" WITH GRANT OPTION |
| GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "analyst"@"%" WITH GRANT OPTION                                                                                                                                                                                                                                                    |
...
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

Solução:

Para corrigir isso, revogamos todos os privilégios e concedemos apenas os necessários:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'analyst'@'%';
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';
-- Then re-grant the specific permissions as shown in Step 3

Problema 2: Permissões ausentes em bancos de dados de cópia

Depois de corrigir os privilégios excessivos, notamos que faltavam as permissões para os bancos de dados de cópia:

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
 ----------------------------------------------------- 

Solução:

Adicionamos as concessões ausentes para os bancos de dados de cópia:

GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

Resultado Final

Depois de aplicar todas essas alterações e correções, as concessões finais deverão ficar assim:

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
| GRANT ALL PRIVILEGES ON "products_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "customers_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "orders_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "analytics_copy".* TO "analyst"@"%"|
 ----------------------------------------------------- 

Conclusão

Configurar permissões de usuário MySQL adequadas pode ser complicado, mas é um aspecto crucial do gerenciamento de banco de dados. Usando cuidadosamente as instruções GRANT e REVOKE e sempre verificando os resultados, você pode criar um ambiente seguro e funcional para seus usuários.

Lembre-se destes pontos-chave:

1. Sempre comece com o princípio do menor privilégio.
2. Use SHOW GRANTS para verificar as permissões após fazer alterações.
3. Tenha cuidado com privilégios globais (ON .).
4. Não se esqueça de FLUSH PRIVILEGES após fazer alterações.

Seguindo essas diretrizes e as etapas descritas nesta postagem, você estará bem equipado para gerenciar as permissões de usuário do MySQL de maneira eficaz.