MySQL melhores práticas de segurança

Palestra de melhores práticas de segurança em MySQL ministrada na universidade Feevale Novo Hamburgo - Software Freedom Day 2017

Transcript

1. © 2017 Percona 1 Marcelo Altmann MySQL: Melhores Práticas de Segurança Senior Support Engineer Dia da Liberdade do Software - Novo Hamburgo - RS 16/09/2017
2. © 2017 Percona 2 Agenda
3. © 2017 Percona 3 Agenda ▪ Intro ▪ Segurança no SO ▪ Segurança na aplicação ▪ Setup Inicial ▪ SSL ▪ ACL ▪ Passwords ▪ Connection Control ▪ TDE ▪Audit Plugin ▪Proxy / Firewall
4. © 2017 Percona 4 Sobre Mim
5. © 2017 Percona 5 Marcelo Altmann ▪Engenheiro de Suporte Senior @ percona • MySQL DBA @ IEDR (CCTLD Irlanda) ▪Oracle ACE Associate ▪Certificaçoes •Oracle Certified Professional, MySQL 5.6 Database Administrator •Oracle Certified Professional, MySQL 5.6 Developer •Oracle Certified Professional, MySQL 5 Database Administrator •Oracle Certified Professional, MySQL 5 Developer •Oracle Certified Associate, MySQL 5.0/5.1/5.5 ▪blog.marceloaltmann.com
6. © 2017 Percona 6 Intro
7. © 2017 Percona 7 Intro ▪ Se eles querem os teus dados, eles vão ter. Tudo é uma questão de tempo, recursos, dinheiro e empenho ▪ ELES - Hackers, Competidores, Ex-funcinários, NSA. ▪ Segurança tem um preço •Dinheiro •Performance •Usabilidade
8. © 2017 Percona 8 Intro - principios básicos ▪ Bloqueio por padrão ▪ Mínimo acesso possível ▪ Minimizar possibilidades ▪ Isolar ▪ Auditar
9. © 2017 Percona 9 Segurança no SO
10. © 2017 Percona 10 Segurança no SO ▪ Desinstalar serviços não utilizados ▪ Não rode compiladores ▪ Firewall - De preferencia externo ▪Desligar outbound para internet ▪Mater o sistema sempre atualizado ▪Adicionar KEY no SSH ▪Não habilitar root logar remoto
11. © 2017 Percona 11 Segurança no SO ▪SELinux •grep mysqld /var/log/audit/audit.log | grep denied ▪yum install policycoreutils-python •grep mysqld /var/log/audit/audit.log | audit2allow •grep mysqld /var/log/audit/audit.log | audit2why
12. © 2017 Percona 12 Segurança no SO ▪Alterar datadir •chcon -R -t mysqld_db_t /mysql/ •semanage fcontext -a -t mysqld_db_t "/mysql(/.*)?" •restorecon -Rv /mysql/ ▪Alterar porta •semanage port -a -t mysqld_port_t -p tcp 3307
13. © 2017 Percona 13 Segurança na aplicação
14. © 2017 Percona 14 Segurança na aplicação ▪Validar todos inputs ▪Não estorne informações (recuperar senha / login) ▪Não conecte como root. ▪Cada app deve ter seu usuário ▪Criptografe informações sensitivas •AES_ENCRYPT() e AES_DECRYPT() •SHA1() e MD5()
15. © 2017 Percona 15 Segurança na aplicação ▪SQL Injection •$query = “SELECT * FROM users WHERE name = ‘“ . $name .”’” •$name = marcelo’ OR ‘a’=’a •SELECT * FROM users WHERE name = ‘marcelo’ OR ‘a’=’a’; ▪Prepared Statements •$stm = $db->prepare(“SELECT * FROM users WHERE name = ?”) •$stm->bind_param(“s”, $name) •$stm->execute()
16. © 2017 Percona 16 Setup inicial
17. © 2017 Percona 17 Setup inicial ▪Conta anônima e test database ▪Conectar sem usuário/senha ▪Todos os privilégios no banco test
18. © 2017 Percona 18 Setup inicial mysql> CREATE TABLE tb1 ( ID INT AUTO_INCREMENT PRIMARY KEY, a bigint); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO tb1 VALUES (NULL, RAND(99999)); mysql> INSERT INTO tb1 SELECT NULL, RAND(99999) FROM tb1; mysql> INSERT INTO tb1 SELECT NULL, RAND(99999) FROM tb1; mysql> INSERT INTO tb1 SELECT NULL, RAND(99999) FROM tb1; mysql> INSERT INTO tb1 SELECT NULL, RAND(99999) FROM tb1; mysql> INSERT INTO tb1 SELECT NULL, RAND(99999) FROM tb1;
19. © 2017 Percona 19 Setup inicial ▪Conta anônima / uso de memória mysql> use information_schema; mysql> SELECT * FROM COLLATIONS a, COLLATIONS b, COLLATIONS c, COLLATIONS d, COLLATIONS e, COLLATIONS f, COLLATIONS g; Killed > dmesg [ 7392.378967] Out of memory: Kill process 30278 (mysql) score 702 or sacrifice child [ 7392.380223] Killed process 30278 (mysql) total-vm:1662332kB, anon- rss:424576kB, file-rss:0kB, shmem-rss:0kB
20. © 2017 Percona 20 Setup inicial ▪mysql_secure_installation •DROP DATABASE test; •DROP USER ''@'localhost'; •DROP USER ''@'HOSTNAME'; • SET PASSWORD FOR root@IP
21. © 2017 Percona 21 SSL
22. © 2017 Percona 22 SSL ▪Por padrão informações trafegam abertas ▪SSL criptografa os dados trafegados ▪Padrão 5.7 ▪Gerar certificados •5.7 - mysql_ssl_rsa_setup •5.6 - openssl - https://goo.gl/5T7zqF
23. © 2017 Percona 23 SSL ▪Instalação [mysqld] ssl-ca=ca.pem ssl-cert=server-cert.pem ssl-key=server-key.pem ▪Conectar mysql --ssl-ca=ca.pem \ --ssl-cert=client-cert.pem \ --ssl-key=client-key.pem
24. © 2017 Percona 24 SSL ▪Forcar usuário a usar SSL CREATE USER 'marcelo'@'localhost' REQUIRE SSL; ▪Forçar todos os usuários a usarem uma conexão segura •Socket / namedpipe •SSL [mysqld] require_secure_transport
25. © 2017 Percona 25 ACL
26. © 2017 Percona 26 ACL ▪GRANT [privilégio] ON [banco].[tabela] TO [usuario]@[host] •GRANT SELECT(campo) ON [banco].[tabela] TO [usuario]@[host] ▪Não adicione GRANT ALL ON *.* ▪Nao adicione % como host ▪Limite os usuários com WITH GRANT OPTION ▪Cuidado com SUPER e PROCESS •SUPER - max_allowed_connections + 1 •PROCESS - SHOW PROCESSLIST
27. © 2017 Percona 27 Passwords
28. © 2017 Percona 28 Password ▪Crie passwords fortes •Letras Maiusculas e Minusculas •Mínimo de 8 characters •Possuir dígitos •Possuir acentos • Não utilize “dictionary” passwords
29. © 2017 Percona 29 Password ▪Password Validation Plugin 5.6+ [mysqld] plugin-load-add=validate_password.so validate-password=FORCE_PLUS_PERMANENT ▪validate_password_length (Default 8) ▪validate_password_mixed_case_count (Default 1) ▪validate_password_number_count (Default 1) ▪validate_password_special_char_count (Default 1) ▪validate_password_dictionary_file ▪validate_password_policy (Default 1)
30. © 2017 Percona 30 Password ▪Mysql_native_password ▪Hash de 41 characters ▪Mesmo hash todos mysql ▪Pode ser hackeado via Rainbow table ▪Hash é enviado aberto pela rede
31. © 2017 Percona 31 Password ▪Sha256_password (built-in 5.6+) ▪Hash é calculado com um salt ▪Mesmo password gera um hash diferente toda vez ▪Password é enviado ou por SSL ou criptografado(RSA)
32. © 2017 Percona 32 Password ▪Configurar cd /var/lib/mysql openssl genrsa -out private_key.pem 2048 openssl rsa -in private_key.pem \ -pubout -out public_key.pem chmod 400 private_key.pem; chmod 444 public_key.pem
33. © 2017 Percona 33 Password ▪Reiniciar MySQL SHOW STATUS LIKE 'Rsa_public_key'\G *************************** 1. row *************************** Variable_name: Rsa_public_key Value: -----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAt3ioXKWcNMUfBmHrc4JC WxXBhi/c+UXXRlV5lZcq2rr2fDIRPNhJy/ctOKvVSW99MF2Y92TUlxNN8WK9J96P X1UjGM3zRuqpd9DH70yYIzte50HHQQ0KYSRdNIiGxXIT4t80bqC3dIZdlRfEiUk5 swJcREzIy96W5ewmhiaOnIh/WPAKKS+a9GgPj+Gb0BDRewLaROiRaZkq1d7yGbJ3 xxUvGHSDChOjF1a5ZOX+4ATBfhhJ8VBrCHG6aXZLuTTk46XAbylB9XDOG4+kKTcT jbUUY41XcXonKAMthhJWoZU406Tahnntof6AO/ZF5BQXv34pv7eBNSLy50cF7cTw VQIDAQAB -----END PUBLIC KEY-----
34. © 2017 Percona 34 Password ▪Criar usuario CREATE USER 'sha256user'@'localhost' IDENTIFIED WITH sha256_password; SET old_passwords = 2; SET PASSWORD FOR 'sha256user'@'localhost' = PASSWORD('Sh@256Pa33');
35. © 2017 Percona 35 Password ▪Forçar usuário a trocar password •MySQL 5.6 ALTER USER 'myuser'@'localhost' PASSWORD EXPIRE; •MySQL 5.7 [mysqld] default_password_lifetime=180 CREATE USER 'myuser'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
36. © 2017 Percona 36 Password mysql> SELECT 1; ERROR 1820 (HY000): You must SET PASSWORD before executing this statement mysql> ALTER USER USER() IDENTIFIED BY 'new_password'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
37. © 2017 Percona 37 Connection Control
38. © 2017 Percona 38 Connection Control ▪MySQL 5.7.17+ ▪Adicionar delay na autenticação após X erros ▪Instalação [mysqld] plugin-load-add=connection_control.so INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
39. © 2017 Percona 39 Connection Control ▪Connection_control_failed_connections_threshold ▪Connection_control_min_connection_delay ▪Connection_control_max_connection_delay
40. © 2017 Percona 40 Connection Control time mysql -u msandbox -pmsandbox2 -P 57182 --protocol=TCP ERROR 1045 (28000): Access denied for user 'msandbox'@'localhost' (using password: YES) real 0m0.014s user 0m0.007s sys 0m0.006s time mysql -u msandbox -pmsandbox2 -P 57182 --protocol=TCP ERROR 1045 (28000): Access denied for user 'msandbox'@'localhost' (using password: YES) real 0m0.014s user 0m0.004s sys 0m0.009s time mysql -u msandbox -pmsandbox2 -P 57182 --protocol=TCP ERROR 1045 (28000): Access denied for user 'msandbox'@'localhost' (using password: YES) real 0m1.513s user 0m0.009s sys 0m0.004s
41. © 2017 Percona 41 TDE - Transparent Data Encryption
42. © 2017 Percona 42 TDE ▪InnoDB MySQL 5.7 ▪Criptografa os dados armazenados no disco ▪Cada tabela tem sua própria chave ▪Chave da tabela é armazenada no header do arquivo ▪Instalação [mysqld] early-plugin-load=keyring_file.so keyring_file_data=/usr/local/mysql/mysql-keyring/keyring
43. © 2017 Percona 43 TDE ▪Verificar se o plugin está instalado mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%'; +--------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------+---------------+ | keyring_file | ACTIVE | +--------------+---------------+
44. © 2017 Percona 44 TDE ▪Criptografar tabelas mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y'; mysql> ALTER TABLE t1 ENCRYPTION='Y';
45. © 2017 Percona 45 Audit Plugin
46. © 2017 Percona 46 Audit Plugin ▪MySQL Enterprise - Pago ▪Macfee - Grátis ▪Percona Server - Grátis ▪Loga atividades do banco de dados ▪Diferente do general log ▪Filtros por command / usuário / banco
47. © 2017 Percona 47 Audit Plugin ▪Formatos - OLD <AUDIT_RECORD "NAME"="Query" "RECORD"="2_2014-04-28T09:29:40" "TIMESTAMP"="2014-04-28T09:29:40 UTC" "COMMAND_CLASS"="install_plugin" "CONNECTION_ID"="47" "STATUS"="0" "SQLTEXT"="INSTALL PLUGIN audit_log SONAME 'audit_log.so'" "USER"="root[root] @ localhost []" "HOST"="localhost" "OS_USER"="" "IP"="" />
48. © 2017 Percona 48 Audit Plugin ▪Formatos - New <AUDIT_RECORD> <NAME>Quit</NAME> <RECORD>10902_2014-04-28T11:02:54</RECORD> <TIMESTAMP>2014-04-28T11:02:59 UTC</TIMESTAMP> <CONNECTION_ID>36</CONNECTION_ID> <STATUS>0</STATUS> <USER></USER> <PRIV_USER></PRIV_USER> <OS_LOGIN></OS_LOGIN> <PROXY_USER></PROXY_USER> <HOST></HOST> <IP></IP> <DB></DB> </AUDIT_RECORD>
49. © 2017 Percona 49 Audit Plugin ▪Formatos - JSON { "audit_record":{ "name":"Query", "record":"4707_2014-08-27T10:43:52", "timestamp":"2014-08-27T10:44:19 UTC", "command_class":"show_databases", "connection_id":"37", "status":0, "sqltext":"show databases", "user":"root[root] @ localhost []", "host":"localhost", "os_user":"", "ip":"" } }
50. © 2017 Percona 50 Audit Plugin ▪Formatos - CSV "Query","49284_2014-08-27T10:47:11","2014-08- 27T10:47:23 UTC","show_databases","37",0,"show databases","root[root] @ localhost []","localhost","",""
51. © 2017 Percona 51 Audit Plugin ▪Mysqlauditgrep - buscar informações nos logs ▪Data ▪Tipo de evento ▪Usuário
52. © 2017 Percona 52 Firewall
53. © 2017 Percona 53 Firewall ▪MySQL Enterprise Firewall - Pago ▪ProxySQL - Grátis ▪https://goo.gl/n4VZzH
54. © 2017 Percona 54 Firewall ▪Bloquear queries - SQL INJECTION! •Original query: SELECT Name FROM world.city WHERE Name = ‘?’ •SQL Injection: ? = São Paulo' OR ID > 0; -- •SELECT Name FROM world.city WHERE Name = 'São Paulo' OR ID > 0; --' Admin> SELECT username, digest, digest_text FROM stats_mysql_query_digest WHERE digest_text LIKE '% OR ID %' ORDER BY first_seen DESC LIMIT 1; +-------------+--------------------+------------------------------------------------------+ | username | digest | digest_text | +-------------+--------------------+------------------------------------------------------+ | application | 0xD8AF41BF32707ABD | SELECT Name FROM world.city WHERE Name = ? OR ID > ? | +-------------+--------------------+------------------------------------------------------+ 1 row in set (0.00 sec)
55. © 2017 Percona 55 Firewall Admin> INSERT INTO mysql_query_rules (rule_id, active, digest, error_msg, apply) VALUES (4,1,'0xD8AF41BF32707ABD','Suspeita de SQL Injection',1); Query OK, 1 row affected (0.00 sec) [root@localhost ~]# mysql -u application -papp -e "SELECT Name FROM world.city WHERE Name = 'São Paulo' OR ID > 0; --' " ERROR 1148 (42000) at line 1: Suspeita de SQL Injection
56. © 2017 Percona 56 Perguntas?
57. DATABASE PERFORMANCE MATTERS Database Performance Matters Database Performance Matters Database Performance Matters Database Performance Matters Database Performance Matters Obrigado!