Transcript Organizational_Compliance_and_Security_200_Level_georgex

Organizational Security
& Compliance
SQL 2012 - 2016
George Walters
Senior Technology Solutions Professional
Data Platform
[email protected]
@gwalters69 on twitter
SQL Server 2016: Everything built-in
built-in
built-in
built-in
built-in
built-in
$2,230
80
69
70
SQL Server
60
43
50
40
34
30
20
10
49
0
6
29
1
SQL Server
SQL Server
2220
4
18
15
0
0
5
$480
22
3
3
0
#1
#2
#3
Oracle
is #5
$120
Microsoft
Tableau
Oracle
2010 2011 2012 2013 2014 2015
SQL Server
Oracle
MySQL
SAP HANA
TPC-H
Self-service BI per user
at massive scale
In-memory across all workloads
Consistent experience from on-premises to cloud
The above graphics were published by Gartner, Inc. as part of a larger research document and should be evaluated in the context of the entire document. The Gartner document is available upon request from Microsoft.
Gartner does not endorse any vendor, product or service depicted in its research publications, and does not advise technology users to select only those vendors with the highest ratings or other designation. Gartner
research publications consist of the opinions of Gartner's research organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research,
including any warranties of merchantability or fitness for a particular purpose.
TPC-H 10TB non-clustered results as of 04/06/15, 5/04/15, 4/15/14 and 11/25/13, respectively. http://www.tpc.org/tpch/results/tpch_perf_results.asp?resulttype=noncluster
National Institute of Standards and Technology Comprehensive Vulnerability Database update
10/2015
2
Progress on SQL Engine security
… A Journey through time
3
ASE256 for backup keys
SHA512 for password hashes
Built-in cryptography hierarchy
Transparent data encryption
Extensible key management
Sign code modules
Encrypted connection on-premises and in
Azure SQL database
Audit, TDE, Always Encrypted in Azure SQL database
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'<UseStrongPasswordHere>';
Go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO
better separation of duties
allow flexible database access for
Mission-critical performance
Performance
Security
Availability
Operational analytics
Always encrypted
Enhanced AlwaysOn
In-memory OLTP
enhancements
Row-level security
Round robin load balancing of
replicas
Insights on operational data;
Works with in-memory OLTP and
disk-based OLTP
Greater T-SQL surface area,
terabytes of memory supported,
and greater number of parallel
CPUs
Sensitive data remains encrypted at
all times with ability to query
Apply fine-grained access control
to table rows
Dynamic data masking
Real-time obfuscation of data to
prevent unauthorized access
Query data store
Other enhancements
Monitor and optimize query plans
Audit success/failure of database
operations
Native JSON
TDE support for storage of inmemory OLTP tables
Temporal database support
Enhanced auditing for OLTP with
ability to track history of record
changes
Expanded support for JSON data
Query data as points in time
Three synchronous replicas for auto
failover across domains
Automatic failover based on
database health
DTC for transactional integrity
across database instances with
AlwaysOn
Support for SSIS with AlwaysOn
Scalability
Enhanced database caching
Cache data with automatic, multiple
TempDB files per instance in multicore environments
Always Encrypted
SQL Server 2016
Azure SQL Database
Benefits of Always Encrypted
Prevents Data
Disclosure
Queries on
Encrypted Data
Application
Transparency
Client-side encryption of
sensitive data using 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.
Allows customers to securely store sensitive data outside
of their trust boundary.
Data remains protected from high-privileged, yet
unauthorized users.
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 Query
@SSN='198-33-0987'
Column
Master
Key
Result Set
@SSN=0x7ff654ae6d
Enhanced
ADO.NET
Library
Result Set
Name
Name
Jim Gray
Jim Gray
ciphertext
Column
Encryption
Key
dbo.Patients
Name
SSN
Country
Jane Doe
243-24-9812 USA
1x7fg655se2e
Jim Gray
198-33-0987 USA
0x7ff654ae6d
John Smith
123-82-1095 USA
0y8fj754ea2c
Types of Encryption for Always Encrypted
Randomized encryption
Encrypt('123-45-6789') = 0x17cfd50a
Repeat: Encrypt('123-45-6789') = 0x9b1fcf32
Allows for transparent retrieval of encrypted
data but NO operations
More secure
Deterministic encryption
Encrypt('123-45-6789') = 0x85a55d3f
Repeat: Encrypt('123-45-6789') = 0x85a55d3f
Allows for transparent retrieval of encrypted
data AND equality comparison
E.g. in WHERE clauses and joins, distinct,
group by
Two types of
encryption available
Randomized encryption uses a
method that encrypts data in a less
predictable manner
Deterministic encryption uses a
method which always generates the
same encrypted value for any given
plain text value
Key Provisioning
1. Generate CEKs and Master Key
Column
Encryption Key
(CEK)
Encrypted
CEK
2. Encrypt CEK
Security
Officer
3. Store Master Key Securely
4. Upload Encrypted CEK to DB
Column
Master Key
(CMK)
CMK Store:
Certificate Store
HSM
Azure Key Vault
…
CMK
Encrypted CEK
Database
Example
Result set (plaintext)
Name
Jim Gray
SQL Server - Untrusted
exec sp_describe_parameter_encryption
@params = N'@SSN VARCHAR(11)'
, @tsql = N'SELECT * FROM Customers WHERE SSN = @SSN'
Plaintext
CEK
Cache
Param
@SSN
Encryption
Type/
Algorithm
Encrypted
CEK Value
DET/ AES
256
CMK Store
Provider
Name
CMK Path
CERTIFICATE
_STORE
Current User/
My/f2260…
EXEC sp_execute_sql
N'SELECT * FROM Customers WHERE SSN = @SSN'
, @params = N'@SSN VARCHAR(11)', @SSN=0x7ff654ae6d
Enhanced
ADO.NET
Param
@Name
Encryption
Type/
Algorithm
Encrypted
CEK Value
Non-DET/
AES 256
Name
0x19ca706fbd9
CMK Store
Provider
Name
CMK Path
CERTIFICATE
_STORE
Current User/
My/f2260…
Result set (ciphertext)
Encryption metadata
using (SqlCommand cmd = new
SqlCommand(
"SELECT Name FROM Customers WHERE SSN
= @SSN“
, conn))
{
cmd.Parameters.Add(new SqlParameter(
"@SSN", SqlDbType.VarChar, 11).Value
=
"111-22-3333");
SqlDataReader reader =
cmd.ExecuteReader();
CMK Store
Encryption metadata
Client - Trusted
Setup (SSMS or SSDT)
User Experience: SSMS or SSDT (Visual Studio)
Select columns to
be encrypted
Set up the keys:
master & CEK
UI for selecting columns (no
automated data classification)
Key setup tool to automate
selecting CMK, generating and
encrypting CEK and uploading
key metadata to the database
Analyze schema
and application
queries to detect
conflicts (build
time)
Static schema
analysis tool
(SSDT only)
Existing App – Setup
User Experience: SSMS or SSDT (Visual Studio)
Select candidate
columns to be
encrypted
UI for selecting columns
(no automated data
classification)
Analyze schema and
application queries to
detect conflicts and
identify optimal
encryption settings
Schema/workload analysis
tool analyzing the schema
and profiler logs
Select desired
encryption settings
for selected columns
UI for configuring
encryption settings on
selected columns
(accepting/editing
recommendations from
the analysis tool)
Set up the keys
Key Setup tool to
streamline selecting CMK,
generating and encrypting
CEK and uploading key
metadata to the database
Encrypt selected
columns while
migrating the
database to a target
server (e.g. in Azure
SQL Database
Encryption tool creating
new (encrypted) columns,
copying data from old
(plain text) columns,
swapping columns and recreating dependencies
Summary: Always encrypted
Protect data at rest and in motion, on-premises & cloud
Data remains encrypted
during query
Apps
No app
changes
Master
key
Encrypted
query
TCE-enabled
ADO .NET library
SQL Server
Capability
ADO.Net client library provides
transparent client-side encryption, while
SQL Server executes T-SQL queries on
encrypted data
Benefits
Columnar
key
Row-Level Security
SQL Server 2016
Azure SQL Database
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.
Customer 1
Customer 2
Customer 3
SQL Database
Benefits of row-level security
Fine-grained
access control
Application
transparency
Centralized
security logic
Keeping multi-tenant
databases secure by limiting
access by other users who
share the same tables.
RLS works transparently at
query time, no app changes
needed.
Enforcement logic resides
inside database and is
schema-bound to the table it
protects providing greater
security. Reduced application
maintenance and complexity.
Compatible with RLS in other
leading products.
Store data intended for many consumers in a single database/table while at the same time restricting
row-level read & write access based on users’ execution context.
Row Level Security 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
Example
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()
Fine-grained access
control over rows in a
table based on one or
more pre-defined filtering
criteria, e.g., user’s role or
clearance level in
organization.
AND @wing = d.Wing;
CREATE SECURITY POLICY dbo.SecPol
ADD FILTER PREDICATE
dbo.fn_securitypredicate(Wing)
ON Patients
WITH (STATE = ON)
Concepts:
Predicate function
Security policy
RLS in Three Steps
Two
Three
One
Policyuser
App
Security
manager
Policy
(e.g., transparently
nurse)
createsselects
filter rewrites
predicate
from Patients
query
and table
security
to applypolicy
filter predicate
in T-SQL, binding the
predicate to the Patients table
Nurse
Policy Manager
Database
Security
Policy
Patients
Application
SELECT * FROM Patients
Filter
Predicate:
INNER
JOIN…
CREATE FUNCTION dbo.fn_securitypredicate(@wing int)
RETURNS TABLE WITH SCHEMABINDING AS
return SELECT 1 as [fn_securitypredicate_result] FROM
SELECT *StaffDuties
FROM Patients
d INNER JOIN Employees e
SEMIJOIN
APPLY dbo.fn_securitypredicate(patients.Wing);
ON (d.EmpId
= e.EmpId)
WHERE e.UserSID = SUSER_SID() AND @wing = d.Wing;
SELECT
FROM Patients,
CREATE Patients.*
SECURITY POLICY
dbo.SecPol
StaffDuties
d
INNER
JOIN
Employees e ON (d.EmpId = e.EmpId)
ADD FILTER PREDICATE
dbo.fn_securitypredicate(Wing)
ON
WHERE e.UserSID = SUSER_SID() AND Patients.wing = d.Wing;
Patients
WITH (STATE = ON)
Create Security Policy
-- The following syntax creates a security policy with a filter predicate
for the Customer table, and leaves the security policy disabled
CREATE SECURITY POLICY [FederatedSecurityPolicy]
ADD FILTER PREDICATE
[rls].[fn_securitypredicate]([CustomerId])
ON [dbo].[Customer];
-- Create a new schema and predicate function, which will use the
application user ID stored in CONTEXT_INFO to filter rows.
CREATE FUNCTION rls.fn_securitypredicate (@AppUserId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
SELECT 1 AS fn_securitypredicate_result
WHERE
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('dbo')
-- application context
AND CONTEXT_INFO() = CONVERT(VARBINARY(128), @AppUserId);
GO
Creates a security policy for row
level security.
The following examples
demonstrate the use of the
CREATE SECURITY POLICY
syntax.
For an example of a complete
security policy scenario, see Row
Level Security.
Dynamic Data Masking
SQL Server 2016
Azure SQL Database
Dynamic Data Masking
Prevent the abuse of sensitive data by hiding it from users
Configuration made easy in the new
Azure portal
Table.CreditCardNo
4465-6571-7868-5796
4468-7746-3848-1978
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.)
4484-5434-6858-6550
Azure SQL Database
SQL Server 2016
CTP2+
Real-time data masking;
partial masking
Other security enhancements
Audit success/failure
of database
operations
Enhanced auditing
for OLTP with ability
to track history of
record changes
Transparent Data
Encryption support
for storage of Inmemory OLTP
Tables
Backup encryption
now supported with
compression
Security resources
MSDN Documentation
https://msdn.microsoft.com/en-us/library/dn765131.aspx
Security Center for SQL Server Database & SQL
Database
https://msdn.microsoft.com/en-us/bb510589.aspx
SQL Server Security Blog
Additional examples, useful tips and tricks
http://blogs.msdn.com/b/sqlsecurity/
SQL Server Label Security Toolkit
Updated version to take advantage of RLS
http://sqlserverlst.codeplex.com/
Real-time
operational analytics
Always Encrypted
End-to-end mobile BI
SELECT Name FROM Patients
WHERE SSN=@SSN
@SSN='198-33-0987'
App
0100101010110
In-memory
ColumnStore
PolyBase
Enhanced
ADO.NET
Library
In-memory
OLTP
Trusted
ETL
T-SQL query
Query
Column
Master
Key
Result Set
Name
SSN
Country
Denny Usher
198-33-0987
USA
SQL Server
Column
Encryption
Key
SQL Server
Result Set
Hadoop
Quote:
Name
SSN
Country
Denny Usher
0x7ff654ae6d
USA
$658.39
2-24
hrs
Real-time business
problem detection
dbo.Patients
Name
SSN
Country
Philip Wegner
1x7fg655se2e
USA
Denny Usher
0x7ff654ae6d
USA
Alicia Hodge
0y8fj754ea2c
USA
SELECT Name FROM
Patients WHERE
SSN=@SSN
@SSN=0x7ff654ae6d
Name
DOB
State
Denny Usher 11/13/58 WA
Gina Burch
04/29/76 WA
• Up to 30x faster transactions with
in-memory OLTP
• Queries from minutes to seconds
• Protect data at rest and in motion
• Without impacting
database performance
• Manage structured & unstructured data
• Simple T-SQL to query Hadoop (HDFS)
• JSON support
In-database
Advanced Analytics
Highest performing
data warehouse
Stretch database
On-premises
SMP
Data marts
SQL Server
Order history
Microsoft
Azure
MPP
Petabytescale
SQL Server +
Analytics Platform System
Cloud
SMP
Data marts
Mission critical OLTP
MPP
Petabytescale
• R built-in to your T-SQL
• Real-time operational analytics
without moving the data
• Open source R with in-memory &
massive scale – multi-threading and
massive parallel processing
Name
SSN
Date
Philip Wenger
cm61ba906fd
2/28/2005
Denny Usher
ox7ff654ae6d
3/18/2005
Alicia Hodge
i2y36cg776rg
4/10/2005
Alta Levy
nx290pldo90l
4/27/2005
Dionne Hardin
ypo85ba616rj
5/12/2005
Kristy Flowers
bns51ra806fd
5/22/2005
Sara Wiley
mci12hh906fj
6/07/2005
Whitney Lang
utb76b916gi
6/18/2014
Lorenzo Olds
px61hi9306fj
7/1/2014
Sophie Cook
ol43bi506gd
7/12/2014
Aida Durham
tx83hal916fi
7/29/2014
Stretch to cloud
Customer data
Product data
R built-in to SQL Server
SQL Server in Azure VM

Azure SQL Data Warehouse
• Scale to MPP on-premises &
in the cloud
• Simple T-SQL to manage structured
and unstructured data
• ½ the cost of Oracle Exadata
• In-memory built-in
• Real-time with direct query capabilities
• Powerful modeling with 250+ built-in
analytical functions
• Mobile reports with online
& offline access
• Modern data visualizations with
Reporting Services or Power BI
Order History
Query
• Data is encrypted & queryable
• Save money & improve
customer experience
• No application changes
App
Learn more!
www.microsoft.com/
SQLServer2016
Thank you!
[email protected]
@gwalters69
Resources:
http://blogs.msdn.com/b/data-platform/
http://www.microsoftvirtualacademy.com
http://channel9.msdn.com
https://azure.microsoft.com/en-us/support/trust-center/