Schemas

Schemas provide a logical namespace for database objects. They also 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 ten individual tables. From SQL Server 2005 onward, however, this model has changed so that Luan now owns a schema, and the ten tables reside within the schema—meaning that Luan implicitly owns the ten tables.

This abstraction simplifies changing the ownership of database objects; in this example, to change the owner of the ten 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 ten 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: SalesOrdersHeader, SalesOrderDetails, SalesPerson, Stores, and Customers. 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 group tables by business rules, as opposed to technical joins.

Consider the AdventureWorks2016 database, specifically the SalesOrderHeader, SalesOrderDetail, SalesPerson, Employee, and Person tables. Figure 4-1 is a partial database diagram of the AdventureWorks2016 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 SalesOrderHeader or SalesOrderDetails tables in the same schema as the Employee or Person tables, because salespeople are unlikely to be authorized to see employee information. Instead, the only tables in the Sales schema should be the SalesOrderHeader, SalesOrderDetail, and SalesPerson tables. Indeed, this aligns with the actual design of the AdventureWorks2016 database.

Figure 4-1. Partial database diagram

Listing 4-1 demonstrates how to create a schema called Chapter4 in the AdventureWorks2016 database. It then assigns the user Danni, SELECT permission to the schema.

Listing 4-1. Create a New Schema

USE AdventureWorks2016
GO

CREATE SCHEMA Chapter4 ;
GO

GRANT SELECT ON SCHEMA::Chapter4 TO Danni ;
GO

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, which means that it is automatically placed in the dbo schema. It is then moved to the Chapter4 schema.

Listing 4-2. Transfer an Object to a Different Schema

USE AdventureWorks2016
GO

CREATE TABLE ChangeSchema
(
ID int
) ;
GO

ALTER SCHEMA Chapter4 TRANSFER dbo.ChangeSchema ;
GO

Ownership Chaining

SQL Server 2016 offers an implementation of row-level security, which is 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, which 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 the principal that owns the schema(s) in which the objects reside.

For example, imagine that you have a view named View1, which 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 he has SELECT permission on the view and the view shares an owner with the base table(s), then his permissions on the underlying table are not evaluated and the query succeeds. This is represented in Figure 4-2.

Figure 4-2. Successful ownership chain

The ownership chain is broken in the event that one of the objects that 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.

Figure 4-3. Broken ownership chain

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 describes the context specifications that can be specified when using EXECUTE AS.

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

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 returns the name of the login of the current security context.

Listing 4-3. Change 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() ;

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.

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 continues to run under the modified context until the end of the session or code module.

Row-Level Security

Implemented in SQL Server 2016, 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, security is implemented in the middle tier and the application connects 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 introduce each of these concepts before demonstrating how the technology could be implemented in the AdventureWorks2016 database.

Implementing RLS

This section discusses how RLS can be implemented. Imagine that you are required to allow managers to view information in the HumanResources.Employee table of the AdventureWorks2016 database. The challenge using traditional permission assignments, however, is that they should only be able to view the information on employees who report to them (either directly or indirectly) .

You can achieve this using RLS, first by creating a security predicate and a security policy. The security predicate defines which rows are accessible by a user based on the OrganizationNode column of the table.

Tip

The OrganizationNode column of the HumanResources.Employee table uses the HierarchyID data type. HierarchyID is a complex data type implemented through CLR; it was first implemented in SQL Server 2008. It exposes a variety of methods to assess a row’s level of the hierarchy. A full method reference for the HierarchyID data type can be found at https://msdn.microsoft.com/en-us/library/bb677193.aspx .

The first step is to create the security predicate. Listing 4-4 is code 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 resides. This is in line with the best practices described in the “Security Predicates” section.

Listing 4-4. Create a Security Predicate

USE AdventureWork2016
GO

CREATE SCHEMA Security ;  
GO  

ALTER FUNCTION Security.fn_securitypredicate(@OrganizationNode HIERARCHYID)  
    RETURNS TABLE  
WITH SCHEMABINDING                                                                                                                    
AS  
RETURN SELECT 1 AS fn_securitypredicate_result   
FROM HumanResources.Employee e1
WHERE @OrganizationNode.IsDescendantOf(OrganizationNode) = 1  
AND LoginID = 'adventure-works' + SUSER_SNAME() ;
GO

You now need to create the security policy. This is demonstrated in Listing 4-5.

Listing 4-5. Create a Security Policy

CREATE SECURITY POLICY Security.EmployeeSecurityPolicy
ADD FILTER PREDICATE Security.fn_securitypredicate(OrganizationNode) ON HumanResources.Employee
WITH (STATE=ON, SCHEMABINDING=ON) ;

If you were now to run the script in Listing 4-6, only employees who report to Roberto are returned.

Listing 4-6. Test the RLS

EXECUTE AS USER = 'Roberto0'
  SELECT * FROM HumanResources.Employee ;
REVERT

The results of this query are illustrated in Figure 4-5.

Figure 4-5. RLS rest results

Dynamic Data Masking

Dynamic data maskingis a technology that was introduced in SQL Server 2016 that allows non-privileged users to see only a subset of an atomic value stored within a tuple. For example, imagine a call center for a credit card company. For data protection, the call center operatives are not authorized to see an entire credit card number. They need to identify the customer, however, and one of the questions used 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 are obfuscated. This can help improve application architecture by pushing code from the middle tier to the back end, which improves reusability and reduces resource consumption in the middle tier.

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

Dynamic data masking can be implemented by using the MASKED WITH syntax in either a CREATE TABLE or an ALTER COLUMN statement. For example, the statement in Listing 4-7 adds a mask to the Sales.CreditCard table in the AdventureWorks2016 database so that users only see the last four digits of credit card numbers when the CardNumber column is queried.

Listing 4-7. Add a Data Mask

USE AdventureWorks2016
GO

ALTER TABLE Sales.CreditCard
ALTER COLUMN CardNumber ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');

Let’s take a look at dynamic data masking in action. Let’s assume that the user brian3 has SELECT privileges to the Sales.CreditCard and runs the query shown in Listing 4-8.

Listing 4-8. Query the Sales.CreditCard Table

EXECUTE AS USER = 'brian3' ;
SELECT TOP 10
       CreditCardID
      ,CardType
      ,CardNumber
      ,ExpMonth
      ,ExpYear
      ,ModifiedDate
  FROM Sales.CreditCard ;
REVERT

The results of this query are shown in Figure 4-6.

Figure 4-6. Results of masked query

To reveal the full value, users must be granted the UNMASK permission. For example, imagine that user brian3 was granted the UNMASK permission, as demonstrated in Listing 4-9. The user is now able to see the whole value of the credit card number.

Listing 4-9. Grant the UNMASK Permission

GRANT UNMASK TO brian3 ;

Summary

SQL Server provides a rich suite of functionality for assisting in the management of data level security. Schemas provide a namespace for objects. When organized by business area (as opposed to technical relationship), they can simplify the administration of security, because it allows 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 username or session context.

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