A Database Designer's Favorite Security and Privacy Features in SQL Server/Azure DB

SQL Server and Azure DB include multiple features that focus on data security, privacy, and developer productivity. In this session, we will review the best features from a database designer's and developer's point of view.
- Always Encrypted
- Dynamic Data Masking
- Row Level Security
- Data Classification
- Assessments
- Defender for SQL Server
- Ledger Tables
...and more

We'll look at new and older features, why you should consider them, where they work, where they don't, who needs to be involved in using them, and what changes, if any, need to be made to applications or tools that you use with SQL Server.

Transcript

1. A Database Designer's Favorite Security and Privacy Features in SQL Server/Azure DB Karen Lopez Datamodel.com
2. Karen Lopez Microsoft MVP, Data Platform Microsoft Certified Trainer, vExpert Data management expert, space enthusiast, and #TeamData evangelist www.datamodel.com @datachick
3. Ledger Tables and DBs Data Classification Intro & Motivations Always Encrypted Dynamic Data Masking Row Level Security Monitoring & Vulnerability Assessments Takeaways 01 02 06 03 07 04 08 05
4. Karen’s Thoughts on Design “Every design decision comes down to cost, benefit and risk.” - Karen Lopez 4
5. Why this topic? Because We Love Our Data
6. POLL: Who Are You?
7. Database designer Data architect Data Modeller Development DBA Accidental DBAs Data Steward/Curator …. 7
8. Recent data breaches/harms not related to ransomware? 8
9. 10 Top Web Security Risks https://owasp.org/www-project-top-ten/ 9
12. ALWAYS ENCRYPTED Because ALWAYS is a good thing
13. Security – Always Encrypted
14. Two Options for AE Standard With Secure Enclaves 14
15. Security - Always Encrypted 15
16. Security - Always Encrypted 16
17. Security – Always Encrypted 17 Enabled at column level Protects data at rest *AND* in memory Uses Column Master Key (client) and Column Encryption Key (server)
18. 18 Security – Always Encrypted Foreign keys must match encryption types Client code needs to support AE (currently this means .NET 4.x or above)
19. Always Encrypted
20. Always Encrypted with Secure Enclaves • https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always- encrypted-enclaves?view=sql-server-ver16 20 Virtualization-based Security (VBS) Enclaves Attestation Enclave Enabled Keys Randomized Only https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves
21. Always Encrypted with Secure Enclaves • https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always- encrypted-enclaves?view=sql-server-ver16 21 In Place Encryption Confidential Queries Indexing https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves
22. Why would a DB Designer love it? 22 Always Encrypted, yeah. Allows designers to not only specify which columns need to be protected, but how. Parameters are encrypted as well Built into the engine, easier for everyone
23. Dynamic Data Masking 23 Really more of a privacy feature than a security one, in Karen’s opinion
24. Security – Dynamic Data Masking 24
25. Data Masking Examples 25 XXXX XXXX XXXX 1234 kxxxxxx@ixxxxx.com $99,9999 June, 99, 9999 KXXXXX Lopez
26. Security – Dynamic Data Masking
27. Dynamic Data Masking 27 Column level--Data in the database, at rest, has no protection. Meant to complement other methods Performed at the end of a database query right before data returned Performance impact small
28. Dynamic Data Masking 28 5 functions available •Default •Email •Custom String •Random •Datetime
29. DDM Functions 29 Example Mask Function XXXX 0 01.01.2000 00:00:00.0000000 0 Based on Datatype String – XXX Numbers – 000000 Date & Times - 01.01.2000 00:00:00.0000000 Binary – Single Byte 0 Default Kxxx@xxxx.com First character of email, then Xs, then .com Always .com Email kxxxn First and last values, with Xs in the middle Custom 12 For numeric types, with a range Random 6/27/1900 For datatime datatypes, plus date Datetime
30. Dynamic Data Masking 30 Data in database is not changed 01 01 Ad-hoc queries *can* expose data 02 02 Does not aim to prevent users from exposing pieces of sensitive data 03 03
31. Dynamic Data Masking 31 Cannot mask an encrypted column (AE) Cannot be configured on computed column But if computed column depends on a mask, then mask is returned Using SELECT INTO or INSERT INTO results in masked data being inserted into target (also for import/export)
32. Granular permissions 32 Specific columns Table Database Schema
33. Why would a DB Designer love it? 33 Allows central, reusable design for standard masking Offers more reliable masking and more usable masking Removes whining assurances “we can do that later”
35. Security – Row Level Security
36. 36 Row Level Security Filtering result sets (predicate-based access) Predicates applied when reading data Can be used to block write access User defined policies tied to inline table functions
37. Row Level Security No indication that results have been filtered 01 If all rows are filtered than NULL set returned 02 For block predicates, an error returned 03 Works even if you are dbo or db_owner role 04
38. Predicates 38
39. Predicates 39
40. Why would a DB Designer love it? 40 Allows a designer to do this sort of data protection IN THE DATABASE, not just relying on code. Many, many pieces of code.
42. SSMS Data Classification – SQL Server 42
43. Data Classification Results 43
44. Data Classification Recommendations 44
46. 46 Azure SQL DB
48. Information Types 48
49. Why a DB Designer Loves Classification 49 Standardized Work for data & compliance pros Enforceable Future-proofing
51. Why use a LEDGER table? More trustworthy More protection from DBA/SysAdmin tampering Don’t need or want full blockchain functionality Want to store data off a full blockchain for better performance
52. Key Features Azure LEDGER Tables Ledger Databases Database Digests Ledger Tables Updatable Append only Immutable storage for transaction recording Ledger Verification
53. Updatable Ledger Tables 53
56. Who did what when?
57. Append Only Ledger Tables 57
58. APPEND ONLY LEDGER TABLES Write and Read only, no updates No history table Ledger View
60. METEORITE LANDINGS 60
61. METEORITE LANDINGS VIEW 61
62. Ledger View Append-only Ledger Table Updatable Ledger Table Ledger View Ledger History Database Digest
63. Changing existing table to a Ledger Table Alter? No. Migrate Create new Ledger Table Copy Data to New Table Clean up previous Table 63
64. Ledger Databases 64
65. AZURE PORTAL Ensures all tables in the database are Ledger Tables
66. TSQL LEDGER DATABASE CREATE DATABASE Database01 ( EDITION = 'GeneralPurpose’, SERVICE_OBJECTIVE='GP_Gen5_2’, MAXSIZE = 2 GB ) WITH LEDGER = ON;
67. LEDGER DIGESTS Holds the database hashes Show the state of the data Stored outside the database server Separation of duties Immutable storage & policies
68. LEDGER DIGESTS JSON format Latest block in the database ledger appended Storage or Confidential Storage sqldbledgerdigests sys.sp_generate_database_ledger_digest Automatic or Manual {"database_name":"SpaceThreats","block_id":0,"hash":"0xBEF648C7AD9464A2B58E337B060 4BA94944A7F8D6A92306F02D0E08542DACDB2","last_transaction_commit_time":"2022-10- 18T18:25:39.9566667","digest_time":"2022-10-18T18:25:43.1473462"}
69. Digest Verification Checks the hashes in the digests 01 Reports based on where it is told the real digest is 02 Dependent on separation of duties + access controls on storage 03
70. Verifying Databases
71. Why a DB Designer Loves a LEDGER Table? 71 More trustworthy More protection from DBA/SysAdmin tampering Don’t need or want full blockchain functionality
73. In SSMS – but only version 18 or LOWER
74. Vulnerability Assessment – SSMS 18.x or lower
75. MICROSOFT DEFENDER FOR DATABASES How is your database protected?
76. Microsoft Defender for Databases
77. How? Secure scores Recommendations Alerting 77
78. Vvulnerabilities
79. Remediating Recommendations
80. Key Takeaways Data classifications are required Can’t secure data we don’t understand Security nearest the data In DB performance Data pros know data Can’t trust everyone anyone Developer productivity Managing Risk Importance of Laziness 80
81. One more time… Every Design Decision must be based on Cost, Benefit and Risk 81 @DATACHICK Datachick@mstdn.ca /in/karenlopez
82. THANK YOU Go out and be great…and Design Secure Databases