Secure Database using SQL Server 2016

Overview

Over the last few years there has been a huge focus on data security in light of increasingly high profile data breaches and security incidents. This had led to customers adopting increasingly rigorous data classification and security policies. These policies have been implemented through application code, best practice guidance and complex and often incomplete auditability support. This leads to increased cost to implement and maintain these policies. SQL Server 2016 introduces a number of major capabilities which enable native database level support for centralized implementation and audit of data security policies. With these capabilities hosters can offer Secure database implementations to their customers. For the implementation, the following SQL Server 2016 capabilities are leveraged:

• TDE and Always Encrypted to protect data at rest and in motion

• Dynamic Data Masking mask sensitive data partially with minimal application impact

• Row Level Security to restrict access to data based on User privilege

• Temporal Database for auditing, detecting malicious data modification and incident recovery

The following sections explain these capabilities and also enumerate considerations that the Hosting Service Provider (HSP) database administrator has to keep in mind while implementing this offer.

DBA Considerations

For a secure database scenario, the most important aspect is to ensure close collaboration with the customer to clearly understand the specific data elements and their security requirements. These requirements are driven by corporate security policies or in response to specific compliance programs like SOX, PCI or HIPAA. These requirements typically fall into the following categories:

• Which user roles in the organization need access to which data elements

• If data access is allowed, then is it full or partial

• How do the user roles get access to the data i.e which applications and delivery methods are used to access the data

• Which data elements can and cannot be modified

• Auditing and incident recovery requirements

SQL Server 2016 Capabilities for a Secure Database

Dynamic Data Masking

Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. It helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It's a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. For more details, check here.

Row-Level Data Security

Row Level Security (RLS) restricts which users can view what data in a table, based on a function. This is very useful in multi-tenant environments where you may want to limit data access based on customer ID. RLS enables customers to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context) and greatly simplifies the design and coding of security in your application. RLS enables you to implement restrictions on data row access. For more details, check here.

Always Encrypted Data

Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to encrypt data at rest and in use for storage in Azure, to enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff. This feature enables data to stay encrypted in transit, at rest and while it is alive in the database. For more details, check here.

Temporal Tables

Temporal tables feature brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution, which is facilitated by temporal tables, such as in case of calculating trends over time, auditing data changes and performing data forensics, reconstructing data state at any time in the past and so on. For more details, check here.

In the rest of this document, we focus on implementing the Secure database using the above capabilities in the context of a customer scenario.

1. Example Scenario

A Geo-Distributed corporation Contoso Inc. has two semi-autonomous subsidiaries ContosoSub1 and ContosoSub2. An Internal HR Application is used by the Corporate HR department as well as the HR departments of the individual subsidiaries. This application is backed by a sensitive database which contains employee details such as Social Security Number (SSN), Age, Gender, Address, Salary and seniority level. The same application is used by the Corporate Expense and Accounting Department to review employees with expense policy violations. With these requirements, Contoso approaches the HSP.

Contoso Corporate Security department defines the data security policy as below

• Sensitive PII data including the SSN, Salary, Age, and Gender are only visible to the HR staff for employees in their purview

• Employees from one subsidiary can only see data for employees in their subsidiary

• The Expense Department is not allowed to see any of the data but needs to uniquely identify the employees.

• SSN cannot be modified following first paycheck without approval from security department

• Employee records should not be deleted from the database

Contoso Corporate Security would like to implement this security policy natively in the database rather than worry about different applications that access this data now or in future.

Setting data scope

The first step is to determine all of the tables and columns that must be secured and setting scope on these columns based on the policy. To assist with this task, it is recommended to create a table or spreadsheet that can be used to gather and refine the security requirements. This may take several iterations with the customer, but you should have confidence in the final table requirements before beginning to implement them.

Shared below is an example that can be considered as a starting point that can be customized:

_Corporations often have very similar data tables so it might be beneficial to maintain a library of forms that can be easily tailored for each specific type of data that needs to be secured - for example templates for HR data, HIPAA data, PCI data, etc.
_

Implementing Security Policy on Database tables

Once the security requirements have been finalized, the next step is for the Database Administrator to design and implement the security policy on the tables. For the Contoso database example, the policy is implemented on the employees table. An overview of the information shared in the table, along with the security requirements for the data are detailed below.

**Employee Table
**

Identifying Security Requirements

The Corporate Security Policy requirements for the Employee table are captured below:

Solution Design

The finalized requirements can be achieved with the following features in SQL Server 2016:

• Dynamic Data Masking
• Row-Level Data Security
• Always Encrypted Data
• Temporal DB feature to identify changes made on the database.

Here is how each of the features help address the specific requirements.

Dynamically Masked Data

We will use this feature to mask the selected data fields - The column data will be displayed as follows when masked.

Column	Masked Output
Birthdate	1900-01-01
SSN	xxx-xx-nnnn

Filtered Data using Row-Level Security

Using RLS, row data will be filtered as follows.

User	Filtered Results
Corporate	All employee records
Subsidiary 1	All employees from subsidiary 1
Subsidiary 2	All employees from subsidiary 2
Accounting	All results
Other	No results

Always Encrypted Data

Always Encrypted feature is used to encrypt the salary data.

Column	Visible Result
Salary	<encrypted>

Creating the HR Database with Employee table

Creating the Employee Database

First step is to create the employee databased, based on the design finalized following discussions with the customer.

USE [master]
GO

/****** Object: Database [ContosoHRDb] ******/
CREATE
DATABASE [ContosoHRDb]
CONTAINMENT =
NONE

ON
PRIMARY

( NAME =
N'ContosoHRDb',
FILENAME
=
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ContosoHRDb.mdf'
, SIZE = 8192KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 65536KB )

LOG
ON

( NAME =
N'ContosoHRDb_log',
FILENAME
=
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ContosoHRDb_log.ldf'
, SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

Creating the Employee Table

Employee information is stored in a table, which is created as follows:

USE [ContosoHRDb]

CREATE
TABLE Employee
(
EmployeeID varchar(10)
NOT
NULL
PRIMARY
KEY
CLUSTERED,
Alias sysname,
Department sysname,
GeoLocation sysname,
FirstName varchar(20),
LastName varchar(20),
MiddleInitial varchar(1),
Gender varchar(1),
Birthdate date,
SSN varchar(11),
Salary money
);

Populating the Employee Table

Once the Employee Table is in place, employee information can be added to the same. This will typically be accomplished using an integrated application. For the purpose of this walkthrough, we can add some demo records as follows:

USE [ContosoHRDb]
GO

INSERT
INTO [dbo].[Employee]
([EmployeeID],[Alias],[Department],[GeoLocation],[FirstName],[LastName],[MiddleInitial],[Gender],[Birthdate],[SSN],[Salary])

VALUES

('00000001','JohnDoe','HR','Corp','John','Doe','A','M','3-12-1995','111-22-3333',45000.00),

('00000002','SteveSmith','HR','Sub1','Steve','Smith','C','M','2-2-1985','111-22-3334',90000.00),

('00000003','SusanJones','HR','Sub2','Susan','Jones','P','F','6-16-1966','111-22-3335',100000.00),

('00000004','PeterJohnson','Engineering','Sub1','Peter','Johnson','K','M','3-7-1970','111-22-3336',750000.00),

('00000005','JaneDoe','Shipping','Sub2','Jane','Doe','N','F','8-1-1990','111-22-3337',35000.00),

('00000006','KevinMcDonald','Shipping','Sub2','Kevin','McDonald','O','M','10-1-1988','111-22-3338',32000.00)
GO

Securing the Customer Data

In this section, we will go over all the steps involved in setting up and securing the database to fulfill the customer requirements.

Creating the User Roles and Users

In the customer organization, there are different departments, each having users in various roles with a different set of access privileges.

USE [ContosoHRDb]
GO

CREATE
ROLE [Corp_HR_Department]
GO

CREATE
ROLE [Sub1_HR_Department]
GO

CREATE
ROLE [Sub2_HR_Department]
GO

CREATE
ROLE [Accounting_Department]
GO

CREATE
USER CorporateHR WITHOUT
LOGIN;
GO
ALTER
ROLE db_datareader ADD MEMBER CorporateHR
GO
ALTER
ROLE db_datawriter ADD MEMBER CorporateHR
GO
ALTER
ROLE db_accessadmin ADD MEMBER CorporateHR
GO
ALTER
ROLE Corp_HR_Department ADD MEMBER CorporateHR
GO

CREATE
USER Sub1HR WITHOUT
LOGIN;
GO
ALTER
ROLE db_datareader ADD MEMBER Sub1HR
GO
ALTER
ROLE db_datawriter ADD MEMBER Sub1HR
GO
ALTER
ROLE Sub1_HR_Department ADD MEMBER Sub1HR
GO

CREATE
USER Sub2HR WITHOUT
LOGIN;
GO
ALTER
ROLE db_datareader ADD MEMBER Sub2HR
GO
ALTER
ROLE db_datawriter ADD MEMBER Sub2HR
GO
ALTER
ROLE Sub2_HR_Department ADD MEMBER Sub2HR
GO

CREATE
USER CorpExpense WITHOUT
LOGIN;
GO
ALTER
ROLE db_datareader ADD MEMBER CorpExpense
GO
ALTER
ROLE Accounting_Department ADD MEMBER CorpExpense
GO

CREATE
USER JohnDoe WITHOUT
LOGIN;
GO
ALTER
ROLE db_datareader ADD MEMBER JohnDoe
GO

Adding the Column Masks for the Birthdate and SSN Columns

To set masks for birth date and SSN, use the following queries:

USE [ContosoHRDb]
GO

/* Mask the employee birthdate */
ALTER
TABLE Employee ALTER
COLUMN [Birthdate] ADD
MASKED
WITH (FUNCTION='default()')
GO

/* Mask the employee social security number XXX-XX-nnnn */
ALTER
TABLE Employee ALTER
COLUMN [SSN] ADD
MASKED
WITH (FUNCTION='partial(0,"XXX-XX-",4)')
GO

Grant Unmask Access for the Corporate HR roles

To unmask data fields for HR roles, do the following steps:

USE [ContosoHRDb]
GO

/* Allow the Corporate HR user to see the masked data */
GRANT UNMASK TO CorporateHR;

/* Allow the Subsidiary 1 HR user to see the masked data */
GRANT UNMASK TO Sub1HR;

/* Allow the Subsidiary 2 HR user to see the masked data */
GRANT UNMASK TO Sub2HR;
GO

Update the Salary Column to Encrypted

Follow the steps outlined below to encrypt the Salary information:

1. Select Salary column and launch encryption wizard

   

2. Click Next on Introduction screen to select columns

   

3. Select Deterministic Encryption as the Encryption type for the column

   

   _NOTE:
   _

• _Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining tables based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. This weakness is increased when there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.
  _
• _Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns.
  _

1. For configuring the Master Key select Auto generate option and select the Windows Certificate Store as the Key store provider

   

2. Click Next to review encryption settings

   

3. Review settings and click Finish to start encryption process. Ensure encryption is finished successfully and click Close

   

   

   Creating the Access Predicate Function

   A predicate defines a logical condition being applied to rows in a table. Set access predicates as follows:

   USE [ContosoHRDb]
   GO

   CREATE
   SCHEMA ContosoHRSecurity;
   GO

   CREATE
   FUNCTION ContosoHRSecurity.accessPredicate(@GeoLocation AS
   sysname)

   RETURNS
   TABLE

   WITH
   SCHEMABINDING
   AS

   RETURN
   SELECT 1 AS accessResult FROM dbo.Employee

   WHERE

   (
           (IS_MEMBER('Corp_HR_Department')
   = 1)
           OR
           ((IS_MEMBER('Sub1_HR_Department')
   = 1)
   AND
   (@GeoLocation =
   'Sub1'))
           OR
           ((IS_MEMBER('Sub2_HR_Department')
   = 1)
   AND
   (@GeoLocation =
   'Sub2'))
           OR
           (IS_MEMBER('Accounting_Department')
   = 1)
           OR
           (IS_MEMBER('db_owner')
   = 1)

   )
   GO

   Creating the Security Policy using the Access Predicate Function

   Here is how to create a security policy using the Access Predicate function:

   use [ContosoHRDb]

   CREATE
   SECURITY
   POLICY ContosoHRSecurity.EmployeeFilter

   ADD
   FILTER
   PREDICATE ContosoHRSecurity.accessPredicate(GeoLocation)
   ON dbo.Employee

   WITH (STATE
   =
   ON);

   GO

   Adding a Temporal History Table

   Add a Temporal History table, leveraging the Temporal DB feature, as follows:

   USE ContosoHRDb
   GO

   CREATE
   SCHEMA EmployeeHistory;

   GO

   ALTER
   TABLE Employee

   ADD

   SysStartTime datetime2(0)
   GENERATED
   ALWAYS
   AS
   ROW
   START
   HIDDEN

   CONSTRAINT DF_SysStart DEFAULT
   CONVERT(datetime2 (0),
   '2000-1-1 23:59:59'),
   SysEndTime datetime2(0)
   GENERATED
   ALWAYS
   AS
   ROW
   END
   HIDDEN

   CONSTRAINT DF_SysEnd DEFAULT
   CONVERT(datetime2 (0),
   '9999-12-31 23:59:59'),

   PERIOD
   FOR
   SYSTEM_TIME (SysStartTime, SysEndTime);
   GO

   ALTER
   TABLE Employee

   SET (SYSTEM_VERSIONING
   =
   ON (HISTORY_TABLE = EmployeeHistory.Employee));
   GO

   Demonstrating the Secure Results

   This section details how to demonstrate the security settings that have been applied on the data base.

   Column Encryption Setting = disabled

   Execute the following queries and inspect the results to see the role-based data access settings in action.

   USE [ContosoHRDb]
   GO
   SELECT
   *
   FROM [Employee]

• The SQL Admin is able to see all the employee data except the salary in the clear as shown below.

USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'CorporateHR';

• Corporate HR role is able to see all the employee data except the salary in the clear from corporate, subsidiary 1 and subsidiary 2 as shown below.

USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'Sub1HR';

• Subsidiary 1 HR role is able to see all the employee data except the salary in the clear, however the results are restricted to the subsidiary 1 employees as shown below.

USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'Sub2HR';

• Subsidiary 2 HR role is able to see all the employee data in the clear except the salary, however the results are restricted to the subsidiary 3 employees as shown below.

USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'CorpExpense';

• The Accounting role is able to see the employee records from corporate and the subsidiaries, however the sensitive data is masked or encrypted as shown below.

USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'JohnDoe';

• The employee from the shipping department (John Doe) is not able to view any of the employee data as shown below.

Column Encryption Setting = enabled

Executing the following queries to see how the Encryption setting affects the data results:

USE [ContosoHRDb]
GO
SELECT
*
FROM [Employee]

• The SQL Admin is able to see all the employee data in the clear as shown below.

USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'CorporateHR';

• Corporate HR user is able to see all the employee data in the clear from corporate, subsidiary 1 and subsidiary 2 as shown below.

USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'Sub1HR';

• Subsidiary 1 HR user is able to see all the employee data in the clear, however the results are restricted to the subsidiary 1 employees as shown below.

USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'Sub2HR';

• Subsidiary 2 HR user is able to see all the employee data in the clear, however the results are restricted to the subsidiary 3 employees as shown below.

USE [ContosoHRDb]
GO

EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'CorpExpense';

• The Accounting user is able to see the employee records from corporate and the subsidiaries, however the sensitive data is masked as shown below.

USE [ContosoHRDb]
GO

EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'JohnDoe';

• The user from the shipping department (John Doe) is not able to view any of the employee data as shown below.

Delete an Employee from the Employee Table (Add History)

The next few steps are intended to demonstrate how the policy of not modifying SSN or deleting the employee records is tracked and implemented using the temporal database feature. Here, we are deleting an Employee record.

USE ContosoHRDb
GO

EXECUTE ('DELETE FROM [Employee] WHERE EmployeeID = ''00000006'';')
AS
USER
=
'CorporateHR';
GO

Change an Employee's SSN (Add History)

Update the SSN field for an Employee as follows:

USE ContosoHRDb
GO

-- Change an employees SSN
UPDATE Employee
SET SSN =
'999-99-9999'
WHERE EmployeeID =
'00000001'
GO

Inspect the Employee Table History

Now, let us review the changes that have been made on the Employee table.

Determine if any Employees have been deleted

Execute this query to see if any records have been removed from the Employee table:

USE ContosoHRDb
GO

-- Determine if any of the employees have been deleted
SELECT
'Deleted Employee',
*
FROM [Employee] FOR
SYSTEM_TIME
AS
OF
'4-1-2016' e1
LEFT
JOIN [Employee] e2 ON e1.EmployeeID = e2.EmployeeID
WHERE e2.EmployeeID IS
NULL
GO

_Assuming Contoso had a policy that all employee records were never deleted, this history audit exposed some tampering had occurred on the Employee table.
_

Determine if any of the Employee SSNs have been modified

Execute this query to determine if the SSN field has been modified for any of the Employees:

USE ContosoHRDb
GO

-- Determine if any of the SSNs have been changed (Current SSN)
SELECT
'Current SSN', e1.EmployeeID, e1.FirstName, e1.LastName, e1.SSN FROM [Employee] e1
LEFT
JOIN [Employee] FOR
SYSTEM_TIME
AS
OF
'4-1-2016' e2 ON e1.EmployeeID = e2.EmployeeID
WHERE e1.SSN != e2.SSN
GO
-- Determine if any of the SSNs have been changed (Temporal Table SSN)
SELECT
'History SSN', e1.EmployeeID, e1.FirstName, e1.LastName, e1.SSN FROM [Employee] FOR
SYSTEM_TIME
AS
OF
'4-1-2016' e1
LEFT
JOIN [Employee] e2 ON e1.EmployeeID = e2.EmployeeID
WHERE e1.SSN != e2.SSN
GO

_Assuming an Employee's SSN never changes, this history audit exposes that an employee's SSN had been tampered with.
_

References

Dynamic Data Masking	Dynamic Data Masking Dynamic Data Masking in Azure SQL Database (Video)
Row-Level Security	https://msdn.microsoft.com/en-us/library/dn765131.aspx SQL 2016 Row Level Security (Video)
Always Encrypted	Always Encrypted (Database Engine) SQL Server 2016 Always Encrypted (Video) Getting Started with Always Encrypted with SSMS (Video)
Temporal Tables	Temporal Tables Temporal in SQL Server 2016 (Video)
Standards	PCI Standards Summary of the HIPAA Privacy Rule

---