Transcript Security

Security
in
2016 & Beyond
Kevin Howell
Microsoft
Data Platform
Technical Solutions Professional
September 2016
Intros
About Me
- Working with SQL Server since ver 6.5 (really 7.0)
- DBA at local insurance company for 15 years
- Now Data Platform TSP @ Microsoft®
About You
- Security Experts/Need of Security Experts
- Goals/Expectations for next 2 hours
- Specific Questions to Answer
Follow-Ups
- Presentation available @ http://www.sqlpsi.com
- Email me @ [email protected]
Agenda
• Security Discussion
• SQL 2016 Security Features
•
•
•
•
Dynamic Data Masking
Row Level Security
Always Encrypted
Stretch Database (time allowing)
• Azure Security
•
Advanced Threat Detection
SQL Server 2016: Everything built-in
80
built-in
built-in
built-in
70
$2,230
60
80
69
70
49
50
SQL Server
60
4943
50
SQL Server
43
40
34
SQL Server
40
29
30
20
30
10
6
34
2220
18
15
4
1
0
0
0
5
$480
22
29
3
3
0
120
built-in
built-in
69
2
SQL Server
3
4
Oracle
5
MySQL
10
#2
20
22
#3
Tableau
18
TPC-H
15
Oracle
Self-service BI per user
In-memory across all workloads
5
4
1
22
Microsoft
6
SAP HANA
6
0
#1
$120
Oracle
is #5
0
3
at massive scale
3
0
0
1
SQL Server
2
3
4
5
6
Consistent experience from on-premises to cloud
Oracle
MySQL
SAP HANA
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.
National Institute of Standards and Technology Comprehensive Vulnerability Database update 10/2015
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
4
SQL Server 2016 Improvements
Performance
Operational analytics
Insights on operational data;
Works with in-memory OLTP and
disk-based OLTP
In-memory OLTP
enhancements
Greater T-SQL surface area,
terabytes of memory supported,
and greater number of parallel
CPUs
Query data store
Security
Dynamic data masking
Real-time obfuscation of data to
prevent unauthorized access
Row-level security
Apply fine-grained access control
to table rows
Always encrypted
Sensitive data remains encrypted
at all times with ability to query
Other enhancements
Monitor and optimize query plans
Audit success/failure of database
operations
Native JSON
TDE support for storage of InMemory OLTP tables
Expanded support for JSON data
Temporal database
support
Query data as points in time
Enhanced auditing for OLTP with
ability to track history of record
changes
Availability
Enhanced AlwaysOn
Three synchronous replicas for
auto failover across domains
Round robin load balancing of
replicas
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 multi-core
environments
Dynamic Data Masking
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
(credit card numbers, SSN, etc.)
Security
Table.CreditCardNo
4465-6571-7868-5796
4468-7746-3848-1978
4484-5434-6858-6550
SQL Database
SQL Server 2016
Real-time data masking;
partial masking
Dynamic Data Masking Walkthrough
2)
App
user data
selects
from
Employee
table
1) Dynamic
Security
officer
defines
dynamic
data masking
policydata
in T-SQL
sensitive
3)
masking
policy
obfuscates
the sensitive
in theover
query
results data in Employee table
ALTER TABLE [Employee] ALTER COLUMN [SocialSecurityNumber]
ADD MASKED WITH (FUNCTION = ‘partial(0,”XXX-XX-”,4)’)
ALTER TABLE [Employee] ALTER COLUMN [Email]
ADD MASKED WITH (FUNCTION = ‘EMAIL()’)
ALTER TABLE [Employee] ALTER COLUMN [Salary]
ADD MASKED WITH (FUNCTION = ‘RANDOM(1,20000)’)
GRANT UNMASK to admin1
SELECT [Name],
[SocialSecurityNumber],
[Email],
[Salary]
FROM [Employee]
DEMO
Dynamic Data
Masking
4 DDM Functions
Default – masks entire data cell, based on data type
Syntax: MASKED WITH (FUNCTION = default()‘)
Email – shows 1st letter of email & “.com” for end
Syntax: MASKED WITH (FUNCTION = ‘email()‘)
Random – use on numeric data to show random # in range
Syntax: MASKED WITH (FUNCTION = 'random([start range], [end range])'
Example (Month): MASKED WITH (FUNCTION = 'random(1, 12)')
Custom – expose prefix & suffix, with custom padding for strings
Syntax: MASKED WITH (FUNCTION = 'partial(prefix,[padding],suffix)'
Example (SSN): MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)')
Row Level Security
Row-Level Security (RLS)
Protect data privacy by ensuring
the right access across rows
Customer 1
Fine-grained access control over specific rows in a
database table
Customer 2
•
Help prevent unauthorized access when multiple users
share the same tables, or to implement connection
filtering in multitenant applications
SQL Database
•
Administer via SQL Server Management Studio or SQL
Server Data Tools
•
Enforcement logic inside the database and schema is
bound to the table
•
Security
Customer 3
Common Row-Level Security Use Cases
Traditional RLS Workloads
Custom business logic to determine which rows each user can SELECT, INSERT, UPDATE, and DELETE
based on their role, department, and security level
Target sectors: Finance, insurance, healthcare, energy, and government
Multitenant Databases
Ensuring tenants can only access their own rows of data in a shared database, with enforcement
logic in the database rather than in the app tier
For example: multitenant shards with elastic database tools in SQL Database
Reporting, Analytics, and Data Warehousing
Different users access same database through various reporting tools, and work with different
subsets of data based on their identity/role
Security
RLS in three steps
Two
Three
App userPolicy
Security
(e.g., transparently
nurse) selectsrewrites
from Patients
query table
to apply filter predicate
Nurse
Database
Security
Policy
Patients
Application
SELECT * FROM Patients
Policy Manager
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 Patients
WHERE
= SUSER_SID() AND Patients.wing = d.Wing;
WITH e.UserSID
(STATE = ON)
Security
DEMO
Row Level
Security
Row Level Security - Predicate
CREATE SCHEMA <<SCHEMA>>; --BEST PRACTICE TO CREATE UNIQUE SCHEMA FOR RLS
GO
CREATE FUNCTION <<SCHEMA>>.<<PREDICATE FUNCTION>>(<<ACCOUNT PARAMETER>> AS sysname)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN SELECT 1 AS <<PREDICATE RESULT SET>>
WHERE <<ACCOUNT PARAMETER>> = USER_NAME() OR USER_NAME() = <<ELEVATED USER ACCOUNT>>;
EXAMPLE
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
Row Level Security - Policy
CREATE SECURITY POLICY <<POLICY NAME>>
ADD FILTER PREDICATE <<SCHEMA NAME>>.<<PREDICATE NAME>>(<<ACCOUNT PARAMETER>>)
ON <<TABLE NAME>>
WITH (STATE = ON);
EXAMPLE
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);
Always Encrypted
Protect data at rest and in motion, both on-premises and in the cloud
Data remains encrypted
during query
Apps
Encrypted
query
TDE-enabled
ADO.NET library
SQL Server
No app
changes
Master
key
Encrypted
columnar
key
Most secure database
Always Encrypted: Overview
Most secure database
Always Encrypted: How It Works
without impacting database performance
Always Encrypted
Apps
NEW 
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
Query
@SSN=0x7ff654ae6d
Enhanced
ADO.NET
Library
Result Set
Result Set
Name
198-33-0987
SSN
Column
Encryption
Key
Country
Denny Usher 0x7ff654ae6d
USA
ciphertext
dbo.Patients
Name
SSN
Country
Philip Wegner
1x7fg655se2e
243-24-9812 USA
Denny Usher
198-33-0987 USA
0x7ff654ae6d
Alicia Hodge
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
Security
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 plaintext
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
Security
Column
master key
(CMK)
CMK store:
Certificate store
HSM
Azure Key Vault
…
CMK
Encrypted CEK
Database
DEMO
Always
Encrypted
Always Encrypted Notes
Desktop or Web Application Changes
Add “Column Encryption Setting=enabled;” to connection string
Parameters must be explicitly/exactly defined
Encryption type
Deterministic – Use if JOINS are needed for encrypted data. Identical data encrypted similarly
Randomized – Use if JOINS are not needed. All data is encrypted uniquely
Key Stores
Windows Certificate Store – localized key store (on-prem use cases)
Azure Key Vault – centralized key store for (web or hybrid use cases)
SQL Threat Detection
SQL Threat Detection
What is it?
-
New security intelligence feature built into Azure SQL Database service
Profiles and detect anomalous database activities
Identifies potential threats to the database
Delivers immediate notifications about suspicious database activities as they occur
Recommends how to further investigate and mitigate the threat.
What does it track/prevent?
- Detects potential vulnerabilities and SQL injection attacks
- Tracks anomalous database access patterns
Use Cases
- Attempted database access by former employee
- Preventing SQL Injection attacks
- Anomalous access from home to production database
Resources
Dynamic Data Masking
On-Prem: https://msdn.microsoft.com/en-us/library/mt130841.aspx
Azure: https://azure.microsoft.com/en-us/documentation/articles/sqldatabase-dynamic-data-masking-get-started/
Row Level Security
https://msdn.microsoft.com/en-us/library/dn765131.aspx
Always Encrypted
https://msdn.microsoft.com/en-us/library/mt163865.aspx
https://msdn.microsoft.com/en-us/library/mt723359.aspx
SQL Threat Detection (Azure)
https://blogs.msdn.microsoft.com/sqlsecurity/2016/08/08/sql-threatdetection-your-built-in-security-expert/
Questions