Powerpoint

Download Report

Transcript Powerpoint 

Securing Data on your Terms
Kristina Rumpff
SQL Saturday #464, Melbourne
20th February 2016
Housekeeping
Mobile Phones
please set to “stun” during sessions
Evaluations
complete online to be in the draw for fantastic prizes
SESSIONS
EVENT
http://www.sqlsaturday.com/464/
sessions/sessionevaluation.aspx
http://www.sqlsaturday.com/464/
eventeval.aspx
Wifi Details
SSID: eduroam
Login: ext-sqlsat
Password: sqlsaturd4y
speaker
Connect with the Community
Event staff, volunteers and speakers
are here to help and answer
questions.
@SQLDropbear
I attack SQL
challenges by
dropping onto
them from above.
Scan the QR code on the speaker
badges to connect and network with
them.
ENCRYPTION
Encryption Summary
TRANSPARENT DATA
ENCRYPTION
What is TDE
 TDE Encrypts data at rest
 This occurs at the file level
 Data within the database is stored in plain
text
Database Level
Table Level
File Level
dbo.Patients
Name
SSN
Country
Jane Doe
198-33-0987
USA
Jim Gray
200-45-7569
USA
John Smith
374-29-0782
USA
dbo.Payment
Name
CardNumber
Jane Doe
2467-3874-8234-2245
Jim Gray
2467-8739-2834-7342
John Smith
3845-9384-8892-8234
Considerations
 Key management
• Backup
• History
 Always On
 You will need the same encryption key across all db’s in the
group
 TempDB
 The TempDB will be encrypted if any database on the instance
is encrypted.
Demo
CELL LEVEL ENCRYPTION
What is Cell Level Encryption
 A symmetric key is used to encrypt the data as it
is inserted, and to decrypt as it is read.
 The end result is that the data is stored
encrypted.
 The database engine encrypts/decrypts.
Considerations
 Key management
• Backup
• History
 Import/Export
 The encrypt/decrypt is part of the T-SQL
statement
Database Level
Table Level
File Level
dbo.Patients
Name
SSN
Country
Jane Doe
198-33-0987
USA
Jim Gray
200-45-7569
USA
John Smith
374-29-0782
USA
dbo.Payment
Name
CardNumber
Jane Doe
2467-3874-8234-2245
Jim Gray
2467-8739-2834-7342
John Smith
3845-9384-8892-8234
Apps
SQL Server
Trusted
Client side
SELECT Name FROM
Patients WHERE SSN=@SSN
SELECT Name FROM
Patients WHERE SSN=@SSN
@SSN=0x7ff654ae6d
Query
@SSN=0x7ff654ae6d
Result Set
Name
Jim Gray
Result Set
Name
Jim Gray
ciphertext
dbo.Patients
Name
SSN
Country
Jane Doe
243-24-9812 USA
1x7fg655se2e
Jim Gray
198-33-0987
0x7ff654ae6d
USA
John Smith
123-82-1095
0y8fj754ea2c
USA
Demo
ALWAYS ENCRYPTED
What is Always Encrypted
 A way to encrypt your data with minimal
change the application.
 Data is encrypted in transit and at rest.
Database Level
Table Level
File Level
dbo.Patients
Name
SSN
Country
Jane Doe
198-33-0987
USA
Jim Gray
200-45-7569
USA
John Smith
374-29-0782
USA
dbo.Payment
Name
CardNumber
Jane Doe
2467-3874-8234-2245
Jim Gray
2467-8739-2834-7342
John Smith
3845-9384-8892-8234
Help protect data at rest and in motion, on-premises & cloud
Apps
SQL Server
Trusted
Client side
SELECT Name FROM
Patients WHERE SSN=@SSN
@SSN='198-33-0987'
Column
Master
Key
Result Set
Name
Jim Gray
Query
SELECT Name FROM
Patients WHERE SSN=@SSN
@SSN=0x7ff654ae6d
Enhanced
ADO.NET
Library
Result Set
Column
Encryption
Key
Name
Jim Gray
ciphertext
dbo.Patients
Name
SSN
Country
Jane Doe
243-24-9812 USA
1x7fg655se2e
Jim Gray
198-33-0987
0x7ff654ae6d
USA
John Smith
123-82-1095
0y8fj754ea2c
USA
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
Considerations
Key management
 The keys need to be backed up
 Always on replica’s need the same keys
Client
 The application client needs access to the key!
 Insert data into encrypted columns by passing the values in SqlParameter
objects.
Demo
ROW LEVEL SECURITY
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 schemabound 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.
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
Example
CREATE FUNCTION dbo.fn_securitypredicate(@wing int)
RETURNS TABLE WITH SCHEMABINDING AS
return SELECT 1 as [fn_securitypredicate_result]
FROM
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.
StaffDuties d INNER JOIN Employees e
ON (d.EmpId = e.EmpId)
Concepts:
WHERE e.UserSID = SUSER_SID()
Predicate function
AND @wing = d.Wing;
CREATE SECURITY POLICY dbo.SecPol
ADD FILTER PREDICATE dbo.fn_securitypredicate(Wing)
ON Patients
WITH (STATE = ON)
Security policy
RLS in Three Steps
One
Two
Three
Policyuser
App
Security
manager
Policy
(e.g., transparently
nurse)
createsselects
filter rewrites
predicate
from Patients
query
and security
table
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)
Demo
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
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
SQL Database
SQL Server 2016
CTP2
Real-time data masking;
partial masking
Masking Types
Default
Full masking according to the data types of the designated field:
i.e. String will result in “XXXX”
Email
Masking will expose the first letter of an email address and will end in “.com”
i.e. [email protected]
Custom String
Will expose the first and last letter and adds a custom padding string in the middle.
i.e KXXXa
Random
For use only on numeric. Will replace the original value within a specified range.
Demo
Questions?
Please make sure you visit our fantastic sponsors:
How did we do?
Please complete an online Evaluation to be included the draw for a
fantastic prize! There is a prize for each session timeslot and for the
overall event survey – so the more feedback you provide the more
chances you have.
Session Surveys
Post-Event Survey
http://www.sqlsaturday.com/464/
sessions/sessionevaluation.aspx
http://www.sqlsaturday.com/464/
eventeval.aspx