adding role-based security and data masking to mysql through a hexatier proxy
TRANSCRIPT
©2002-2016 Teladoc, Inc. All rights reserved.
Adding Role-Based Security and Data Masking to MySQL through a Hexatier Proxy
10/28/2016
©2002-2016 Teladoc, Inc. All rights reserved.
Introductions
Scott Uhrick• 22 Years an Oracle DBA in Health Care• 2 Years a MySQL DBA with Teladoc
Teladoc• First and largest telehealth platform in the United States• Provides 24/7 access to Board-certified medical care• 15 million members• 1.6+ million visits since inception
©2002-2016 Teladoc, Inc. All rights reserved.
“A system should be as complex as it needs to be and no more than that” – Albert Einstein (very loosely paraphrased)
The Goal – Reduce Complexity in Security Administration
• The lack of role-based security in current versions of MySQL forces privileges to be managed at the level of the individual user.
• Managing privileges at this level makes it difficult to keep privileges consistent among users with identical job functions.
• Workarounds usually consist of standard “grant privilege” scripts which act as a creation template for standardized user-types.
• Such solutions can keep users consistent upon creation but does not affect;• Changing role requirements• Auditing (who has this role?)• Multiple roles per person • Human error
©2002-2016 Teladoc, Inc. All rights reserved.4
Hexatier Functionality
Hexatier provides database security at several different levels;
• Role-Based Data Access• Segregation of duties• Simplified administration
• Data Masking• Data masked in query result sets returned through Hexatier proxy• Same roles controlling data access can be used in data masking
• ie, not one-size-fits-all as with in-database encryption
• Data Discovery• Can scan tables to discover sensitive data
• Data Activity Monitoring• Logging can be set for individual Hexatier rules & policies, allowing
auditing to be as broad or narrow as desired.
• SQL Injection Attack Prevention
©2002-2016 Teladoc, Inc. All rights reserved.5
Hexatier Functionality in Use at Teladoc
Hexatier provides database security at several different levels;
• Role-Based Data Access• Segregation of duties• Simplified administration
• Data Masking• Data masked in query result sets returned through Hexatier proxy• Same roles controlling data access can be used in data masking
• ie, not one-size-fits-all as with in-database encryption
• Data Discovery• Can scan tables to discover sensitive data
• Data Activity Monitoring• Logging can be set for individual Hexatier rules & policies, allowing
auditing to be as broad or narrow as desired.
• SQL Injection Attack Prevention
©2002-2016 Teladoc, Inc. All rights reserved.6
Hexatier Architecture
• Hexatier is a network proxy process running on a Linux server, designed to receive and forward MySQL database connections
• Applications and MySQL clients see the proxy as a MySQL database. Hexatier receives the connections, applies rules and passes approved connections and queries on to the database
©2002-2016 Teladoc, Inc. All rights reserved.7
Hexatier Architecture
AppServers
Desktops
HexatierProxy
(10.240.20.100)
MySQLDatabase
AppServers
Desktops
HexatierProxy
(10.240.20.100)
MySQLDatabase
X
Note – As MySQL database accounts can be configured to include a host address, the architecture can be designed to ensure the proxy cannot be bypassed. All traffic will be subject to Hexatier authority.
©2002-2016 Teladoc, Inc. All rights reserved.8
Hexatier Dashboard
Opening screen displays • # of sessions• Recent violations• System Performance• Top users, rules and ip addresses
©2002-2016 Teladoc, Inc. All rights reserved.9
Table Access Control
• Hexatier allows users to be assigned to groups, and those groups to be assigned privileges.
• Privileges are managed via a series of rules, applied in a top-down manner to each data request.
• Hexatier’s use of rules and user-groups allows a form of role-based security to operate, greatly simplifying security management.
©2002-2016 Teladoc, Inc. All rights reserved.10
Table Access Control – Users on MySQL
Corresponding Users on Database:
• Users are created on the database with full privileges, relying on Hexatier to apply security.• This is secure as access is allowed ONLY through Hexatier proxy’s IP address (10.240.20.100)
©2002-2016 Teladoc, Inc. All rights reserved.11
Table Access Control – Users on Hexatier
Users (inherited from database) :
Users Groups (can contain users or other user groups) :
©2002-2016 Teladoc, Inc. All rights reserved.12
Table Access Control - Policies
• Ordering of rules is important, most restrictive at the bottom to catch “none of the above” and block all access. People with no rules assigned have no access.
• Grantee’s can be individual users or pre-defined groups of users ( roles ! )
Policies define table access rules
©2002-2016 Teladoc, Inc. All rights reserved.13
Table Access Control – Policy Contents
Contents of a Policy :
Users affected by policy
Restrictions can be database, table or column-based
Allowed privileges
Log violations?
©2002-2016 Teladoc, Inc. All rights reserved.14
Table Access Control - Example using “nyug1”
User “nyug1” defined on database Usergroup “dw_NYUG_members” created, user “nyug1” added to group
Usergroup “dw_demo_hexatier_ro” created, user “dw_NYUG_members” added to group Note – Up to this point Hexatier
is authorizing no privileges, user “nyug1” has no privs on the database. Usergroups are just logical constructions.
©2002-2016 Teladoc, Inc. All rights reserved.15
Table Access Control – Policy Example
Goal – set up policy allowing select on database “demo_hexatier”
Who
What
Privs
©2002-2016 Teladoc, Inc. All rights reserved.
Additional Policy Options for Table-Based Security
16
Apply only to defined ip addresses
Apply only during defined periods
Apply only to defined applications
©2002-2016 Teladoc, Inc. All rights reserved.
Table Access ControlExample using nyug1, effect of policy ;
1) Can see data in database “demo_hexatier”
2) No rows returned when querying database “DW“
3) Unable to update data in database “demo_hexatier”
©2002-2016 Teladoc, Inc. All rights reserved.
What We’ve Just Done
18
Created user
nyug1
Created logical group of users
dw_NYUG_members
Created group to relate policies to user-groups
dw_demo_hexatier_ro
Created policy to define rules
dw_demo_hexatier_ro
Database
demo_hexatier
Users can be easily moved between user-groups
User-groups can be moved between policies
= Role-Based Security
©2002-2016 Teladoc, Inc. All rights reserved.19
Table Access Control – New Role for DMLCreate new user group to hold users allowed DML
Create new policy allowing DML, assign above user group to it
©2002-2016 Teladoc, Inc. All rights reserved.
What We’ve Just Done
20
dw_NYUG_members dw_demo_hexatier_ro dw_demo_hexatier_ro demo_hexatier
dw_WashDC_members
Moving users between groups, groups between rules
dw_demo_hexatier_rw dw_demo_hexatier_rw
©2002-2016 Teladoc, Inc. All rights reserved.
Additional Policy Options for Query-Based Security
21
Proxy can be set to record queries run against a database for a set period(in the case below, one week). Results in a “Query Group”.
Resulting Query Group Policy can be used to limit queries to those in the group or to simply notify when queries outside the group are run.
©2002-2016 Teladoc, Inc. All rights reserved.22
Data Masking
The eternal quest for protecting production data on non-production systems.
Options;
• Don’t have production data on non-production systems (produce and maintain a set of test data which reflects all possible data gotchas).
• Who maintains and how to ensure all necessary test cases are included?
• Develop on non-production data, QA on copy of production.• Reduces the exposure of production data to a subset of staff, the minimum
necessary to ensure quality releases.• Increased development time due to increased iterations between
development & QA.
• Encrypt or remove sensitive data from development copy of production.• One-size-fits-all, everyone has same level of obfuscation.• Encryption of indexed columns invalidates range scans.• Removal of data can invalidate foreign keys.
©2002-2016 Teladoc, Inc. All rights reserved.23
Data Masking through Hexatier
• Proxy holds rules describing which tables and columns should have data masked before being returned to client.
• Rules can be configured and granted dynamically.
• Different rules can apply to different users & user-groups.
• Data remains in the clear within database.
Not a perfect solution.
• As return set is masked, processes or analysis dependent on that result set are incorrect.
©2002-2016 Teladoc, Inc. All rights reserved.24
Data MaskingRule below instructs proxy to mask “first_nm”, “last_nm”, “user_nm” and “ssn” from role “dw_NYU_members;
If the data-types of the columns being masked by the policy are the same, there is great flexibility in how they are masked.
©2002-2016 Teladoc, Inc. All rights reserved.25
Effect of Data Masking on “nyug1”
Designated columns masked for “nyug1”
Masked tables remain join-able
©2002-2016 Teladoc, Inc. All rights reserved.26
Activity Auditing
• Each table access control rule can be set to write audit records each time it is invoked.
• Each data masking rule can be set to write audit records each time it is invoked.
• As each security installation will contain a rule to deny access to any query not matching a granting rule, auditing this rule records all attempts to access prohibited data.
©2002-2016 Teladoc, Inc. All rights reserved.27
Activity Auditing
Audit Record of a Policy Violation :
©2002-2016 Teladoc, Inc. All rights reserved.28
Performance Benchmarking
Industry-standard TPCC benchmarks were run against the Hexatier POC deployment with the following feature combinations enabled;
1. Baseline (proxy bypassed)2. Hexatier controling table access3. Hexatier access control and PHI masking4. Hexatier access control, PHI masking & full
transaction logging
©2002-2016 Teladoc, Inc. All rights reserved.29
Performance Benchmarking
System Details• Database
– AWS RDS MySQL, m3xlarge (4 CPU, 15GB ram, prov io ssd)
• Proxy Server– AWS EC2 Centos, m4large (2 CPU, 8GB ram, prov io ssd)
# Virtual Users
Transactions Per Minute
10 20 30 40 50 60 70 80 90 1000
100000
200000
300000
400000
500000
600000
700000
800000
900000
1000000
Baseline, Proxy BypassedProxy, Table Access Control, No MaskingProxy, Table Access Control, PHI MaskingProxy, Table Access Control, PHI Masking, all transactions logged
Note : Teladoc’s production database db1a.us1 averages approx. 35,000 TpM during business hours
©2002-2016 Teladoc, Inc. All rights reserved.
Limitations and Implementation Suggestions
30
Hexatier as currently supplied has a few limitations to be aware of;
• Policies cannot cross schemas.• Cannot join “billing.table1” to “product.table2”
• Have had to maintain a few non-Hexatier accounts for the applications requiring this.
• Lack of consistent masking• While data can be masked a variety of ways it cannot be consistently
and uniquely obfuscated.• Eg., “John Smith” can be masked to “XXX XXXXX” but not to a
value which remains uniquely and consistently encrypted across the database. (“Hdbr 4Thdr” each time it is displayed).
• Some functions lack comments fields or the ability to track who-has-what.• Use of naming conventions necessary to get around this.