Contextual View

If a tree falls in a forest and no one is around to hear it, does it make a sound?

Problem statement. Filtering data based on our needs is a fundamental requirement in many applications. We rarely need to show all the data. Filtering is done by applying logical conditions on top of the data either on the application side (not recommended) or as part of SQL while retrieving the data. Filter logic is applied based on a particular task and often based on system and user interaction. For example, if a user wants data for a given year that (s)he enters into the application or a system requests logs for the last minute we apply correspondent filters into the query. In some cases, we also need extra filters that tied to the context, for example, user, application, date range, configuration etc. Those filters are then applied in addition to user/system conditions.

05 contextual views 01

Here I show three examples of queries against three views. Highlighted red are conditions that are requested by the applications, where the remaining of the condition depends on the context of the request. Queries for the first example would look like

SELECT *
FROM   VIEW
WHERE  Year = 2018 AND
       LastUpdateDateTime BETWEEN 
           DATEADD(day,DATEDIFF(day,0,GETDATE()),0) AND 
           DATEADD(second,-1,DATEADD(day,DATEDIFF(day,0,GETUTCDATE())+1,0))

One common feature of these examples is that they all depend on some kind of anchor which defined as part session/transaction: current date, a user who made the request, application who made the request.

The problem is not that we cannot add these conditions. The problems are that

  • we may need to duplicate it in more than one instance, hence violating the DRY principle.
  • it is error-prone as the main view may return data for a larger result set that is required, so if somebody misses it or adds incorrect condition the result will be incorrect

In this post, we will review how can we hide the implementation of the context behind the view. I have described a more general pattern of abstraction here.

Contextual View. Contextual views return data that are not deterministic and depends on another state/anchor/context. So depending on implementation, the same view can return different data from user to user, from day to day, from database to database even if the underlined data has not been modified. That is the main premise. Filter conditions depend on the state of the session.

Ideally, I would like to see queries above as following with an assumption that data has not been updated between request.

05 contextual views 02

This may be a bit counter-intuitive, however, I think, Oracle, for example, may be using similar, if not the same, technique to provide access to the schema specific data dictionaries.

Implementation is quite easy. All we need to do it to apply the filter logic that is tied to the state (using one of the contextual function, see the section below for full list) of the session. The rest is limited by the imagination of the developer.

It is important to mention that SQL Server has a powerful implementation of this pattern on engine level using  Row-Level Security, which could be used to achieve similar results.

Demo. I have used this technique to hide sensitive data from the requester based on his/her branch of the organization (s)he worked. Higher the position, more data they can see. Below is a simple implementation of this pattern. First thing first we are going to setup table that represents organization structure, for simplicity I will skip full implementation of the entity and keep all the entities with bare minimum information.

--DROP TABLE BusinessUnit
CREATE TABLE BusinessUnit
	(BusinessUnitId INT IDENTITY (1,1) NOT NULL,
	BusinessUnitName VARCHAR(255) NOT NULL,
	ParentBusinessUnitId INT NULL,
	CONSTRAINT PK_BusinessUnit PRIMARY KEY CLUSTERED (BusinessUnitId),
	CONSTRAINT PK_BusinessUnit_REF_BusinessUnit 
		FOREIGN KEY (ParentBusinessUnitId) 
			REFERENCES BusinessUnit(BusinessUnitId),
	CONSTRAINT UK_BusinessUnit UNIQUE (BusinessUnitName) );

CREATE FUNCTION fn_BusinessUnit_GetBusinessUnitId
	(@BusinessUnitName VARCHAR(255)) 
RETURNS INT 
AS
BEGIN
	RETURN (
		SELECT	BusinessUnitId
		FROM	BusinessUnit 
		WHERE	BusinessUnitName = @BusinessUnitName);
END;

INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId)
VALUES ('Corporate', NULL);
INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId)
VALUES ('Midwest', dbo.fn_BusinessUnit_GetBusinessUnitId ('Corporate'));
INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId)
VALUES ('East', dbo.fn_BusinessUnit_GetBusinessUnitId ('Corporate'));
INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId)
VALUES ('Illinoice', dbo.fn_BusinessUnit_GetBusinessUnitId ('Midwest'));
INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId)
VALUES ('Indiana', dbo.fn_BusinessUnit_GetBusinessUnitId ('Midwest'));


SELECT	*
FROM	BusinessUnit

05 contextual views 03

The next let’s create a table with users. Each user belongs to a single and only single business unit. I will also provide a database username associated with the user. In this case it is SQL user, however, if you have AD (or any other user management service) setup then this could be AD account.

--DROP TABLE ApplicationUser
CREATE TABLE ApplicationUser(
	ApplicationUserId INT IDENTITY (1,1) NOT NULL,
	FirstName NVARCHAR(255) NOT NULL,
	LastName NVARCHAR(255) NULL,
	UserName VARCHAR(50) NOT NULL,
	BusinessUnitId INT NOT NULL,
	CONSTRAINT PK_ApplicationUser PRIMARY KEY (ApplicationUserId),
	CONSTRAINT PK_ApplicationUser_REF_BusinessUnit 
		FOREIGN KEY (BusinessUnitId) 
			REFERENCES BusinessUnit(BusinessUnitId),
	CONSTRAINT UK_ApplicationUser UNIQUE (UserName)
);

INSERT INTO ApplicationUser (
	FirstName, LastName, UserName, BusinessUnitId)
VALUES
	('Jane', 'Doe', 'jdoe01', dbo.fn_BusinessUnit_GetBusinessUnitId ('Corporate')),
	('John', 'Doe', 'jdoe02', dbo.fn_BusinessUnit_GetBusinessUnitId ('Midwest')),
	('John', 'Roe', 'jroe01', dbo.fn_BusinessUnit_GetBusinessUnitId ('Midwest')),
	('Baby', 'Doe', 'bdoe01', dbo.fn_BusinessUnit_GetBusinessUnitId ('Illinoice')),
	('Baby', 'Roe', 'broe01', dbo.fn_BusinessUnit_GetBusinessUnitId ('Illinoice'))

SELECT	*
FROM	ApplicationUser;

05 contextual views 04
Now we have all the information to create our contextual view. Let’s say our goal is to provide access to the application user data, however, each user can only see the data either on the same node of organization hierarchy or below, but not above or sibling/peer branches. In order to do so using the contextual views pattern, we can create a view.

CREATE	VIEW vwUserApplicationUser
AS
WITH cte_BusinessUnit as(
	SELECT	BU.BusinessUnitId, BU.BusinessUnitName, 0 HierarchyLevel
	FROM	BusinessUnit BU
	WHERE	EXISTS (
			SELECT	*
			FROM	ApplicationUser AU
			WHERE	AU.BusinessUnitId = BU.BusinessUnitId AND
				AU.UserName = SUSER_SNAME())
	UNION ALL
	SELECT	BU.BusinessUnitId, BU.BusinessUnitName, CBU.HierarchyLevel + 1 HierarchyLevel
	FROM	BusinessUnit BU
			INNER JOIN cte_BusinessUnit CBU
				ON CBU.BusinessUnitId = BU.ParentBusinessUnitId)
SELECT	AU.FirstName, AU.LastName, AU.UserName, AU.BusinessUnitId, BU.BusinessUnitName, BU.HierarchyLevel
FROM	ApplicationUser AU
		INNER JOIN cte_BusinessUnit BU
			ON	BU.BusinessUnitId = AU.BusinessUnitId
WHERE	BU.HierarchyLevel > 0 OR 
	AU.UserName = SUSER_SNAME()

I am using SUSER_SNAME build-in function to anchor the view result to the requester. in order to test this view, let’s create a user for each level of the hierarchy and execute the view.

USE [master]
CREATE LOGIN [jdoe01] WITH PASSWORD=N'pwd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN [jdoe02] WITH PASSWORD=N'pwd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN [bdoe01] WITH PASSWORD=N'pwd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [BLOG]
CREATE USER [jdoe01] FOR LOGIN [jdoe01] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [jdoe02] FOR LOGIN [jdoe02] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [bdoe01] FOR LOGIN [bdoe01] WITH DEFAULT_SCHEMA=[dbo]

GRANT SELECT ON vwUserApplicationUser TO [jdoe01]
GRANT SELECT ON vwUserApplicationUser TO [jdoe02]
GRANT SELECT ON vwUserApplicationUser TO [bdoe01]

EXECUTE AS LOGIN = 'jdoe01'; 
select * from vwUserApplicationUser
REVERT;  
EXECUTE AS LOGIN = 'jdoe02'; 
select * from vwUserApplicationUser
REVERT;  
EXECUTE AS LOGIN = 'bdoe01'; 
select * from vwUserApplicationUser
REVERT;

05 contextual views 05

Notice that the view and filter (or absence of the filter condition) is the same, however, it returns different result depending who is connected to the database – this view is requester aware. We can use the same technique to filter data in other tables that should implement requester aware logic.

Similar way we can use other functions to make views aware of the current date using GET_UTCDATE(), the current application using APP_NAME or other scenarios

Context-specific functions. This post is mostly about SQL Server, so all the example here are based on the t-SQL syntax and the SQL Server built-in functions. SQL Server provides a variety of built-in functions out of the box that provides information about configuration, environment, and session.

Configuration functions can be used in cases when the same code base deployed to multiple instances with different configurations and behavior of the code will depend on the host database setting. Here are the functions that can potentially be used

  • @@LANGUAGE
  • @@OPTIONS
  • @@REMSERVER
  • @@SERVERNAME
  • @@SERVICENAME
  • @@SPID
  • @@Version – need extra parsing.

I have not had a case of using Cryptographic functions, however, theoretically, all of then can be used as well.

Date and Time functions, that do not accept any parameters are one of the most commonly used functions set to enable time aware objects

  • @@DATEFIRST
  • CURRENT_TIMESTAMP
  • GETDATE
  • GETUTCDATE
  • SYSDATETIME
  • SYSDATETIMEOFFSET
  • SYSUTCDATETIME

Another large category is Metadata functions, specifically.

  • APP_NAME
  • DB_ID
  • DB_NAME
  • ORIGINAL_DB_NAME
  • SERVERPROPERTY – need to pass specific parameter name that is part of the logic

Security Functions:

  • CURRENT_USER
  • HAS_DBACCESS
  • HAS_PERMS_BY_NAME
  • IS_MEMBER
  • IS_ROLEMEMBER
  • IS_SRVROLEMEMBER
  • LOGINPROPERTY
  • ORIGINAL_LOGIN
  • SESSION_USER
  • SESSIONPROPERTY – expects an option
  • SUSER_ID
  • SUSER_NAME
  • SUSER_SID
  • SUSER_SNAME
  • SYSTEM_USER
  • USER
  • USER_ID
  • USER_NAME

System functions.

  • CONNECTIONPROPERTY – expects an option
  • CONTEXT_INFO
  • HOST_ID
  • HOST_NAME

We can technically also use PUBLISHINGSERVERNAME replication function.

Special case. The implementation possibilities are limited by available built-in functions. I had once a requirement to build a query that depended on the global configuration that was set by the administrator. This setting was driven by the business team and did not have specific logic. I have used the configuration service instead of built-in functions in conjunction with this pattern to change the logic of view filters.

Summary. In this post, we have reviewed the concept of context-aware views and provided a small demo of requester aware view.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: