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.
Create a New Schema
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.
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.
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 |
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. |
Change Security Context
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).
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 .
Create An Application.Employees Table
Create a Security Predicate
Create a Security Policy
Test the RLS
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.
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 |
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 |
Create And Populate The Application.CreditCards Table
Add a Data Mask
Query the Sales.CreditCard Table
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.