Overview



left-914400SQL 2016 Secure Database0SQL 2016 Secure DatabaseSecure Database using SQL Server 2016Version 1.0June 2016Prepared byMicrosoft ServicesTable of Contents TOC \o "1-3" \h \z \u 1Overview PAGEREF _Toc453230401 \h 51.1DBA Considerations PAGEREF _Toc453230402 \h 51.2SQL Server 2016 Capabilities for a Secure Database PAGEREF _Toc453230403 \h 61.2.1Dynamic Data Masking PAGEREF _Toc453230404 \h 61.2.2Row-Level Data Security PAGEREF _Toc453230405 \h 61.2.3Always Encrypted Data PAGEREF _Toc453230406 \h 61.2.4Temporal Tables PAGEREF _Toc453230407 \h 62Example Scenario PAGEREF _Toc453230408 \h 82.1Setting data scope PAGEREF _Toc453230409 \h 82.2Implementing Security Policy on Database tables PAGEREF _Toc453230410 \h 92.3Identifying Security Requirements PAGEREF _Toc453230411 \h 102.4Solution Design PAGEREF _Toc453230412 \h 102.4.1Dynamically Masked Data PAGEREF _Toc453230413 \h 112.4.2Filtered Data using Row-Level Security PAGEREF _Toc453230414 \h 112.4.3Always Encrypted Data PAGEREF _Toc453230415 \h 122.5Creating the HR Database with Employee table PAGEREF _Toc453230416 \h 122.5.1Creating the Employee Database PAGEREF _Toc453230417 \h 122.5.2Creating the Employee Table PAGEREF _Toc453230418 \h 122.5.3Populating the Employee Table PAGEREF _Toc453230419 \h 132.6Securing the Customer Data PAGEREF _Toc453230420 \h 132.6.1Creating the User Roles and Users PAGEREF _Toc453230421 \h 132.6.2Adding the Column Masks for the Birthdate and SSN Columns PAGEREF _Toc453230422 \h 142.6.3Grant Unmask Access for the Corporate HR roles PAGEREF _Toc453230423 \h 142.6.4Update the Salary Column to Encrypted PAGEREF _Toc453230424 \h 152.6.5Creating the Access Predicate Function PAGEREF _Toc453230425 \h 222.6.6Creating the Security Policy using the Access Predicate Function PAGEREF _Toc453230426 \h 222.6.7Adding a Temporal History Table PAGEREF _Toc453230427 \h 232.7Demonstrating the Secure Results PAGEREF _Toc453230428 \h 232.7.1Column Encryption Setting = disabled PAGEREF _Toc453230429 \h 232.7.2Column Encryption Setting = enabled PAGEREF _Toc453230430 \h 252.7.3Delete an Employee from the Employee Table (Add History) PAGEREF _Toc453230431 \h 272.7.4Change an Employee’s SSN (Add History) PAGEREF _Toc453230432 \h 272.7.5Inspect the Employee Table History PAGEREF _Toc453230433 \h 273References PAGEREF _Toc453230434 \h 29OverviewOver 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 motionDynamic Data Masking mask sensitive data partially with minimal application impact Row Level Security to restrict access to data based on User privilegeTemporal Database for auditing, detecting malicious data modification and incident recoveryThe 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 ConsiderationsFor 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 elementsIf data access is allowed, then is it full or partialHow do the user roles get access to the data i.e which applications and delivery methods are used to access the dataWhich data elements can and cannot be modifiedAuditing and incident recovery requirementsSQL Server 2016 Capabilities for a Secure Database Dynamic Data MaskingDynamic 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 SecurityRow 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 DataAlways 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 TablesTemporal 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.Example ScenarioA 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 belowSensitive PII data including the SSN, Salary, Age, and Gender are only visible to the HR staff for employees in their purviewEmployees 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 departmentEmployee records should not be deleted from the databaseContoso 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 scopeThe 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 tablesOnce 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 TableIdentifying Security RequirementsThe Corporate Security Policy requirements for the Employee table are captured below:Solution DesignThe finalized requirements can be achieved with the following features in SQL Server 2016:Dynamic Data MaskingRow-Level Data SecurityAlways Encrypted DataTemporal DB feature to identify changes made on the database.Here is how each of the features help address the specific requirements.Dynamically Masked DataWe will use this feature to mask the selected data fields - The column data will be displayed as follows when masked.ColumnMasked OutputBirthdate1900-01-01SSNxxx-xx-nnnnFiltered Data using Row-Level SecurityUsing RLS, row data will be filtered as follows.UserFiltered ResultsCorporateAll employee recordsSubsidiary 1All employees from subsidiary 1Subsidiary 2All employees from subsidiary 2Accounting All resultsOtherNo resultsAlways Encrypted DataAlways Encrypted feature is used to encrypt the salary data.ColumnVisible ResultSalary<encrypted>Creating the HR Database with Employee tableCreating the Employee DatabaseFirst 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 )GOCreating the Employee TableEmployee 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 TableOnce 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]GOINSERT 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)GOSecuring the Customer DataIn 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 UsersIn the customer organization, there are different departments, each having users in various roles with a different set of access privileges. USE [ContosoHRDb]GOCREATE ROLE [Corp_HR_Department]GOCREATE ROLE [Sub1_HR_Department]GOCREATE ROLE [Sub2_HR_Department]GOCREATE ROLE [Accounting_Department]GOCREATE USER CorporateHR WITHOUT LOGIN;GOALTER ROLE db_datareader ADD MEMBER CorporateHRGOALTER ROLE db_datawriter ADD MEMBER CorporateHRGOALTER ROLE db_accessadmin ADD MEMBER CorporateHRGOALTER ROLE Corp_HR_Department ADD MEMBER CorporateHRGOCREATE USER Sub1HR WITHOUT LOGIN;GOALTER ROLE db_datareader ADD MEMBER Sub1HRGOALTER ROLE db_datawriter ADD MEMBER Sub1HRGOALTER ROLE Sub1_HR_Department ADD MEMBER Sub1HRGOCREATE USER Sub2HR WITHOUT LOGIN;GOALTER ROLE db_datareader ADD MEMBER Sub2HRGOALTER ROLE db_datawriter ADD MEMBER Sub2HRGOALTER ROLE Sub2_HR_Department ADD MEMBER Sub2HRGOCREATE USER CorpExpense WITHOUT LOGIN;GOALTER ROLE db_datareader ADD MEMBER CorpExpenseGOALTER ROLE Accounting_Department ADD MEMBER CorpExpenseGOCREATE USER JohnDoe WITHOUT LOGIN;GOALTER ROLE db_datareader ADD MEMBER JohnDoeGOAdding the Column Masks for the Birthdate and SSN ColumnsTo 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)')GOGrant Unmask Access for the Corporate HR rolesTo 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;GOUpdate the Salary Column to EncryptedFollow the steps outlined below to encrypt the Salary information:Select Salary column and launch encryption wizardClick Next on Introduction screen to select columnsSelect Deterministic Encryption as the Encryption type for the columnNOTE: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.For configuring the Master Key select Auto generate option and select the Windows Certificate Store as the Key store providerClick Next to review encryption settingsReview settings and click Finish to start encryption process. Ensure encryption is finished successfully and click CloseCreating the Access Predicate FunctionA predicate defines a logical condition being applied to rows in a table. Set access predicates as follows:USE [ContosoHRDb]GOCREATE SCHEMA ContosoHRSecurity;GOCREATE FUNCTION ContosoHRSecurity.accessPredicate(@GeoLocation AS sysname) RETURNS TABLE WITH SCHEMABINDINGAS 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) )GOCreating the Security Policy using the Access Predicate FunctionHere is how to create a security policy using the Access Predicate function:use [ContosoHRDb]CREATE SECURITY POLICY ContosoHRSecurity.EmployeeFilterADD FILTER PREDICATE ContosoHRSecurity.accessPredicate(GeoLocation) ON dbo.EmployeeWITH (STATE = ON);GOAdding a Temporal History TableAdd a Temporal History table, leveraging the Temporal DB feature, as follows:USE ContosoHRDbGOCREATE 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);GOALTER TABLE Employee SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = EmployeeHistory.Employee));GODemonstrating the Secure ResultsThis section details how to demonstrate the security settings that have been applied on the data base.Column Encryption Setting = disabledExecute the following queries and inspect the results to see the role-based data access settings in action.USE [ContosoHRDb]GOSELECT * FROM [Employee]The SQL Admin is able to see all the employee data except the salary in the clear as shown below.USE [ContosoHRDb]GOEXECUTE ('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]GOEXECUTE ('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]GOEXECUTE ('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]GOEXECUTE ('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]GOEXECUTE ('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 = enabledExecuting the following queries to see how the Encryption setting affects the data results:USE [ContosoHRDb]GOSELECT * FROM [Employee]The SQL Admin is able to see all the employee data in the clear as shown below.USE [ContosoHRDb]GOEXECUTE ('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]GOEXECUTE ('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]GOEXECUTE ('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]GOEXECUTE ('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]GOEXECUTE ('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 ContosoHRDbGOEXECUTE ('DELETE FROM [Employee] WHERE EmployeeID = ''00000006'';') AS USER = 'CorporateHR';GOChange an Employee’s SSN (Add History)Update the SSN field for an Employee as follows:USE ContosoHRDbGO-- Change an employees SSNUPDATE EmployeeSET SSN = '999-99-9999'WHERE EmployeeID = '00000001'GOInspect the Employee Table HistoryNow, let us review the changes that have been made on the Employee table.Determine if any Employees have been deletedExecute this query to see if any records have been removed from the Employee table:USE ContosoHRDbGO-- Determine if any of the employees have been deletedSELECT 'Deleted Employee', * FROM [Employee] FOR SYSTEM_TIME AS OF '4-1-2016' e1LEFT JOIN [Employee] e2 ON e1.EmployeeID = e2.EmployeeIDWHERE e2.EmployeeID IS NULLGOAssuming 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 modifiedExecute this query to determine if the SSN field has been modified for any of the Employees:USE ContosoHRDbGO-- Determine if any of the SSNs have been changed (Current SSN)SELECT 'Current SSN', e1.EmployeeID, e1.FirstName, e1.LastName, e1.SSN FROM [Employee] e1LEFT JOIN [Employee] FOR SYSTEM_TIME AS OF '4-1-2016' e2 ON e1.EmployeeID = e2.EmployeeIDWHERE e1.SSN != e2.SSNGO-- 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' e1LEFT JOIN [Employee] e2 ON e1.EmployeeID = e2.EmployeeIDWHERE e1.SSN != e2.SSNGOAssuming an Employee’s SSN never changes, this history audit exposes that an employee’s SSN had been tampered with.ReferencesDynamic Data MaskingDynamic Data MaskingDynamic Data Masking in Azure SQL Database (Video)Row-Level Security 2016 Row Level Security (Video) Always EncryptedAlways Encrypted (Database Engine)SQL Server 2016 Always Encrypted (Video) Getting Started with Always Encrypted with SSMS (Video)Temporal TablesTemporal Tables Temporal in SQL Server 2016 (Video) StandardsPCI Standards Summary of the HIPAA Privacy Rule ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download