Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Data Privacy in the Database, Shahid's NoBS Guide to Privacy by Design

Data Privacy in the Database, Shahid's NoBS Guide to Privacy by Design

If your database schema is not designed for privacy, don’t let your colleagues fool you into thinking it’s something you can just add in later as a layer. If your modern APIs and integration points aren’t privacy-aware by design, you cannot simply patch it in a future release. If you’re writing code for zero trust, you can’t just change it easily later. Join long-time healthcare CTO Shahid Shah as he talks about the technical means to ensure privacy by design, such as how to create database schemas that allow privacy and security at the table and row levels and how to write code and perform QA to ensure privacy is in the software development lifecycle. This is a can’t miss talk for CTOs, heads of product management, senior engineers, senior architects, developers, programmers, and database professionals (DBAs).

This presentation was delivered at AIN's "Embedding Privacy in Digital Health" event held virtually May 25-26, 2022.

3962189473d062fdc76ce9a07cbe89fd?s=128

Shahid N. Shah

May 26, 2022
Tweet

More Decks by Shahid N. Shah

Other Decks in Programming

Transcript

  1. Shahid’s NoBS Guide to Data Privacy for Good Engineers BLUF1:

    If you’re not using policy-based cell-level security inside a modern (probably relational) database, you probably don’t have a secure data architecture. 1 Bottom Line Up Front
  2. Common design patterns and their flaws Monolithic App Service-based Apps

    and Microservices Presentation (e.g., HTML/Win) Application (e.g., “web” server) Storage Individual Login Group Permissions Authentication Authorization App/service user/password can see all tables and data, no policies enforced inside the database. Presentation (e.g., HTML) Service 1 Storage AuthN/AuthZ Service Service 2 Service… Extended Authorization
  3. Things get worse with more devices Individual Login Group Permissions

    Authentication Authorization App/service user/password can see all tables and data, no policies enforced inside the database. Presentation (e.g., HTML) Service 1 Storage AuthN/AuthZ Service Service 2 Service… Extended Authorization RPM Device RPM Device Mobile Device
  4. Major flaws in existing architectures Data security is not where

    data is stored: in the database Apps and services used shared passwords and authentication No policy-based enforcement of authorization or data permissions No reasonable auditing of data access / mutation at the person entity or non-person entity (NPE) levels
  5. How to modernize: privacy-aware and privacy by design architecture •

    “Zero Trust” data architecture should be the goal • Shared app or service passwords must die • Server and device certificates must replace passwords • Identities must be severable from transactions and analytics
  6. Choose the right database • NoSQL and “schemaless” databases should

    be avoided – they’re harder to secure at the data level • Stick with relational database management systems (RDBMs) unless you’ve got data architecture experts on the team • PostgreSQL is a great open source and free choice – if you don’t know what else to choose start here • MySQL and family are also reasonable options • If you can afford ORACLE and MS SQL*Server or other commercial offerings they’re excellent options too
  7. Use your database for secure data management, not your service

    layers or apps • Use severable identities – create identities and identifiable data in separate schemas or separate databases • Use database policies to enforce row-level and cell-level data security • Don’t design for FHIR or healthcare-specific use cases, design for hackers who will get access to shared passwords or cases when your devs will accidentally commit passwords into Git • Build in telemetry access into the database don’t depend on layers of security for data architecture (layers are good for app security, not data security)
  8. Examples of data security in the database CREATE TABLE passwd

    (…); CREATE ROLE admin; -- Administrator CREATE ROLE bob; -- Normal user CREATE ROLE alice; -- Normal user -- Be sure to enable row-level security on the table ALTER TABLE passwd ENABLE ROW LEVEL SECURITY; -- Create policies -- Administrator can see all rows and add any rows CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true); -- Normal users can view all rows CREATE POLICY all_view ON passwd FOR SELECT USING (true); -- Normal users can update their own records, but -- limit which shells a normal user is allowed to set CREATE POLICY user_mod ON passwd FOR UPDATE USING (current_user = user_name) WITH CHECK ( current_user = user_name AND shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh') ); -- Allow admin all normal rights GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin; -- Users only get select access on public columns GRANT SELECT (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell) ON passwd TO public; -- Allow users to update certain columns GRANT UPDATE (pwhash, real_name, home_phone, extra_info, shell) ON passwd TO public;
  9. Conclusion: If you’re not using policy-based cell- level security inside

    a modern (probably relational) database, you probably don’t have a privacy-aware and privacy by design data architecture.