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.

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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)

    View Slide

  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;

    View Slide

  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.

    View Slide