Organizational Security & Compliance Marcin Szeliga
[email protected]
Security challenges Weak passwords Password policy enforcement Lack of audit information Capture and audit DDL activities Data confidentiality Built-in encryption and key management Metadata protection Catalog security Schema-level permissions User/schema separation Granular permission to execute statements in a module Execution context › Protect access to the DB Encrypted login credentials, Connection end-points › › › › › ›
Organizational Security & Compliance › Data protection – Powerful encryption technologies – Advanced security infrastructure – Data protection extends to sql azure
› Control access – – – – –
User-defined server roles Default schema for groups Contained database authentication Sharepoint active directory Control access in the cloud
› Compliance – Sql server audit – Third-party verified (common criteria) – Compliance in the cloud
Data protection › Powerful encryption technologies – ASE256 for backup keys – SHA512 for password hashes
› Built on advanced security infrastructure – – – – –
Built-in cryptography hierarchy Transparent data encryption Always Encrypted Extensible key management Sign code modules
› Data protection in the Cloud – Encrypted connection in SQL Azure
Encryption hierarchy Key Secured By
Password
Key
Certificate Associated with
Wraps
Public Key
Private Key Secured By
Master Key Secured By
DP API
Service Key Secured By
Password
Transparent Database Encryption: benefits › Entire database is protected › NO application changes › Applications do not need to explicitly encrypt/decrypt data! › No restrictions with indexes or data types (except FILESTREAM cannot be encrypted)
› Performance cost is small › Backups are unusable without key › Can be used with Extensible Key Management
Transparent Data Encryption: mechanism › Very simple: – – – –
Database pages are encrypted before being written to disk Page protection (e.g. checksums) applied after encryption Page protection (e.g. checksums) checked before decryption Database pages are decrypted when read into memory
› When TDE is enabled, initial encryption of existing pages happens as a background process – Similar mechanism for disabling TDE – The process can be monitored using the encryption_state column of sys.dm_database_encryption_keys › Encryption state 2 means the background process has not completed › Encryption state 3 means the database is fully encrypted
Transparent Data Encryption: enabling › Create a master key – CREATE MASTER KEY ENCRYPTION BY PASSWORD = '
';
› Create or obtain a certificate protected by the master key – CREATE CERTIFICATE MyDEKCert WITH SUBJECT = 'My DEK Certificate';
› Create a database encryption key and protect it by the certificate – CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyDEKCert;
› Set the database to use encryption – ALTER DATABASE MyDatabase SET ENCRYPTION ON;
Transparent Data Encryption: backups › A backup of a TDE encrypted database is also encrypted using the database encryption key › To restore the backup OR attach the database, the DEK must be available! – There is no way around this – if you lose the DEK, you lose the ability to restore the backup (that’s the point!) – Maintain backups of server certificates too
Transparent Data Encryption: limitations › If TDE is enabled, a database will not use instant file initialization – Can cause significant performance drop – Especially important for database restore operations during disaster recovery
› FILESTREAM data is not encrypted
Always Encrypted › Help protect data at rest and in motion, on-premises & cloud Apps
SQL Server
Trusted
SELECT Name FROM Patients WHERE SSN=@SSN
Client side
SELECT Name FROM Patients WHERE SSN=@SSN @SSN='198-33-0987'
Column Master Key
Result Set
Query
@SSN=0x7ff654ae6d
Enhanced ADO.NET Library
Result Set
Name
Name
Jim Gray
Jim Gray dbo.Patients
Column Encryption Key
cipherte xt
Name
SSN
Country
Jane Doe
1x7fg655se2e 243-24-9812
USA
Jim Gray
198-33-0987 0x7ff654ae6d
USA
John Smith
0y8fj754ea2c 123-82-1095
USA
Key Provisioning
Column Encryption Key (CEK)
› Generate CEKs and Master Key › Encrypt CEK › Store Master Key Securely › Upload Encrypted CEK to DB
Column Master Key (CMK)
Encrypted CEK CMK Store: Certificate Store HSM Azure Key Vault …
CMK
Encrypted CEK Database
Dynamic Data Masking › Prevent the abuse of sensitive data by hiding it from users › Configuration made easy in the new Azure portal › Policy-driven at the table and column level, for a defined set of users › Data masking applied in real-time to query results based on policy › Multiple masking functions available (e.g. full, partial) for various sensitive data categories (e.g. Credit Card Numbers, SSN, etc.)
Organizational Security & Compliance › Data protection – Powerful encryption technologies – Advanced security infrastructure – Data protection extends to sql azure
› Control access – – – – –
User-defined server roles Default schema for groups Contained database authentication Sharepoint active directory Control access in the cloud
› Compliance – Sql server audit – Third-party verified (common criteria) – Compliance in the cloud
Control access › User-Defined Server Roles: allow flexible database access for better separation of duties › Default Schema for Groups: allow database schema ties to Windows Group for better schema management › Contained Database Authentication: allow authentication into user database without logins for better application manageability › SharePoint ActiveDirectory: help secure end-user data analytics with built-in IT controls, including SharePoint & ActiveDirectory security models
Row-level security › Protect data privacy by ensuring the right access across rows › Fine-grained access control over specific rows in a database table › Help prevent unauthorized access when multiple users share the same tables, or to implement connection filtering in multitenant applications › Administer via SQL Server Management Studio or SQL Server Data Tools › Enforcement logic inside the database and schema bound to the table
RLS Concepts › Predicate function – User-defined inline table-valued function (iTVF) implementing security logic – Can be arbitrarily complicated, containing joins with other tables
› Security predicate – Applies a predicate function to a particular table (SEMIJOIN APPLY) – Two types: filter predicates and blocking predicates
› Security policy – Collection of security predicates for managing security across multiple tables
CREATE SECURITY POLICY mySecurityPolicy ADD FILTER PREDICATE dbo.fn_securitypredicate(wing, startTime, endTime) ON dbo.patients
RLS Example › Fine-grained access control over rows in a table based on one or more predefined filtering criteria, e.g., user’s role or clearance level in organization
CREATE FUNCTION dbo.fn_securitypredicate(@wing int) RETURNS TABLE WITH SCHEMABINDING AS return SELECT 1 as [fn_securitypredicate_result] FROM StaffDuties d INNER JOIN Employees e ON (d.EmpId = e.EmpId) WHERE e.UserSID = SUSER_SID() AND @wing = d.Wing;
CREATE SECURITY POLICY dbo.SecPol ADD FILTER PREDICATE dbo.fn_securitypredicate(Wing) ON Patients WITH (STATE = ON)
Dynamic Data Masking › Prevent the abuse of sensitive data by hiding it from users › Configuration made easy in the new Azure portal › Policy-driven at the table and column level, for a defined set of users SQL Database › Data masking applied in real-time to SQL Server 2016 query results based on policy › Multiple masking functions available (e.g. full, partial) for various sensitive data categories (e.g. Credit Card Numbers, SSN, etc.)
Table.CreditCardNo 4465-6571-7868-5796 4468-7746-3848-1978 4484-5434-6858-6550
Real-time data masking; partial masking
Organizational Security & Compliance › Data protection – Powerful encryption technologies – Advanced security infrastructure – Data protection extends to sql azure
› Control access – – – – –
User-defined server roles Default schema for groups Contained database authentication Sharepoint active directory Control access in the cloud
› Compliance – Sql server audit – Third-party verified (common criteria) – Compliance in the cloud
Help ensure compliance › SQL Server Audit for all editions helps enable standardization & better performance – Audit Resilience: recover audit from temporary file & network issues – User-Defined Audit: allow custom audit events for greater flexibility – Audit Filtering: filter unwanted audit events for greater flexibility
SQL Server Audit: Audit Action Groups and Actions › Groups of actions (Audit Action Groups) can be audited at various levels – Server level events can only be audited at the server level › E.g. logon, logoff, Service Broker, DBCC
– Database level events can be audited at the per-database level or across the whole server › E.g. DDL, object permissions changes
› Individual actions can be audited only at the database level – SELECT UPDATE INSERT – DELETE EXECUTE RECEIVE (Service Broker) – When a REFERENCES permission is checked
SQL Server Audit: Audit Specifications › Defined at the server and database level – One Server Audit Specification per instance – One Database Audit Specification per database per instance
› Defines what is audited at that level › Contains audit action groups and audit actions (at the database level)
› No auditing available at the per-column level
SQL Server Audit: Targets and Audit Records › Events are converted to audit records – Fixed schema, although not all events populate all columns – Schema described in BOL (search for SQL Server Audit Records)
› Audit records are written to the specified target file – Any binary file, Windows Application Log, Windows Security Log – Oversize records are chained with a sequence number
› Note: using the Security Log requires extra setup – Description in BOL (search for Understanding SQL Server Audit)
› For fine-grained permission setting on the target, use a binary file with custom permissions
SQL Server Audit: performance › Leverages high performance Extended Events infrastructure to generate audits › Runs within Database Engine rather than as a side/separate app
› Targeted to be faster than SQL Trace
SECURITY Data protection Control access Compliance
