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 

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 

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 

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 

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 

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 

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) 

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; 

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.