Zero Trust Approach for MySQL Authorization: ProxySQL + LDAP

This presentation was given at ProxySQL Technology Day Ghent 2019 by Alexander Rubin from Virtual Health.

Transcript

1. PRIVILEGED + CONFIDENTIAL Alexander Rubin VirtualHealth Zero Trust for MySQL with ProxySQL
2. PRIVILEGED + CONFIDENTIAL About me Working with MySQL for 10-15 years ◦ Started at MySQL AB 2006 • Sun Microsystems, Oracle (MySQL Consulting) • Percona since 2014 ◦ Recently joined Virtual Health
3. PRIVILEGED + CONFIDENTIAL 1. Encryption a. Data in Flight: SSL/TLS b. Data at Rest 2. Audit trail: logging actions 3. User authentication and authorization Protecting data in MySQL: Requirements
4. PRIVILEGED + CONFIDENTIAL User authentication & Zero Trust
5. PRIVILEGED + CONFIDENTIAL • Zero trust concept created by Google ◦ Not only focused on perimeter security ◦ Authenticate internal users ◦ Authorization required for all actions Trust, but always verify credentials...
6. PRIVILEGED + CONFIDENTIAL • Database access ◦ Developers ◦ Support ◦ Business Analysts • Challenges ◦ Security ◦ Performance impact Internal users access - User Auth
7. PRIVILEGED + CONFIDENTIAL • Use shared account ◦ Big NO • Create and manage 30+ MySQL user accounts on all MySQL servers ◦ Not easy to manage • Use LDAP Auth on MySQL server (Percona Server, MariaDB, etc) ◦ Requires setup for Linux / PAM ◦ Need to reconfigure 100s of servers • Use Hashicorp vault to create MySQL user dynamically ◦ MySQL “Bastion” ◦ https://medium.com/hootsuite-engineering/mysql-bastion-streamlined-db-acces s-with-proxysql-vault-and-ad-aa79877247b4 Internal users access: options
8. PRIVILEGED + CONFIDENTIAL • Can we use a Proxy + LDAP ? ProxySQL High-performance MySQL proxy https://proxysql.com/ Internal users access: options
9. PRIVILEGED + CONFIDENTIAL Internal users access: architecture
10. PRIVILEGED + CONFIDENTIAL • LDAP / Active Directory (Samba) • ProxySQL 2.0 + LDAP plugin LDAP authentication in ProxySQL 2.0 is implemented as a plugin that is not part of the core of ProxySQL itself. To load the plugin, it is required to specify in ProxySQL config file (`proxysql.cfg`) in the global section, using option `ldap_auth_plugin` : ldap_auth_plugin="/path/to/MySQL_LDAP_Authentication_plugin.so" Internal users access: components
11. PRIVILEGED + CONFIDENTIAL update global_variables set variable_value = 'ldap://localhost:10389' where variable_name = 'ldap-uri'; update global_variables set variable_value = 'dc=example,dc=com' where variable_name = 'ldap-root_dn'; update global_variables set variable_value = '@example.com' where variable_name = 'ldap-bind_dn_suffix'; update global_variables set variable_value = "@example.com" where variable_name = 'ldap-bind_dn_suffix'; LOAD LDAP VARIABLES TO RUNTIME; SAVE LDAP VARIABLES TO DISK; Internal users access: config
12. PRIVILEGED + CONFIDENTIAL • CN=arubin is a member of mysqlro group • Map mysqlro group to mysql_readonly account INSERT INTO mysql_ldap_mapping (priority, frontend_entity, backend_entity) VALUES (10, 'mysqlro', 'ldapro'); Internal users access: Credentials mapping
13. PRIVILEGED + CONFIDENTIAL $ mysql -h 127.0.0.1 -P 6033 -uarubin -p 2019-06-12 14:50:24 [INFO] LDAP: search sAMAccountName string: ldap://localhost:10389/?sAMAccountName?sub?(member:1.2.840.113556.1.4.1941:=CN=arubin,CN=Users,..) 2019-06-12 14:50:24 [INFO] ldap search sAMAccountName completed after 4563us. 2019-06-12 14:50:24 [INFO] arubin@mysql.virtualhealth.com: sAMAccountName: mysqlro 2019-06-12 14:50:24 [INFO] LDAP: f_e: mysqlro, FE: mysqlro, BE: ldapro 2019-06-12 14:50:24 [INFO] LDAP: Adding user arubin in cache 2019-06-12 14:50:24 [INFO] LDAP: user arubin found in cache 2019-06-12 14:50:24 [INFO] LDAP: user arubin found in cache and is not expired Internal users access: Credentials mapping LDAP / AD account ProxySQL
14. PRIVILEGED + CONFIDENTIAL mysql> set global general_log=1; Query OK, 0 rows affected (0.00 sec) 2019-06-15T02:43:25.205634Z 9004 Query select /* mysql_user=arubin */ * from mysql.user ProxySQL: query fingerprint
15. PRIVILEGED + CONFIDENTIAL Goal: tight control to the data • Blacklist queries: restrict access to specific data, i.e. PII/PHI information • Rewrite queries - de-identify data on the fly Example: change the PHI data to FAKE data Alexander Rubin -> John Smith <real phone number> -> 555-555-05-55 User Authorization
16. PRIVILEGED + CONFIDENTIAL delete from mysql_query_rules; INSERT INTO mysql_query_rules (rule_id, active, match_digest, flagOUT, apply) VALUES (89,1,'^SELECT', 100, 0); INSERT INTO mysql_query_rules (rule_id, active, flagIN, match_digest, destination_hostgroup, apply) VALUES (1001,1, 100, 'WHERE', 1, 1); INSERT INTO mysql_query_rules (rule_id, active, flagIN, error_msg, apply) VALUES (1002,1, 100, 'Query not allowed', 1); LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK; Blacklisting full data retrieval (including mysqldump)
17. PRIVILEGED + CONFIDENTIAL mysql> select id from profile where id = 1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> select id from profile limit 10; ERROR 1148 (42000): Query not allowed mysqldump --single-transaction -h 127.0.0.1 -udev -p -P6033 faker profile > profile_test.sql Enter password: mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `profile`': Query not allowed (1148) Blacklisting full data retrieval (including mysqldump)
18. 18 Thank you!