© Peter A. Carter 2018
Peter A. CarterSecuring SQL Serverhttps://doi.org/10.1007/978-1-4842-4161-5_4

4. Data-Level Security

Peter A. Carter1 
(1)
London, UK
 

Below the principal hierarchy, SQL Server provides a rich set of functionality for securing data. This chapter will discuss the appropriate use of schemas, ownership chaining, impersonation, row-level security, and dynamic data masking.

Schemas

Schemas provide a logical namespace for database objects and provide a layer of abstraction between objects and their owners. Every object within a database must be owned by a database user. In much older versions of SQL Server, this ownership was direct. In other words, a user named Luan could have owned 10 individual tables. From SQL Server 2005 onward, however, this model has changed so that Luan now owns a schema, and the 10 tables reside within the schema, meaning that Luan implicitly owns the 10 tables.

This abstraction simplifies changing the ownership of database objects; in this example, to change the owner of the 10 tables from Luan to Paul, you need to change the ownership of a single artifact (the schema) as opposed to changing the ownership of all 10 tables.

Well-defined schemas can also help simplify the management of permissions, because you can grant a principal the permissions on a schema, as opposed to the individual objects within that schema. For example, assume that you have five sales-related tables: Orders, OrderLines, Invoices, InvoiceLines, and SpecialDeals. If you put all five tables within a single schema named Sales, you would then be able to assign the SELECT, UPDATE, and INSERT permissions on the Sales schema to a database role, which contains the sales team’s database users. Assigning permissions to an entire schema does not just affect tables, however. For example, granting SELECT on a schema also gives a user the permissions to run SELECT statements against all views within the schema. Granting the EXECUTE permission on a schema grants EXECUTE on all procedures and functions within the schema. For this reason, well-designed schemas will group tables by business rules, as opposed to technical joins.

Consider the WideWorldImporters database , specifically the Orders, OrderLines, People, and StockItems tables. Figure 4-1 is a partial database diagram of the WideWorldImporters database, which shows that these tables are physically joined with primary key and foreign key constraints. Even though the tables are physically joined, it would not be sensible to place the Orders or OrderLines tables in the same schema as the StockItems or People tables, as salespeople are unlikely to be authorized to see employee details or change the details of stock items. Instead, the only tables in the Sales schema should be tables that salespeople are authorized to view or maintain. Indeed, this aligns with the actual design of the WideWorldImporters database.
../images/395795_2_En_4_Chapter/395795_2_En_4_Fig1_HTML.jpg
Figure 4-1

Partial database diagram

Listing 4-1 demonstrates how to create a schema called Chapter 4 in the WideWorldImporters database. It then assigns the user Danni, SELECT permission to the schema.
USE WideWorldImporters
GO
CREATE SCHEMA Chapter4 ;
GO
GRANT SELECT ON SCHEMA::Chapter4 TO Danni ;
GO
Listing 4-1

Create a New Schema

To change a table’s schema post-creation, use the ALTER SCHEMA TRANSFER statement, as demonstrated in Listing 4-2. This script creates a table without specifying a schema. This means that it is automatically placed in the dbo schema. It is then moved to the Chapter4 schema.
USE WideWorldImporters
GO
CREATE TABLE ChangeSchema
(
ID int
) ;
GO
ALTER SCHEMA Chapter4 TRANSFER dbo.ChangeSchema ;
GO
Listing 4-2

Transfer an Object to a Different Schema

Ownership Chaining

SQL Server 2016 and SQL Server 2017 offer an implementation of row-level security, which will be discussed in the Row-Level Security section of this chapter. In previous versions of SQL Server, however, row-level security could be rather tricky to implement. The standard way to implement row-level security was to use views or procedures that limited the amount of data that was returned. Users can be granted permissions to the procedures and views, which form an abstraction layer, without granting the user permissions to the underlying tables.

This method works because of a concept called ownership chaining. When multiple objects are called sequentially by a query, SQL Server regards them as a chain. When you are chaining objects together, the permissions are evaluated differently, depending on what principal owns the schema(s) in which the objects reside.

For example, imagine that you have a view named View1 and the view is based on two tables: Table1 and Table2. If all three of these objects share the same owner, then when a SELECT statement is run against the view, the caller’s permissions on the view are evaluated, but their permissions on the underlying tables are not.

This means that if you want to grant UserB the SELECT permissions on specific rows within Table1, then you can create a view that stores a query that returns the rows that this user is permitted to see. At this point, the user can run a SELECT statement from the view, as opposed to the base table. As long as they have SELECT permission on the view and the view shares an owner with the base table(s), then their permissions on the underlying table are not evaluated, and the query succeeds. This is represented in Figure 4-2.
../images/395795_2_En_4_Chapter/395795_2_En_4_Fig2_HTML.jpg
Figure 4-2

Successful ownership chain

The ownership chain is broken in the event that one of the objects the view is based on does not have the same owner as the view. In this scenario, permissions on the underlying table are checked by SQL Server, and an error is returned if the user does not have appropriate permissions to the underlying table. This is illustrated in Figure 4-3.
../images/395795_2_En_4_Chapter/395795_2_En_4_Fig3_HTML.jpg
Figure 4-3

Broken ownership chain

Caution

It is important to note that ownership chains lead to DENY assignments being bypassed. This is because neither the GRANT nor DENY assignments of the user will be evaluated.

Impersonation

Impersonation refers to the practice of executing T-SQL statements or code modules under the context of a different security principal. This helps you to enforce the principal of least privilege by assigning fewer permissions to users but elevating those permissions at the point when a section of code is executed.

In SQL Server, impersonation can be implemented through the EXECUTE AS clause. The EXECUTE AS clause can be placed in the header of a stored procedure, function, or DML Trigger. EXECUTE AS can also be used during a session to change the security context. Table 4-1 details the context specifications that can be specified when using EXECUTE AS.
Table 4-1

EXECUTE AS Context Specifications

Usage

Context Specification

Session

• LOGIN

• USER

Procedures, Functions, and DML Triggers

• CALLER

• SELF

• OWNER

• USER

Database-level DDL Triggers

• CALLER

• SELF

• USER

Server-level DDL Triggers

• CALLER

• SELF

• LOGIN

Queues

• CALLER

• SELF

• USER

Table 4-2 explains the usage of each of the context specifications.
Table 4-2

Context Specification Usage

Context Specification

Description

CALLER

The code will execute under the original context. This is the default behavior for all modules, except queues.

SELF

The code will execute under the context of the principal that created, or last altered, the module.

OWNER

The code will execute under the context of the principal that owns the module or the schema in which the module resides.

USER

The code will execute under the context of a specific database user.

LOGIN

The code will run under the context of a specific Login.

The script in Listing 4-3 demonstrates the EXECUTE AS functionality by using a system function named SUSER_SNAME(). This function returns the name of a Login from a SID that is passed a parameter. If no parameter is passed, then it will return the name of the Login of the current security context.
--Execute under current security context
SELECT SUSER_SNAME() ;
--Switch to the context of Danni
EXECUTE AS USER = 'Danni' ;
--Execute under Danni's security context
SELECT SUSER_NAME() ;
Listing 4-3

Change Security Context

The results of this query are shown in Figure 4-4. As you can see, the first query ran under the context of my Login. After using the EXECUTE AS statement, however, the security context changed to Danni.
../images/395795_2_En_4_Chapter/395795_2_En_4_Fig4_HTML.jpg
Figure 4-4

Results of change security context query

To revert back to the original security context, the code must use the REVERT statement. If no REVERT statement is supplied in the code, then the code will continue to run under the modified context until the end of the Session, or code module.

Caution

The user that creates the code module that contains the EXECUTE AS clause or the user executing the ad-hoc SQL within a session, using the EXECUTE AS clause, must have the IMPERSONATE permission on the security context that the code will run under.

Row-Level Security

In newer versions of SQL Server, specifically SQL Server 2016 and above, row-level security (RLS) allows DBAs to simplify the management of fine-grain security by providing an out-of-the-box technology. In many cases, where security would be implemented in the middle tier and the application would connect to SQL Server using a single Login; RLS can also assist with improving architectural principals by pushing logic and security to the back end.

RLS is implemented through a Security Policy and Security Predicates. The following sections will introduce each of these concepts before demonstrating how the technology could be implemented in the WideWorldImporters database.

Security Predicates

A Security Predicate is a function that is applied to a result set to determine what rows can be returned, or modified, by the user accessing the data. The functions are inline table-valued functions, which must be created by the DBA. There are two types of Security Predicate that can be implemented: Filter Predicates and Block Predicates.

Filter Predicates filter the rows that are returned to a user when they query a table or view. This type of predicate is silent, meaning that the user or application is given no indication that rows have been filtered from the result set. Filter Predicates affect SELECT, UPDATE, and DELETE statements.

Unlike Filter Predicates, Block Predicates will return an error if they are violated. This type of predicate can be used to explicitly block INSERT, UPDATE, and DELETE statements, which would violate the predicate. For UPDATE statements, Block Predicates can be defined as BEFORE or AFTER. When defined as BEFORE, then the predicate is applied based on the original value. When defined as AFTER, the predicate is applied based on the value of a tuple after the UPDATE statement has been applied. As you would expect, if the Predicate is for an INSERT statement, then AFTER is the only option, and if the Predicate is for a DELETE statement, BEFORE is the only option.

It is a good idea to create a new schema in which to place your Security Predicates. This is because any user should be able to access the functions, and placing the RLS objects in a separate schema makes it easy to manage these permissions.

When creating the function, it is also a good idea to use SCHEMABINDING. This is because any function calls or joins to other tables can be made without additional permission configuration. If you do not use SCHEMABINDING, then SELECT or EXECUTE permissions will be required on the referenced objects by users calling the Security Predicate.

Note

If you use SCHEMABINDING, then it is not possible to alter the columns in the table or view that are referenced by the Security Predicate. This can make table modifications trickier but also prevent accidental changes.

Security Policies

A Security Policy binds the Security Predicate(s) to tables and views. It is the Security Policy that invokes the Security Predicate and specifies how the predicate should be used (filter, block before, block after).

A Security Policy can be created using the CREATE SECURITY POLICY statement. Table 4-3 details the arguments that can be specified when creating a Security Policy.
Table 4-3

CREATE SECURITY POLICY Arguments

Argument

Description

schema_name.security_policy_name

The name to be assigned to the Security Policy and the schema in which it should be created

ADD

Specifies if the predicate should be FILTER or BLOCK

PREDICATE

The two-part name of the Security Predicate

column_name | expression

The column name or expression that should be used as the input parameter for the Security Predicate

table_schema.table_name

The two-part name of the target table to which the Security Policy will be applied

block_DML_operations

If the ADD argument set to BLOCK, then the DML operations to block will be defined.

STATE

Specifies if the Security Policy will be enabled on creation

SCHEMABINDING

Specifies if Security Predicates that are bound to the Security Policy must be created with SCHEMABINDING

NOT FOR REPLICATE

Specifies that the security policy should not be executed when a replication agent modifies the target object

Implementing RLS

In this section, we will discuss how RLS can be implemented. Imagine that we have a requirement for managers to be able to view details within an employee’s table of the WideWorldImporters database. The challenge using traditional permission assignments, however, is that they should only be able to view the details of employees who report to them (either directly or indirectly).

We can achieve this using RLS, first by creating a Security Predicate and a Security Policy. The Security Predicate will define which rows are accessible by a user based on the manager column of the table.

Tip

The Manager column of the Application.Employees table uses the HierarchyID data type. HierarchyID is a complex data type, implemented through CLR, that was first implemented in SQL Server 2008. It exposes a variety of methods, which can be used to assess a row’s level of the hierarchy. A full discussion around the HierarchyID data type can be found in the Apress title SQL Server Advanced Data Types www.apress.com/us/book/9781484239001 .

Before we begin, we will create a table in the WideWorldImporters database, which we will use in the following examples. The table details an employee hierarchy, using the HIERARCHYID data type. The table is loosely based on the AdventureWorks HumanResources.Employees table. The table can be created and populated using the script in Listing 4-4.
USE WideWorldImporters
GO
CREATE TABLE Application.Employees
(
 EmployeeID    INT           NOT NULL    PRIMARY KEY,
 NINumber      NVARCHAR(15)  NOT NULL,
 LoginName     NVARCHAR(256) NOT NULL,
 Manager       HIERARCHYID   NULL,
 Title         NVARCHAR(256) NOT NULL
) ;
INSERT INTO Application.Employees
VALUES(1, 295847284, 'WideWorldImportersken0', '/', 'Chief Executive Officer'),
(2 ,245797967 ,'WideWorldImporters erri0', '/1/', 'Vice President of Engineering'),
(3 ,509647174 ,'WideWorldImporters oberto0', '/1/1/', 'Engineering Manager'),
(4 ,112457891 ,'WideWorldImporters ob0',    '/1/2/', 'Senior Tool Designer'),
(5 ,695256908 ,'WideWorldImportersgail0', '/1/1/1/', 'Design Engineer'),
(6 ,998320692 ,'WideWorldImportersjossef0', '/1/1/2/', 'Design Engineer'),
(7 ,134969118 ,'WideWorldImportersdylan0', '/1/3/', 'Research and Development Manager'),
(8 ,811994146 ,'WideWorldImportersdiane1', '/1/3/1/', 'Research and Development Engineer'),
(9 ,658797903 ,'WideWorldImportersgigi0', '/1/3/2/', 'Research and Development Engineer'),
(10 ,879342154 ,'WideWorldImportersmichael6', '/1/3/3/', 'Research and Development Manager'),
(12 ,480168528 ,'WideWorldImporters hierry0', '/1/2/1/', 'Tool Designer'),
(13 ,486228782 ,'WideWorldImportersjanice0', '/1/2/2/', 'Tool Designer'),
(14 ,42487730 ,'WideWorldImportersmichael8', '/1/2/1/', 'Senior Design Engineer'),
(15 ,56920285 ,'WideWorldImporterssharon0', '/1/2/1/1/', 'Design Engineer'),
(17 ,253022876 ,'WideWorldImporterskevin0', '/2/', 'Head of Marketing'),
(18 ,222969461 ,'WideWorldImportersjohn5', '/2/1/', 'Marketing Specialist'),
(19 ,52541318 ,'WideWorldImportersmary2', '/2/1/1/', 'Marketing Assistant'),
(20 ,323403273 ,'WideWorldImporterswanida0', '/2/1/2/', 'Marketing Assistant'),
(21 ,243322160 ,'WideWorldImporters erry0', '/2/2/', 'Marketing Specialist'),
(22 ,95958330 ,'WideWorldImporterssariya0', '/2/3/', 'Marketing Specialist'),
(23 ,767955365 ,'WideWorldImportersmary0', '/2/4/', 'Marketing Specialist'),
(24 ,72636981 ,'WideWorldImportersjill0', '/2/5/', 'Marketing Specialist'),
(26 ,277173473 ,'WideWorldImporterspeter0', '/3/', 'Production Control Manager') ;
Listing 4-4

Create An Application.Employees Table

Our first step will be to create the Security Predicate. Listing 4-5 details the code that could be used to write such a predicate function. Notice that before creating the predicate function, the script creates a new schema, called Security, in which the function will reside. This is in line with the best practices described in the Security Predicates section.
USE WideWorldImporters
GO
CREATE SCHEMA Security ;
GO
CREATE FUNCTION Security.fn_securitypredicate(@Manager HIERARCHYID)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    FROM Application.Employees e1
    WHERE @Manager.IsDescendantOf(Manager) = 1
        AND LoginName = 'WideWorldImporters' + USER_NAME() ;
GO
Listing 4-5

Create a Security Predicate

We will now need to create the Security Policy. This is demonstrated in Listing 4-6.
CREATE SECURITY POLICY Security.EmployeeSecurityPolicy
ADD FILTER PREDICATE Security.fn_securitypredicate(Manager) ON Application.Employees
WITH (STATE=ON, SCHEMABINDING=ON) ;
Listing 4-6

Create a Security Policy

If we were now to run the script in Listing 4-7, only employees who report to Terri would be returned.
USE WideWorldImporters
GO
CREATE USER terri0 WITHOUT LOGIN ;
ALTER ROLE db_datareader ADD MEMBER terri0 ;
GO
EXECUTE AS USER = 'terri0'
  SELECT * FROM Application.Employees ;
REVERT
Listing 4-7

Test the RLS

The results of this query are illustrated in Figure 4-5.
../images/395795_2_En_4_Chapter/395795_2_En_4_Fig5_HTML.jpg
Figure 4-5

RLS test results

Dynamic Data Masking

Dynamic Data Masking is a technology that was introduced in SQL Server 2016, which allows non-privileged users to see only a subset of an atomic value, stored within a cell in a table. For example, imagine a call center for a credit card company. The call center operatives are not authorized to see an entire credit card number, for data protection. They need to identify the customer, however, and one of the questions that they use for the security checks is the last four numbers of the credit card number.

In this scenario, dynamic data masking could be used on the credit card number column, so that all but the last four digits or the number are obfuscated. This can help improve application architecture, in some circumstances, by pushing code from the middle tier, to the back-end, which improves re-usability and reduces resource consumption in the middle tier. Of course, if you have multiple application servers, then you may want the overhead to remain in the middle tier.

Table 4-4 details the dynamic masking functions that are available in SQL Server 2016.
Table 4-4

Dynamic Data-Masking Functions

Function

Supported Data Types

Description

default

char, nchar, varchar, nvarchar, text, ntext, bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real, date, datetime2, datetime, datetimeoffset, smalldatetime, time, binary, varbinary, image

Fully masks a value. The type of masking depends on the data type of the value

partial

char, nchar, varchar, nvarchar

Accepts a prefix, a masking value, and a suffix

email

char, nchar, varchar, nvarchar

Reveals only the first letter of the e-mail address, the @ symbol, and the domain suffix

random

bigint, decimal, int, numeric, smallint, smallmoney, tinyint, float, real

Replaces a value with a random value, from within a specified range

Before we get started with Dynamic Data Masking, let’s create and populate a table that we will use in the following examples. The table contains credit card details and is loosely based on the Sales.CreditCard table from the AdventureWorks database. The table can be created using the script in Listing 4-8.
USE WideWorldImporters
GO
CREATE TABLE Application.CreditCards
(
      CardID     INT           NOT NULL,
      CardType   NVARCHAR(50)  NOT NULL,
      CardNumber NVARCHAR(20)  NOT NULL,
      ExpMonth   INT NOT NULL,
      ExpYear    INT NOT NULL,
      CustomerID INT NOT NULL
) ;
INSERT INTO Application.CreditCards
VALUES(1, 'SuperiorCard', '33332664695310', 10, 20, 991),
(2, 'Distinguish', '55552127249722', 11, 21, 156),
(3, 'ColonialVoice', '77778344838353', 10, 21, 1),
(4, 'ColonialVoice', '77774915718248', 12, 22, 920),
(5, 'Vista', '11114404600042', 12, 22, 949),
(6, 'Distinguish', '55557132036181', 12, 22, 912),
(7, 'Distinguish', '55553635401028', 10, 19, 65),
(8, 'SuperiorCard', '33336081193101', 10, 19, 69),
(9, 'Distinguish', '55553465625901', 12, 19, 846),
(10, 'SuperiorCard', '33332126386493', 12, 19, 495),
(11, 'SuperiorCard', '33335352517363', 11, 19, 6),
(12, 'SuperiorCard', '33334316194519', 11, 18, 79),
(13, 'Vista', '11119775847802', 10, 18, 60),
(14, 'Distinguish', '55553287727410', 12, 18, 60),
(15, 'SuperiorCard', '33336866065599', 11, 20, 817),
(16, 'Vista', '11111985451507', 10, 22, 22),
(17, 'ColonialVoice', '77771220960729', 12, 22, 929),
(18, 'ColonialVoice', '77773971683137', 10, 20, 473),
(19, 'ColonialVoice', '77779803886862', 20, 19, 505),
(20, 'SuperiorCard', '33332150058339', 10, 21, 436) ;
Listing 4-8

Create And Populate The Application.CreditCards Table

Dynamic Data Masking can be implemented by using the MASKED WITH syntax in either a CREATE TABLE or ALTER COLUMN statement. For example, the statement in Listing 4-9 will add a mask to the Application.CreditCards table in the WideWorldImporters database so that users will only see the last four digits of credit card numbers, when the CardNumber column is queried.
USE WideWorldImporters
GO
ALTER TABLE Application.CreditCards
ALTER COLUMN CardNumber ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');
Listing 4-9

Add a Data Mask

So, let us take a look at Dynamic Data Masking in action. Let us assume that the user terri0 has SELECT privileges to the Application.CreditCards and runs the query in Listing 4-10.
EXECUTE AS USER = 'terri0' ;
  SELECT
       CardID
      ,CardType
      ,CardNumber
  FROM Application.CreditCards ;
REVERT
Listing 4-10

Query the Sales.CreditCard Table

The results of this query are shown in Figure 4-6.
../images/395795_2_En_4_Chapter/395795_2_En_4_Fig6_HTML.jpg
Figure 4-6

Results of masked query

In order to reveal the full value, users must be granted the UNMASK permission. For example, imagine that the user terri0 was granted the UNMASK permission, as demonstrated in Listing 4-11. The user will now be able to see the whole value of the credit card number.
GRANT UNMASK TO terri0 ;
Listing 4-11

Grant the UNMASK Permission

Summary

SQL Server provides a rich suite of functionality for assisting in the management of data-level security. Schemas provide a namespace for objects and, when organized by business area (as opposed to technical relationship), can be used to simplify the administration of security, as it allows it you to assign permissions based on business role.

Each new version of SQL Server introduces new security features, and SQL Server 2016 is no exception. Row Level Security (RLS) introduces the ability to restrict the rows within a table, based on a user’s security attributes, such as user name or session context.

Dynamic data masking allows non-privileged users to see a partially obfuscated value, instead of the full value within a column. For example, a call center operative can see just the last four digits of a customer’s credit card number, as opposed to the full number.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset