Transcript [Powerpoint] - NewSecurityFeaturesx

SQL Server 2016 New Security
Features
Gianluca Sartori
@spaghettidba
#SQLSAT454
Sponsors
#SQLSAT454
Gianluca Sartori
 Independent SQL Server consultant
 SQL Server MVP, MCTS, MCITP, MCT
 Works with SQL Server since version 7
 DBA @ Scuderia Ferrari
 Blog:
 Twitter:
#SQLSAT454
spaghettidba.com
@spaghettidba
Agenda




Security Boundaries
Always Encrypted
Row Level Security
Dynamic Data Masking
#SQLSAT454
Why New Security Features?
 SQL Server has plenty security features
 TDE
 Protects database files and backups at rest
 Cell-Level Encryption
 Encrypts single values in database tables
 SSL
 Protects data on the network
#SQLSAT454
Security Boundaries – Open
Apps
Database
Manager
User
SSMS
Developer
DBA
Software Vendor
#SQLSAT454
Unauthorized
Users
Security Boundaries – Non Sensitive
Apps
Software Vendor
Database
Manager
User
Copy
SSMS
DBA
Copy
Developer
#SQLSAT454
Unauthorized
Users
Security Boundaries – Sensitive
Apps
Software Vendor
Database
Manager
User
Copy
SSMS
DBA
Copy
Developer
#SQLSAT454
Unauthorized
Users
ALWAYS ENCRYPTED
#SQLSAT454
Always Encrypted – Key Features
Prevents Data
Disclosure
Queries on
Encrypted Data
Application
Transparency
End-to-end
encryption of
individual columns
in a table with keys
that are never given
to the database
system.
Support for equality
comparison, incl.
join, group by and
distinct operators.
Minimal application
changes via server
and client library
enhancements.
#SQLSAT454
Always Encrypted
 Sensitive data is encrypted at column level
 Data is protected from high-privileged users
 DBAs
 System Admins
 Hackers
 Cloud providers
 Third-parties
 Data is stored securely outside security
boundaries
 The database never sees unencrypted data
#SQLSAT454
Always Encrypted – How it works
App
SELECT Name FROM
Patients WHERE SSN=@SSN
Column
Master
Key
Query
SELECT Name FROM
Patients WHERE SSN=@SSN
@SSN=0x7ff654ae6d
SQL Server
Enhanced
Native
Client
ADO.NET
.NET
4.6
Library
@SSN='198-33-0987'
dbo.Patients
Result Set
Name
Jim Gray
Application - Trusted
#SQLSAT454
Column
Encryption
Key
Name
SSN
Country
Jane Doe
Jim Gray
1x7fg655se2 USA
e
0x7ff654ae6d USA
John Smith
0y8fj754ea2c USA
SQL Server - Untrusted
Encryption Types
 Deterministic Encryption
Same plaintext value  Same encrypted value
Supports indexing, equality comparison, JOINs,
DISTINCT
 Randomized Encryption
Same plaintext value  Different encrypted value
Supports retrieval of encrypted data
No SQL operations supported
#SQLSAT454
Working with Always Encrypted
DEMO
#SQLSAT454
TDE vs Always Encrypted
Always Encrypted
TDE
Column level
Client encryption
Server doesn’t know
encryption keys
Database level
Server encryption
Server knows encryption
keys
Data in memory is encrypted Data in memory is in
plaintext
Data travels the network
Data travels the network in
encrypted
plaintext
#SQLSAT454
Custom encryption vs Always Encrypted
Always Encrypted
Custom Encryption
Slight application changes
Needs obtrusive changes
Disallows saving plaintext
data
Plaintext data can be saved
by accident
Allows indexing of
cyphertext *
Allows indexing of
cyphertext *
* depending on encryption algorithm
#SQLSAT454
Always Encrypted - Limitations




Deterministic encryption needs _BIN2 collation
Not all datatypes supported
Partial support for triggers
Unsupported features:





Full-text search
Replication
Change Data Capture
In-Memory OLTP
Stretch Database
#SQLSAT454
What changes for Applications?
 ConnectionString must include new key:
Column Encryption Setting=enabled;
 Ad-hoc queries not supported
SELECT SomeColumn
FROM SomeTable
WHERE EncrypedColumn = 'SomeValue';
 Needs correctly parameterized queries
SELECT SomeColumn
FROM SomeTable
WHERE EncrypedColumn = @param;
#SQLSAT454
Always Encrypted for Existing Data
 Existing columns must be encrypted client side
 Easiest way: Import / Export wizard
Ad-hoc wizard
In SSMS 2016?
#SQLSAT454
Performance Impact
#SQLSAT454
Space Usage Impact
#SQLSAT454
Q&A
Questions?
#SQLSAT454
DYNAMIC DATA MASKING
#SQLSAT454
Dynamic Data Masking – Key Features
Limits Sensitive
Data Exposure
Useful for
Compliance
Application
Transparency
Sensitive data is
masked.
Administrators
designate how much
of the sensitive data
to reveal.
Helps adhering to
privacy standards
imposed by
regulation
authorities.
No application
changes. Existing
queries keep
working.
#SQLSAT454
Dynamic Data Masking
Unmasked Data
SSN
DATA
062-56-4651
Database
Privileged User
Masked Data
SSN
XXX-XX-XXXX
Non-Privileged
User
#SQLSAT454
dbo.Employees
Name
SSN
Salary
Jane Doe
062-56-4651 2.500
Jim Gray
915-12-9845 2.350
John Smith
354-21-9184 1.500
Dynamic Data Masking
 Obfuscates data using 3 masking functions
 Default: depends on data type
 Email: aXXX.XXXX.com
 Partial: prefixXXXXXXsuffix
 Data is stored unmasked
 Masking happens on resultset formation
 GRANT UNMASK to disclose data
 Works in Azure SQL Database (preview)
#SQLSAT454
Dynamic Data Masking - Limitations
 Not all datatypes supported
 Not intended as a complete protection feature for
sensitive data
 Ad-Hoc queries disclose data. Ex: WHERE Salary > 2000
 INSERT…SELECT does not preserve masking
 Some quirks
 Not suitable for handing out copies of the database
to software vendors or third-parties
#SQLSAT454
Working with Dynamic Data Masking
DEMO
#SQLSAT454
Q&A
Questions?
#SQLSAT454
ROW-LEVEL SECURITY
#SQLSAT454
Row Level Security – Key Features
Fine-grained
access control
Centralized
Security Logic
Application
Transparency
In multi-tenant
databases, limits
access by other
users who share the
same tables.
Predicate-based
access control logic
resides inside the
database and is
schema-bound to
the tables it protects.
No application
changes. Existing
queries keep
working.
#SQLSAT454
Row-Level Security
EMEA
Salesperson
Manager
LATAM
Salesperson
APAC
Salesperson
#SQLSAT454
dbo.Customer
Name
Area
Budget
Evil Inc.
EMEA
2.500
Wealthy Corp.
LATAM
2.350
Greedy Corp.
APAC
1.500
Row-Level Security - Concepts
 Predicate function
User-defined inline iTVF implementing access control logic.
Can be arbitrarily complicated
 Security predicate
Applies a predicate function to a particular table (APPLY)
Two types: filter predicates and blocking predicates
 Security policy
Collection of security predicates
Manages security across multiple tables
#SQLSAT454
Row-Level Security – How it works
dbo.Customer
Name
Area
Budget
Evil Inc.
EMEA
2.500
Wealthy Corp.
LATAM
2.350
Greedy Corp.
APAC
1.500
Security Policy
SELECT *
FROM Customer
EMEA
Salesperson
#SQLSAT454
SELECT *
FROM Customer
APPLY itvf_securityPredicate()
DBA
Working with Row-Level Security
DEMO
#SQLSAT454
Row-Level Security - Limitations
 SCHEMABINDING: all tables in the predicate
function must reside in the database
 Performance impact: queries are rewritten
 When authenticating the application,
CONTEXT_INFO() can be used to filter on real user
 Not really secure if users can run ad-hoc queries
 Don’t lock out the DBA!
#SQLSAT454
Q&A
Questions?
#SQLSAT454
Resources
Always Encrypted on MSDN
Getting Started With Always Encrypted
Performance impact of Always Encrypted
Dynamic Data Masking on MSDN
Using Dynamic Data Masking
Row-Level Security on MSDN
Introduction to Row-Level Security
Row-Level Security Limitations
#SQLSAT454
Evaluations
 Don’t forget to compile evaluations form here
 http://speakerscore.com/sqlsat454
#SQLSAT454
#sqlsat454
THANKS!
#SQLSAT454