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:
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.
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:
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 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 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 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.
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
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.
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.
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
The Corporate Security Policy requirements for the Employee table are captured below:
The finalized requirements can be achieved with the following features in SQL Server 2016:
Here is how each of the features help address the specific requirements.
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 |
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 feature is used to encrypt the salary data.
Column | Visible Result |
Salary | <encrypted> |
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
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
);
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
In this section, we will go over all the steps involved in setting up and securing the database to fulfill the customer requirements.
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
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
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
Follow the steps outlined below to encrypt the Salary information:
NOTE:
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
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
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
This section details how to demonstrate the security settings that have been applied on the data base.
Execute the following queries and inspect the results to see the role-based data access settings in action.
USE [ContosoHRDb]
GO
SELECT
*
FROM [Employee]
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'CorporateHR';
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'Sub1HR';
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'Sub2HR';
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'CorpExpense';
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'JohnDoe';
Executing the following queries to see how the Encryption setting affects the data results:
USE [ContosoHRDb]
GO
SELECT
*
FROM [Employee]
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'CorporateHR';
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'Sub1HR';
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'Sub2HR';
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'CorpExpense';
USE [ContosoHRDb]
GO
EXECUTE ('SELECT * FROM [Employee];')
AS
USER
=
'JohnDoe';
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
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
Now, let us review the changes that have been made on the Employee table.
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.
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.
Dynamic Data Masking | |
Row-Level Security | |
Always Encrypted | Always Encrypted (Database Engine) |
Temporal Tables | |
Standards |