Transcript SQLSaturday_Powerpoint_

SQL Server 2016 Security
Features
Marek Chmel
Microsoft MVP: Data Platform
Microsoft MCT: Regional Lead
MCSE: Data Platform
Certified Ethical Hacker
Sponsors
Session Agenda






Dynamic Data Masking
Row Level Security
Always Encrypted
Transparent Data Encryption
Threat Detection
Other Security Enhancements
Dynamic Data Masking
Regulatory
Compliance
Sensitive Data
Protection
Masking with Minimal Impact on Applications
 No need to modify existing application queries
 Complimentary to other data protection features
Dynamic Data Masking Walkthrough
ALTER TABLE [Employee] ALTER COLUMN
[SocialSecurityNumber]
ADD MASKED WITH (FUNCTION = ‘SSN()’)
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]
Non -Privileged User
Privileged User
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.
Dynamic Data Masking
DEMO
The need for row-level security
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 is bound to the table
Benefits of Row-Level Security
Store data intended for many consumers in a single database/table while at the
same time restricting row-level read and 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
Binds a predicate function to a particular table, applying it for all queries
Two types: filter predicates and blocking predicates (coming soon)
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
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
Fine-grained access control
over rows in a table based on
one or more pre-defined
filtering criteria, such as user’s
role or clearance level in
organization
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)

Concepts:

Predicate function

Security policy
Common RLS 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
Row Level Security
DEMO
The need for Always Encrypted
Allows customers to securely store sensitive data outside of their trust boundary.
Data remains protected from high-privileged, yet unauthorized users.
How it works
Help protect data at rest and in motion, on-premises and in the cloud
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
plaintext value
Key provisioning
Always Encrypted
DEMO
Support for Transparent Data Encryption


In SQL Server 2016, the storage for
memory-optimized tables will be
encrypted as part of enabling TDE
on the database
Simply follow the same steps as you
would for a disk-based database
Considerations for TDE
 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.
Transparent Data Encryption
DEMO
Database Threat Protection
 New feature on Azure SQL Database
 Works together with auditing
 Analyzes audit logs for possible
threats
 Notifies reactively if it finds an
anomaly
 Works with any tier of Azure SQL DB
 Requires a storage account for audit
logs
Evaluation forms
 Session evaluation forms
http://www.sqlsaturday.com/529/sessions/sessionevaluation.aspx
 Event evaluation forms
http://www.sqlsaturday.com/529/EventEval.aspx